For this mini-project, I decided to take a look at the City of the Calgary compensation data which is publicly available on the City of Calgary Open Data Platform
ETL
The source data is relatively clean, containing just 4 columns - Year, Position Title, Minimum Annual Base Rate, and Maximum Annual Base Rate. With just under 15000 rows, it was a <1MB csv file. I decided to make things more interesting by importing the csv into SQL to use SSMS for some prelimniary analysis.
Afterwards I made the following changes:
- Removed Max Base Rate as I deemed it inapplicable to most new hires
- Introduced new columns: Career Level, Salary Group, Department (Note, I used AI to help with categorising the position titles into departments)
- Filtered for 2019 to 2023 data
Visualisation
The data was then imported into Power BI where the dashboard was built highlight some potentially interesting trends and comparisons.
Interactive Dashboard
The interactive report can be used below. If you face any issues with using it the embedded report below, it can be viewed externally at this link. Content is best consumed on desktop.