SAP S/4HANA: enabling SAP Datasphere Inventory Reporting

Purpose

Are you looking for insights into how existing CDS views are structured from a technical perspective regarding Inventory reporting in your SAP S/4HANA source system? Do you also want to enable inventory reporting requirements in Datasphere based on those insights. If so, you’ve come to the right blog!

In this blog, we provide a step-by-step approach to identifying SAP standard CDS views and their components in SAP S/4HANA. We then explain how to use custom CDS views to bring this information into Datasphere, allowing you to accelerate your inventory modeling and analysis processes.

Use Case

In our previous blog ( ‘…Gaining Additional Insights into Technical Details of CDS Views’ ) we created a data model in Datasphere to analyze the SAP S/4HANA SAP consumption CDS view ‘C_MaterialStockByKeyDate’ (Material Stock at posting date). As a business requirement, the goal was to replace the old BW Inventory Management report using 2LIS extractors (2LIS_03_BF, _UM and _BX). Additionally, after some investigation, the ‘C_StockQtyCurrentValue_2’ (Current stock quantity and value) was also examined in a similar way.

Since these views and their components alone are not suitable to get all the relevant information from S/4 HANA into Datasphere, a customized model is proposed, that closely aligns with the setup of the SAP standard CDS views.

For more details on the new inventory setup in S/4 HANA, refer to this insightful blog on the ‘Simplified Data Model (NSDM) for Inventory Management Tables’.

Initial insights (1): ‘…What Are the Components of the ‘…’ SAP Standard Consumption CDS Views’?

The primary components retrieved for both ‘C_MaterialStockByKeyDate’ and ‘C_StockQtyCurrentValue_2’ CDS views are:

• ‘Movement’-related information, ensuring a correct Inventory Balance

I.e. both use the MATDOC (‘Material Documents’) table as their source. 

2. Revaluation-related information, ensuring the correct Inventory Valuations

The differences in approach of Valuation is that e.g. MBEW (‘Material Valuation’) might be easier to take the ‘Current’ Valuation (as it only has 1 entry per Material, Plant and Valuation Area), whereas the CKMLHD (‘Material Ledger: Header Records’) and CKMLCR  (‘Material Ledger: Period Totals Records Values’) combination can do the same when taking the ‘maximum’ and using that as the ‘Current’ by default. Though, these tables are easier to use and to do it dynamically if a historical Key Date is chosen. 

3. Interesting and less-standard Master Data and/or components needed for logic

Datasphere Developments (1):

• Replication of the S/4 HANA tables towards Datasphere

All the ‘Main Table Sources’ are taken over via a Delta-enabled Replication Flow towards Datasphere, via a Custom CDS View using the Annotation:

I.e.:

I.e. enabling the Delta-settings (creating the Delta-tables in parallel) and filters wherever applicable (e.g. CKMLCR.curtp = ‘10’ and the leading Fiscal Year Variant for I_FISCALCALENDARDATE).

• Determining the ‘maximum’ Cost Estimate

Where:

• PRO_INV_0001: Uses I_FISCALCALENDARDATE to generate a subset based on a Key Date Input  Parameter (retrieving the  ‘maximum’ Fiscal Year / Period).

• PRO_INV_0002: Creates a reusable view combining relevant columns from the CKMLHD and CKMLCR tables, applying logic similar to the I_MLINVENTORYPRICERAWDATA CDS view.

• PRO_INV_0003: Merges 0001 and 0002 to determine the ‘maximum’  Price and Price Unit, per Cost Estimate and Valuation Area, given the Fiscal Year / Periods valid as per the Key Date Input Parameter.

2. Aggregated Material Documents with the ‘maximum’ Cost Estimate 

Where:

• PRO_INV_0007: Filters out unnecessary detail from the MATDOC table and can be persisted for performance optimization.

• PRO_INV_0004: Links the Cost Estimate and Valuation Area from the Material Documents to the maximum Cost Estimate:

Where the ‘new’ Inventory Value is calculated as:  ( STOCK_QTY) *  ( PRICE  /  PEINH)). Where the ’STOCK_QTY' is coming directly from the MATDOC table and the ’PRICE’ and ’PEINH’ are the derived ’maximum’ values from the CKMLCR table.

3. Combining all elements

Where:

• PRO_MD_0001: Generates relevant ‘time information’ filtering by Current Date, pre-filling the Input Parameter in the Analytical Model.

• PRO_INV_0005: the Fact Model in the Analytical model, including Associations (e.g. for the Stock Type descriptions) and Access Controls.
  

4. Overview of the conceptual Datasphere model

A complete picture of the above items (excluding e.g. Associations, Data Access Controls, ..), would then be:

Datasphere Example Result (1):

As an example, a Material will be used that has been Revaluated for one of its Plants over time, i.e. example Raw Material RM20. When looking at this data in the Analytical Model (Key Date the 12th of March), the following output is generated:

S/4 HANA Example Validation (1):

A couple of validation checks can be done within the S/4 HANA system:

• Checking the ‘Quantity’ results of the ‘Material Stock by key date’ Query via the Query Browser in the Fiori Portal (/n/ui2/flp -> Query Browser)

 I.e. this matches the STOCK_QTY in the Datasphere output.

2. Validation of the results from the MB52 Transaction (Display Warehouse Stocks of Materials)

I.e. the ‘Total Value’ for the Unrestricted Stock matches the CC_CURR_AMT_LC column (‘Current Amount in Local Currency), being the recalculated value.

3. Explaining the difference between the ‘original’ value in Datasphere (DMBTR_STOCK) and the recalculated. Value (CC_CURR_AMT_LC)

For the RM20 (Material) – 1710 (Plant) – 171B (Storage Location) combination, there is a difference between the calculated 7056 USD and 5566 USD from the ‘original’ Material Documents (total Quantity being 3920 PC).

When looking at the Material Ledger: Header Records (CKMLHD), this is related to Cost Estimate 000100000064:

This Cost Estimate entry in the Material Ledger: Period Totals Records Values (CKMLCR) shows a switch in Standard Price from 1.30 USD -> 1.80 USD from January 2025 onwards:

This is related to Price Change Document 3000000106 (Transaction MR21):

(or, via Transaction CKM3N (Material Price Analysis)).

Doing the calculation, the new 1.80 USD per PC difference compared to the historical valuations is the explanation of what is shown in Datasphere, i.e. 3920 PC * 1.80 USD = 7056 USD as ‘current’ Valuation.

4. Historical Key Date Input Parameter validation

Using the same example Material and Plant, but then for a Key Date e.g. in December 2024, to validate the accuracy of the inventory valuation before the Price change of 1.30 USD -> 1.80 USD:

I.e. a correct Valuation, both from the MATDOC source data, as well as taking the December 2024 Valuation (1.30 USD) as maximum for the recalculation, as 2980 PC * 1.30 USD = 3874 USD.

Additional topics (1)

Some additional considerations on top of the simple conceptual model might be:

• Additional valuations-relevant information in S/4 HANA CDS views, related to migration of information, e.g. FMLV_XBEW_CKMLCR_SALKV, can be investigated further.

• If there is too much information in the MATDOC table, not relevant for Inventory Management reporting, additional filters can be applied.

• Larger tables such as the MATDOC and CKMLCR table can be stored in-memory.

• Aggregated views on top of these tables can be persisted (e.g. the MATDOC KEY1, ... 6 fields are not required).

• If there is too much historical data in the MATDOC table, a setup with the MATDOC_EXTRACT table in combination with (filtered) MATDOC entries and cumulative Measures can be investigated further (similar to the ‘original’ 2LIS_03_BF and _BX setup in BW).

• Based on e.g. the Movement Type, Stock Type, Special Stock Type and Stock Category restricted Key Figures should be made instead of only having a generic Quantity and Value Measure, similar to the BW ‘Process Key’ concept.

Key Takeaways:

• Use case: S/4 HANA Inventory Management uses a new (simplified) data model, requiring a revised approach to data extraction for Business Intelligence systems.

• Technical insights and Datasphere implementation: This blog provides a structured method for understanding and modeling S/4 HANA’s inventory management data in Datasphere.

And many more…

If you would like to know more about standardizing the usage of S/4 HANA CDS views within its Datasphere context? Please contact Nico van der Hoeven (+31651528656).