Connect BambooHR to Power BI to get real time visibility of your HR data
On a recent project a client had a requirement to connect BambooHR to Power BI. This blog seeks to share some of the steps and experience.
Getting Real Time Analytics
Whilst the business was utilising BambooHR, a popular HR software solution that offers comprehensive HR management capabilities. The data reports within the system did not give the business a real time or bespoke view of their employees or performance metrics.
The business was therefore utilising report extracts or other spreadsheets to build management information. This was either delayed due to time constraints or inaccurate due to the potential for data entry. Connecting to the BambooHR system allowed the business to get real time data.
Connect to BambooHR
BambooHR has documentation on the web site to show how to connect to the API’s.
These instructions explain how to get an API key from the system and the various calls that can be made.
The bulk of the extracts were undertaken with a custom report, the time-off report and status report.
System Architecture
It is possible to connect BambooHR to Power BI. However due to clients need it data was loaded into the data warehouse. This allowed the data to be transformed and joined with other key business data sources. In this case the ideal option was to use Azure Data Factory as the tool to connect, transform and load into an Azure SQL database.
Azure Data Factory Pipelines
ADF has numerous tools to connect to API’s and export to SQL. The transformation tools allow the JSON to be transformed into a relationship database.
Database Architecture
The database tables were built with a landing schema, with procedures were then used to insert or update the data into the main tables. This allowed for data quality checks as well as an improvement in performance. In the case of the employee details this was build using a slow changing dimension to give the business a full history of changes over time.
The data was manipulated in the SQL database to build the Power BI models.
Power BI
The Power BI reports were built or amended with the BambooHR data. This included an absence report based on Bradford factor, overtime reports and van tracking data. The ability to make this live and up to date has transformed the business and its relationship with the data and other systems.
Orchestration
Azure Data Factory and Power BI were used to schedule the timing of pipelines to refresh the load, run procedures and run the reports.
Conclusion
This blog has shown some of the benefits and steps to Connect BambooHR to Power BI or any other HR system.
The benefits of making the data available in real time and integrated into your business can be game changing, removing the frustration of not being able to see what you need to having the ability to receive the information you need when you need it
Contact us if you want to find out more.
Find out about our Business Intelligence Consultancy Service.
Or find other useful SQL, Power BI or other business analytics timesavers in our Blog
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work and are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people. Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
Blog Posted by David Laws
