Concept of Inventory Management, Full data loading steps and cleaning process, and Data Testing of Inventory Data in SAP BW.
The Loading process for Inventory Data sources is slightly different from the other LO Cockpit data sources.
This slight difference results in incorrect stock values after repeated efforts spent in loading Inventory data.
Listed below are the steps that can be followed by developers while they are loading the Inventory data for the first time after the GO-LIVE of BI system or by the support guys who have issues with regards to incorrect stock values. The Steps deal with loading the complete inventory data instead of providing some patch work by doing full repairs.
The Blog assumes that the standard data flow has been used i.e.
2LIS_03_BX —-> 0IC_C03
2LIS_03_BF —-> 0IC_C03
2LIS_03_UM —-> 0IC_C03
2LIS_03_BX – Opening Stock Data Source. The values obtained through this data source are the current status of the inventory.
2LIS_03_BF – Daily Inflow and Outflow of stock entries. This data source is responsible for providing the stock received or consumed on a particular date.
2LIS_03_UM – Holds the value of the stock. Since the key figures in this data sources are related to stock value they are of comparatively less importance in most of the systems.
TECHNICAL CONCEPT OF INVENTORY MANAGEMENT
We will see the logical concept of inventory management or what is happening while we are doing the data load.
SAP Inventory Management system allows to manage stocks on a quantity and value basis in order to plan, enter, check goods movements and carry out physical inventories.
The first Data Source (2LIS_03_BX) is used to extract an opening stock balance on a detailed level (material, plant, storage location and so on).
Now, assume that the stock on Dec-25 is 95, and assume that first time we are filling our set up tables for BX on Dec-25. So, only the opening stock or current stock will be loaded through BX. That is the current stock value at the time of triggering the data load will be loaded through BX data source. So, in this case, when we are triggering the data load on Dec-25, the current stock value (95) will be loaded through BX data source. The remaining data (Dec-21 to Dec-24 in this example), which is the history, will not be loaded through BX data source.
Now, there is another thing called VALIDITY TABLE related to inventory management. Validity table defines the range of the characteristic values for which the non-cumulative Key Figure is valid. We need to maintain a validity table for InfoCubes which contain non-cumulative key figures. Validity table is based on time characteristic with combination of any other characteristic in the Infocube depending on the requirement.
Example: Let us say your cube has 0calday, and it has data from 01/01/2010 to 12/31/2010. If you run a query and check (say its stock cube), stock balance per day per plant, it will calculate balances for each plant and show starting from 01/01/2010 onwards. However, if you had a plant that became operational on 06/01/2010, you wouldn’t want to see a 0 stock balance for it starting from 01/01/2009 but from the date the plant actually existed i.e. 06/01/2010. In this case, if your validity table had only 0calday, your report would show 0 Stock for each day from 01/01/2010 to 06/01/2010 for this new plant, while if you defined it at plant level, you will see the report starting at 06/01/2010 while it will start from 01/01/2010 for other plants. The Validity Table automatically contains the “most detailed” of the selected time characteristics which is also known as reference characteristic.
So, coming back to our BX data source, as mentioned earlier, BX data source will load only the opening balance or current stock value. It will not load any previous historical inflow and outflow. So, when this current stock value is loaded into BW, we should update a marker to it to show the system that this particular value is the current stock value or present stock value. So, for this reason, after loading the data through BX data source, we are compressing that particular data with marker update.
- Updating the marker is a special feature available only for noncumulative InfoCubes.
- To improve the performance of the query read, especially when reading the current stock.
- It is used to reduce the time of fetching the non-cumulative key figures while reporting.
- It helps to easily get the values of previous stock quantities while reporting.
- The marker is a point in time which marks an opening stock balance.
- Data up to the marker is compressed.
T-code for Validity Table – RSDV
If we execute without loading any data to the inventory cube.
Now, after loading the data to the cube (without compressing the request), if we check the validity table, it will be as shown below.
Please note that, the date 21.02.2014 was the date when I filled the set up tables. Here, you can see the list of all plants with from and to date showing the same date as the date of loading.
Now, let us see what the validity table will show after compression of the BX request. Let us compress the BX request with marker update, and the validity table will be as shown below.
As we can see that there will not be any change in the date in the validity table even after compression of BX request. So, it is clear that BX is loading only the current stock, or the date of stock at the time of data loading. We know that BF will load all the historical transaction except the current stock value. So, BX will load the current stock value of all the materials on the date of data load, and BF will load all the historical stock values of all the materials except the current stock value.
Now, let us load the BF data source with “init with data transfer” setting in the info package (without compressing the request in the info cube).
Here, you can see that the FROM DATE has changed for each plant. The FROM DATE field will now show the stock initialization date for each plant. For example, you can see from the screenshot that the first record is PLANT 3 with date 08.11.1995. So, this means that the PLANT 3 was opened and the first stock initialization happened for that plant in 08.11.1995. Similarly, all plants will be updated with the corresponding initialization dates. So, it is also clear that the BF data source loaded the historical data.
And, after loading BF and UM the first time, we should compress those requests without marker update (with tick mark on NO MARKER UPDATE). So, this will not set a marker on the historical data. Marker will be on the current stock value which was loaded by BX data source.
“To Mode” Column; This column can have the following values. Default is blank
- 1. ” ” (blank) – This is the default value means you cannot change the validity table entries.
- 2. F – This means fixed. You can put a fixed value for validity date.
- 3. R – This means relative. You can put a relative value for validity date.
If you made “To mode” as F, you can put the fixed date value in the column “Fixed to time”. This will be the date to which you need to extend the validity table.
If you made “To mode” as R, leave the column “fixed to time” untouched with its default value and edit the column “To rel”. Here you will give the offset in days by which you need to extend the validity table.
Validity table update:
The system automatically generates the validity table corresponding to the definition that was made. This table is updated automatically when loading data. If the Validity Table contains only the Time characteristic then the lower limit is defined with the data record that is first according to the Time characteristic and upper limit with the highest value. If additional characteristics added in the validity table then limits set by combination of characteristics.
DELTA LOADS AFTER THE INITIALIZATION
Now, after the loading the initialization from the set up tables, next we need to load the daily delta loads. For this, we will not be touching the BX data source (it is a one-time load for loading the opening stock). Now, for deltas, we will be loading the BX and UM with delta update in the info package. For this, we need to compress the delta request of BX and UM with marker update (without a tick mark on NO MARKER UPDATE).
Consider the above table shows the stock details of one particular material. As a summary, we can say that; assume that we are doing our initial data load on Dec 25. So, only Dec-25 value of 95 will be coming through BX data source and the marker will be updated as current stock value. Then when we run the BF data source, the history will be loaded except the initial stock. After that, consider a new record came on Dec 26 (5 new pieces of that material came and the total material stock became 100), when we run the delta update through BF data source, the stock value 100 on Dec-26 will be updated in BW, and now as we know that since this 100 is the current stock value, we need to update the marker on this new delta request, thus improving the query performance. Otherwise without a marker update, query will take long time to find the current stock value of a particular material since the cube has non-cumulative key figures.
STEP BY STEP PROCESS OF CLEANING UP OF INVENTORY DATA
1. Delete all the data from the cube 0IC_C03.
Make sure that no inventory postings are happening in R3.
2. Delete the initializations from the infopackages. For that, double click and open the infopackages.
Delete the initializations for BF and UM data sources.
3. Now, delete all the data from PSA for BX, BF, and UM.
4. Execute program RMBWV303 to clear the extraction queue in R/3 system.
5. Delete setup table contents in R/3 for Application 03 T-code- LBWG.
Now, you can cross check your set up tables using SE11.
Similarly you can check all the 3 set up tables for BX, BF, and UM.
STEP BY STEP TO LOAD INVENTORY DATA
1. Fill Setup Table for 2LIS_03_BX T-code – MCNB.
Where 5000 is the number of data records per LUW.
You can check the no of records in set up tables by giving the set up table name in SE11, or using the extract checker RSA3, or by using the T-code NPRT.
2. Fill Setup Table for 2LIS_03_BF using T-code – OLI1BW.
3. Now, in the same way, fill the set up tables for 2LIS_03_UM using T-code OLIZBW. But, note that for UM, Company code field is mandatory. You should give values for company code to fill the set up tables for UM.
4. Now, after filling the set up tables, you need to trigger the info packages starting with BX. Execute the info package for 2LIS_03_BX with update mode Generate initial status.
5. Now, compress the BX Request with marker update in 0IC_C03 Cube in BI (i.e. marker option in cube unticked).
Click on RELEASE.
6. Execute init with data transfer for 2LIS_03_BF.
7. Compress Request without marker update in 0IC_C03 in BI (i.e. marker option in cube ticked).
8. Follow the same method for UM data source. First trigger the info package with “init with data transfer” and then trigger the DTP, which will load the UM data to the cube. Then, compress that particular UM request without marker update (with tick mark on No Marker Update).
9. So, now your initial loading is completed. Now, you are loading only delta. Remember that, you will load BX only once. BX DATA LOADING IS A ONE TIME ACTIVITY. After that, for delta, you will be loading only BF and UM data sources daily, that is inflow-outflow and revaluation. And, while loading delta for BF and UM, YOU SHOULD DO COMPRESSION WITH MARKER UPDATE (remove the tick mark on NO MARKER UPDATE).
HOW TO MATCH THE DATA IN THE REPORT WITH THE SOURCE
This is a SALES AND STOCK report which was executed for Dec 2012. You can see material number and opening stock in the report. Now, we need to check whether the values are same in ECC. Here we are seeing the opening stock for Dec 2012. Please note that,
Opening stock of Dec 2012 = Closing stock of Nov 2012
Now, just come to your source R/3 system, and go to T-code MB5B.
In the report you can see that the opening stock for material number 12447126 on Dec 2012 is 58. Now come to your MB5B T-code in R/3 system (as shown above). Enter the material number, plant, and give the selection date as 30.11.2012 (this is the stock at the last date of Nov 2012, that is closing stock of Nov = opening stock of Dec 2012). Now, select the option “SPECIAL STOCK”, and give an input to the column “SPECIAL STOCK INDICATOR”(its K in our example). Then select “Totals Only – Non-Hierarchical Representation Layout”, then execute.
Data is matching.
************************End of Document***********************
************************Thanks and have a nice day ****************************
Onf of the best documents on LO extraction.All the points explained very clearly with snapshot.
Thanks buddy.. 🙂
Very Nice Explanation...
Very GOOD document for beginners. Thanks
Thanks Renju.I was exactly looking for the same document recent days.
Very nice document.
Clear cut document..Superr!
Good Doc on Inventry management.
Good Document and good presentation, every word in this document is crystal clear. explaind with good examples, Thanks
Good doc, however below paragraph is bit contradictory. you are saying BX data source meant for one time activity and again you are telling for delta loads we are using BX data source i think its not the case ideally we wont use this(BX) for daily delta. correct me if i am wrong!!
BX Loading is one time activity and there will be no Deltas for BX. BF & UM have deltas. Deltas should be compressed with marker update(no tick mark).
Thanks for your info
I am aware of that mechanism, but my concern is with respect to below line in the blog.
I think, Insted of BF He has typed BX.
@Naveen Kumar might be, although its typo 😉 it'll be great author can do necessary corrections so that reader will not be freaked!!
Thanks & Regards,
Simple and best document on Inventory extraction !!
Very good explain!, Thanks for share by RJ.
It helped me a lot..Thank you for summarizing all in one document.
Good Document..Thanks for sharing 🙂 .
Crystal clear explanation about inventory management .Though we see so many articles related to inventory management in SAP BW none of them are helped me understand clearly. But this article is superb!! Thaks for sharing 🙂
very helpfull, appreciate.
thanks for sharing.
Very well explained.
Thank you very much :-).
Hello every one,
First of all Excellent doc. I have a requeriment to load data into the infocube 0IC_C03. We have 6 years of history. I restructured the setup tables in a ERP sandbox system with almost all data (less last 4 months). In that system, transaction OLI1BW for 2LIS_03_BF took 34 hours, executed with posting date (01.07.2015-31.12.2099). I think in ERP productive we won't have all that time to restructure, so I have some questions, if you can help me:
What do you recommend me to load one year of history?.
What would you do with each restructure transaction?
How would you use filter loads?
Concept has been showcased explicitly...
Need some help on the inventory management delta queues.
We are working on BW 3.5 version,
we have a delta load which loads from 2LIS_03_BF datasource to three data targets(2 cubes and 1 ODS). we deployed a transport to production on 8th feb since then ODS became inactive, hence the daily delta loads are loaded to Cubes but not to ODS. we have activated the ODS on 25th Feb and since then delta requests are loaded to all the three data marts. Activation of data requests in ODS is failing as it is asking to correct the previous requests that were not loaded in ODS but those are available in Cubes.
Currently we are marking the QM status from green to Red for all the requests that were missed in ODS then we are activating the delta requests. we have loaded selective full load for the periods the delta was missed and activated the data.but delta load requests are still failed.
can anyone help us on how to fix them permanently?
What about, if we forgot to unckeck the marker update with a delta data, is it possible to revert this request? What we can do to fix it?
The Best document on Inventory so far , Thanks a lot Renjith , keep up the good work !!!
Good Document. Presently we have upgraded the system (BW on Hana)and wanted to load only for last 3 years of data in new system. please help any special approach for this mechanism. Current system (BW 7.0) which has around 15 years of data.
very helpful thank u...