Case Study: Transform HR data with Power BI
Power BI can bring together data from other systems and transform it in ways that are useful to employees. In this case study, we share the benefits of using Power BI for HR (Human Resource) data to share information with employees.
The challenge
This case study is for a system we use at Gravity Union for tracking time: Harvest. Employees use this system to enter their personal time (e.g. sick days, vacation) and project hours that get billed to clients.
Employees are interested in how their time is spent historically, both on projects and time off. They don’t always have access to information in Harvest due to closed projects and governance of the data. If they do have access, the process is manual to aggregate the data across multiple months, and returns a spreadsheet of difficult-to-use data.
Some employees are also interested in seeing how billable work at Gravity Union is trending, and some people want to see how their billable hours stack up compared to the rest of the team. Currently, these requests for information must go through project managers because employees don’t have access to everything.
The final requirement is that any solution must respect the privacy of the individual employees, ensuring personal information is available only to the individual.
The solution
The solution uses Power BI to extract and transform Harvest data, refreshing on a nightly basis. We present embedded Power BI reports on a dedicated SharePoint page. The report supplies personalized drilldown views to show hours distributed across a range of granularities, from Clients, through to Projects and Tasks.
Example of Power BI dashboard of time tracking data
Employees also get a current snapshot showing any personal time they’ve accrued or used over a time frame of their choice:
Example of Power BI dashboard for vacation time
The processed data, and related reporting is kept in a limited access HR workspace to prevent unauthorized use of the underlying information. Reports take advantage of Row Level Security within Power BI to allow a single report to provide custom output for each employee.
The benefits
The solution helps every employee at Gravity Union. People now have access to their work history, including time off used and remaining, whenever they want to see it.
Employees also have more insight into the organization's performance. Employees can see the ups-and-downs of billable work at Gravity Union, and how their efforts are affecting the health of the company.
Bottlenecks are reduced as well. The HR team is no longer a block for supplying vacation information and project managers no longer need to compile data for employees (or turn down requests when they are busy).
Overall this solution increases transparency and saves effort for the HR team.
Tips for building your own Power BI dashboard for HR data
Typically HR data is sensitive and personal, so you need to consider the security setup of Power BI. Here are a couple of tips to make sure you share the appropriate information with the right people:
Use row level security
Row level security is straightforward to work with, but you must consider how will each row be attributed to a user. Things like email addresses or employee numbers would be unique values that allow Power BI to direct results appropriately.
Think about what workspace to use
We also had to plan the best to place to keep the Power BI assets. Careful consideration should be given to:
Who needs access to the dataset
Who needs access to develop/edit the report
Who will deploy the report
Who needs to view the finished product
The Power BI developer needed access to the dataset and the report for creation and deployment, while the HR team needed to own and protect them. All remaining employees need to view the results, without being able to access the underlying data, or edit the report itself.
We thought about using the workspace related to the HR SharePoint site, but this is a secure area with sensitive information such as employee files. It is only accessible by the HR team. If we placed the Power BI data and report there, the HR team would have access, but the Power BI developer would not. This means that any future updates or maintenance would be the responsibility of the HR team.
Instead, we created a new workspace, one that includes access for the HR team and the Power BI developer, but isn’t tied to the existing HR SharePoint site. It’s a good compromise in ensuring governance and accountability down-the-road.
When it came to providing a home for employees to view the report, we had to decide between embedding the report in each person’s personal SharePoint page, or finding a single central location that everyone had access to.
The first option would provide an extra layer of security through SharePoint, as the only person who can access an employee’s personal page is that employee. However, it means adding the report to multiple pages, one for each existing employee.
We decided to use the centralized option, as Row Level Security was already providing appropriate data restrictions, and we would only have to add the report a single time, to a new page, in our communication site.
Reach out if you need help or advice on building a Power BI solution for your organization. We’d be happy to help!
