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 to check the new InfoCube. If it is valid, click to activate the new InfoCube.

 

 

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:

1. 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.

Table 7.1. SALES DATA

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

1. 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:

1. If the characteristics, such as IO_SREG, IO_SOFF, and IO_SREPN2, have a one-to-many relationship, group them in the same dimension.
2. 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.