Several data degradation problems can occur with true time-dependent master data attributes (i.e., those supplied from the source system as time dependent). The problems result in an apparent mismatch between report data and master data. I’ll explain how to confront this data inconsistency that can occur across all versions of SAP NetWeaver BI with data from any SAP or non-SAP system.

The previous articles in my series covered what to do with time-independent attributes that required historical accuracy. In many respects, handling time-dependent master data attributes is exactly the same as for time-independent master data attributes. Indeed, you can apply most of the same problems and solutions.

However, there is one further issue to consider with true time dependency — that is, the possibility to back-date and forward-date changes to master data. This causes the same problems as the forward-dating of transactional data dates, whereby the master data and the transactional data appear to be inconsistent.

Back and Forward Dating

A common example of this is employee master data attributes. A promotion causing a change in grade of employees may be back-dated several months after it is entered in the system. The system records transactional data relating to those employees (for example, attendance/ absences) against their old grade, not the new one. This can result in skewed analysis for the intervening months for organizations conducting annual reviews.

Let’s consider the example in Figure 1. I will use the same data flow that I used in part 2 of this series.

Figure 1

Data flow for time-dependent master data

Step 1. Starting position. Employee A is currently grade X. This data exists in the source system on January 1, 2007, as time dependent. A BW developer loads the master data on this day and the grade InfoObject value is recorded in SAP NetWeaver BI as a time- dependent attribute of the employee InfoObject value.

Step 2. Transactional data entered. Employee A suffers an accident at work on January 3, 2007. This is recorded in the source system and loaded by a BW developer into SAP NetWeaver BI. The grade (here X) is read from master data (as per the data flow) and recorded in the DataStore object shown in Table 1.

Step 3. Master data changes (back-dated). On January 5, 2007, employee A has his details updated to reflect the outcome of a promotion review. He changes from grade X to grade Y but this is made effective from January 1, 2007. A BW developer loads the master data from the source system into SAP NetWeaver BI. The master data now looks like Table 2.

As you can see, the employee master data does not agree with the transactional data. A user would lose confidence in the SAP NetWeaver environment given the above information — even though it was correct when loaded.

However, unlike time-independent master data attributes tracked as time dependent (as in the previous article), this may apply to transactional data valid several months ago, not just that forward-dated from today. Therefore, knowing what transactional data to reprocess is extremely difficult. Indeed, the only way to adopt this solution is to base it on a business process so that transactional data more recent than the oldest possible (back-dated) change date of time-dependent master data attributes is reprocessed. In part 2 of this series, I showed you how to do this from the current date onward. You need to use the same approach for a certain number of days ago (relevant to your business process) onward.

However, you can only use a single value (the default is the current date) for the BEx Query key date — although single-value variables are permitted. You can adopt this instead of the solution provided in part 2 of this series as an alternative to reprocessing and reloading data. There are several ways of accessing the master data when executing a BEx Query.

Options to Access Master Data

The first way to access master data is to use time-dependent navigational attributes and the BEx Query key date. By not including the InfoObjects directly in the InfoCube but flagging them as navigational, the BEx Query key date now becomes relevant. SAP refers to this as the tracking history business scenario called time dependent. For those not familiar with the BEx Query key date, it is part of the properties of the BEx Query (Figure 2). When an InfoProvider has navigational attributes that relate to time-dependent master data, the value of the key date determines which one of the many master data records to display, as each record has mutually exclusive Valid from and Valid to values.

Figure 2

BEx Query properties including Key Date

However, you only can enter a single value (the default is the current date) for the BEx Query key date, although a variable can fill it. Therefore, it is only possible to get accurate results for a single day per BEx query (by using the same day for selection of the transactional data from the InfoProvider and for the BEx Query key date to determine the time-dependent navigational attributes). This is not useful in cases in which you want accurate information for more than one day (for example, an entire year’s worth). This is because it is unlikely your users want to run the same BEx query once for each day of the month (i.e., up to 31 times) and manually total the results just to get accurate information.

One advantage of using the BEx Query key date (which I’ll revisit later) is that you can alter your modeling approach. So far, I’ve only used master data attributes. However, as an alternative, I could use master data hierarchies. On the Hierarchy tab of the characteristic definition, I can set the with hierarchies check box (Figure 3). You can model any master data attribute as a master data hierarchy. Because several hierarchies can exist for the same InfoObject, you could model several attributes in this way (with each attribute as a different hierarchy). You can set hierarchies to time dependent, either at the whole hierarchy level (entire hierarchy is time dependent) or at the individual node level (time-dependent hierarchy structure). When the hierarchy is time dependent (for either type), then you use the BEx Query key date to determine which hierarchy or nodes you use to display the data for that specific key date.

Figure 3

Characteristic hierarchy options

In addition to the time-dependent options, you can also set hierarchies to be version dependent. You can use these options in conjunction with one another. Version-dependent hierarchies work in a similar manner to time-dependent hierarchies. In both cases, another copy of the hierarchy exists and you can capture it at a certain point in time. This can be particularly useful when you have an attribute modeled as a hierarchy that changes for several values at the same time (for example, employee cost centers, assuming biannual reorganization).

One major difference between using version- and time-dependent hierarchies in older versions of SAP NetWeaver BI is that in a BEx query, you can specify exactly which version to use for each hierarchy. Therefore, if you have more than one characteristic with navigational attributes being modeled as a hierarchy, you can effectively have different time points displayed because you are not relying upon the single BEx Query key date to determine the values of all hierarchies. However, in SAP NetWeaver 2004s, it is now possible to set the date for each hierarchy independently. Nevertheless, this still means you are using one view of the hierarchy for all data regardless of the date the data is being displayed.

Another option is only available within the BI capabilities in SAP NetWeaver 2004s. As before, I model the attribute as a time-dependent hierarchy structure (it is not possible if the entire hierarchy is time dependent). The hierarchy is flagged to use temporal hierarchy joins (revisit Figure 3, where the check box Use Temporal Hierarchy Join is visible).

The next step is to create the key date derivation type. Enter transaction code RSTHJTMAINT. Then, create a new key date derivation type here (I used the name MY_DERIV). Click on the create icon and the screen shown in Figure 4 appears.

Figure 4

Key date derivation type

In this transaction, you can determine the date in the InfoProvider data to use when executing the BEx query. This normally is 0CALDAY, but all time characteristics (Basis Time Characteristic option), reference characteristics of time characteristics (Time characteristic option), and InfoSet fields derived from time characteristics (Time Reference from InfoSet option) are available.

For characteristics that represent a span of days instead of a single day, it is possible to specify how to determine a date. This can be the first day, the last day, or, as in my example for calendar year/month, an offset number of days from the first day.

Save this derivation and you can use it in conjunction with your characteristic time-dependent hierarchy structure in the BEx query. This is done by including the characteristic within a BEx query and choosing which hierarchy to adopt. The dialog box shown in Figure 5 appears.

Figure 5

Characteristic hierarchy BEx Query options

In addition to choosing the hierarchy, you also can choose the hierarchy date. The first three options allow this date to be chosen in different ways (the first is the BEx Query key date, the second is a manually entered date, and the third is a date from a single-entry variable). Remember that for each of these you only can use one value. However, the fourth and final option allows data to derive the date using the derivation type I have just created. This means each record in the InfoProvider can appear under the node in the hierarchy valid according to the record’s date, not a single date. The SAP documentation (https://help.sap.com/saphelp_nw2004s/helpdata/en/03/eca042bde0c611e10000000a1550b0/frameset.htm ) demonstrates the differences between these two approaches. Essentially, it means that the BEx Query shows the employee’s accident against the employee’s grade, which was valid for the date the accident occurred (and is specified in the derivation type) — keeping in mind that each accident has a different date.

However, a disadvantage of using hierarchies is that a BEx query can display only one hierarchy for any given characteristic at any one time. Therefore, if you want to navigate by more than one attribute in the same BEx query (for my employee example, I might want to show information by grade and age group), hierarchies are insufficient.

A better approach is using InfoSets in SAP NetWeaver 2004s. These have a special feature known as temporal joins, specifically for use with time-dependent master data. Temporal joins ensure that when you link one time- dependent master data characteristic to another, the system creates an artificial validity period for each appropriate combination of Valid to and Valid from dates. This is not the same as a normal join. The SAP documentation (https://help.sap.com/saphelp_nw2004s/helpdata/en/11/723c3b35703079e10000000a114084/frameset.htm ) has a very good illustration of this at the bottom of the page.

However, even this temporal join does not help with the InfoProvider, because this is not a time-dependent master data characteristic. Nevertheless, there are two other features you can deploy to make it act as such. The first is to use a temporal operand. To do this, you flag characteristics within one of the InfoProviders within the InfoSet as a Key Date (Figure 6). Only those characteristics that relate to dates have this check box available.

Figure 6

Key Date option for InfoProviders in InfoSets

As in the key date derivation type, there may be characteristics that represent a span of time instead of a single value. These are handled in the same way. After you select the Key Date check box, the dialog shown in Figure 7 appears with the same selection options. Note that you also can use key date derivation types here (in the same manner as for hierarchy temporal joins).

Figure 7

Key date value determination options

Now, the temporal operand I checked restricts the final output when reading data from this InfoSet. If you imagine the InfoCube has been joined to the characteristic with time-dependent attributes, there will be several records for each row in the InfoCube, representing all of the various Valid from and Valid to combinations. The temporal operand is then applied to this data to select from it all entries that are not valid for the date contained in the temporal operand. However, as this date is derived from the record in the InfoProvider, this happens on a record-by- record basis, not by using one date for the entire data set.

The other option available with InfoSets is to use pseudo time dependency. With this approach, rather than specifying a particular key date from the data, you specify an entire time interval. To do this, bring up the context menu on the InfoProvider within the InfoSet. The menu option Define Time Dependency is available (Figure 8).

Figure 8

Define pseudo time dependency

The menu option then brings up a dialog to select how to determine the pseudo time dependency. This can use any pair of characteristics to act as the Valid from and Valid to (as if dealing with master data) or a single characteristic to represent the entire validity interval. Figure 9 shows these options.

Figure 9

Definition of characteristics for pseudo time dependency

Now the SAP NetWeaver 2004s system treats the InfoProvider just like time-dependent master data in the InfoSet. This means it is subject to the same implications for temporal joins as you saw earlier in the SAP documentation. Bear in mind that because you now have a range of dates (instead of a single key date), you can still end up with multiple records for the same record in the InfoProvider. Therefore, you should use this option with caution.

Nevertheless, this option has its uses. I mentioned previously that these techniques apply to looking up data from any object, not just master data. If you have information in a DataStore object (formerly called an operational data store [ODS] object) with Valid from and Valid to values, then you can use this feature to join it to another InfoProvider with a temporal operand for pinpoint accuracy that reflects the latest data in both objects. Also, if you report on changes to transactions over time (for example, status information), then pseudo time dependency may be a very useful way of representing this.

Remember that SAP NetWeaver InfoSets in the BI capabilities in SAP NetWeaver 2004s can now include InfoCubes (although no more than two are recommended in any one InfoSet), whereas in previous versions, these were not permitted. Therefore, in older versions, the transactional data should be stored in DataStore objects to use in SAP NetWeaver InfoSets.

Weigh Your Options

Throughout this entire article series, I have looked at various ways of modeling data to avoid inconsistencies and inaccuracies. Remember that you can use all of these methods and techniques in conjunction with one another. Therefore, you could have one attribute (for example, employee age group) that changes so infrequently that although you want the history, my first solution (an intermediate DataStore object for consistency) would suffice. Simultaneously, you could have another attribute (for example, employee cost center) you want to track more accurately, so you could use an InfoSet with temporal operands to report on this information.

The crucial element is to balance the complexity, performance, and flexibility of the modeling approach against the accuracy required. Table 3 summarizes the modeling methods I’ve explained in this article series and their relative scores in these areas. I have not included consistency because all of the techniques mentioned ensure consistency of data.

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.