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

Exercise No. 50: Getting Mails (dfm)

The following E/R schema is about getting mails.

A user who is of a certain type gets a mail. The mail is delivered by an Internet Service Provider (ISP) and may be encrypted; this encryption is of a certain encryption standard. The mail itself is of a certain type and belongs to a certain group (parallel hierarchy). The time dimension consists of month and quarter.

Figure D.E.42.1 - getting mails

Please identify the fact of interest and build the attribute tree. Some of the attributes of a mail are not of interest for our data warehouse: sender and body can be neglected. We will only classify mails by an encryption standard. 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 two additional fact attributes; one counting the number of gets, and one counting the number of users.

Solution

Entity GETS 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.42.2 - The attribute tree

User, isp, mail and encryption are defined as dimensions, dimension day is introduced as a range of the date attribute.We decide we only need a classification of the encryption by the encryption standard and therefore graft encryption. We are not interested in information about the sender and the body of a mail and therefore prune them.

The dimension encryption standard is tagged as optional. We add two key figures, counting the number of gets and the number of users. Number of users is semi-additive, using the average operator along the date dimension.

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

Figure D.E.42.3 - The fact schema

no. of gets = COUNT(GETS)

no. of users = COUNT(GETS)

Exercises List