In one of our SAP BW project there were many Info cubes which took a lot of time to load delta data into targets in production.
Here I am taking an example of Y_****22 Info cube.
In BI project Y_****22 Info cube took a lot of time for Data Loading. Data loading took most of the time in SID Generation step. It was due to the Info cube design.
How to check the ratio of fact and dimension table?
Go to SE38, execute program SAP_INFOCUBE_DESIGNS and search your cube.
Go to DB02 and check the distinct values of characteristics used in your dimension tables. In this case (Y_****22) the distinct values of two chars “Document Number” and “Approved Time” were very high as compare to other characteristics.
So I used these characteristics in Separate Line Item Dimension’s.
Line Item Dimensions: Master data SID tables directly connected to Fact table. It means instead of dimensional ID’s directly SID’s will connect to fact table. Performance will be improved in compare to earlier one.
Go to DB02 -> Space -> Segments -> Detailed Analysis -> Segment / Object
Add technical name of the dimension table in “Segment / Object”.
Here you can check the distinct value’s and you can easily found that which of the characteristics is increasing the size of your dimension table.
After this change I observe a performance got highly increased in term of data loading.
Attaching the screenshot of SAP BW environment before image and after image with same number of records. You will see the difference of time to load the data.
this load was taking around 15 to 20 mins daily to complete this load.
Now this load takes only 1-2 minutes of time to complete.
Outcome and Fruit’s:
Giving one more successful example.
SID Creation performance before redesigning Info cube:
SID Creation performance after redesigning the above same Info cube:
In the above example the info cube took hours to generate SID’s . After correct remodelling the same cube the same step was done in few minutes only.
Sample Case:
How to select dimensions as Line Item Dimension and others as simple dimensions?
It’s the case from Y_****21 cube.
The stats before redesigning:
The first dimension has fact to dimension table size ratio is more that 20%.
Go to DB02 and enter the red dimensioned table:
Here I found there are some characteristics which has high number of distinct values which increase the size of this dimension table.
So we made use of these characteristics in separate Line Item dimensions. It helps in decrease the size the actual dimension table and it will help in increase the data loading performance. Now this cube will not take time in SID generation step while data load.
We can also adjust the other dimensions after seeing the size of dimensions and characteristics distinct values. In this infocube I made the changes in the third dimension as well to decrease the size from 11% like adjusting characteristics in another dimension after seeing its size.
Now the data loading performance for this cube is highly increased and daily delta get completed in only few minutes.
Check the following screenshot:
Thank You.
I hope you like this blog and helpful.
Okumaya devam et...
Here I am taking an example of Y_****22 Info cube.
In BI project Y_****22 Info cube took a lot of time for Data Loading. Data loading took most of the time in SID Generation step. It was due to the Info cube design.
- If you can check in the following screenshot, the ratio of the fact table to dimension table for 2nd and 3rd dimension is more than 20% which was wrong. It means Info cube design has some problem.
How to check the ratio of fact and dimension table?
Go to SE38, execute program SAP_INFOCUBE_DESIGNS and search your cube.
- How to reduce the size of dimension table?
Go to DB02 and check the distinct values of characteristics used in your dimension tables. In this case (Y_****22) the distinct values of two chars “Document Number” and “Approved Time” were very high as compare to other characteristics.
So I used these characteristics in Separate Line Item Dimension’s.
Line Item Dimensions: Master data SID tables directly connected to Fact table. It means instead of dimensional ID’s directly SID’s will connect to fact table. Performance will be improved in compare to earlier one.
- How to check distinct values in DB02?
Go to DB02 -> Space -> Segments -> Detailed Analysis -> Segment / Object
Add technical name of the dimension table in “Segment / Object”.
Here you can check the distinct value’s and you can easily found that which of the characteristics is increasing the size of your dimension table.
After this change I observe a performance got highly increased in term of data loading.
Attaching the screenshot of SAP BW environment before image and after image with same number of records. You will see the difference of time to load the data.
this load was taking around 15 to 20 mins daily to complete this load.
Now this load takes only 1-2 minutes of time to complete.
Outcome and Fruit’s:
- It results in better performance of data loads and not gives much burden to SAP BW servers and SAP BASIS team.
- Now End Users get reporting data on time.
Giving one more successful example.
SID Creation performance before redesigning Info cube:
SID Creation performance after redesigning the above same Info cube:
In the above example the info cube took hours to generate SID’s . After correct remodelling the same cube the same step was done in few minutes only.
Sample Case:
How to select dimensions as Line Item Dimension and others as simple dimensions?
It’s the case from Y_****21 cube.
The stats before redesigning:
The first dimension has fact to dimension table size ratio is more that 20%.
Go to DB02 and enter the red dimensioned table:
Here I found there are some characteristics which has high number of distinct values which increase the size of this dimension table.
So we made use of these characteristics in separate Line Item dimensions. It helps in decrease the size the actual dimension table and it will help in increase the data loading performance. Now this cube will not take time in SID generation step while data load.
We can also adjust the other dimensions after seeing the size of dimensions and characteristics distinct values. In this infocube I made the changes in the third dimension as well to decrease the size from 11% like adjusting characteristics in another dimension after seeing its size.
Now the data loading performance for this cube is highly increased and daily delta get completed in only few minutes.
Check the following screenshot:
Thank You.
I hope you like this blog and helpful.
Okumaya devam et...