Today’s business is changing fast and organizations need different versions of time-based truth in business intelligence. This makes almost any data element, such as customer, product, or organizational unit, subject to time-based dependencies. The relationships of these entities are difficult enough to model without having to consider time dependencies. This is a hard fact of life. Data models need to be dynamic and able to change with time.

Some real-life business trends are reorganizations (such as mergers and acquisitions), introduction of new products, and line extensions that drive complex changes in product hierarchies, along with constant re-segmentation of the market to identify new and more lucrative customer groupings. These business-driven demands translate into a need for more complex modeling techniques in SAP NetWeaver BI.

You can address time-dependent requirements in different ways in SAP NetWeaver BI. Common mechanisms include time-dependent hierarchies and time-dependent attributes of InfoObjects. However, the issue of time dependency becomes much more complex when you must link time-dependent data together. In these situations you need to merge the different time intervals and validity periods of relationships. This is not a trivial exercise.

I’ll explain how to model these time-dependent data relationships in SAP NetWeaver BI. The intended audience for this article is solution architects and technical SAP experts. The methods described apply to SAP BW 3.5 and SAP NetWeaver BI 7.0.

Business Examples

Before I show you the alternate solutions, I’ll take a look at several examples of complex time-dependent relationships. For instance, when the HR team assigns employees to HR positions, it needs to link together many different time-dependent objects:

• The position is time dependent and it usually changes its characteristics over time (e.g., job descriptions or compensation change)
  
  
• The assignment itself is time dependent (e.g., different people can hold the same position over time)
  
  
• Employees are time dependent (e.g., their skill sets change or they are promoted)

Another example is the modeling of organizational structures. This is often accomplished using time-dependent hierarchies. However, sometimes you need to flatten these hierarchies. This means that the implicit assignments of, for example, a Profit Center to its Business Segment via the levels of the hierarchy, is made explicit. The Business Segment becomes a direct attribute of the Profit Center. To accomplish this, time-dependent joins are required.

Yet another example is the modeling of time-dependent transitive attributes (attributes of attributes). If a characteristic is included in an InfoCube as a navigation attribute, you can use it to navigate in queries. This characteristic can itself have further navigation attributes, called transitive attributes. These attributes are not available for navigation automatically in the query. To make a transitive attribute available for reporting it has to be made a direct attribute via a time-dependent join.

Time-Dependent Joins

So, how do you join two time-dependent objects in SAP NetWeaver BI? Figure 1 provides an example to illustrate the problem that I will use to show you how. Assume that the Profit Center has a time- dependent attribute called Management Unit. The InfoObject Management Unit on its part has a time-dependent attribute called Business Segment.

Figure 1

The two time-dependent attribute relationships linked via Management Unit

Say the company reorganizes the following, as shown in Figure 2:

Figure 2

Time intervals for Profit Center and Management Unit

• The Profit Center “SAP NetWeaver BI Sales” belongs to the Management Unit “Business Intelligence.” On 1/1/2003, the company assigns SAP NetWeaver BI Sales to the Management Unit “Consumer Products.”
  
  
• The Management Unit “Business Intelligence” belongs to the Business Segment “Services.” On 3/16/2002, the company assigns it to the Business Segment “Solutions.”
  
  
• The Management Unit “Consumer Products” belongs to the Business Segment “Discrete Manufacturing.” On 7/1/2005, the company moves it to the Business Segment “Consumer Non- Durables.”

An organizational restructuring of businesses represents a typical scenario in which reassignments of Profit Centers and Management Units occur. When organizations restructure a business, they often want to compare financial numbers as per the old organizational structure with the numbers according to the new structure. Therefore, you need a time-dependent storage of the changing relationships. A common remark about BI software is that it is not flexible enough to cope with changing business structures. Modeling these structures using time-dependent attributes overcomes this limitation.

Given the setup depicted in Figure 2, the goal is to make the Business Segment an attribute of the Profit Center for reporting purposes. All relations are time dependent, which means you must build a time-dependent join via the Management Unit.

How do you join the Profit Center “SAP NetWeaver BI Sales” (black line) with the two relevant entries in the Management Unit table (blue lines)? You can create the link easily if the attributes are not time- dependent. A simple join (via InfoSets) of the tables or a lookup (transformation) during staging achieves this.

Even if one of the two objects is time dependent this problem is still relatively easy to solve. For example, you could use a time-dependent master data lookup in the transformations using a master data key date — the date you want to load for the valid master data. However, the problem of joining becomes significantly more complex when both objects are time dependent, as shown in the last row in Table 1.

*Using the lookup with key date sacrifices the time dependency of the second attribute.

Table 1 Time-dependent modeling options

In my example, I am staging into the result table (i.e. InfoObject Profit Center) illustrated in Table 2. In this table, Management Unit and Business Segment are both time-dependent attributes. If the source tables both had Profit Center as a common primary key then you wouldn’t need to add any code.

Table 2 The desired format of the Profit Center master data: Management Unit and Business Segment are both time-dependent attributes

Tables 3 and 4 show this case in which both source tables have Profit Center as a primary key. In this case, the SAP staging engine takes care of the logic to merge the different time intervals as part of its standard capabilities. It stages Business Segment as an attribute of Profit Center. However, when staging into a DataStore object (DSO) or staging a transitive attribute into an InfoObject you must program the merging of time intervals as described in the following section.

Table 3 Source table 1 with Profit Center as the key and Management Unit as a time-dependent attribute

Table 4 Source table 2 with Profit Center as the key and Business Segment as a time-dependent attribute

Process Overview

In the next more complex example, Profit Center is the primary key and Management Unit is a foreign key that derives the transitive attribute Business Segment. Table 3 and Table 5 show what the source tables look like for this example.

Table 5 Source with Management Unit (not Profit Center) as key and Business Segment as a time-dependent attribute

You can solve the problem of merging time-dependent objects with foreign key relationships by linking the objects for each time interval. In short, time-dependent joins are processed per time interval. In the following section, I will discuss the ABAP code required to implement time-dependent joins in SAP NetWeaver BI.

Figure 3 illustrates a join between a Profit Center (black line) and its Management Units (blue lines) over time. The result is depicted in gray: a merge of the time intervals of the Profit Center and its relevant Management Units. Note that the new time intervals of “SAP NetWeaver BI Sales” are more granular than the original ones to accommodate for both attributes of Profit Center: Management Unit and the new (transitive) attribute Business Segment. Although it is relatively easy to depict the time-dependent join in Figure 3, it is not trivial to realize this merge in ABAP.

Figure 3

Join Profit Center “SAP NetWeaver BI Sales” with its Management Units’ Business Segments

Detailed Process

To illustrate the time-dependent join in ABAP, I use the data for the Profit Center InfoObject ZPRFT_CTR and the Management Unit InfoObject ZMUNIT shown in Figures 4 and 5. Note that the data in these figures reflects the time intervals from Figure 2.

Figure 4

Profit Center master data table (InfoObject ZPRFT_CTR)

Figure 5

Management Unit master data table (InfoObject ZMUNIT)

The result of the time-dependent join between the two tables is the output of a custom program, which you can download by clicking this link. You can also download a description of the ABAP program structure to see how the code works.

You can execute this code in transaction SE38, provided you create the two InfoObjects ZPRFT_CTR and ZMUNIT in your system. However, in a live SAP NetWeaver BI system it makes more sense to use this code in extractors or staging routines. Figure 6 shows the results.

Figure 6

Results of the time-dependent join between Profit Center and Management Unit — output of the ABAP program

To create this time-dependent join, I used the ABAP statement PROVIDE. It facilitates merging the Profit Center and Management Unit time intervals. Basically, the download code assigns the relevant attributes (i.e., the values of Management Unit and Business Segment) per time interval to the Profit Center currently processed in the ABAP loop. The code then appends the attributes to the result table (ZTARGET). Failing to handle the time intervals correctly results in an overlapping time interval error during the master data load, as shown in Figure 7.

Figure 7

Error in the SAP NetWeaver BI load monitor when time intervals overlap during the load of Profit Center

Technically, the statement block PROVIDE and ENDPROVIDE defines a loop on the time-dependent tables handed in the PROVIDE statement. In my case, I process two tables per unique Profit Center/Management Unit combination: one that contains the relevant time intervals for the Profit Center and one that contains the management time intervals.

You must specify the following extensions of the PROVIDE statement in the downloaded code.

• FIELDS: Specifies the relevant fields for the PROVIDE (using an asterisk signifies all fields)
  
  
• BOUNDS: Specifies the names of the fields used to determine the interval boundaries (in my example, datefrom and dateto)
  
  
• BETWEEN: Specifies the maximum boundaries between which the PROVIDE statement should operate (e.g., beginning of time 10001010 to end of time 99991231)
  
  
• INCLUDING GAPS: Specifies whether the tables passed into the PROVIDE can contain gaps in the intervals. This does not happen when loading InfoObjects.
  
  
• VALID_FLAG: The valid flag is set to X if the time interval is relevant for the particular table. The variables l_flg1 and l_flg2 are used as valid flags in a manner illustrated in Figure 8.

Figure 8

Visualization of the values of the PROVIDE valid flag, depending on the time intervals of the records currently processed

Figure 9 shows the generic syntax of the PROVIDE statement. Figure 10 shows the PROVIDE code from the downloaded code. For further details on the PROVIDE statement refer to the F1 help in the ABAP editor.

PROVIDE FIELDS {*|{comp1 comp2 ...}} 
               FROM itab1 INTO wa1 VALID flag1 
               BOUNDS intliml1 AND intlimu1 
               [WHERE log_exp1] 
        FIELDS {*|{comp1 comp2 ...}} 
               FROM itab2 INTO wa2 VALID flag2 
               BOUNDS intliml2 AND intlimu2 
               [WHERE log_exp2] 
               ... 
        BETWEEN extliml AND extlimu 
        [INCLUDING GAPS]. 
  ... 
ENDPROVIDE

  PROVIDE
        FIELDS *
          FROM lt_zprft_ctr_tmp INTO ls_zprft_ctr_tmp
           VALID l_flg1
           BOUNDS datefrom AND dateto
        FIELDS *
          FROM lt_zmunit_tmp INTO ls_zmunit_tmp
           VALID l_flg2
           BOUNDS datefrom AND dateto
        BETWEEN ‘10000101’ AND ‘99991231’. 
   . . . . . 
  ENDPROVIDE

Due to the technical implementation of the PROVIDE command by SAP, the date- interval columns must not overlap and must be sorted in ascending order. It is necessary to process each unique Profit Center/Management Unit combination separately in the PROVIDE parameters. You can do this by building subsets of the Profit Center table lt_zprft_ctr and the Management Unit table lt_zmunit.

In my example of the Profit Center “SAP NetWeaver BI Sales” the system performs the following steps in the PROVIDE loop. During the first iteration, the system joins the Profit Center “SAP NetWeaver BI Sales” with the Management Unit table time intervals for “Business Intelligence.” The system passes the two tables in Figure 11 into the PROVIDE loop.

Figure 11

Records processed during the first iteration of the PROVIDE loop

During the second iteration, the system joins the Profit Center “SAP NetWeaver BI Sales” with the Management Unit table time intervals for “Consumer Products.” In this iteration, the system passes the two tables in Figure 12 into the PROVIDE loop.

Figure 12

Records processed during the second iteration of the PROVIDE loop

Profit Center records without a Management Unit are not passed into the PROVIDE loop, as there are no counterparts in the Management Unit table to join them with. They are simply appended to the result table. The result for “SAP NetWeaver BI Sales” after the PROVIDE is shown in Figure 13. This reflects the intervals depicted in the gray section at the bottom of Figure 3.

Figure 13

Result of the PROVIDE code: the time dependent join that links Profit Center, Management Unit, and Business Segment per time interval