Some business scenarios require a financial dimension for stock reporting, allowing inventory movement analysis by GL Account and Cost Center. This enables controllers producing stock movement and balance reports together with income statements by sold article, location and time period. In this post I will review a design option in SAP BI for stock reporting with the financial dimension. As a datasource example I will use a standard table in SAP Retail, containing stock movements with GL account split. The approach described below has been implemented for a leading retailer with several hundred outlet stores.
Regular stock reporting View
The following chart represents a regular view on a stock reporting multiprovider based on a number of infoproviders (usually infocubes). With the regular view we can provide inventory reporting by product, organization, supplier and document type. By choosing either a non-cumulative or a snapshot model we can accumulate stock movements with a daily or monthly detail. (See also previous post )
Stock Reporting with a Financial Dimension
When we add a financial dimension to the reporting multiprovider we should realize that this increases number of records stored in the cubes and extends data granularity (10M records per day vs. 2M records for stock movements only). At the same time daily stock reporting usually does not require financial details. Therefore, in order to keep database size within a reasonable range we can add the financial dimension only to the monthly snapshot reporting infocube:
Source for transactional data
In SAP Retail 5 GL lines are generated on average for every stock movement. If we want to capture these lines we have to activate line generation for ACCTIT, a special database table in SAP Retail. ACCTIT table has hundreds of fields. When building a view on it (ZV_ACCTIT) we have to select only those fields that need to be processed in reporting.
ACCTIT table keeps stock movements together with corresponding FIGL lines. Therefore, the flow delivered by standard Material Movements extractor can be replaced by the flow from ACCTIT. In addition to stock movements delivered by 2LIS_03_BF ACCTIT contains relevant P&L and stock revaluation entries that are used for stock financial reporting.
The same dataflow as described in the previous post can be used for stock financial reporting. Stock daily cube contains only daily stock movements and balances. On the other hand stock monthly cube keeps related GL lines in addition to stock movements aggregated by month. With such approach the size of the infocubes stays manageable. Retention policy should be based on actual data volumes, for instance, 6 months for daily data and 13 months for monthly.
Build generic datasource for GL stock movements
When building a generic datasource we have to take into account that we are processing 10 – 20 million delta records per day. In case we need to reload several days extraction may become an impossible task. I suggest building a generic delta-enabled extractor based on a timestamp. For this purpose ACCTIT table should be enriched with a timestamp field (TS), populated at the moment when an ACCTIT record is created. It is important that the timestamp value is from the same time zone as the delta queue timestamp value (transaction code RSA7).
In the transaction code RSO2 we define a generic datasource (ZST_Movements), using a timestamp field (TS) for delta management with upper safety limit of 1800 seconds (30 minutes).
Apply necessary filters
When processing data from ACCTIT it is important to separate stock movement lines from GL lines. Stock Daily infocube contains only stock movements, therefore, an appropriate filter has to be applied to all ACCTIT lines: KOART = “M”.
Both daily and monthly infocubes should have correct stock quantities, therefore revaluation transactions should have empty Quantity amounts. ACCTIT delivered quantity should be set to 0 for revaluation transactions where Movement Type is blank (BWART=‘ ‘) OR Document Type is ‘PR’ (BLART=‘PR’).
Data Load Statistics
The approach outlined above has been tested at a retailer with over 700 outlet stores and 2 million stock movements per day. The actual number of lines produced is 10M per day (stock movements with FIGL breakdown).
Daily load to the infocube of 10M records takes approximately 4 hours, while updating snapshot DSO, which runs in parallel, takes about 1 hour longer. Weekly or monthly upload to the monthly cube (FULL load of 50M snapshot lines) takes about 7 hours.