Blog

Select Distinct SD Line Graphic Element
Connecting BambooHR to Power BI

Connect BambooHR to Power BI: Streamlining HR Analytics

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.

Connecting BambooHR to Power BIConnecting BambooHR to Power BI

 

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.

 

Azure Data Factory

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

Blog Posted by David Laws

David Laws Principal Consultant

LinkedIn