End-to-End Process (PBI)

This is a simplified, generic version of a more comprehensive document I’ve crafted for numerous project stakeholders. You’re free to utilize it, but please provide attribution.

Prepared by : Christian Robles

February 28, 2025

End-to-End Process of Creating a Dashboard for a sample Multi-Chain Restaurant Group

This document outlines the comprehensive process of creating a Power BI dashboard for a multi-chain group of restaurant brands, focusing on key performance indicators (KPIs) and critical data analysis insights. The project utilizes data from the HR's CRM system, specifically a master list of employees and per-cutoff Excel files that capture employee location movements. The document details each step from problem definition to data loading, transformation, modeling, and visualization, culminating in a published dashboard ready for sharing.

1. Problem Definition

The primary objective of this project is to develop a dashboard that provides insights into employee movements, headcount metrics, and other relevant KPIs for a multi-chain restaurant group. Key questions to address include:

What are the current headcount figures?
How do employee movements affect operational efficiency?
What is the attrition and retention rate across different restaurant brands?

2. Identifying KPIs

To effectively analyze the data, the following KPIs were identified:

Total Headcount
Starting Headcount
Ending Headcount
Attrition Rate
Retention Rate
Growth Rate
Average Tenure
Age Group Distribution

3. Data Issues

Before proceeding with data loading, potential issues were identified:

Inconsistent data formats across different Excel files.
- Some date columns needs to be set to date using US locale.
Missing or blank cells in critical columns.
Variations in naming conventions for employee locations.
Multiple Employee No. format (Starts with D, Integers starting with 1, starting with 2)

4. Data Discovery and Loading

Data Sources

Masterlist: An Excel file containing comprehensive employee data.
Per Cutoff Excel Files: Multiple Excel files capturing employee location movements.

Loading Data into Power BI

Masterlist: Loaded directly from the Excel file using Power Query.
Per Cutoff Files: Loaded from a folder containing multiple Excel files.

5. Data Transformation in Power Query

Creating a Cutoff Files Table

Create a reference table listing all cutoff files with an index and cutoff date based on filenames.
Clean and transform the data:
- Promote headers.
- Remove blank cells in the first column.
- Remove rows containing ":" in the second column.

Merging Cutoff Files

Merge all relevant data from the cutoff files into a single table for analysis.

6. Data Modeling

Star or Snowflake Schema

Establish relationships between the main employee data table and a calendar table to facilitate time-based analysis.

7. DAX Calculations

Key Calculations

Next Location: Calculate the next location for each employee based on location changes.
Tenure Days: Calculate tenure days for each employee using various date comparisons.
Headcount Metrics: Calculate total headcount, starting headcount, ending headcount, attrition rate, retention rate, and growth rate.

DAX Measure Formulas

Next Location =     VAR CurIndex = 'Employee Master List'[Index] VAR NxtIndex = CurIndex + 1 RETURN CALCULATE(         FIRSTNONBLANK('Employee Master List'[Location], 1),         FILTER(             'Employee Master List',             'Employee Master List'[Index] = NxtIndex         )     )Tenure Days =     DATEDIFF(HiredDate, MovementDate, DAY)
Total Headcount =     COUNTROWS(EmployeeTable)
Attrition Rate =     DIVIDE(CALCULATE(COUNTROWS(EmployeeTable), EmployeeStatus = "Separated"), TotalHeadcount)
Retention Rate =     1 - AttritionRate
Growth Rate =     DIVIDE(EndingHeadcount - StartingHeadcount, StartingHeadcount)

8. Additional KPIs and Age Grouping

Create calculated columns for age groups and other relevant KPIs to enhance the dashboard's analytical capabilities.

9. Visualizations

Design visuals for each KPI, ensuring clarity and ease of interpretation. Use charts, tables, and cards to represent data effectively.

10. Summary of Main Steps

Define the problem and identify KPIs.
Address data issues and prepare data sources.
Load and transform data using Power Query.
Model data using a star or snowflake schema.
Implement DAX calculations for key metrics.
Create visuals for the dashboard.
Provide Key Takeaways and Insights.
Publish and share the dashboard with stakeholders.

This end-to-end process ensures a comprehensive approach to developing a Power BI dashboard that meets the analytical needs of a multi-chain restaurant group, providing valuable insights into employee dynamics and operational performance.

Page updated

Report abuse