How to improve runtime of data loading to cube
In SAP BI projects we may face issues with the run time of loading data to cubes and which may considerably affect the SLA. In order to avoid that this document will help to find a performance optimization technique to successfully reduce the runtime of loading to cubes.
Execute a standard ABAP program SAP_INFOCUBE_DESIGNS in se38 and check the output of the report
Above is an example of the output of the standard report where can see no of rows of fact table of a cube,the no. of rows of each of the dimensions of the cube and the ratio of the dimeniosn to fact table size .
In the above example we can see 2 of the dimensions are marked in red ,this signifies the bad dimensions. In this report we will consider any dimension having dimension to fact table size ratio above 20 percentage as a bad dimension and it will be highlighted in red.
STEP 2:Check the DTP loading step where we have the step ” Conversion of characteristic values to SIDs” .In our fact table we have the dimension ids and in dimension tables we find the SIDS of characteristic values. It can be observed in the history of the data loading of cube how much time is spent on this particular step and if its very alarming then with the help of the the program output we can check which of the dimensions are marked in red i.e. exceeds the optimized level of ration percentage which is 20.
This particular step has the conversion of char values to SIDs and if any of the cubes have bad dimensions reflected then we need to take care of it in order to improve the load performance.
STEP 3: Now we need to identify a way to improve the the load by improving this particular step of the load.
We need to execute a function module in se37 , RSD_CUBE_GET and give the input parameters as
Execute again and when the following window appears ,double click on E_T_DIME ,Check the number range object ID corresponding to the dimension of the cube marked in red in the report SAP_INFOCUBE_DESIGNS.
As per my example we can see below dimensions and its corresponding number range object id
STEP 4: Goto Transaction SNRO and in the input we need to put the Number range object IDs being selected in the above step.
After choosing the option Buffering to main memory we can set upto 1500 objects as per SAP recommendations but it will depend upon each and every scenario depending upon the system health and to check that basis help can be taken to choose what exact figure to apply in main memory.
The purpose of using the buffers is to replace with the access to hit the database table SID/DIM ID table to look up the values which in case of huge number of records can increase the run gradually.Using buffer will reduce the time considerably.