HR Insights With MySQL and PowerBI
Dashboarding • DAX • SQL
Title | HR Insights with MySQL and PowerBI |
Industry Focus | HR |
Problem Statement | Deep dive into company’s HR database to answer HR department questions about workforce |
Business use case |
|
Goals/Metrics | Query the organization's database to answer questions from the HR department and create a Power BI dashboard to visualize (i) Overall metrics (Headcount, number of employees per department etc) and (ii)specific details for each employee. |
Deliverables |
|
Database creation
First, I created the database on MySQL using INSERT statements
MySQL Database
Screenshot of EER diagram
Database querying
I queried the database based queries organized into 9 key SQL concepts, each in its own folder in my Github repo, to showcase my proficiences clearly. The repo is version-controlled to elegantly track changes to the files.
Screenshot of my MySQL WorkBench environment
Screenshot of one of the .sql files in the repository
Dashboarding
For the dashboard design (as with all of my designs generally), I initially had decision paralysis in trying to decide what design pattern to use. I eventually stuck with a minimalist black and white design that I really like.
Sketches
Power BI Desktop ®
Loading data
I created a connection to the instance of MySQL on my machine by providing the server name and the database name as shown below .
Instead of connecting to the whole database and importing all tables, I only imported two views that I had created earlier, as best practice suggests to limit data access.
The views encompass joins that would otherwise have impacted the performance of the Power BI engine if the raw tables were imported.
Choosing the data source
Connecting to the database
Importing views
Data Transformation
Before loading the data, I used Power Query to check for errors in the data like nulls or misspellings. Later on while writing DAX, I figured I would need a `Age` column, so I went back to Power Query to use the Columns From Example feature to generate ages from the birthdate column in the employees table.
Power Query environment
Creating a full_name column
Creating Measures (DAX)
As most Power BI experts preach, I avoided using implicit measures and stuck to writing all my measures in DAX.
The full list of DAX measures I wrote can be found here:
Female = DIVIDE([Total Female Employees], [Head Count])
% Male = DIVIDE([Total Male Employees],[Head Count])
Age =
IFERROR(
VALUES('employees v_employee_details'[Age]),
"select an employee")
Avg Salary = AVERAGE('employees v_employee_details'[salary])
Current Department = CALCULATE(VALUES('employees v_employee_details'[dept_name]),'employees v_employee_details'[dept_start_date] = MAX('employees v_employee_details'[dept_start_date]))
Visuals
After creating visuals with cards, filters and a bar chart , the dashboard looked like this:
Testing the measures
Final look 👀
After applying styling to give it a minimalist look, I ended up with this dashboard.
The central feature is a search bar where a user can search for an employee's name and the dashboard will update and reflect that employees details
Conclusion
The whole project spanned about 3 weeks. My knowledge in SQL was deepened and I freshened up on my Power BI skills. I would have loved to be able to upload the dashboard to a public server where people can view the dashboard and interact with it like Tableau Public, but I do not have access to a business email that Power BI requires to enable that.
Connect with me on LinkedIn and on Twitter!
