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

Exercise No. 31: Shipments (dfm)

The following simplified E/R-diagram describes the shipment of goods.

Batches belonging to certain groups are shipped to certain destinations in various countries via different modes of transportation. A certain cost center is responsible for each shipment. The time dimension consists of month and year.

Figure D.E.68.1 - Shipments

Let us assume that the attribute costs already includes all tariffs. Further information about the tariffs shall not be transferred into the data warehouse. Analysis will take place at the cost center group level, information about cost centers is not needed.

Please identify the fact of interest and build the attribute tree.

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

Solution

Entity SHIPMENT 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.68.2 - The attribute tree

Batch, destination and mode of transportation are defined as dimensions, dimension month is introduced as a range of the date attribute. Attribute costs is chosen as a fact attribute.

All information about tariffs is pruned, and the cost center sub-tree is grafted.

The weight of a batch cannot be used for aggregation and becomes a non-dimension attribute.

Figure D.C.68.3 - The fact schema

costs = SUM(SHIPMEMT.costs)

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

Exercises List