3

I work for a hotel company and I have set up a fact table with the granularity of a stay night for each guest, e.g. if a guest stays for 3 nights, there would be a row for each night of the stay.

What I am trying to do is create a measure for the occupancy percentage (rooms booked divided by available rooms).

I have a column in the fact table that says how many rooms the hotel has, but just summing up that value doesn't work because then it is just multiplying the number of rooms by the number of guests. So I need to sum up the total guests and then divide by the number of rooms that that particular hotel has. Does this make sense?

[Measures].[On The Books] / [Measures].[Rooms Available]

The SQL for this would this:

SELECT  stay.PropertyKey, prop.RoomsAvailable, stay.StayDateKey, COUNT(stay.Confirmation) AS Confirmation,
        CAST(COUNT(stay.Confirmation) AS DECIMAL(13,9)) / CAST(prop.RoomsAvailable AS DECIMAL(13,9)) AS OccupancyPercentage
    FROM dbo.FactStayNight stay
    INNER JOIN
    (
        SELECT DISTINCT PropertyKey, RoomsAvailable
            FROM dbo.FactStayNight
    ) prop
        ON stay.PropertyKey = prop.PropertyKey
            GROUP BY stay.PropertyKey, stay.StayDateKey, prop.RoomsAvailable
Femmer
  • 133
  • 12

1 Answers1

2

Your fact table is good, apart from the column with total number of rooms. The fact row is at the granularity level "Room", but the total number of rooms is at granularity level "Entire Hotel". (You can imagine a "Real estate assets" hierarchy dimension, assuming you don't have one:

Hotel Floor Room )

Possible solutions:

  1. Add a "number of rooms" available in your Date dimension, at the Day level (strictly, "Night" level). This will sum commensurably with COUNT(Guests staying on that day). You could even adjust this number to reflect e.g. rooms under repair in particular periods.
  2. You could implement a Room dimension, with each guest's Fact_NightStayed assigned to a Room. Then make what is technically called a "headcount" table, just like your Fact_NightStayed. But this table would be a "roomcount" table: a row indicates that a room exists on a particular day (or, if you decide, that a room exists and is usable i.e. not broken/being repaired). Pre-populate this table with one row per room per date, into the future up to a date you decide (this would be an annual refresh process). Then, joining Fact_NightStayed to Fact_RoomCount, your measure would be COUNT(NightStayed)/COUNT(RoomCount).

Watch out for aggregating this measure (however you implement it) over time: the aggregation function itself from the Day leaf level up the Date hierarchy should be AVG rather than SUM.

SebTHU
  • 1,385
  • 2
  • 11
  • 22
  • 1. This would not work as each hotel has a different amount of rooms. 2. This could work, I will try it and let you know. Thanks! – Femmer Jan 28 '20 at 18:06
  • I have created a new fact table with the following breakdown PropertyKey, DateKey, AvailableRooms I guess I do not know how to join these in the measure creation. When I just do a simple measure of [Measures].[On The Books] / AVG([Measures].[Rooms Available]) it does a total of RoomsAvailable still. How do I join it by date and property? – Femmer Jan 28 '20 at 19:03
  • I am very new to MDX, so thank you in advance. We are just starting out in the Cube development. – Femmer Jan 28 '20 at 19:08
  • For the simplest measure calculation Occupancy%=(RoomsOccupied/RoomsAvailable), your new fact table needs to be at the same granularity level as your existing Fact_NightStayed table. Otherwise when you join, each row will have NightsStayed=1, RoomsAvailable=58 (say there are 58 rooms in the hotel on that date). Both of these will be summed, which is what you seem to be getting. – SebTHU Jan 31 '20 at 13:15
  • Alternatively, with your PropertyKey-level Available Rooms fact table, you could set the aggregation function of AvailableROoms to MAX rather than SUM. That will have bad knock-on effects however when you aggregate it across other dimensions (e.g. over longer time periods than a day). Fixable with calculated measures and SCOPE statements, but that's very complicated. – SebTHU Jan 31 '20 at 13:18