BambooHR integration 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 focussed on BambooHR UK but the system is used worldwide.
This blog seeks to share some of the steps and experience. These methods will no doubt have lots of similarities with BambooHR alternatives
Getting Real Time Analytics.
Whilst the business was utilising BambooHR system, 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 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. Rather than using the hr Bamboo login, connecting to the BambooHR system APIs allowed the business to get real time data.
BambooHR integration
The Bamboo HR hr system has documentation on the web site to show how to connect to the API’s.
These instructions explain how to obtain an API key from the system and describe the various calls that can be made.
We undertook the bulk of the extracts using a custom report, the time-off report, and the status report.
System Architecture
It is possible for BambooHR integration directly to Power BI. However, due to client needs, we loaded the data into the data warehouse. This allowed us to transform the data and join it 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
We built the database tables with a landing schema and then used procedures 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.
We manipulated the data in the SQL database to build the Power BI models.
Power BI
We built or amended the Power BI reports 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
We used Azure Data Factory and Power BI 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 of BambooHR integration with Power BI or any other reporting system. This method with be similar with alternatives to BambooHR.
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
We select our Business Analytics Timesavers from our day-to-day analytics consultancy work. They consist of everyday things that really help analysts, SQL developers, BI developers, and many others. 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
