: Designing a Dimensional Database

Exercise #1
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 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’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.
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 dimensions.
Ask yourself, “What data characterizes the various aspects of that business event?”
3) Either sketch the star schema or make a list of the dimensions and the fact.
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?