Multi-Cubes

Aggregates reduce data retrieval volume by creating small, virtual InfoCubes from the original InfoCube. A multi-cube seems to work in the opposite manner, by combining multiple InfoCubes to build a larger InfoCube. The larger InfoCube contains no data, however.

A good multi-cube example is BW Statistics, which will be discussed in Section 13.1.

The following steps show how to create a multi-cube.

Work Instructions

Step 1. Suppose we have another InfoCube for delivery, and its data model is as shown in Screen 8.27.

We would like to build a new InfoCube that brings the previous sales InfoCube and the new delivery InfoCube together to give a bigger picture of business, from sales to delivery.

 

 

SCREEN 8.27

Step 2. Follow the procedure in Section 2.5 to create a new InfoCube.

Enter a name and a description, select the MultiCube option, and then click to continue.

 

 

SCREEN 8.28

Step 3. The window in Screen 8.29 lists all available InfoCubes. Select the two InfoCubes IC_NEWBC2 and IC_NEWBC4 to build the new multi-cube. Click to continue.

 

 

SCREEN 8.29

Step 4. Select four characteristics from the Template table, and move them to the Structure table on the left, using .

 

 

SCREEN 8.30

Step 5. Select a time characteristic from the Template table, and move it to the Structure table on the left, using .

 

 

SCREEN 8.31

Step 6. Select two key figures from the Template table, and move them to the Structure table on the left, using .

 

 

SCREEN 8.32

Step 7. Click to set up the union conditions.

 

 

SCREEN 8.33

Step 8. Click to get help from BW.

 

 

SCREEN 8.34

Step 9. Accept the recommendation from BW, and then click to continue.

 

 

SCREEN 8.35

Step 10. After checking and activating the multi-cube, display its data model. As you see, it looks like a regular InfoCube.

 

 

SCREEN 8.36

Result

Run transaction SE11 to display the /BIC/FIC_DEMOMC table definition (Screen 8.37). Note that the multi-cube is an ABAP structure.

SCREEN 8.37

In the same way as we create queries for a basic cube, we can create queries for the multi-cube. Screen 8.38 shows the query definition, and Screen 8.39 shows its result.

SCREEN 8.38

SCREEN 8.39

As shown in Screen 8.39, when the characteristics selected by a multi-cube query are not shared across all underlying basic cubes, the query result will produce multiple lines. This is one drawback of the multi-cube technique. In this example, we want SREP08 and DAGE05 to appear in the same line.

Tracing the SQL statements during the query execution, we find out our multi-cube query was split into two subqueries on the underlying basic cubes:

SELECT "DP"."SID_0RECORDTP" AS "S0002",
 "DU"."SID_0UNIT" AS "S0005",
 "DT"."SID_0CALDAY" AS "S0004",
 "D2"."SID_IO_MAT" AS "S0006",
 "D1"."SID_IO_CUST" AS "S0007",
 "D3"."SID_IO_SREPN2" AS "S0008",
 COUNT (*) AS "1ROWCOUNT",
 SUM ("E"."/BIC/IO_QUAN") AS "IO_QUAN"
FROM "/BIC/E100014" "E",
 "/BIC/D100014P" "DP",
 "/BIC/DIC_NEWBC2U" "DU",
 "/BIC/DIC_NEWBC2T" "DT",
 "/BIC/DIC_NEWBC22" "D2",
 "/BIC/DIC_NEWBC21" "D1",
 "/BIC/D1000143" "D3"
WHERE "E"."KEY_100014P" = "DP"."DIMID" AND
 "E"."KEY_IC_NEWBC2U" = "DU"."DIMID" AND
 "E"."KEY_IC_NEWBC2T" = "DT"."DIMID" AND
 "E"."KEY_IC_NEWBC22" = "D2"."DIMID" AND
 "E"."KEY_IC_NEWBC21" = "D1"."DIMID" AND
 "E"."KEY_1000143" = "D3"."DIMID" AND
 (((("DP"."SID_0CHNGID" = 0)) AND
 (("DP"."SID_0RECORDTP" = 0)) AND
 (("DP"."SID_0REQUID" <= 1099))))
GROUP BY "DP"."SID_0RECORDTP",
 "DU"."SID_0UNIT",
 "DT"."SID_0CALDAY",
 "D2"."SID_IO_MAT",
 "D1"."SID_IO_CUST",
 "D3"."SID_IO_SREPN2"

and

SELECT "DP"."SID_0RECORDTP" AS "S0002",
 "DU"."SID_0UNIT" AS "S0005",
 "DT"."SID_0CALDAY" AS "S0004",
 "D2"."SID_IO_MAT" AS "S0006",
 "D1"."SID_IO_CUST" AS "S0007",
 "D3"."SID_IO_DAGE" AS "S0009",
 COUNT (*) AS "1ROWCOUNT",
 SUM ("E"."/BIC/IO_DQUAN") AS "IO_DQUAN"
FROM "/BIC/E100020" "E",
 "/BIC/D100020P" "DP",
 "/BIC/DIC_NEWBC4U" "DU",
 "/BIC/DIC_NEWBC4T" "DT",
 "/BIC/DIC_NEWBC42" "D2",
 "/BIC/DIC_NEWBC41" "D1",
 "/BIC/DIC_NEWBC43" "D3"
WHERE "E"."KEY_100020P" = "DP"."DIMID" AND
 "E"."KEY_IC_
 "E"."KEY_IC_NEWBC42" = "D2"."DIMID" AND
 "E"."KEY_IC_NEWBC41" = "D1"."DIMID" AND
 "E"."KEY_IC_NEWBC43" = "D3"."DIMID" AND
 (((("DP"."SID_0CHNGID" = 0)) AND
 (("DP"."SID_0RECORDTP" = 0)) AND
 (("DP"."SID_0REQUID" <= 1101))))
GROUP BY "DP"."SID_0RECORDTP",
 "DU"."SID_0UNIT",
 "DT"."SID_0CALDAY",
 "D2"."SID_IO_MAT",
 "D1"."SID_IO_CUST",
 "D3"."SID_IO_DAGE"

Each subquery used an aggregate: aggregate 100014 for the IC_NEWBC2 (sales) subquery, and aggregate 100020 for the IC_NEWBC4 (delivery) subquery.

Note

The aggregated data are stored in the E fact table, rather than the F fact table. Section 12.2.5, "InfoCube Compression," provides more information on the differences between these two types of fact tables.

The subqueries were processed in parallel. One work process sent the second subquery to the database without waiting for the database to return the result of the first subquery. After both subqueries received their data from the database, the same work process then presented the final result.