InfoCube Design Alternative II-Dimension Characteristics
In this section, we discuss yet another design alternative. This time, we will create a new IO_SREP, called IO_SREPN2. We will treat IO_SREG and IO_SOFF as independent characteristics, just like IO_SREPN2, and put them all together in the same dimension. Figure 7.3 shows a simplified star schema for this design.
Figure 7.3. BW STAR SCHEMA OF ALTERNATIVE II INFOCUBE DESIGN
In Figure 7.3, /BIC/SIO_SREG and /BIC/SIO_SOFF have their own master data table and text table. These tables are not shown in the figure.
This design methodology is known as the Dimension Characteristics method. The following steps explain how to build this new design.
Work Instructions
Step 1. Repeat the Steps in Section 2.3 to create IO_SREPN2. It has no hierarchies.
SCREEN 7.22
Step 2. Create an InfoCube and include IO_SREG and IO_SOFF as characteristics.
SCREEN 7.23
Step 3. Assign IO_SREG, IO_SOFF, and IO_SREPN2 to the same dimension as shown in this screen. (See Screen 2.27 for the difference.)
Click
SCREEN 7.24
Step 4. We also need to include IO_SREG and IO_SOFF in the communication structure. (See Screen 3.52 for the difference.)
SCREEN 7.25
Step 5. Load data into the new InfoCube and create a query.
In the left panel, we see the three characteristics Sales office, Sales region, and Sales representative. They are all in the same dimension.
SCREEN 7.26
Step 6. As before, we specify 31.12.9999 as the key date, and run the query.
SCREEN 7.27
Result
Screen 7.28 shows the query result. The Denver office is listed under the Midwest region, instead of the West region, although we specified 31.12.9999 as the key date. This result arises because the sales transactions conducted by the Denver office all took place before January 1, 2000 (see Table 1.4). In the data warehousing world, this query result is referred to as a yesterday-or-today scenario – the data were valid when they were generated.
In a yesterday-and-today scenario, the data that were valid yesterday and today are displayed. In our example, we would not see the Denver office data in a yesterday-and-today scenario. For further information on this scenario, refer to ASAP for BW Accelerator, "Multi-Dimensional Modeling with BW."
Now we know that our new InfoCube design does not provide the two views of data that we saw earlier with the time-dependent hierarchy structure and time-dependent navigational attributes – namely, the today-is-yesterday scenario and the yesterday-is-today scenario.
SCREEN 7.28
Note
From a performance point of view, this design improves upon the two earlier options, because it places IO_SREG and IO_SOFF closer to the fact table.
Performance is, of course, one of the major concerns in data warehousing. Here are some guidelines for dealing with this issue:
- If IO_SREG and IO_SOFF data are included in the transaction data, as shown in Table 7.1, use IO_SREG and IO_SOFF as dimension characteristics instead of characteristic attributes.
| IO_CUST | IO_SREG | IO_SOFF | IO_SREPN2 | IO_MAT | IO_PRC | 0UNIT | IO_QUAN | IO_REV | 0CALDAY |
|---|---|---|---|---|---|---|---|---|---|
| CUST001 | EAST | ATLANTA | SREP01 | MAT001 | 2 | CS | 1 | 2 | 19980304 |
| CUST002 | EAST | NEW YORK | SREP02 | MAT002 | 2 | CS | 2 | 4 | 19990526 |
| CUST002 | EAST | NEW YORK | SREP02 | MAT003 | 5 | CS | 3 | 15 | 19990730 |
| CUST003 | EAST | NEW YORK | SREP03 | MAT003 | 5 | CS | 4 | 20 | 20000101 |
| CUST004 | MIDWEST | DALLAS | SREP04 | MAT004 | 50 | EA | 5 | 250 | 19991023 |
| CUST004 | MIDWEST | DALLAS | SREP04 | MAT005 | 100 | EA | 6 | 600 | 19980904 |
| CUST004 | MIDWEST | DALLAS | SREP04 | MAT005 | 100 | EA | 7 | 700 | 19980529 |
| CUST005 | MIDWEST | CHICAGO | SREP05 | MAT006 | 200 | EA | 8 | 1600 | 19991108 |
| CUST006 | MIDWEST | CHICAGO | SREP06 | MAT007 | 20 | EA | 9 | 180 | 20000408 |
| CUST007 | MIDWEST | CHICAGO | SREP07 | MAT008 | 3 | DZ | 10 | 30 | 20000901 |
| CUST007 | MIDWEST | CHICAGO | SREP07 | MAT008 | 3 | DZ | 1 | 3 | 19990424 |
| CUST008 | MIDWEST | DENVER | SREP08 | MAT008 | 3 | DZ | 2 | 6 | 19980328 |
| CUST008 | MIDWEST | DENVER | SREP08 | MAT009 | 2 | CS | 3 | 6 | 19980203 |
| CUST008 | MIDWEST | DENVER | SREP08 | MAT010 | 1 | LB | 4 | 4 | 19991104 |
| CUST009 | WEST | LOS ANGLES | SREP09 | MAT011 | 1.5 | LB | 5 | 7.5 | 20000407 |
| CUST010 | WEST | SEATTLE | SREP10 | MAT011 | 1.5 | LB | 6 | 9 | 20000701 |
| CUST010 | WEST | SEATTLE | SREP10 | MAT011 | 1.5 | LB | 7 | 10.5 | 19990924 |
| CUST010 | WEST | SEATTLE | SREP10 | MAT012 | 2 | LB | 8 | 16 | 19991224 |
| CUST010 | WEST | SEATTLE | SREP10 | MAT013 | 3 | CS | 9 | 27 | 20000308 |
| CUST011 | WEST | SEATTLE | SREP10 | MAT014 | 1 | LB | 10 | 10 | 19980627 |
| CUST012 | SREP11 | MAT014 | 2 | LB | 1 | 2 | 19991209 | ||
| CUST012 | SREP11 | MAT015 | 3 | CS | 2 | 6 | 19980221 | ||
| CUST012 | SREP11 | MAT015 | 2 | CS | 3 | 6 | 20000705 | ||
| CUST012 | SREP11 | MAT015 | 3.5 | CS | 4 | 14 | 20001225 |
- If IO_SREG and IO_SOFF are frequently used for navigation, use IO_SREG and IO_SOFF as dimension characteristics instead of characteristic attributes.
When considering the dimension in which a characteristic should be placed, follow these two guidelines:
- If the characteristics, such as IO_SREG, IO_SOFF, and IO_SREPN2, have a one-to-many relationship, group them in the same dimension.
- If the characteristics, such at IO_MAT and IO_CUST, have a many-to-many relationship, group them in different dimensions. In some special cases, when the combinations of the relations are small, such as materials and colors, you might consider grouping them within one dimension.
Another advantage of this InfoCube design is that we can create aggregates on IO_SREG and IO_SOFF. As in Alternative I, however, the levels of the sales organization are fixed in Alternative II.