Boost Hotel Insights with Power BI: Transforming Data into Dashboards with Star Schema

As I dive deeper into Week 4 of the Cyber Shujaa program, I'm learning how to turn raw data into actionable insights. In this article, I walk you through how I transformed raw hotel booking data into a clean star schema, developed meaningful DAX measures, and built an interactive Power BI dashboard all designed to support hotel business intelligence and decision-making.

This hands-on project is part of my practical learning journey in data analytics and visualization under the Cyber Shujaa program, and it's equipping me with real-world skills in data modeling, business reporting, and insights generation.

Step 1: Load and Transform Data

Key Datasets Used:

• dim_hotel: Hotel metadata (property name, location, star rating)

• dim_rooms: Room categories and pricing

• dim_date: Full date dimension

• fact_bookings: Raw booking transactions

• fact_aggregated_bookings: Pre-summarized booking data by date/property/room

Transformations in Power Query:

• Ensured proper data types (e.g., check_in_date as date, IDs as whole numbers)

• Renamed columns for consistency

• Created Date column in dim_date to match check_in_date in fact tables

• Verified foreign key integrity (e.g., room_category matches dim_rooms[room_id])

Step 2: Build the Star Schema Model

Relationships

Star Schema Layout

• Fact Tables (center): fact_bookings, fact_aggregated_bookings

• Dimension Tables (surrounding): dim_hotel, dim_rooms, dim_date

This model ensures fast queries, clean DAX, and long-term scalability.

Step 3: Creating Useful DAX Measures and Columns

Key DAX Measures

daxCopyEditRevenue = SUM(fact_bookings[revenue_realized])

Total Bookings = COUNT(fact_bookings[booking_id])

Total Capacity = SUM(fact_aggregated_bookings[capacity])

Total Succesful Bookings = SUM(fact_aggregated_bookings[successful_bookings])

Occupancy % = DIVIDE([Total Succesful Bookings], [Total Capacity], 0)

Average Rating = AVERAGE(fact_bookings[ratings_given])

No of days = DATEDIFF(MIN(dim_date[date]), MAX(dim_date[date]), DAY) + 1

Total cancelled bookings = 
    CALCULATE([Total Bookings], fact_bookings[booking_status] = "Cancelled")

Cancellation % = DIVIDE([Total cancelled bookings], [Total Bookings])

Total Checked Out = 
    CALCULATE([Total Bookings], fact_bookings[booking_status] = "Checked Out")

Total no show bookings = 
    CALCULATE([Total Bookings], fact_bookings[booking_status] = "No Show")

No Show rate % = DIVIDE([Total no show bookings], [Total Bookings])

Booking % by Platform = 
    DIVIDE(
        [Total Bookings],
        CALCULATE([Total Bookings], ALL(fact_bookings[booking_platform]))
    ) * 100

Booking % by Room class = 
    DIVIDE(
        [Total Bookings],
        CALCULATE([Total Bookings], ALL(dim_rooms[room_class]))
    ) * 100

ADR = DIVIDE([Revenue], [Total Bookings], 0)

Realisation % = 1 - ([Cancellation %] + [No Show rate %])

RevPAR = DIVIDE([Revenue], [Total Capacity])

DBRN = DIVIDE([Total Bookings], [No of days])

DSRN = DIVIDE([Total Capacity], [No of days])

Calculated Columns

wn = WEEKNUM(dim_date[date]) #get the week number


day type = 

 Var wkd = WEEKDAY(dim_date[date],1)

 return
 IF(
 wkd>5,""Weekend"",""Weekday"")

Use fact_aggregated_bookings for faster visuals when analyzing large volumes.

Step 4: Building the Interactive Dashboard

The dashboard was designed to empower hotel managers with real-time, multi-location visibility.

Key Visuals:

• KPI Cards: Revenue, Occupancy %, ADR, RevPAR, Realisation, DSRN

• Line Chart: Monthly revenue trends

• Line and stacked column chart: Realisation % and ADR by platform

• Donut chart: % revenue by category

• Table: Property by key metrics

Interactive Features:

• Slicers: Hotel, Room Class, Date Range

• Tooltips: On hover, show booking count, average rating, and stay duration

Business Insights Gained

• Identified seasonal revenue patterns

• Compared weekday vs weekend performance

• Analyzed cancellation/no-show trends

• Measured realized revenue vs potential

• Ranked performance by room class and platform

Conclusion

By following a structured approach:

• Built a clean star schema model

• Defined clear, impactful DAX measures

• Delivered a scalable and insightful dashboard for hotel decision-makers

This project is a great example of how Power BI + strong data modeling = better business decisions.