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

Exercise No. 37: Sixt Car Rental Service (log)

This is an example about the Sixt car rental service.

Sixt cars are rent to customers who can be classified into customer groups (private, business, etc.). There's an option to rent a driver for the car as well. The car is serviced by a garage and belongs to a branch of the Sixt car rental service. This branch is responsible for a certain "renting" area and also belongs to the nationalwide operating Sixt car rental service (parallel hierarchy).

We have five dimensions with the following hierarchical structure (the third dimension features a parallel hierarchy at level 1):

Table L.E.22.1 - the Sixt car rental service

"Fee", "driven miles" and "no. of rents" are the key figures. Each customer has an additional attribute "C_cu_add", the customer's address and each driver has an additional attribute "D_dr_dob", the driver's date of birth.

Please draw the Classic Star Schema for this example, create the dimension tables "customer" , then build the Fact Constellation Schema. Also draw the example's dimension tables partitioned and use them to build a Snowflake Schema.

Solution

The Star Schema includes a central fact table and five dimension tables. Dimension "branch" features a parallel hierarchy at level 1 ("branch" and "branch area"). Dimension "customer" has an additional attribute "C_cu_add" at level 0, the customer's address. And dimension "driver" has an additional attribute "D_dr_dob" at level 0 as well, the driver's date of birth.

Figure L.E.22.1 - The Classic Star Schema

Here you can see a part of the dimension table "customer":

Table L.E.22.2 - The content of dimension table "customer"

The Fact Constellation Schema for this case consists of the five dimension tables and 12 fact tables (1x1x3x2x2):

Figure L.E.22.2 - The Fact Constellation Schema

The example's dimension tables drawn partitioned show the parallel hierarchy in a more obvious way: (the tables for the total aggregation level have been neglected in Figure L.E.22.3)

Figure L.E.22.3 - DT partitioned

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

Figure L.E.22.4 - The Snowflake Schema

Exercises List