How to do an infocube redesign

An infocubes performance can be increased by reducing the size of the dimension tables. This “how to” describes how to find out which cubes have large dimension tables and it provides steps on how to reduce the size of the dimension tables.

1.                  To determine which cubes have large dimension tables go to transaction SE38. Type in SAP_INFOCUBE_DESIGNS in the program name box and click on the execute icon.

2.                  You should then get an output that looks like the screen below.

 

3.                  The % in the last column indicates the % of that dimension table to the fact table. If the dimension table is too big, that row is usually highlighted in red.

4.                  In order to rectify this, you can do one of two things:

a.      If there are too many characteristics in that dimension, you can create more dimensions (if possible) and move some of the characteristics into the new dimension. This will split some of the data in the dimension table making it smaller.

b.     If there is a characteristic that has a lot of data in this particular cube, you can move it into a dimension on its own and mark the dimension as a line item dimension. This stores that characteristic value in the fact table as apposed to the dimension table and helps improve performance on the infocube.

For both of the above solutions you will need to empty the cube before making the change. You cannot move a characteristic or mark a dimension as line item in a cube which contains data. In order to do this without reloading data from the source system follow these steps:

1.                  First create a copy of the cube.

2.                  Then create a transformation from the original cube to the copy cube. Map the fields on a one-to-one basis.

3.                  Then create a DTP and load the copy cube with the data from the original cube.

4.                  You will now have a full copy of the data from the original cube.

5.                  Delete the data from the original cube.

6.                  Make the changes to the original cube. Move the characteristics from large dimensions into new dimensions and mark some of the dimensions as line item dimensions if necessary.

7.                  When you are done with your changes activate the original cube and create a transformation from the copy cube to the original cube.

8.                  Load the original cube from the copy cube.

9.                  Delete the copy cube.

10.              Run SAP_INFOCUBE_DESIGNS and check the improvement after your change. Repeat the above steps if necessary.