Design a data warehouse to manage a hotel chain and to address the described issues.
Problem specifications
A big hotel chain has over 500 hotels of different categories all over the world. Every day in the data base of each hotel, information on free, reserved, and unavailable rooms and corresponding customers is stored. Rooms may be unavailable due to maintenance.
The hotel chain managers would like to build a data warehouse to analyze room states and income.
The hotel chain managers would like to know for each hotel every day:
- the percentage of reserved rooms
- the percentage of free rooms
- the percentage of unavailable rooms
The income and the percentage of rooms must be known according to:
- geographical location of the hotel (state, region, and province)
- hotel category (5 stars, 4 stars, …)
- room features (number of beds, TV, whirlpool bath, …)
- date, day of the week, month, year
The hotel chain managers would like to analyze the daily, holiday, monthly and yearly income.
Some frequent queries the managers would like to execute are the following.
a) In 2005, for each state and month, analyze the portion of rooms which are reserved, free, and unavailable.
b) In 2005, for each state, analyze the portion of rooms which are reserved. Associate a rank to each state according to the portion of reserved rooms for that state in 2005 with respect to all the rooms for that state. The state with the highest ratio of reserved rooms in 2005 must rank first.
c) In 2005, for each state and month, analyze the income of 4-star hotels and the cumulative income of 4-star hotels.
d) For each state and year, analyze the total income of public holidays.
e) In 2005, for each hotel, analyze the total income of the rooms with satellite TV and whirlpool bath.
Design
1) Design the data warehouse according to the specifications; in particular, the designed data warehouse must promptly answer to all the frequent queries.
The following information is also known:
- the data warehouse has the information of 2005 and 2006
- number of states: 40
- number of cities: 400
- number of hotels: 500
- number of different room features: 8
2) Possible changing dimensions must be properly handled: decide how to address such issue.
3) Use the extended SQL language to answer frequent queries (c) and (e).
4) Considering the designed data warehouse and its cardinalities, decide whether and which materialized views and/or indexes are convenient to improve response time of the frequent queries (consider all the frequent queries). Explain reasons for your choices.