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

Exercise No. 41: Getting Mails at Home (log)

This exercise is about getting mails at home.

A user who is of a certain type gets a mail. The mail is delivered by an Internet Service Provider (ISP) and may be encrypted by a certain encryption standard. The mail itself is of a certain type and belongs to a certain group (parallel hierarchy). The time dimension consists of month and quarter.

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

Table L.E.44.1 - getting mails

There are two key figures in this example, number of gets and number of users. Each mail has an additional attribute "M_ma_siz", the size of the mail and each mail group has an additional attribute "G_gr_des", the description of the group.

Please draw the Classic Star and 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 "mail" features a parallel hierarchy at level 1 ("type" and "group"); 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 ("number of gets" and "number of users") in this example:

Figure L.E.44.1 - The Classic Star Schema

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

Figure L.E.44.2 - The Fact Constellation Schema

The normalized dimension tables, neglecting the tables for the total aggregation level:

Figure L.E.44.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:

Figure L.E.44.4 - The Snowflake Schema

Exercises List