To line item or not to line Item? That is the question. (Improving data loads times for Infocubes)

[miguel_peredozurcher]

When we load data to a cube, it can happen that it takes a long time in the STEP generating SIDs.

In this example you see that it took 20 mins to generate the SIDs, and the total tine was 21 Mins 23 secs.

If you look in SM66 while the load is running and you refresh often enough, you will see something like this:

The system is reading from NRIV, meaning the number range intervals, since it needs to create new SIDs for Dimensions, and probably those dimensions have a lot of different values.

Here design plays also an important role, meaning that characteristics that have a lot of different values should be modeled as line item dimensions.

But putting characteristics as line item dimension has also other not that well known side effects described in this note:  1517248 - F4 help uses M mode instead of D mode for line item characteristic

For example we should put 0material as a line item dimension following design guidelines*, but because of note 1517248 we can’t do that. If we put it as a Line Item dimension in the search help, the users will see the complete list of materials, instead of only the ones that are sold, and if you put in a non-line item dimension then the performance of loading to the cube can be affected.

Then the questions pop ups: To line item or not to line Item?

* 0material is just an example this depends on a lot of factors, most of the cases it will ok to have 0material not as a line item dimension; but if you want to revisit BW 101 design, you can check this note: 1461926 - FAQ: BW report SAP_INFOCUBE_DESIGNS )

If you decide to go for a Non-line item design, the solution to improve the times it takes to generate new SIDs (thus the loading time) is described in note:

857998 - Number range buffering for DIM IDs and SIDs

But for that the system needs to be open; and for production that is usually not the case.

Luckily SAP introduced this note:

1947601 - SP33:'RSDG_NUMBR_BUFFER_RESET' for setting buffer of the generated BW number range objects

this allows turning on the buffer even if a system is closed.

One heads up when buffering these tables is mentioned in case “C_2” of this Note

1331403 - SIDs, Numberranges and BW Infoobjects

So don’t try to be a hero setting huge values, start with  the recommended values.

Any comments or similar experiences ?