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

Exercise No. 34: Writing Articles (log)

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

We have four dimensions with the following hierarchical structure:

Table L.E.7.1 - Author writes an article

There is only one key figure in this example "no. of writings". The following additional attributes have to be considered: the author's address "AU_au_add", the lector's date of birth"LE_le_dob" and the article's title"AR_ar_tit"

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

Solution

This case's Classic Star Schema has six tables, a central fact table and five 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 in this example only one key figure "no of. writings":

Figure L.E.7.1 - The Classic Star Schema

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

Figure L.E.7.2 - The Fact Constellation Schema

The example's dimension tables drawn partitioned and normalized show the parallel hierarchy in a more obvious way: (the tables for the total aggregation level have been neglected)

Figure L.E.7.3 - DT partitioned

The Snowflake Schema is a combination of the Fact Constellation Schema's fact tables and the dimension tables either partitioned or normalized. In our case we have partitioned dimension tables:

Figure L.E.7.4 - The Snowflake Schema

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

Exercises List