InfoCube Design Alternative I Time-Dependent Navigational Attributes

In the Part I InfoCube design, IO_SREG and IO_SOFF are in a hierarchy of IO_SREP as the hierarchy's node values (Screen 3.46). In this section, we will discuss an alternative by putting IO_SREG and IO_SOFF into a new IO_SREP, or IO_SREPN1, as time-dependent navigational attributes.

First, we show how to build this new design.

Work Instructions

Step 1. In the definition of IO_SREPN1, append IO_SOFF and IO_SREG after the existing IO_SREPNM as attributes.

Make IO_SOFF and IO_SREG time-dependent by selecting the corresponding rows in the column Time-Dependent. Click to switch them from display attributes to navigational attributes.

 

 

SCREEN 7.12

Step 2. Enter the description Sales office for the navigational attribute IO_SOFF, and Sales region for IO_SREG.

(Screen 7.13 shows the description for IO_SOFF only.)

 

 

SCREEN 7.13

Step 3. Click to check the InfoObject. If it is valid, click to activate the InfoObject. The time-dependent master data table /BIC/QIO_SREPN1 and the time-dependent SID table /BIC/YIO_SREPN1 are created. Their names appear in the Time-dependent master data tables block.

 

 

SCREEN 7.14

Step 4. Double-click the SID table /BIC/YIO_SREPN1 to reach its definition screen.

Here we see two time-related fields, DATETO and DATEFROM. When we load data, we use these two fields to specify a record's valid period. DATETO is part of the key that makes a record unique.

We also see two other SID fields, S__IO_SOFF and S__IO_SREG. BW uses them to link IO_SREPN1 to IO_SOFF and IO_SREG, moving IO_SOFF and IO_SREG one more level away from the fact table. This layout will negatively affect query and load performance.

 

 

SCREEN 7.15

Step 5. Follow the work instructions in Section 2.5 to create a new InfoCube called IC_NEWBC1. In the InfoCube definition, click to specify the navigational attributes.

 

 

SCREEN 7.16

Step 6. In the pop-up window, check the I/O column to activate the two attributes, and then click to continue.

 

 

SCREEN 7.17

Step 7. After checking, activating, and loading data into the new InfoCube, we can create a query.

In the left panel, we see that Sales office (navigational attribute), Sales region (navigational attribute), and Sales representative (characteristic) all reside in the same dimension. From the query point of view, the navigational attributes are just like their characteristic, allowing for drill-down.

Click to specify a key date.

 

 

SCREEN 7.18

Step 8. Enter 31.12.9999 as the key date, and then click to continue.
 

SCREEN 7.19

Result

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

SCREEN 7.20

Create another query, but this time enter 31.12.1999, instead of 31.12.9999, as the key date (Screen 7.19). Screen 7.21 displays the result of the new query, which shows that the Denver office is now located in the Midwest region.

SCREEN 7.21

Note

Based on our discussion, we can draw a simplified star schema as shown in Figure 7.2 for this design alternative.

Figure 7.2. BW STAR SCHEMA OF ALTERNATIVE I INFOCUBE DESIGN

In Figure 7.2, /BIC/SIO_SREG and /BIC/SIO_SOFF have their own master data table and text table. These tables are not shown in the figure.

Because IO_SREG and IO_SOFF are placed one level farther away from the fact table, query performance will be poor with this design.

Because IO_SREG and IO_SOFF are hidden inside IO_SREPN1, we cannot build aggregates on IO_SREG and IO_SOFF.

Navigational attributes facilitate system maintenance. For example, if we need to reassign sales offices and sales regions, we can create new records in the master data table with corresponding valid dates.

This design, however, is not flexible enough to permit structure changes, because the levels of the sales organization are fixed.