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

Exercise No. 40: Compact Disc (log)

In the following exercise a customer asks a store assistant for a compact disc via different modes of communication (phone, face to face, email, etc.). The compact disc is of a certain type and is released by a certain artist (parallel hierarchy). The artist belongs to a certain music company. The time dimension consists of day, week and month.

We have five dimensions with the following hierarchical structure (the dimension "compact disc" features a parallel hierarchy at level 1):

Table L.E.35.1 - compact disc

There are two key figures in this example, "solved" and "no. of inquiries". Each compact disc has an additional attribute "C_cd_tit", the title of the compact disc.

Please draw the Classic Star, create the dimension table "compact disc" and draw the Fact Constellation Schema. Then draw the example's dimension tables normalized and use them to build a Snowflake Schema!

Solution

The Star Schema for this case includes a central fact table and five dimension tables. Dimension "compact disc" features a parallel hierarchy at level 1 ("type" and "artist"); The dimension tables consist of a generated key as well as a key and a text field for each hierarchy (except the total aggregation) and the level attribute. The fact table uses the generated keys from the dimension tables as a combined key and contains two key figures ("solved" and "no. of inquiries") in this example:

Figure L.E.40.1 - The Classic Star Schema

To create dimension table "compact disc" we have to make up some compact discs and categories, each being a record in the table. The special thing about dimension table "compact disc" is the additional attribute "C_cd_tit". It is handled like a text attribute, there is no need for a key as it cannot be used for aggregation (it is not part of the hierarchy): The dimension table features a parallel hierarchy, to distinguish between "compact disc type" and "artist" at level 1, the usually numeric level attribute is replaced by a named one.

Table L.E.40.2 - The content of dimension table "compact disc "

The Fact Constellation Schema for this case consists of five dimension tables and 12 fact tables (1x1x1x4x3):

Figure L.E.40.2 - The Fact Constellation Schema

Normalizing the dimension tables completely means that only the key of the next higher level is kept in each table. Please note that the tables for the total aggregation level have been neglected in Figure L.E.40.3

Figure L.E.40.3 - DT normalized

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

Figure L.E.40.4 - The Snowflake Schema

Exercises List