Project 1: Data Visualization

| Power BI | PowerPoint |

A company owning multiple hotel chains was facing business challenges and sought to leverage data intelligence to drive revenue growth. I was tasked with creating a revenue dashboard displaying key performance metrics, some of which are specific to the hospitality industry.

The Business Objective:

Create a dashboard that can answer level I-III questions, allowing upper management to make strategic decisions faster. The revenue dashboard must provide surface-level overviews with the capability to drill down dynamically.

The Data:

The data I was given came in 5 separate databases: 3-dimensional tables and 2 fact tables, a star schema.

The 3 dimension tables include:

Dates: Indicating days of the week, along with week numbers of all dates in the sample data. It’s to be noted that hotels consider weekdays Sunday-Thursday, and Weekends are Friday-Saturday.
Hotels: Shows the various properties and their IDs, and whether they’re a “business” or “luxury” hotel.
Rooms: Shows the different room types

The 2 fact tables:

Bookings: Displayed records of each booking, its status, check-in, and check-out dates.
Aggregated Bookings: Combined all the data per hotel property.

Cleaning:

I inserted the data into Power Query to clean up the tables and to create some calculated fields. Most notably, I made a “Capacity” dimension within the aggregate bookings fact table, which took the total rooms per property and subtracted out the rooms unavailable due to maintenance and other issues.

Relationships:

Next, I created the relationships within the data utilizing Power Pivot, setting up the star schema. These relationships allow for the data to have granular and dynamic relationships when filtering, which will be useful for drilling down into the data.

KPIs:

To prepare for the dashboard creation, I had to put on my mathematician hat and pour a cup of coffee. The client wanted some industry-specific key performance indicators including (with DAX format):

ADR (Average Daily Rate): Essentially, the average price for a room. = Revenue / Total Bookings

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

Revenue = Sum of revenue_realized from Booking table (in Rs)

Revenue = SUM(fact_bookings[revenue_realized])

Total bookings = Count of booking_id from Bookings table

Total Bookings = COUNT(fact_bookings[booking_id])

Avg rating = Average of ratings from Bookings table

Average Rating = AVERAGE(fact_bookings[ratings_given])

Total Capacity = Sum of capacity on Aggregated Bookings table

Total Capacity = SUM(fact_aggregated_bookings[capacity])

Occupancy rate = Total successful bookings / Capacity

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

Total successful bookings = Sum of successful bookings from Agg. Bookings table

Total Successful Bookings = SUM(fact_aggregated_bookings[successful_bookings])

Total cancelled bookings = Count of cancelled bookings from Bookings table

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

Cancellation rate = Total cancelled bookings / Total bookings

Cancellation % = DIVIDE([Total Cancelled Bookings], [Total Bookings])

DSRN (Daily Sellable Room Nights) = Total Capacity / # of days

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

Then I did the Week/Week % change for all of the most important KPIs

Occupancy WoW change % = 
Var selv = IF(HASONEFILTER(dim_date[wn]),SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))
var revcw = CALCULATE([Occupancy %],dim_date[wn]= selv)
var revpw =  CALCULATE([Occupancy %],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

return


DIVIDE(revcw,revpw,0)-1

ADR WoW change % = 
Var selv = IF(HASONEFILTER(dim_date[wn]),SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))
var revcw = CALCULATE([ADR],dim_date[wn]= selv)
var revpw =  CALCULATE([ADR],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

return


DIVIDE(revcw,revpw,0)-1

Revenue WoW change % = 
Var selv = IF(HASONEFILTER(dim_date[wn]),SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))
var revcw = CALCULATE([Revenue],dim_date[wn]= selv)
var revpw =  CALCULATE([Revenue],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

return


DIVIDE(revcw,revpw,0)-1

Revpar WoW change % = 
Var selv = IF(HASONEFILTER(dim_date[wn]),SELECTEDVALUE(dim_date[wn]),MAX(dim_date[wn]))
var revcw = CALCULATE([RevPAR],dim_date[wn]= selv)
var revpw =  CALCULATE([RevPAR],FILTER(ALL(dim_date),dim_date[wn]= selv-1))

return


DIVIDE(revcw,revpw,0)-1