For sales reports, mangoIndia wants to add category information for sold products. Assume that the product category assignments change with time in the original source (ERP an OLTP system)
Some report users in SAP BW/4HANA require relationship that was captured when the sales order was created. Others would like to execute reports based on the latest product category available in master data tables.
As a data modeler, your role is to decide how to store these changes in SAP BW/4HANA model.
The above figure shows the changes to category made for a product and a new product addition in master data. This is the data how it exists in OLTP source system.
The below figure shows the SAP BW/4HANA tables showing the storage of product master data (non time dependent and time dependent) and the sales order transaction data table:
If you want to keep the master data changes, you can define category as time dependent attribute of product.
If you do not want to keep changes, define it as non time depended attribute. In this case no validity period is generated. The old values are overwritten.
If you want to see aggregated values with respect to category without including product, flag category as navigational attribute.
Category can also be used from transaction data.
However in such case, new sales orders reflect these changes and old sales orders remain the same.
If category information is not present in transaction data, it can be taken from master data.
If we have flagged category as time dependent, we need to do temporal join between master data and transaction data tables in a composite provider.
In such case a key date can be selected while executing the query. In this case, if key date is changed to old value, current sales also show with old master data.
It is possible to have a combination of these scenarios, for example, category is present in both transaction data as well as time dependent master data navigation attribute.
If the query contains both these fields, the report user can use either of these fields to visualize the data.
Note: Similar scenario holds for time dependent hierarchies.