Reduce inventory query runtime from 0IC_C03 infocube

[Former Member]

We want to optimize the Material Stock infocube (0IC_C03) in order to have better query runtime, it uses non-cumulative key figures. Before the compression of the request of the infocube, the query runtime is around 10 min. Doing the compression of all the requests with marker update the query runtime is around 50 min.

The model we are using has a DSO before the infocube because we want to save the history at granular level.

The DSO has just one request of 20 million records updated as delta. The history goes from NOV 2008 to FEB 2010.

The infocube has several requests that are divided by periods (selection of different posting date) all the requests are updated as full:

01.NOV2008-31DIC2008

01.JAN2009-30MAR2009

01.APR2009-30JUN2009

01.JUL2009-30.SEP2009

01.OCT2009-31.DIC2009

01.JAN2010-28.FEB2010

The model doesnu2019t use 2LIS_03_BX, it uses just 2LIS_03_BF and 2LIS_03_UM, therefore we are compressing the infocube requests with marker update

The expected result would be that the query runtime be less than 10 min.

Regards.

[Former Member · Accepted solution]

Hi There,

Since you are not using 2LIS_03_BX, consider using just 2LIS_03_BF and 2LIS_03_UM in an new Infocube using cumulative (standard) key figures. We were able to mimic t-code MB5B with tracibility to material movement documents.

We also used an intermediate standard DSO for material documents. Check SAP notes, the 2LIS_03_BF extractor requires an an additional infoObject as 'Counter'

Features:

1) No quiet time required. Documents posted as created regardless of posting date.

2) Date selection must be from "begining of time" to inventory date.

3) Compression CAN be used.

4) Matches MB5B for validation!

5) Aggregates can be used!

Good luck!

John Hawk

[Former Member]

Thanks so far for your contributions.

The scenario we are using is just with extractors 2LIS_03_BF/2LIS_03_UM, we are not using 2LIS_03_BX.

I have two questions regarding just the compression of the requests:

1. With the previous explained scenario (NO 2LIS_03_BX extractor), is it necessary to compress the first request of the init delta with marker update ?

2. Or should the Infocube (0IC_C03) be compressed with marker update just for the subsequent requests ? That means compress with marker update from the 2nd. request

Regards,

Scardenas

[Former Member]

Hi,

Check out these points.......

2lis_03_bx(Initialization) --- one should load the records pertaining to the opening stock balance/or the stock present at the time of implementation. At this time we will make marker to update (uncheck 'no marker update') so that the value of current stock quantity is stored in the marker.

2lis_03_bf, 2lis_03_um (Initialization) -

---

when loading the historical movements (stock movements made previous to the time of implementing the cube) we must check marker update so that the marker should not be updated (because of these historical movements, only the stock balance / opening stock quantity has been updated; i.e. we have already loaded the present stock and the aggregation of previous/historical data accumulates to the present data).

2lis_03_bf, 2lis_03_um (Deltas) -

---

After every successful delta load, we should not check marker update (we should allow to update marker) so that the changes in the stock quantity should be updated in the marker value. The marker will be updated to those records which are compressed.

Regards,

Vishnu.

[maxilopez]

Hi,

I think that starting to use 2LIS_03_BX would help you a great deal as far as improving performance is concerned. Otherwise, even using compression, to calculate stocks at present time, a huge quantity of records historic has to be summarised.

In order to do so, you should:

1) Initialize markers at R/3 through MCNB transaction.

2) Setup statistical data through transactions OLI1BW and OLIZBW

3) Execute initialization InfoPackage of 2LIS_03_BX.

4) Compress with marker update (the option "No marker update" should NOT be marked).

5) Initialize delta of 2LIS_03_BF and 2LIS_03_UM with data transfer.

6) Compress without marker update (the option "No marker update" should be marked).

7) Initialize delta extraction jobs at LBWE through the option "Job control".

😎 Schedule periodic delta executions.

You can find a detailed how-to guide at:

http://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/f83be790-0201-0010-4fb0-98bd7c01e....

I hope it helps you.

Regards,

-

Maximiliano