Addressing performance issues is extremely important when selecting and implementing BI data model with high volume of stock movements. When dealing with millions of movements per day, collecting, aggregating, and reporting on this detail becomes a challenging task. In the post below I will compare a non-cumulative vs. a snapshot model for inventory management reporting. I will review reporting capabilities of each model, and will give some practical tips on implementing them in SAP BI. Special emphasis in the post is given to data load and query runtime performance. The post can be used as a practical illustration to the “How to…Handle Inventory Management Scenarios in BW Guide provided by SAP.
Address performance in the design
Let’s imagine we are loading 5M stock movements per day. They may be arriving in SAP BI via a standard extractor 2LIS_03_BF or via a set of generic extractors. Reporting on daily stock movements and stock balances becomes a technically challenging task as we have to store movements in the database for several months and process millions of movements per day.
Let us imagine we have 1M product articles and 100 plant/storage location combinations. In order to be able to design reports on daily stock balances we are facing two options:
1. Store stock balances for each article code per day in the database
2. Store opening balance per article and all movements per article in the database. Recalculate balances for every day at the query run time.
If we choose option 1 (store daily balances) we have an issue related to unnecessary data volume processing, because we would have to store 1M balances per day for each plant and storage location. If we have 100 plant/storage location combinations we end up with over 100M records per day. Needless to say this volume is vast for processing on a daily basis.
In the second scenario (keep opening balances and movements) we would have to load opening balances once, and store daily movements (5M records per day). This is more practical from a data volume point of view. However, as balances are recalculated at report run time, we may have an issue with query performance.
The second option is known as a non-cumulative model, as we have to use non-cumulative key figures and special non-cumulative infocube functionality in SAP BI.
How to choose data model
Depending on reporting requirements we have to select an appropriate data model, taking into account data volumes to be processed. If business needs require monthly reports by article and movement type we should be implementing a monthly snapshot infocube. In this case we would have to store article balances for each month. In the case of 13 months of data retention our database size should stay manageable.
On the other hand if business requirements include daily reporting for the last few months on goods issued/received, we may want to select a daily stock model with a set of non-cumulative key figures. The daily cube may have higher data granularity, however data retention should be limited to address query performance run-time.
In the case of combined reporting requirements we have to consider implementing both models: a non-cumulative daily together with a monthly snapshot as proposed in the figure below:
All queries based on the reporting multiprovider should be pointing to either daily or monthly infocube depending on type of reporting.
Use of non-cumulative key figures in the daily stock model
When implementing a daily stock infocube with non-cumulative key figures one has to specify over which characteristics non-cumulative approach applies. The point is that we have to tell the infocube if stock levels are not supposed to cumulate only over calendar days or some other characteristics as well. An example of a second non-cumulative characteristic would be 0PLANT in case cumulating stock over plants does not make business sense.
To set this setting in the infocube change mode go to: Extras – Maintain non-cumulative values:
The Snapshot view
Monthly snapshot view produces a total for movements for a month. This reduces the size of data stored in the database dramatically, as all records with the same movement type, storage location and article are aggregated into one line. At the same time we have a calculated monthly balance for each article stored in the cube. Therefore, query run-time performance is better when reporting on stock balances as opposed to the non-cumulative model.
In order to properly process transactions for future and prior periods in the transformations we should know which period is current. I suggest introducing a new characteristic ZCURPER for storing current period’s value. This can be a 6 character infoobject storing current period in the format YYYYMM.
Transactions for prior period (if any) should be updating stock balances directly in the monthly infocube. This is necessary as in the current month stock balances for prior periods have been calculated in the snapshot DSO and posted to the cube. Therefore if prior period transactions are arriving in the current month we need to make adjustments for prior periods in the infocube directly.
Period closing steps for the snapshot view
In the end of the month we have to run a period closing process chain that would copy data from the snapshot DSO to the monthly cube, and generate stock opening balances for the next period. This can be achieved with the following steps:
- Copy all accumulated movements + balances for the month (DSO -> Cube)
- Empty snapshot DSO
- Copy balances for current period to opening balances of the next period (Cube -> DSO)
- Assign next period value to the characteristic containing current period (ZCURPER).
When copying balances for current period back to the snapshot DSO we want to keep only needed granularity. Usually we just need to know stock balances at the end of the period by article, storage location and plant.
The following code can be applied in the start routine of the transformation connecting monthly infocube with the DSO:
LOOP AT SOURCE_PACKAGE ASSIGNING <SOURCE_FIELDS>.
IF <SOURCE_FIELDS>-CALMONTH <> curper OR
<SOURCE_FIELDS>-/BIC/ZSTQBAL = 0.
* Remove records for Prior & Future periods
* Current Period – remove entries without balances
DELETE SOURCE_PACKAGE.
ELSE.
* Current Period - copy stock balances to Period All
<SOURCE_FIELDS>-CALMONTH = '999912'.
* Collapse breakdown by movement type
<SOURCE_FIELDS>-MOVETYPE = ' '.
ENDIF.
ENDLOOP.
Example of a Reporting Multiprovider
The following key figures can be used in the multiprovider for inventory management reporting:
- Value Received
- Value Issued
- Quantity Received
- Quantity Issued
- Closing Balance Quantity Daily (non-cumulative)
- Closing Balance Value Daily (non-cumulative)
- Closing Balance Quantity Monthly
- Closing Balance Value Monthly
Dimensions in the reporting multiprovider may look as in the figure below. There are two reporting views in this model: Product Category Managers view vs. Controllers view. They correspond to the Daily and Monthly infocubes described above:
Keeping infocubes performing
Both daily and monthly infocubes should have reasonable number of records in order to deliver better performing queries. The number of records is based on data retention policy for each cube. In case we can set a limit to a few hundred million records (specifically less than 200M) we can count on a reasonable query runtime performance. In addition to that the following points have to be addressed to improve query runtime:
- compress requests in the cube
- implement aggregates based on statistics of frequently executed queries
In order to keep intended data volume in each infocube we have to maintain clean up procedures. Ways on how to design and implement such procedures will be discussed in the further posts.