Building a Service Industry Power BI Solution in Under 1 Hour

In this tutorial, Devin Knight walks viewers through the process of building a Power BI solution for the service industry, specifically using restaurant data. The goal is to demonstrate how to go from a raw dataset to an insightful, actionable Power BI report in under an hour. This process not only emphasizes the technical aspects of Power BI, but also the importance of asking the right questions to generate meaningful insights from data.

Key Questions to Drive Insights

To begin, Devin presents a set of critical questions that will guide the analysis process:

• Which restaurants are generating the most revenue?
• Which main entrée is the fastest to prepare?
• Which server is upselling the most?
• Which order type has the highest average total order value?

These questions help frame the dataset, guiding the Power BI professional in what metrics and dimensions to look for and what relationships to form in the data. This approach ensures that the final report answers the business’s most pressing questions.

Defining Metrics and Dimensions

Devin then focuses on the difference between metrics (quantitative data) and dimensions (descriptive data) as they apply to the questions posed. Key metrics include revenue, preparation speed, and upselling quantities, while dimensions like restaurant names, menu items, servers, and order types provide context to those metrics.

• Revenue will be a key metric for tracking restaurant performance.
• Speed of preparation for entrée items is essential for operational efficiency.
• Upselling can be tracked by measuring the quantity of items sold by each server.
• Order value can be segmented by order type, like dine-in or take-out, to understand revenue generation patterns.

After identifying the metrics, Devin introduces the concept of a star schema, which organizes the data into fact and dimension tables. This design allows Power BI to perform optimally when creating reports and performing analysis.

Creating a Star Schema

The star schema structure starts with the fact table, which contains the metrics, and then links it to various dimension tables that provide context. For this restaurant dataset, Devin creates tables for:

• Orders - the fact table containing metrics like revenue and order totals.
• Restaurants - a dimension table that categorizes data by restaurant.
• Servers - to analyze upselling performance.
• Menu Items - to track food item sales and preparation time.
• Date - crucial for time-based analysis like year-over-year comparisons.

Once these tables are defined, they are linked in Power BI’s model view. Devin advises on the best practices for designing these relationships and explains the difference between a star schema and a snowflake schema, with the former being the preferred method in Power BI.

Data Transformation with Power Query

Next, Devin dives into Power Query to manipulate and prepare the data. The data is transformed into the appropriate fact and dimension tables, ensuring that each table only contains the necessary information. For example:

• The restaurant dimension includes only the restaurant ID, name, and location.
• The server dimension includes server ID and server name.
• The menu item dimension includes menu item names and categories.
• The date dimension allows for time-based analysis.

Data cleaning and transformation in Power Query are key steps to ensure that the dataset is structured correctly for analysis. Devin also highlights a common mistake when working with the menu items table and how to fix it by removing unnecessary columns.

Building Relationships and Creating DAX Calculations

Once the data is clean, Devin demonstrates how to create relationships between the tables. For example, the orders table is linked to the date table using a one-to-many relationship, allowing for time-based analysis of order totals.

After setting up the relationships, Devin moves on to creating DAX (Data Analysis Expressions) measures to calculate important business metrics like:

• Total Revenue - the sum of all order amounts.
• Total Orders - the count of all orders.
• Average Order Value - the total revenue divided by total orders.
• Average Prep Time - the average time it takes to prepare items.
• Average Prep Time for Main Entrees - using the CALCULATE function to filter the data.

Creating Reports and Visuals

Devin finishes the session by building the actual reports. The first visual created is a card visual that shows the total revenue, average order value, and average prep time for the restaurant. Other visuals include:

• Column charts to compare total revenue by order type and restaurant performance.
• Tree maps to show menu item performance by revenue.
• Scatter charts to analyze server performance based on prep time, customer ratings, and order quantity.
• Bar charts to compare revenue and customer ratings by restaurant.

Devin also discusses how to improve report usability, such as creating hierarchies for better drilldowns and hiding unnecessary columns from the report view to simplify the user experience.

Conclusion

In just under an hour, Devin demonstrated how to take a raw dataset and transform it into an interactive, insightful Power BI report for a restaurant service industry. By focusing on key business questions and leveraging Power BI’s powerful modeling and visualization tools, viewers learned how to structure, analyze, and visualize data to make informed decisions.

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.