The hotel chain operates 500 hotels in 40 states. Primary source of revenue is accommodations in hotel rooms. Each of hotels has between 30 and 500 rooms. There are different types of rooms (standard, suites, deluxe) of different sizes (small, medium, and large). Each room may incorporate optional features like refrigerator or kitchenette. The biggest challenge is how to price the rooms of different types. To determine how to price and market room types, analysts need to look at the use of hotel capacities over the time (occupancy rate or utilization rate). Occupancy rate is calculated as the ratio of occupied rooms to the total number of rooms. Some of the rooms can be temporarily unavailable for occupancy (e.g. renovation ). So, total number of rooms includes occupied rooms, not occupied rooms, and unavailable rooms. Hotels have OLTP databases with detailed information about hotel occupancy: Room_Type (Type, Description) Room (Number, Type, Size, Number_of_Beds, Bed_Type, Max_Occupants, Refrigerator, Kitchenette) Customer (ID, Name, Address) Occupancy (Date, Room_Number, Customer_ID, Number_Occupants, Number_Days, Daily_Price, Daily_Taxes) Unavailability (Date, Room_Number) We need to design a data warehouse that will help vice president of Sales and Marketing in pricing the rooms at the maximum the market will bear at close to 100% occupancy. If rooms are not filling, the Sales Department can modify the pricing or market more aggressively to bring customers in. Our primary objectives are: For any day, allow capacity utilization (occupancy rate) to be analyzed across room types or hotels. Over time, allow analysis of average utilization levels for specific hotels or room types. For each room type and hotel, capture the revenue for comparison to occupancy level (revenue is calculated as amount customer pays minus all taxes). Make sure that your design allows to deliver the following results: What was the occupancy rate of for each hotel in Virginia last night? What was last nights occupancy rate for each room type in Virginia hotels? What were the occupancy rates of Virginia hotels for July? What were average room revenues for different hotels during Christmas season