BW Star Schema

In Chapter 1, we introduced the concept of a star schema. In Chapter 2, Screen 2.31 showed an InfoCube data model. Armed with this information, the question then becomes:

What does the InfoCube data model look like at the database level?

In this section, we will explore the relationships between database tables. They will give us a clear idea of what the star schema looks like in BW. Again, we use a step-by-step procedure.

Work Instructions

Step 1. Run transaction SE11, enter /BIC/FIC_DEMOBC as the fact table name, and then click .

 

 

Note

From Screen 4.3, we know that /BIC/FIC_DEMOBC is the name of the fact table.

If we are interested in only the table contents, and not the table definition, we can run transaction SE16 instead.
 

SCREEN 7.1

Step 2. The Check table column lists parent tables of the fact table. Double-click /BIC/DIC_DEMOBC3 to display the sales representative dimension table.

 

 

Note

BW uses D to name dimension tables.

• DP is for the data packet dimension. We will discuss its role in BW in Chapter 12.
• DT is for the time dimension.
• DU is for the unit dimension.

SCREEN 7.2

Step 3. The dimension table does not have any check tables, but it has a field called SID_IO_SREP. Click to display the table's contents.

 

 

SCREEN 7.3

Step 4. Click to execute.

 

 

SCREEN 7.4

Step 5. Notice that SID_IO_SREP 11 corresponds to DIMID 23.

From Screen 4.4, we know that DIMID 23 is the value of the field KEY_IC_DEMOBC3 in the first row of the table /BIC/FIC_DEMOBC.

Then what does SID_IO_SREP 11 represent?

 

 

SCREEN 7.5

Step 6. Repeat Step 1 to display the contents of IO_SREP's SID table, /BIC/SIO_SREP. This screen shows the SID table definition. Click to display the table's contents.

 

 

SCREEN 7.6

Note

Here SID is Surrogate-ID, not the System ID used to name an SAP system.BW uses S to name a characteristic's SID table.

Step 7. The contents of the SID table /BIC/SIO_SREP are displayed.

In this screen, we see that SID 11 corresponds to SREP01, a sales representative ID in the first record of Table 3.3.

 

 

SCREEN 7.7

Following the same approach, we can discover the relationships between the SID table /BIC/SIO_SREP, the master data table /BIC/PIO_SREP, and the text table /BIC/TIO_SREP. The contents of the latter two tables are shown in Screens 7.8 and 7.9.

Step 8. Repeat Step 1 to display the contents of IO_SREP's master data table, /BIC/PIO_SREP. This screen shows the table's contents.
 

SCREEN 7.8

Step 9. Repeat Step 1 to display the contents of IO_SREP's text table, /BIC/TIO_SREP. Screen 7.9 shows the table's contents.

 

 

SCREEN 7.9

Step 10. Repeat Step 1 to display the contents of IO_SREP's hierarchy table, /BIC/HIO_SREP. Screen 7.10 shows the table's contents.
 

Note

Screen 7.10 shows the contents of the hierarchy table, /BIC/HIO_SREP. Unlike the master data table and the text table, the hierarchy table does not link to the SID table. BW builds the hierarchy based on the information in the tables /BIC/IIO_SREP, /BIC/KIO_SREP, and /BIC/SIO_SREP.

SCREEN 7.10

Result

Based on our discussion, we can draw a simplified star schema as shown in Figure 7.1.

Figure 7.1. BW STAR SCHEMA FOR PART I INFOCUBE DESIGN

In Figure 7.1, the solid lines are the true masterdetail relationships, reinforced by foreign keys. The dashed lines are relationships that are maintained by ABAP programs, but not reinforced by foreign keys.

The dashed-line relationships allow us to load transaction data even when the database does not contain any master data. Screen 7.11 shows the InfoPackage used in Section 3.9. Under the Update parameters tab, notice the Always update data, even if no master data exists for the data option.

SCREEN 7.11

With this star schema in mind, let's look at three InfoCube design alternatives.