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

Exercise No. 10: Cost Center (dfm)

The following E/R schema describes a cost center.

Building projects which are located in a certain country belong to a cost center. A certain cost accounting staff - who belongs to the cost accounting department - is responsible for each cost center. Each cost center has serveral employees assigned. An employee is of a certain type. The time dimension consists of month and year.

Figure D.E.4.1 - cost center

Please identify the fact of interest and build the attribute tree. Analysis will take place at the cost accounting department level, information about the individual cost accounting staff is not needed. Some attributes cannot be used for aggregation in a meaningful way. Those should be tagged as non-dimension attributes in the fact schema.

Then derive the fact schema from the tree by defining dimensions, fact attributes and hierarchies and build a glossary for the fact attributes if there are any.

Solution

Entity COST CENTER is chosen as the only fact. Its identifier becomes the root of the attribute tree. All other attributes or identifiers become nodes.

Figure D.E.4.2 - The attribute tree

Building project, cost accounting department and employee are defined as dimensions, dimension month is introduced as a range of the date attribute. The attribute costs becomes the only fact attribute.

The cost accounting staff sub-tree is grafted. The description of a building project and the date of birth of an employee cannot be used for aggregation and are therefore tagged as non-dimension attributes.

Figure D.E.4.3 - The fact schema

costs = SUM(COST CENTER.costs)

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

Exercises List