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

Exercise No. 9: Sales Process (dfm)

This E/R schema shows a sales process.

Customers buy products which can be part of a product group. These products are sold by a salesman who is responsible for a certain sales area. The time dimension consists of week, month and year.

Figure D.E.3.1 - sales process

Please identify the fact of interest and build the attribute tree. Let us assume that information about the attribute costs of a product group shall not be transferred into the data warehouse. Costs are only interesting for the cost accounting department. 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.

Please include an additional fact attribute counting the number of purchases and calculate the fact attribute revenue.

Solution

Entity BUYS 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.3.2 - The attribute tree

Customer, salesman and product are defined as dimensions, dimension week is introduced as a range of the date attribute.The attribute group of the dimension product is tagged as optional. The attribute revenue has to be calculated and becomes a fact attribute and number of purchases is added.

The information about costs is pruned. The address of a customer cannot be used for aggregation and is tagged as a non-dimension attribute.

Figure D.E.3.3 - The fact schema

revenue = SUM (BUYS.quantity*BUYS.price)

no. of purchases = COUNT (BUYS)

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

Exercises List