Identify Tracking History Scenarios

Now, let's have a look at the requirements of Itelo when modeling master data. To enrich sales reports, ITelO wants to add product category information for sold products.

However, the product category of a product in the source system changes sometimes.

In SAP BW/4HANA reporting, some users only require the relationship of product and product category that was captured in the sales order when it was created. Others would like to perform their analysis based on the latest assignment from the master data tables. You must consider how to document changes to these assignments in the SAP BW/4HANA model by identifying tracking history scenarios.

The preceding figure shows the data that exists in the source (OLTP) system. The data represents the product master data table (in two versions), and a sales orders table.

The example in the preceding figure focuses on changes that are made to the product category (food, chemicals, and so on) concerning the product master data. Assume that on Jan 1, 2018, product category assignments changed for the product alcohol, and a new product Ozone was added to the product master data.

The example data is used to explore the modeling possibilities regarding tracking history in SAP BW/4HANA.

Let's look at the tables used in SAP BW/4HANA as an example.

If you want to document master data changes, you can define category as a time-dependent attribute of product. Remember, if you do want to display the category values without displaying the product values, choose the navigational attribute option. Every time the assignment changes, new master data records are loaded to SAP BW/4HANA. The new assignment's validity period starts on the current date. In our example, this date is Jan 1, 2018. The validity period for the previous assignment ends on the previous day. In our example, this date is Dec 31, 2017.

If you do not need to document changes, the category could be modeled as not time-dependent attribute. No validity period is created. The old assignment can no longer be retrieved.

As an alternative, you can store the assignments within the transactional data table. Load transactional data immediately after the creation of sales orders in the source system, and capture the relationship between product and product category that exists in that moment. If you make new assignments in the source system, new sales orders reflect these changes but old sales orders remain the same. Thus, the same product can be listed with different categories in the transactional data table. After the aggregation of data from different sales orders, if the same product was sold in the same product category, an aggregated value will be kept. But different combinations of product and product category remain separated.

If the category characteristic is not provided in the transactional data table of the source system when the transactional data is loaded, you can create this information in SAP BW/4HANA, for example with a look up from the product master data table, to determine the category value.

Let's now explore different modeling possibilities regarding tracking history:

• Scenario A: historical truth.

• Scenario B: current view.

• Scenario C: time-dependent view.

How can the data of these models be reported? In the preceding figure, the category in a Query is a characteristic of the transactional data.

In our example, sugar was always sold in the food category, and the row shows values from 2017 and 2018. Alcohol was partly sold as chemicals and partly as food, hence two different rows appear in the report. Ozone was always sold as chemicals.

The master data table is not used at all when building the report. In the preceding figure, it is marked in gray for this reason.

If you remove the product characteristic from the drilldown, the category is still reflected as a historical truth in this aggregated view.

The advantage of this design is that all values appear as sums as they would have been calculated on the sales day.

As a disadvantage of the historical truth display, it seems as if sales revenues for the food category went up drastically, and chemicals performed more poorly. However, this is an effect of the hidden changes of master data assignments, not a real sales trend.

How can the real sales trends be made visible?

In the preceding figure, the category is modeled as a time-independent navigation attribute of product. Now, for each product, there is only one assignment, according to the latest relationship between product and category.

The category column of the transactional data table is not used at all.

If you remove product from the drilldown, in the example, all sales of sugar and alcohol will be added up to the total food sales revenue value. The total value for chemicals contains the products that currently belong to chemicals. In the example, only Ozone is still assigned as chemicals.

The advantage of scenario B is that the aggregated report reflects real sales trends: food and chemicals show a positive trend.

As a disadvantage of scenario B, the 2017 data does not reflect the truth: it seems as if no chemicals were sold in 2017. Let's have a look at scenario C.

In the preceding figure, the category is modeled as a time-dependent navigation attribute of product.

When Queries are built, a key date is specified in the Query properties to indicate to the system what master data record it must use.

As a default, this key date is the date when the report is executed. Then, historical assignments are ignored. The numbers in the result are the same as in Scenario B (Current View).

However, with this model design, a different view can be chosen. If the key date is changed to Jan 1, 2017, the assignment of other validity periods is ignored (as marked in gray on the preceding figure). Instead, all products are consistently displayed with the category that was assigned on Jan 1, 2017, as if no master data assignments were changed. Ozone, which was not registered on that date, is displayed with the Not assigned category (displayed as #).

As an advantage, trends and 2017 values are correct. As a disadvantage, the current sales revenues of 2018 are represented with historical assignments.

Let's see how a CompositeProvider can be used for tracking history scenarios.

In a CompositeProvider, you can use a Characteristic InfoObject with time-dependent attribute master data in order to define a temporal join. The two additional fields, 0DATEFROM and 0DATETO, are compared with a time characteristic of the join partner. This time characteristic has to be specified as key date in the context menu of the join partner. If the time characteristic is an interval, such as 0CALYEAR, the first or the last day of the interval can be derived, or a specific key date derivation type defines how a key date is derived.

In the preceding figure, you can see the definition of the CompositeProvider for this scenario.

So, let's now recap the tracking history scenarios.

Depending on the scenario, values are aggregated in different ways.

It is possible to combine Scenario A with other scenarios. For example, category can be a characteristic in the transactional data and it can also be a time-dependent navigation attribute of product (scenario C). If the Query contains both, the person who designs the report can choose the most appropriate way to visualize the data.

There is no best scenario. It depends on your requirements.

Scenario A (Historical Truth) is best in terms of reporting performance and complexity. All values can be read from one table.

Scenario B (Current View) is best in terms of storage space. There is no redundancy. Only the currently needed values are stored. However, you loose all information about historical assignments.

Scenario C (Time-Dependent) is best in terms of flexibility because the business user can choose freely what key date to use.

Remember that scenario A can be combined with other options: most often, scenario A and C, or A and B are chosen.

Furthermore, note that the value of historical data decreases over time.