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

Exercise No. 23: Travelling (log)

We will now build a logical model for the "Travelling" case.

We have four dimensions with the following hierarchical structure:

Table L.E.47.1 - Travelling

There are two key figures in this example, "expenses" and "number of persons". Each person has an additional attribute "P_pe_add", the person's address, each type has an additional attribute "P_ty_des", the description of a type and each class has an additional attribute "P_cl_des", the description of a class.

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

Table L.E.47.1 - Travelling

There are two key figures in this example, "expenses" and "number of persons". Each person has an additional attribute "P_pe_add", the person's address, each type has an additional attribute "P_ty_des", the description of a type and each class has an additional attribute "P_cl_des", the description of a class.

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

Solution

This case's Classic Star Schema has five tables, a central fact table and four dimension tables. The latter 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 in this example ("expenses" and "number of persons"):

Figure L.E.47.1 - The Classic Star Schema

The Fact Constellation Schema for this case consists of four dimension tables and 27 fact tables (1x3x3x3):

Figure L.E.47.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.47.3

Figure L.E.46.3 - DT normalized (right)

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

Exercises List