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

Exercise No. 57: E-Commerce (dfm)

In this simplified E/R schema a customer pays for a product by credit card via a webbrowser. The customer belongs to a certain customer group and the product is of a certain type. The transaction is encrypted which is done by a certain encryption standard. The time dimension consists of day, week and month.

Figure D.E.48.1 - e-commerce

Please identify the fact of interest and build the attribute tree. Some attributes are not of interest for this data warehouse: We are not interested in the date of birth of a customer and the version of a webbrowser. Other 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 products.

Solution

Entity PAYS 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.48.2 - The attribute tree

Customer, product, credit card, webbowser and encryption are defined as dimensions, dimension day is introduced as a range of the date attribute. Sum is defined as a fact attribute and number of products is added; remember number of products is semi-additive!

The description of a product cannot be used for aggregation and is therefore tagged as a non-dimension attribute.

Figure D.E.48.3 - The fact schema

sum = SUM(PAYS.sum)

number of products = COUNT(PAYS)

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

Exercises List