Homework #1

Data Warehouse

Topic: Dimensional modeling in the data warehouse

Regarding the star example below, answer the following questions.

1. Identify the facts or the metrics.

2. List the business dimensions as well as the primary key of each one of them. 3. Regarding the fact table, identify the P.K. and the F.K.s

Exercise 2

A data warehouse of a bus company contains information about bus segments. It consists of six dimensions, namely, departure station, arrival station, trip, bus, arrival time, and departure time. It also has three measures, namely, number of passengers, duration, and distance (number of kilometers).

Draw a Multi-Dimensional schema for the bus data warehouse.

Exercise 3

Design the star schema to analyse the employee salary according to their age (by year or by decade), their study level and the geographic location of the entreprise. The study level analysis can be performed according to the higher study level reached (primary, secondary school, university level) or according to the last obtained degree (primary degree, secondary degree, bachelor, master, PhD). The geographic entreprise location analysis can be conducted by city, region, or country.

Exercise 4

1) The manager of an international company would like to know the quantity sold for each product and for each store by day, week, month, and year. For each store, the manager would like to know its corresponding city and country.

You have to design a star schema to achieve the goal of this manager.

1

2) If you think that some of your proposed dimension tables in Ql, are not normalized, you have to convert your star schema into snowflake schema to normalize these dimension tables.

Exercise 1 Regarding the star exarnple below, answer the following questions. 1. Identify the facts or the metrics. 2. List the business dimensions as well as the primary key of each one of them. 3. Regarding the fact table. identify the P.K. and the F.K.s Exercise 2 A data warehouse of a bus company contains information about bus segments. It consists of six dimensions, namely, departure station, arrival station, trip, bus, arrival time, and departure time. It also has three measures, namely, number of passengers, duration, and distance (number of kilometers). Draw a Multi-Dimensional schema for the bus data warehouse. Exercise 3 Design the star schema to analyse the employee salary according to their age (by year or by decade), their study lovel and the geographic location of the entreprise. The study level analysis can be performed according to the higher study level reached (primary, secondary school, university level) or according to the last obtained degree (primary degree, secondary degree, bachelor, master, PhD). The geographic entreprise location analysis can be conducted by city, region, or country. Exercise 4 1) The manager of an international company would like to know the quantity sold for each product and for each store by day, week, month, and year. For each store, the manager would like to know its corresponding city and country. You have to design a star schema to achieve the goal of this manager. 1 2) If yow think that some of your groposed dimenaion tables in Q, are hot normalired, you have in comvert your star sehema into showflake schema to normalize these dimerision tables