Dimension Modeling for the Hotel Industry

About the Business:

To illustrate the concept of dimension modeling, I would like to take the example of the Hospitality industry. And to take a more tangible example, I would like to focus on Hilton Worldwide Holdings (HLT from now on). HLT encompasses a large portfolio of hotels and resorts worldwide. It was founded by Conrad Hilton in 1919 in Cisco, Texas, and later bought over by the Blackstone Group for about $26 billion in 2007. It is the sixth largest hotel chain globally by the number of Hotels.

Performance Metrics:

Now if I was the CEO of Hilton Worldwide (Christopher J. Nassetta), and I wanted to see some performance metrics for HLT I would probably want to see some of the points below:

* Which line of hotels is the most profitable? - To find out which hotel chain is raking in the most revenue.

* Which line of hotels is the most popular? - To find out which hotel chain is attracting the most customers.

* Which location-segment is the most popular? - To find out which locations do customers frequently visit.

* Which market-price segment is the most popular? - To find out segment is the most popular amongst Luxury, Upscale, Midscale, Economy
  
* What is the occupancy of a specific hotel for a particular time period? (Paid Rooms Occupied/Rooms Available) - To find out how occupied is a hotel during a time period.

* What is the Revenue per Available Room for a specific hotel chain? (Total Room Revenue / Total Rooms Available) - To find out the revenue earned per room in a given period.

* What is the Average Daily Rate for a particular hotel? (Rooms Revenue / Paid Rooms Occupied) - To find out the average rental income per paid occupied room in a given time period.

There might be many more metrics but let's focus on these for now. Now to get all the above information we will have to capture a lot of data. Particularly, we will need data of bookings done at hotels, the hierarchy of hotels (which chain do they belong to), the revenue paid by customers for the bookings (and the discount), the number of rooms categorized by class in each hotel, etc. The definition of "revenue" might be different for each business segment but for this article, let's assume the revenue is the amount a customer pays for a hotel room.

Dimensional Modeling:

To accommodate the lowest level of facts, we will be capturing the booking data i.e. transaction data of each customer. Measures like Revenue per available room, Average Daily rate can be derived by the BI tool based on the data captured. Revenue over a time period can be done by adding the revenue obtained from each room of a hotel over a period categorized by date (since all dimension models should have a date dimension). We can also have a periodic snapshot table over a month or over a quarter identifying the revenue and the average occupancy over a period.

The dimensions for such a model would include the following:

• Hotel Chain Dimension
• Hotel Dimension
• Customer Dimension
• Date Dimension
• Room Type Dimension
• Deal Dimension

A high level dimensional model for the hotel industry would look something like this: