Studierende stehen vor dem LC und blicken lächelnd einer Kollegin mit einer Mappe in der Hand nach.

Exercise No. 31: Shipments (log)

This cube stores data about batches beeing shipped to certain destinations via different modes of transportation. A cost center group is responsible for each shipment.

We have five dimensions with the following hierarchical structure:

Table L.E.68.1 - shipments

There is just one key figure, the "costs" incurred. Each batch has its weight as an additional attribute ("B_ba_weight").

Please draw the Classic Star and the Fact Constellation Schema. Then draw the example's dimension tables partitioned and normalized and use them to build a Snowflake Schema!

Solution

The central fact table and five dimension tables build the example's Classic Star Schema:

Figure L.E.68.1 - The Classic Star Schema

In this case the Fact Constellation Schema has only eight fact tables (1x1x2x2x2):

Figure L.E.68.2 - The Fact Constellation Schema

Due to the low number of hierarchy levels in this example the partitioned and normalized dimension tables look pretty much the same: (the tables for the total aggregation level have been neglected in the drawings)

Figure L.E.68.3 - Dimension tables - partitioned (left) and normalized (right)

The Snowflake Schema is a combination of the Fact Constellation Schema's fact tables and the dimension tables either partitioned or normalized:

Figure L.E.68.5 - The Snowflake Schema with the dimension tables partitioned

This exercise is part of a case study: dfm - apa - log

Exercises List