Forum Discussion

justivan's avatar
justivan
Icon for Helper II rankHelper II
3 years ago

Is this a candidate for a new fact table? ( Room Inventory )

am employed at a Destination Management Company, working within the accommodation department. Recently, I was assigned the task of creating a report to monitor the allocation usage by our clients (operators). To simplify, I have excluded some tables from the screenshot below and only included the relevant ones. The fact table we use is called 'Booking,' which contains data on bookings broken down by date, covering the check-in and check-out dates. Originally, this information is stored in the database as a single row containing both check-in and check-out dates. I used Power Query to transform the data into a format where each date is represented individually.

In our hotel contracts, we have a limit ('N') on the number of rooms we can sell per day. We distribute these rooms among our clients (operators). For accounting purposes, the operators are categorized into different entities in our system, but the allocation we provide them is shared across these entities, referred to as OperatorGroup.

I have three measures in place for this report:

  1. Utilized allocation: This measure is obtained by simply counting the rows in the 'Booking' table.

  2. Allocation: To calculate this measure, we sum the 'Quantity' column from the 'RoomInventory' table.

  3. Remaining allocation: As the name suggests, this measure is derived by subtracting the 'Utilized allocation' from the 'Allocation.'

Now, my question is whether it would be appropriate to make the 'RoomInventory' table as a fact table. I removed the relationship from the screenshot to avoid clutter.

1 Reply