BW: Inventory Snapshot solution
Applicability: SAP BW on HANA, BW/4HANA and Embedded BW solutions.
Business Case: Every BW system running an inventory system wherein the stock inflow and outflow is maintained also needs a solution for the daily inventory snapshot for a particular day, it easy to manage when data volume is less however in Retail like systems, wherein the volume is huge it becomes imperative to store these snapshots(mostly it comes in handy when you are running out of compute space).
Example: Consider you have an article A which you are selling in a Store S, now this article sells like hot cake and needs to be replenished daily so you get the Article A in Store S from a vendor or Distribution Center(DC) daily so ideally you will get 4 KPIs here amount of article A delivered to the store(inflow), amount of article sold(outflow), amount of article at starting of day(Opening balance) and amount of article left after the day closes or EoB i.e.(Closing Balance). We can have a lot more KPIs which can be derived as well however we will talk about these for now.
The opening balance today is usually the closing balance of yesterday for the article site combination so we can compute either of them i.e. the Opening Balance or Closing Balance which gives how much stock or article we are left after the Close of Business on a particular day. This view of stock is called Snapshot. Now assume you have 100,000 articles and 500 sites; imagine the snapshots for just one day and if this data needs to be retained for 1 year then you get 100000 X 500 X 365 = 18.25 Billion records.
Current Scenario/Issue: Today to compute and get the inventory snapshot from Inventory target with non-cumm. KFs depending upon the amount of data, time taken might easily go into hours to take care of any full fledged retail solution.(50 Million records per day from our example). Mostly we use APD to execute the snapshot output with BEx Query captured into Direct update DSO => This process is both time and resource consuming and even the most optimized APD run might take quiet long to yield the output. We faced this particular issue after a BW technical upgrade to 7.4 SP18. The APDs stopped considering the parallelization and execution started consuming 2 hours.
Solution: We can use the given algorithm below to push down entire BW – BEx level computation to HANA to capture the snapshot for today for all the inventory. Based on a few research runs we have been able to compute 7+ Million records in under 3 mins which takes over 2 hours using APD today in BW on HANA system.
Algorithm: Understanding this will need some basic background understanding of MM cube operation via non-cummulative KPIs of SAP BW on HANA. Obviously we have just one fact table now and that itself is being used for snapshot computation in BEx for a particular day.
1) Compute Reference Day or day on which the last Marker was updated.[RSDDSTATCOND table contains the latest information on cube compression which would also imply marker update normally].
2) Compute CALDAY which will be yesterday’s date only.(assuming you are capturing Closing Balance).
3) The push-down logic is simple:
Snapshot on Day(x) = You will need data for all available article site combinations prevalent today which needs to mashed up with snapshot computation for each article-site combination which yields data till yesterday with marker addition based on inflow and outflow equation. The marker value can be obtained from Marker Value =1 corresponding to the date extracted from point 1.
One can now sum it up as its happening in BEx to achieve the summation of (Inflow – Outflow + marker) from Marker date to yesterday’s date.
Exceptions: One might find the row count is not matching the BEx output in certain scenarios those are due to historical values which need to be adjusted for the existing Article-Site Combinations.
End Result: For the computation run based on our scripting/design logic stated above, we could achieve the entire snapshot computation of 7+M rows in under 4 mins which was taking over 2 hours with APD based method.
We found that system parameters like available memory, CPU and concurrent loads have a considerable impact on the overall efficacy of this solution as entire computation has been pushed down.
Pros: We found great turn around time on HANA calculation compared to OLAP Manager of BW application layer as we were able to embed this logic using AMDP into a transformation.
Cons: Calculation on the run-time takes a lot of resources in terms of HANA memory and CPU cycles which can be curtailed by using UDF to control the memory spikes during this operation.
Interesting concept. Disclaimer - I dabble in BW. So why wouldn't the Stock/Requirements list work? It seems to show the in and out of materials and you can take a snapshot of it.
Just thinking about it in another way.
HANA is surely capable of handling such situations at runtime, no doubt however consider the situation mentioned in the example above wherein the Article Site combination is just huge and then consider the algorithm where BW OLAP system will need to give you snapshot comparision based reporting for certain dates; amongst billions of rows ... beyond a point it doesn’t really make architectural sense to have so much data computed on runtime.
So to answer your question yes, where the data volume is limited its never a problem however in certain scenarios it does make sense to store the snapshots.
Hope I was able to answer your query.
How did you manage to create the snapshot?
Actually PBS Software has a feature in their CBW Add-On to create a snapshot of data into a SAP IQ database - SAP First Guidance – combining SAP-NLS with the PBS NLS Add-On
Best Regards Roland
I used the algorithm(mentioned in the blog) to create a calculation view to cater to the snapshot scenario directly on SAP BW on HANA System to store it in a standard DSO.
I believe PBS system is creating snapshots and sending it across to NLS system for storage and I am not sure of the performance they are getting there nevertheless its a good concept to store the snapshots outside HANA to leverage upon reporting and reduce redundancy in HANA DB itself.
can you share the script ?
I believe the guidelines don't expect us to do that however if you follow the algorithm stated perhaps you can build the script easily on your own.
Thanks. i will do as suggested.
It’s really very knowledgeable.
SAP Business Warehouse is SAP’s Enterprise Data Warehouse product. It can transform and consolidate business information from virtually any source system.
Thank you and yes you are right ?
Thanks for sharing, Abhishek.
I ran into described scenario when implementing BW 7.4 retail-inventory solution in Norway - based on ECC on Oracle - with painful
and long-running inventory snapshot(s).
next project will be retail in UK with S4H as source and BW4H as EDW. instead of implementing snapshot in BW I thought
about creating snapshot based on ABAP CDS View on S4H table MBEW (i.e. MBV_MBEW).
did you also look into this?
You are correct, it's just an algorithm so it can easily be implemented using ABAP CDS views only minor tweaks might be required to achieve the right output directly from source.
I haven't applied this logic into the Source system yet but I am sure it's achievable. I will update it here if I get to do so. Thanks.
FYI, I explored CDS Views provided by SAP on S4 and it looks like "I_StockQuantityCurrentValue" or one of the Sub-Queries will cover requirement for Stock-Snapshot (as data source for BW data flow).
Hi Andreas - that was an interesting observation, have you tried it? Is the suggestion to generate the snapshot in S4 and then extract it to a BW aDSO target?
Neville, Yes, we ended up building custom ABAP CDS view on top of "I_MaterialStock_Aggr" and used this view for extraction into BW/4 HANA via ODP.
Great Information Abhishek !