How to improve runtime of data loading to cube

Introduction:

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.

Procedure:

STEP 1:

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.