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

Exercise No. 19: Service Management (log)

Continuing APA case "Service Management" we will now build the logical model.

We have four dimensions with the following hierarchical structure:

Table L.E.14.1 - Service management

The case involves two key figures: "costs" and "solved". Each problem has a detailed description ("P_pro_des"), each customer has his address ("C_cus_add") as an additional attribute.

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

Solution

The example's Classic Star Schema, including additional attributes in the "problem"- and "customer"-dimensions:

Figure L.E.14.1 - The Classic Star Schema

Four dimension tables and 24 fact tables (2x2x2x3) form the Fact Constellation Schema:

Figure L.E.14.2 - The Fact Constellation Schema

The normalized dimension tables, neglecting the tables for the total aggregation level:

Figure L.E.14.3 - DT normalized

The Snowflake Schema can be built by combining the fact tables of the Fact Constellation Schema and the normalized dimension tables:

Figure L.E.14.4 - The Snowflake Schema

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

Exercises List