Developers must fully understand the nuances of SAP functionality, such as the intricacies of the delta process. Otherwise, SAP NetWeaver BI data models can suffer from data degradation. This can manifest itself in inconsistent or inaccurate data. In some cases, key figures can appear as inappropriately split between characteristics, or as assigned to the wrong characteristics. Data degradation can occur across all versions of SAP NetWeaver BI and with data from any SAP or non-SAP system.

While the inconsistencies and inaccuracies only appear in specific situations and are sometimes limited to a subset of information, they can wreak havoc on user acceptance and confidence in the SAP NetWeaver BI system. Demanding end users rather than specific business requirements often necessitate changing your data modeling strategy.

This article series explains where data problems can appear. Once you’re armed with the tools for understanding the situation, you can avoid the problem in the first place and demonstrate the significance of inaccuracies that unsophisticated data models cause.

In this series, I’ll share common data modeling mistakes and provide appropriate solutions. Correct data modeling requires knowledge of a collection of areas such as time-dependency scenarios, delta updates, different data targets, and transformations (previously update rules in SAP BW 3.x). This article examines how to ensure consistency with the historic time-dependency scenario when using time-independent attributes.

In later articles, I’ll show how you can improve the accuracy of this method, what happens when you use time-dependent attributes, and how SAP NetWeaver 2004s improves possible options.

Each solution I’ll describe brings its own implications and performance considerations. Make sure to consider the trade-off between accuracy and performance. In addition, balance the business benefit of 100% accurate information against the complexity of the system. My approach to the trade-offs is to use more than one option simultaneously. By adopting the solution I discuss here — an intermediate DataStore object with time-independent master data attributes — you can avoid inconsistency, although the information may not be perfectly accurate. This inaccuracy arises from characteristic values relevant to when the system processed the data in SAP NetWeaver BI, not when it created the transaction or when the transaction took place in the source system. However, this technique is often good enough for most users because any difference between the two data values is typically small.

Historical Truth

SAP refers to four different business scenarios when dealing with tracking history: historical truth, current, time-dependent, and comparable. Throughout this series, I will use the historical truth business scenario to illustrate the data modeling considerations. I chose this scenario because the other three scenarios do not encounter the problem of loading master data using a delta process into an InfoCube. In addition, SAP recommends the historical truth business scenario in its data modeling training course.

For those not familiar with SAP’s business scenarios, historical truth means displaying information from when events happened, not reflecting any subsequent changes. SAP developers usually model this scenario using characteristics directly within InfoCube dimensions. See the sidebar “Introduction to Tracking History: Historical Truth Business Scenario” for more details. Next, I’ll look at the implications when master data attributes are time-independent.

My examples use the tracking history historical truth business scenario that involves looking up master data attributes. However, this is not the only way data inconsistency and inaccuracy can occur. The same principles apply when looking up values from any object, such as DataStore objects (formerly called operational data store [ODS] objects) or custom tables.

A common mistake with the historical truth business scenario is to perform a master data attribute lookup in the transformation into an InfoCube. While this does not usually cause problems with full updates, data inconsistency can occur with delta updates. This results from the fact that transformations update InfoCube key figures only by aggregation, although transformations can update DataStore object key figures by either overwriting or aggregation (Figure 1). See “The Delta Process” sidebar for more details. For this article, assume all aggregation is summation.

Figure 1

Aggregation for DataStore object key figures

The delta image to reverse the previous record performs a master data attribute lookup in the transformation. However, the lookup gathers the latest master data attribute values. If master data attributes have changed since a BW developer loaded the original transactional record, a mismatch occurs. Figure 2 shows this in action. Next, I’ll explain the changes that data undergoes in your SAP NetWeaver BI system.

Figure 2

Starting data flow from source system to InfoCube

Step 1. Starting position. Material A is part of material group X. This master data is valid and exists in the source system on January 1, 2007. A BW developer loads the master data on January 1, 2007 using an InfoPackage or the data transfer process and stores it in SAP NetWeaver BI against the material InfoObject. The material InfoObject has material group defined as a time-independent attribute.

Purchase order 001 enters the source system for material A with value 10 on January 1, 2007. When a BW developer loads the record into the InfoCube, a transformation performs a master data attribute lookup to find out the material group. Table 1 shows the data stored in the InfoCube.

Step 2. Master data changes. On January 2, 2007, material A changes in the source system to become part of material group Y. A BW developer loads this change into SAP NetWeaver BI against the material master data attributes.

Step 3 (standard). Transactional data changes. On January 3, 2007, purchase order 001 changes in the source system from a value of 10 to a value of 12. When a BW developer loads the change into SAP NetWeaver BI, the delta process sends two records, one a before image with ROCANCEL set to X and a value of -10 and another an after image with ROCANCEL empty and a value of 12. Both records perform a master data attribute lookup during the transformation to find the material group.

However, because the material group has changed, the before image finds the new material group (Y) and not the old one (X). This causes a mismatch in the InfoCube. Table 2 shows the data stored in the InfoCube.

When the users run a BEx query for material group, they see a value of 10 against material group X and a value of 2 against material group Y. This is not normally the desired effect. Usually, you would expect to record the entirety of the purchase order against either material group X or Y, not split between them simply because of when the system loaded the data.

This example uses characteristics, but the problem also occurs with key figures. Assume that you use a lookup to find a reference amount for multiplying a key figure. For example, let’s say you have a material price 10 for a quantity of 2, resulting in a value of 20. Consider what happens when you change the reference amount: The material price changes to 11. Subsequently, a delta process sends new information: In this case, the quantity changes to 3. The system would multiply the before image quantity of -2 by the new amount of 11 for a result of -22. The old record would have a different key figure value of 20. The aggregated total in the InfoCube of 31 would appear inconsistent, as you can see in Table 3.

Add an Intermediate DataStore Object

The first step of my technique is to incorporate an intermediate DataStore object. As transformations for DataStore objects overwrite records, your SAP NetWeaver BI system can determine the correct material group. Figure 3 shows the data flow.

Figure 3

Data flow from source system to intermediate DataStore object to InfoCube

Now, let’s examine how data changes in your SAP NetWeaver BI system based on this new architecture. The first two steps are the same and the modified steps 3 and 4 use the intermediate DataStore object.

Step 3 (modified). Transactional data changes. The source system sends the same pair of before and after images to SAP NetWeaver BI as in the standard step 3. The transformation still performs a master data attribute lookup. However, SAP NetWeaver BI loads the data into a DataStore object so that it overwrites records (based on the key fields) instead of aggregating them.

Step 4. SAP NetWeaver BI DataStore object data changes. The DataStore object generates its own before and after images. This reflects the fact that the material group has changed from X to Y (recall that the overwritten DataStore object record originally contained material group X). SAP NetWeaver BI then loads the material group into the InfoCube. You can see the results in Table 4.

When users run a BEx query for material group, they see a value of 12 against material group Y and nothing against material group X. By introducing a DataStore object, the InfoCube now provides aggregated information against only one material group (Y). The data is now consistent.

Considerations for Intermediate DataStore Objects

This scenario requires a second data transfer process and DataStore object activation. This may be a problem if the overall SAP NetWeaver BI load process has significant time constraints. However, it is almost unavoidable if data for the historical truth business scenario must be consistent. An operational manager looking at order details may lose confidence in the SAP NetWeaver BI system if (as in my earlier example) the same material on an order simultaneously appears allocated to two different material groups.

Of course, if inconsistencies occur often (depending upon the frequency of master data attribute and transactional data changes), then aggregated results are materially skewed. Additional configuration (one DataStore object, one transformation, and one data transfer process) is limited because all InfoObjects already exist. You need to focus on balancing data load performance against business demands for consistent information. Each business is different, so information usage should determine your consistency requirements. Analysis of long-run trends needs less exact information than data for precise financial output.

As noted earlier, this scenario does not solely apply to master data attributes. Frequently, developers use routines in transformations to look up information in DataStore objects or customer tables. Data inconsistency can occur when data from the lookup changes. New data that the system loads into the DataStore object or altered values in the customer table might change the lookup data. In this situation, you should apply the same solution of using an intermediate DataStore object.

However, recording all values against the changed master data attribute (in this example, material group Y) may not be entirely desirable. This solution uses the latest information (material group Y), so it is debatable how historic the new information in material group Y is. This is because the historical truth (characteristics in the InfoCube) consistently reflects the attribute values when the data was last loaded, not when it was first loaded (which is often more desirable and accurate). This is still an improvement on the previous situation where attribute values were different for each data load.

Bear in mind that if BW developers reload or reprocess the data, then the material group holds the new latest value because loading values into the DataStore object overwrites the older data. Therefore, you should never delete information in the DataStore object (although you may archive or move it to near-line storage) unless you can guarantee that you will not require the information in the future.

A further consideration for data modelers unhappy with the level of accuracy is the level of exactness required. The solution I discussed ensures that information is consistent, which may be good enough. Managers often require rough trends, not pinpoint accuracy, so Pareto’s rule of 80:20 may well apply. Consultants should take frequency of changes and data usage requirements into account before introducing extra complexity (for extra accuracy) that may never provide business benefit. In my next article, I’ll discuss some more complex techniques to address inaccuracies and obtain exact data.

Figure 1

Read master data transformation rule type

Historical truth is required when tracking performance over time. A simple example is holding purchasing managers accountable for purchase value of an entire material group. You should only measure this for materials during the time they are the manager’s responsibility. Consider a scenario where a material group reorganization takes place halfway through a year. Materials previously in one material group become part of another (such as a material game console changing from material group electrical goods to material group home entertainment). Materials that now fall in a manager’s area should only contribute to second-half purchases and materials no longer part of their area should still contribute to first-half purchases.

An alternative to historical truth is current. In the purchasing manager example, the purchasing manager would be accountable for the total full-year purchase value of all materials in their material group at year end, regardless of what proportion of the year the manager was actually responsible for the material.

For example, material A is part of material group X. This master data is valid and exists in the source system on January 1, 2007. A BW developer loads the master data on January 1, 2007 into SAP NetWeaver BI against the material InfoObject. For simplicity, I assume a BW developer manually loads all data, although you would undoubtedly automate data in production environments. The material InfoObject has material group defined as a time-independent attribute. Table 1 shows a transaction entered in the source system on January 1, 2007.

During the transformation, SAP NetWeaver BI determines the characteristic value of the material group using the material characteristic value (either through a master data attribute lookup or a routine). In my example, SAP NetWeaver BI assigns material group X to the data in the SAP NetWeaver BI data target (Table 2). When users run a BEx query for material group, they retrieve a value of 10 against material group X.

In February, material A becomes part of material group Y. A BW developer loads the new master data to update the material InfoObject attributes. Bear in mind nothing exists in master data in either system to indicate the old value.

Note
If the purchasing manager wants to forecast purchase values for next year based on total values for last year, he should use the business scenario called current. This requires use of navigational attributes.

Table 3 shows a new transaction in the source system on March 1, 2007. Assume the source system sends information in a delta process, so SAP NetWeaver BI only receives the new transaction.

The same transformation processes the data and stores it in the data target. You can see the data in the data target from both transactions in Table 4.

When users run a BEx query for material group, they retrieve a value of 10 against material group X and 20 against material group Y. Had they used the material group as a navigational attribute of material, the BEx query would retrieve a value of 30 against material group Y. Navigational attributes, here material group, use the latest values currently in master data attributes that apply to characteristic values (in my example, material in the InfoCube).

In my previous example of a purchasing manager, the manager runs a report showing the total purchase value for his material group. By storing material group with the purchase order, purchase order 001 does not contribute to the purchased value report of the manager responsible for material group X. This is correct because the manager was not responsible for material A at the time it was purchased (as it was still in material group X).

Duncan Foster

Duncan Foster is an information strategy consultant with CSC EMEA NR (Ireland, Netherlands, and UK) and is responsible for SAP NetWeaver's advancement. He specializes in helping organizations determine management information and performance management strategies, as well as supplies architectural oversight and project management services. He has worked with SAP since 1999 and SAP NetWeaver BI since 2001, improving the business performance and quality of decision making for thousands of users.

You may contact the author at dfoster20@csc.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.