17 Sept 2008

Dimension Size Vs Fact Size

The current size of all dimensions can be monitored in relation to fact table by t-code se38 running report SAP_INFOCUBE_DESIGNS.Also,we can test the infocube design by RSRV tests.It gives out the dimension to fact ratio.

The ratio of a dimension should be less than 10% of the fact table.
In the report,
Dimension table looks like /BI[C/O]/D[xxx]
Fact table looks like /BI[C/0]/[E/F][xxx]
Use T-CODE LISTSCHEMA to show the different tables associated with a cube.
When a dimension grows very large in relation to the fact table, db optimizer can't choose efficient path to the data because the guideline of each dimension having less than 10 percent of the fact table's records has been violated.

The condition of having large data growth in a dimension is called degenerative dimension.To fix, move the characteristics to different dimensions. But can only be done when no data in the InfoCube.

Note : In case if you have requirement to include item level details in the cube, then may be the Dim to Fact size will obviously be more which you cant help it.But you can make the item charecterstic to be in a line item dimension in that case.Line item dimension is a dimension having only one charecterstic in it.In this case, Since there is only one charecterstic in the dimension, the fact table entry can directly link with the SID of the charecterstic without using any DIMid (Dimid in dimension table usually connects the SID of the charecterstic with the fact) .
Since link happens by ignoring dimension table ( not in real sense ) , this will have faster query performance.

No comments: