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

Exercise No. 57: E-Commerce (log)

We now build a logical model for the "e-commerce" case.

We have six dimensions with the following hierarchical structure:

Table L.E.48.1 - e-commerce

There are two key figures in this example, "sum" and "number of products". Each product has an additional attribute "P_pr_des", the description of a product.

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

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 ("sum" and "number of products") in this example:

Figure L.E.48.1 - The Classic Star Schema

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

Figure L.E.48.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.48.3 - DT partitioned

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

Exercises List