Atlas Labs HR Metrics Data Analysis
In this project, I would be tackling a fictitious company, Atlas Labs’ HR analytics. The HR department at Atlas Labs want to :
- Monitor key key HR metrics on employees
- Understand what factors impact attrition
By the end of this project, I would have built a dashboard for the head of HR that showcases the organization’s key HR metrics. This will enable her to compare metrics with organizations across the industry and understand how our employees are performing.
My focus will be on building the data model, conducting an analysis and designing a report to addresses the request at hand. To kickstart the data modeling, I import and load the relevant files (EducationLevel.csv, Employee.csv, PerformanceRating.csv, RatingLevel.csv, SatisfiedLevel.csv) into Power Query. In deciding which of these tables will be my fact table, I consider :
- which tables contain quantity data relevant for the analysis to be done ?
- which tables contain data that can be aggregated ?
- which tables contain foreign keys that connect them to primary keys in other tables ?
- which tables store data at its lowest level ?
After considering these factors, I decide that PerformanceRating table will be my fact table, rendering the other tables dimension tables. I will prefix “dim” or “fact” to each table name for clarity.
To facilitate accurate date and time reporting, I create a dedicated date table and connect it to my fact table.
I now connect my date table to the Employee table. Since the Employee table is already connected to my fact table. Power BI will not allow for this new connection to be active. To circumvent this restriction, I make the connection between these two dimension tables present but inactive.
I then connect my Employee table to the EducationLevel table via the EducationalLevelId which is present in the latter as simply “Education”
FactPerformanceRating table has four columns containing a satisfaction rating system from 1 to 5. There is a DimRatingLevel that serves as a legend for these ratings. I connect these 2 tables 4 times — only one of these connections can be active.
The same process is repeated for SelfRating and ManagerRating columns with the DimRatingLevel table.
We end up with a snowflake schema.
One of my mandates on this project, is to tackle employee attrition within the company. To do this I will create TotalEmployees, ActiveEmployees, InactiveEmployees and AttritionRate using DAX and contain them in a dedicated Measures table.
Now, in analyzing hiring trends over time, I want to visualize TotalEmployees by Date.
This creates a chart that doesn’t look right. What could be causing this issue? My DimDate and Measures table have no connection. To resolve this, I create a dummy date column in my Measures table with which I create an inactive connection to my DimDate table. This enables me to use DAX function “USERELATIONSHIP” to connect them.
This solves the issue and I am able to create a new measure that reflects what is to be achieved. This new measure- TotalEmployeesDates is replaced in my chart and voila. I then add attrition to the chart so that we can easily see over time how many employees remained and how many left.
Now I want to investigate the various department teams and the roles they typically hire into the organization. To achieve this, i represent the number of active employees within the various departments and the roles they occupy in a bar chart and a tree map.
Having a good understanding of Atlas’ general HR metrics, I delve into the companies diversity and inclusion metrics. I will start off with age demographics. After carding the minimum and maximum age of all employees in the system, I create age bins using conditional columns in power query. This enables me to visualize the total employees by age and employees by age sliced into gender.
Moving on to marital status and ethnicity. Since there are only 3 options under marital status, I opt for a donut chart to visualize my total employees by marital status metric. Curious about how salaries play out over different ethnicities, I create a new measure Average Salary. This measure is used in visualizing a juxtaposition of the number of employees per ethnicity and the average salaries.
Satisfied with the demographic metrics so far, I move on to track employee performance reviews. I want the HR head to be able to search for the performance of any given employee. To facilitate this, I create a filter to house every employee in the system’s full name. This is followed by 3 cards denoting said employees start date, most recent review and their next review(365 days from most recent review).
Now to assess individual review and performance ratings over the period, I create 6 measures — Job Satisfaction, Relationship Satisfaction, Environmental Satisfaction, Work Life Balance, Self Rating and Manager Rating — which calculate the maximum value in our database for each reported rating. I am now able to see these additional metrics for any selected employee.
For instance, looking at a selected employee “Estelle Chung” who was employed in 2018, and was last reviewed on 24th March 2022, we observe a consistent downward trend in her reported satisfaction levels over the years.
It therefore comes at no surprise that her manager’s rating is also showing the same trend. Even she admits that she is not doing too well by 2022 through her own self rating. We don’t want to loose Estelle, she’s been with us for 4 years now and knows the job. One way, we can reduce the chances of her leaving us could be to call an HR meeting in which we discuss her current challenges and come up with a roadmap to improvement.
After tackling employee performance reviews, I would like to look into employee attrition. I start off by carding the attrition rate (16.12%) dimension I previously created. I then create a chart that visualizes attrition rate across departments and job roles. Following this, I visualize attrition rate over time. This requires creating a date bound attrition rate using “USERELATION” DAX function.
What factors other than department and job role could be affecting attrition within the company ? Our dataset includes information on how frequently travel is required of an employee. We also have data telling us if an employee does overtime and the number of years an employee has worked at Atlas. I create 3 graphs showing attrition rate over these metrics to see how each factor affects attrition.
From this analysis, we see that employees that travel frequently, those that do overtime and those that haven’t been with us long are more likely to leave Atlas, with those who have worked with us for a year having the highest attrition rate of all with 34.46 %. That will be a great place to start our employee retention efforts. Also, though frequent travelers make up a relatively small proportion of our staff, they have a disproportionately high attrition rate. One way to tackle this could be to survey employee feelings around travel frequency to get to the root of the issue and possibly review travel requirements.
Confident that my analysis covers the requirements of this project, I now tackle the aesthetic of my dashboard. A visually pleasing report will partake in encouraging user engagement. Part of my process involves selecting a color scheme, aligning charts and texts, using descriptive titles and labels, arranging charts to tell compelling story and adding navigation. My goal is to create a beautiful, interactive and reusable dashboard that users will be happy to visit as needed. See below for what I settled on.
