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

Exercise No. 48: Lecture at the MIT (log)

This case is about lectures at the MIT.

Lectures at the MIT are hold by a lecturer. This person can be an assistant or a professor (parallel hierarchy). Students attend a lecture. A lecture has a certain subject which belongs to a certain subject group and takes place in a certain lecture hall. This hall is part of a university building. The time dimension consists of day and week.

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

Table L.E.32.1 - lectures at the MIT

There is only one key figure "number of lectures" in this example. Each subject has an additional attribute "SU_su_des", the description of the project.

Please draw the Classic Star and the Fact Constellation Schema. Then draw the example's dimension tables partitioned.

Solution

The Star Schema for this case includes a central fact table and five dimension tables. Dimension "lecturer" features a parallel hierarchy at level 1 ("professor" and "assistant"); 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 no key figure in this example:

Figure L.E.32.1 - The Classic Star Schema

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

Figure L.E.32.2 - The Fact Constellation Schema

Partitioning the Classic Star's dimension tables by the level attribute results in one table for each hierarchy level (in each dimension). All tables include the fields of the higher levels, the level attribute itself is omitted:

Figure L.E.32.3 - DT partitioned

Exercises List