Posted: June 10th, 2015

Emerging IT Infrastruchuer/Exercises on Dimensional Modelling:

Emerging IT Infrastruchuer/Exercises on Dimensional Modelling:

Exercises on Dimensional Modelling:
Exercise #1:
Given the table below represent it into a classification tree

Exercise #2:
Given a hierarchical clustering of distances in kilometers between some Italian cities below:

Question: Analyze the table below and designate three regions (North, Centre and South) and assign to each region its cities based on the closeness between these cities given the distance of each city from the sea which exist in the south region as follows: NA: 10km, BA= 40km, RM=219+10km, FIs=268+219+10, TOs=400+FIs, MIs=TOs+138

Exercise #3:
Consider the market basket data in the Table below.

Transaction ID    Items bought
1    Milk,Beer,Diapers

2    Bread, Butter, Milk
3    Milk, Diapers, Cookies

4    Beer, Cookies, Diapers
5    Milk, Diapers, Bread, Butter

6    Milk, Diapers, Bread, Butter

7    Bread, Butter, Diapers
8    Beer, Diapers

9    Milk, Diapers, Bread, Butter

10    Beer, Cookies

Question: What is the maximum number of association rules that can be extracted from this data?

Exercises on Dimensional Modelling:
Exercise #1:
Transform a two dimension table into a cube and answer question like slicing, dicing operations (see Lecture #2 on DWH slide 14 to 26)
Exercise #2:
You are the data design specialist on the data warehouse project team for a manufacturing company. Design a STAR schema to track the production quantities. Production quantities are normally analyzed along the business dimensions of product, time, parts used, production facility, and production run. State your assumptions.
Exercise #3:
Data Selection: Designing a Dimensional Database
The TU Hotel chain is a small chain with properties throughout Pennsylvania, Arizona, Florida, and New York.  They have a central database to store and track guest reservations. In 2008, they put cafes in many of their hotels, called “Café in the Hotel.” They have an order-tracking system that relays customer orders from the wait staff to the kitchen.
TU Hotels would like to use the data they have collected to better understand the performance of their hotels and cafes. They also have access to a database from the online review site “HotelComplainer.com.”
Your task is to design two dimensional data marts using data from those three databases. You will plan the star schema for each data mart by choosing the dimensions, facts, and attributes from the data contained in those databases. The relational schemas of those databases are on the following page.
NOTE: You will not actually come up with answers to these questions since you have no data. You’ll just be designing the star schemas that can address these questions about the business.
The questions to be addressed by each data mart are listed in the table below. You should include only the data fields you need in each data mart, but the data marts can contain some of the same data (i.e., the same fields can appear in both data marts).
To complete the exercise, you’ll need to perform the following steps:
1)    Identify the main business event for each data mart. This will be the fact.
Ask yourself, “What is the basic business event that generates the performance metric (revenue)?”
2)    Identify the attributes associated with the fact.
Ask yourself, “How is the business event quantified (measured)?”
3)    Identify the dimensions and their attributes.
Ask yourself, “What data characterizes the various aspects of that business event?”
4)    Either sketch the star schema or make a list of the dimensions and the fact and their attributes. You can use the examples in the slides as a guide.
Data Mart 1: Hotel Performance    Data Mart 2: Restaurant Performance
•    During which month are the most rooms rented?
o    Identify the “off season” (if any) for our hotels in Arizona, Florida, Pennsylvania, and New York
•    Which hotel generates the most (non-restaurant) revenue?
•    What is the average length of stay in hotels with 4.5 or more stars?
•    Do smokers stay longer than non-smokers?
•    For a given hotel, how many customers come from out of state?    •    Which hotel restaurant generates the most revenue?
•    Do the best rated hotels generate more restaurant revenue?
•    What is the most frequently ordered item in the Philadelphia metropolitan area?

Exercises on Dimensional Modelling:
Exercise #1:
Transform a two dimension table into a cube and answer question like slicing, dicing operations (see Lecture #2 on DWH slide 14 to 26)
Exercise #2:
You are the data design specialist on the data warehouse project team for a manufacturing company. Design a STAR schema to track the production quantities. Production quantities are normally analyzed along the business dimensions of product, time, parts used, production facility, and production run. State your assumptions.
Exercise #3:
Data Selection: Designing a Dimensional Database
The TU Hotel chain is a small chain with properties throughout Pennsylvania, Arizona, Florida, and New York.  They have a central database to store and track guest reservations. In 2008, they put cafes in many of their hotels, called “Café in the Hotel.” They have an order-tracking system that relays customer orders from the wait staff to the kitchen.
TU Hotels would like to use the data they have collected to better understand the performance of their hotels and cafes. They also have access to a database from the online review site “HotelComplainer.com.”
Your task is to design two dimensional data marts using data from those three databases. You will plan the star schema for each data mart by choosing the dimensions, facts, and attributes from the data contained in those databases. The relational schemas of those databases are on the following page.
NOTE: You will not actually come up with answers to these questions since you have no data. You’ll just be designing the star schemas that can address these questions about the business.
The questions to be addressed by each data mart are listed in the table below. You should include only the data fields you need in each data mart, but the data marts can contain some of the same data (i.e., the same fields can appear in both data marts).
To complete the exercise, you’ll need to perform the following steps:
1)    Identify the main business event for each data mart. This will be the fact.
Ask yourself, “What is the basic business event that generates the performance metric (revenue)?”
2)    Identify the attributes associated with the fact.
Ask yourself, “How is the business event quantified (measured)?”
3)    Identify the dimensions and their attributes.
Ask yourself, “What data characterizes the various aspects of that business event?”
4)    Either sketch the star schema or make a list of the dimensions and the fact and their attributes. You can use the examples in the slides as a guide.
Data Mart 1: Hotel Performance    Data Mart 2: Restaurant Performance
•    During which month are the most rooms rented?
o    Identify the “off season” (if any) for our hotels in Arizona, Florida, Pennsylvania, and New York
•    Which hotel generates the most (non-restaurant) revenue?
•    What is the average length of stay in hotels with 4.5 or more stars?
•    Do smokers stay longer than non-smokers?
•    For a given hotel, how many customers come from out of state?    •    Which hotel restaurant generates the most revenue?
•    Do the best rated hotels generate more restaurant revenue?
•    What is the most frequently ordered item in the Philadelphia metropolitan area?

Exercises on Dimensional Modelling:
Exercise #1:
Transform a two dimension table into a cube and answer question like slicing, dicing operations (see Lecture #2 on DWH slide 14 to 26)
Exercise #2:
You are the data design specialist on the data warehouse project team for a manufacturing company. Design a STAR schema to track the production quantities. Production quantities are normally analyzed along the business dimensions of product, time, parts used, production facility, and production run. State your assumptions.
Exercise #3:
Data Selection: Designing a Dimensional Database
The TU Hotel chain is a small chain with properties throughout Pennsylvania, Arizona, Florida, and New York.  They have a central database to store and track guest reservations. In 2008, they put cafes in many of their hotels, called “Café in the Hotel.” They have an order-tracking system that relays customer orders from the wait staff to the kitchen.
TU Hotels would like to use the data they have collected to better understand the performance of their hotels and cafes. They also have access to a database from the online review site “HotelComplainer.com.”
Your task is to design two dimensional data marts using data from those three databases. You will plan the star schema for each data mart by choosing the dimensions, facts, and attributes from the data contained in those databases. The relational schemas of those databases are on the following page.
NOTE: You will not actually come up with answers to these questions since you have no data. You’ll just be designing the star schemas that can address these questions about the business.
The questions to be addressed by each data mart are listed in the table below. You should include only the data fields you need in each data mart, but the data marts can contain some of the same data (i.e., the same fields can appear in both data marts).
To complete the exercise, you’ll need to perform the following steps:
1)    Identify the main business event for each data mart. This will be the fact.
Ask yourself, “What is the basic business event that generates the performance metric (revenue)?”
2)    Identify the attributes associated with the fact.
Ask yourself, “How is the business event quantified (measured)?”
3)    Identify the dimensions and their attributes.
Ask yourself, “What data characterizes the various aspects of that business event?”
4)    Either sketch the star schema or make a list of the dimensions and the fact and their attributes. You can use the examples in the slides as a guide.
Data Mart 1: Hotel Performance    Data Mart 2: Restaurant Performance
•    During which month are the most rooms rented?
o    Identify the “off season” (if any) for our hotels in Arizona, Florida, Pennsylvania, and New York
•    Which hotel generates the most (non-restaurant) revenue?
•    What is the average length of stay in hotels with 4.5 or more stars?
•    Do smokers stay longer than non-smokers?
•    For a given hotel, how many customers come from out of state?    •    Which hotel restaurant generates the most revenue?
•    Do the best rated hotels generate more restaurant revenue?
•    What is the most frequently ordered item in the Philadelphia metropolitan area?