Table L.E.18.1 - Cost center accounting

"Amount" is the only key figure, representing the balance of the corresponding cost center. Each cost center has an additional attribute "C_cc_resp", the person that is responsible for the cost center.

Please draw the Classic Star Schema for this example, create the dimension tables "cost element" and "cost center", then build the Fact Constellation Schema.

Also draw the example's dimension tables partitioned and normalized.

Figure L.E.18.1 - The Classic Star Schema

Dimension table "cost element" has a parallel hierarchy, to distinguish between "cost element group" and "cost element type" at level 1, the usually numeric level attribute is replaced by a named one:

Table L.E.18.2 - The content of dimension table "cost element"

The special thing about dimension table "cost center" is the additional attribute "C_cc_resp". It is handled like a text attribute, there is no need for a key as it cannot be used for aggregation (it is not part of the hierarchy):

Table L.E.18.3 - The content of dimension table "cost center"

The Fact Constellation Schema for this case consists of the three dimension tables and 18 fact tables (3x3x2):

Figure L.E.18.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 in Figure L.E.18.3)

Figure L.E.18.3 - DT partitioned (left) and normalized (right)