Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Integration with BambooHR

Does anyone have any experience in using BambooHR as a data source for Power BI? There's no built-in connector for it obviously, but we're not sure the best way to get data exported from BambooHR and into a data source we can use from Power BI.

1 ACCEPTED SOLUTION

BambooHR has a REST API. Try reviewing it for what you need and using it in a web request from Power BI: http://www.bamboohr.com/api/documentation

View solution in original post

16 REPLIES 16
IntaBruce
Helper II
Helper II

Well I got so far but am now stuck again.  I have managed to connect and authenicate using an API key and can extract the employee directory.  What I want now is the time tracking data but can't see a way to get this.

The API described on the Bamboohr website requires you to enter the employee id but I want to extract all employees' tracking data so I can report on it in Power BI.

Any advice?

Thanks

Hiya, I've not tried using the time tracking in Bamboo, so don't have any direct experience unfortunately. It sounds like you might need to investigate creating a custom function, which is a little advanced - not impossible but it'll take a bit of understanding. Basically, it's like if you wanted to scrape data off a series of nearly identical webpages, or from an Excel workbook with a series of nearly identical tabs: you want to take data from a series of nearly identical API requests, with one parameter changing each time (the employee id). A custom function lets you do the same thing again and again with just the employee id changing, and outputs all the results into a single table. 

It's not something I've done a lot so I can't help with the specifics, but this page might be a good starting point. Alternatively a similar issue is pagination with APIs - when an API gives the results in pages of 100 records or something like that, so you have to do a series of nearly identical queries to get all the pages of results. This page has quite a few useful links for tackling that, which might be a transferrable solution.

Good luck! 

IntaBruce
Helper II
Helper II

Hi,
i've generated an API key and tried connecting but Power BI keeps giving this error:

IntaBruce_0-1657104563092.png

What am I doing wrong?

Hi,

It's hard to know without seeing the steps leading up to that error message - when you got to the bit asking how you want to connect to the web address, did you perhaps choose the Web API option for credentials? I only got it working using Basic credentials, as above. It's counterintuitive.

 

Cheers,

Matt

Thanks Matt, I should have read your instructions a little more carefully.  I've managed to connect now.  I just need to work out how to unpack the tables.

Good luck! Glad you're connected, and I can't fault you for assuming that Web API would be the straightforward option when you're trying to connect to an API... 

hello789567
Advocate I
Advocate I

Does anyone have any updates on this, or had any success? 

Hi there,

 

I've just managed to get it working, hopefully this will help you too.

You need two bits of information to make it work, your subdomain and an API key. The subdomain is whatever your company's address starts with, e.g. if the users log on to https://contoso.bamboohr.com, then it's contoso. This article says how to generate an API key.

Once you've got that:

  • Get data > Web.
  • URL: use the following, changing the subdomain bit: https://api.bamboohr.com/api/gateway.php/subdomain/v1/employees/directory
  • For the credentials, you don't want the Web API option, but just select Basic. Put your API key in the Username field, leave the password blank. Obviously!

That will get you towards a table showing what everyone's ID is in the system and some basic user info. To get other details you have to change that /employees/directory part of the URL; you could replace it with /time_off/whos_out to find out who's on holiday today. This page looks to be the best source of what those different address endings could be. Good luck!

 

Cheers,

Matt

Has anyone managed to access the BambooHR timesheet entries report in PBI? I think the issue is that it needs a date filter to create the report

Anonymous
Not applicable

In case it helps anyone else, here's how I connected to our BambooHR data:

 

1. <Optional> Create a service account in BambooHR and grant permissions to all the required fields for your query as well as the ability to create custom reports. Your service account will also need to have appropriate licensing in Office365.

2. Create a custom report in BambooHR with the fields required. Note the report ID at the end of the address when viewing the report.

3. Generate an API key in BambooHR. Make note of this API key because you will need it later.

4. Open Power BI and Get Data > Web > Address: https://api.bamboohr.com/api/gateway.php/companyDomain/v1/reports/id?format=csv (filling in the companyDomain and report ID with your data) > Username: <API key> > Password: <blank>

5. Create your visuals and publish your report to the Power BI service. 

6. Refresh your data. If prompted, update the credentials for BambooHR using the Username: <API key> and Password: x.

 

Good luck to you!

 

Anonymous
Not applicable

I'm able to connect to our Bamboo data in Power BI Desktop, but once I publish the report it won't refresh the data. The Power BI service says that "Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again." When I edit the credentials, it won't let me proceed without a password. Has anyone gotten around this issue?

Anonymous
Not applicable

I was able to enter "x" in the password field and connect for refresh. 

Matt, thanks for following up with all of this detail.

You're welcome - I'm sure I'll be searching for how to connect again in six months time so hopefully I'll come across my own instructions!

BambooHR has a REST API. Try reviewing it for what you need and using it in a web request from Power BI: http://www.bamboohr.com/api/documentation

Hi Deldersveld,

I would be very interested in the solution if you found any to easily integrate power BI to BambooHR

 

thanks,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors