Self-service headcount analytics using Microsoft BI: salary analysis

Mike Burger Slalom Consulting

Mike Burger

In many companies, executives are often concerned about employee turnover. In this scenario, employee exit surveys have revealed that the staff and managers have stated that their salaries are lower than the market averages, and that the only reason they are leaving is due to significant salary increases. An HR analyst has been tasked with using both internal and external data to confirm or deny this theory. The salary analysis below was built using the Microsoft BI solution stack and can be implemented with any company that captures employee salary information.


Using Excel and the company’s Headcount Analytics HR cube, all of the necessary analysis can be performed. To perform this analysis, the analyst must have Power Query installed. To start, the analyst opens Excel and creates a PowerPivot model using the Analysis Services HR cube as the source.


The analyst enters the proper information to connect to the cube and builds a view similar to the one shown below.  This view filters a list of employees to only show the staff and manager-level employees that are “salaried full time.”

Table Import Wizard

It’s great to have all the internal data readily available for analysis, but a major piece of data is missing. The analyst needs to be able to find some industry averages to determine if the company’s employees are underpaid. To do this, the analyst uses the Excel Power Query add-in. Within the “Online Search” box the analyst types in “United States average income” and a list of external and public data sources are revealed. The analyst decides to choose the “Median personal income by educational attainment” data source.

Median Personal Income

By clicking on the data source, the analyst imports the data into Excel from the web and a new “Query Settings” window is displayed. Using the basic Power Query functionality, the analyst manipulates the data to format it so it can be used for analysis. The Power Query add-in will track all manipulations so they can be automated and repeated again in the future.

The analyst knows that employees must have a Bachelor’s or Master’s degree to be hired at her company. For this reason, the analyst can remove all unrelated columns from the public data set and use the Bachelor’s and Master’s data as an average minimum and average maximum salary within the market. Additionally, the analyst also knows that her company only hires full-time employees, so she’s able to remove the unneeded rows. The public data is now filtered to only show salaried, full-time employee records as shown below.

Median Personal Income by Educational Attainment

Lastly, the analyst needs to import this external data to the existing PowerPivot model. To do this, the analyst simply clicks on the “Load to data model” link found in the “Query Settings” window and creates a table join between the two tables in the PowerPivot model.


Now that the external data is added into the data model, the analyst can use PowerPivot indicators to easily view the salary of each employee compared to the industry averages. Due to the overwhelming percentage of red indicators, the analyst can tell that most employees are in fact below the national average. The analyst takes this information to her manager to determine if an action should be taken.


Easier data consumption, happier employees

Using only Excel and our self-service headcount analytics solution, organizations can now easily consume data from both internal and external data sets. By using external data companies can answer many questions related to employee salary information, and possible reasons for issues with retention. Actions can be made based on the analysis in order to help improve employee satisfaction and retention.

To learn more about our self-service headcount analytics solution using Microsoft BI, check out our previous posts in the series:

About mikeburger
I am a Solution Principal at Slalom Consulting. I have extensive experience on large end to end data warehouse implementations. Over the last 6 years I have had a strong concentration with the Microsoft SQL Server stack.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: