InfoCube Design Alternative III Time-Dependent Entire Hierarchies

The original design in Section 3.6.2, "Hierarchy," uses a time-dependent hierarchy structure. Although that design performs more poorly than the alternative II InfoCube design, its sales organization hierarchy is very flexible. That is, we can easily add or delete levels in the sales organization. The major drawback of the Section 3.6.2 design, however, is that we cannot create aggregates on a time-dependent hierarchy structure.

This section introduces another type of hierarchy, called a time-dependent entire hierarchy, that does allow us to create aggregates. Figure 7.4 illustrates how it differs from the time-dependent hierarchy.

Figure 7.4. A COMPARISON OF TWO TYPES OF HIERARCHIES

 

As shown in Figure 7.4(a), time-dependent hierarchy structures consist of nodes or leaves that are time-dependent. The hierarchy itself is not time-dependent.

As shown in Figure 7.4(b), time-dependent entire hierarchies consist of nodes or leaves that are not time-dependent. The hierarchy itself is time-dependent.

The following steps explain how to build the design for our example with time-dependent entire hierarchies.

Work Instructions

Step 1. Create a new IO_SREP, called IO_SREPN3. Select the Entire hierarchy is time-dependent option.

 

 

SCREEN 7.29

Step 2. Select Create hierarchy from the InfoObject menu.

 

 

SCREEN 7.30

Step 3. Specify the valid dates, and then click to continue.

 

 

SCREEN 7.31

Step 4. Using the procedure given in Section 3.6.2, create a hierarchy. Notice that the Denver office is placed in the West region.

 

 

SCREEN 7.32

Step 5. Create another hierarchy, and specify its valid dates as being from 01.01.1000 to 31.12.1999. Now the Denver office appears in the Midwest region.

 

 

SCREEN 7.33

Step 6. Notice the two hierarchies. Each has an associated valid date.

 

 

SCREEN 7.34

Step 7. Follow the procedure in Chapter 2 to create a new InfoCube, IC_NEWBC3.
 

Follow the procedure in Chapter 5 to create a new query, IC_NEWBC3_Q01. When selecting hierarchies, we have two hierarchies, not just one as shown in Screen 5.10.

Select one hierarchy, and click . Save and run the query.

SCREEN 7.35

Result

The first query result shows that the Denver office is located in the West region (Screen 7.36).

SCREEN 7.36

Create another query. This time, select IO_SREPN3_H1999 as the hierarchy (not IO_SREPN3_H9999, as shown in Screen 7.35). The result of this query puts the Denver office in the Midwest region (Screen 7.37).

SCREEN 7.37

Note

The time-dependent hierarchy structure in the Section 3.6.2 design and the time-dependent entire hierarchies in the new design created in Section 7.4 produce the same query results (see Screens 5.14, 5.15, 7.36, and 7.37).

BW hierarchies are very flexible. We can easily add or delete nodes and leaves. Likewise, we can alter the number of levels.

If the hierarchy does not change very often and is not very large, then this new design is a good one. Note, however, that its performance is not as good as that of the Alternative II InfoCube design. We can create aggregates that compensate for the loss in performance to a certain extent.