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

Exercise No. 60: Real Estate III (log)

This cube stores data of an information system of a real estate company.

We have six dimensions with the following hierarchical structure:

Table L.E.64.1 - real estate

"Price" and "Number of sales" are key figures. Each real estate has an additional attribute "R_re_addr", the address.

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

Solution

This case's Classic Star Schema has seven tables, a central fact table and six 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 ("price" and "no of sales") in this example:

Figure L.E.64.1 - The Classic Star Schema

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

Figure L.E.64.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.64.3.

Figure L.E.64.3 - DT normalized

The Snowflake Schema is a combination of the Fact Constellation Schema's fact tables and the normalized dimension tables:

Figure L.E.64.4 - The Snowflake Schema

Exercises List