Connecting to Paylocity

[Anonymous]

Hi,

I am curious if anyone has connected PowerBI to Paylocity. I have been using PowerBI by connecting it to our erp system (using an ODBC) but have been struggling to find a way to connect Paylocity. I was trying to connect it to their API feed but dont have too much knowlege in that area. 

[michaelgar]

We have created a service for connectivity to Paylocity, which PowerBi can use. The service pulls all the data from the API and Time and Labor data. Setup is close to instant if you already have creds to the API service. Everything is updated Nightly at 1 AM. If you are interested, we can implement this for you as well. 

Tables:

  Paylocity_AdditionalDirectDeposit  

  Paylocity_AdditionalRate  

  Paylocity_BenefitSetup  

  Paylocity_CustomBooleanField  

  Paylocity_CustomDateField  

  Paylocity_CustomDropDownField  

  Paylocity_CustomNumberField  

  Paylocity_CustomTextField  

  Paylocity_DepartmentPosition  

  Paylocity_Earning  

  Paylocity_EmergencyContact  

  Paylocity_Employee  

  Paylocity_FederalTax  

  Paylocity_HomeAddress  

  Paylocity_LocalTax  

  Paylocity_MainDirectDeposit  

  Paylocity_NonPrimaryStateTax  

  Paylocity_PayStatements  

  Paylocity_PrimaryPayRate  

  Paylocity_PrimaryStateTax  

  Paylocity_TaxSetup  

  Paylocity_TimeAndLabor  

  Paylocity_WebTime  

  Paylocity_WorkAddress  

  Paylocity_WorkEligibility  

[Syndicate_Admin]

@darrenfishell We're doing a GET request only integration. It's possible using the Web Contents connector. You need to iterate through the pages until you receive an empty result, and then use a list combine to append them together. Use independent functions in PowerQuery editor. The issue we're running into now is that Paylocity requires {employeeID} in the URL to reach the other endpoints for a GET request. Using a Relative Path to jump a step and iterate across employee ID with a merge is problematic because it would exponentially increase the API calls and get limited by Paylocity. 

[Syndicate_Admin]

I am working through this now. My research has indicated that a custom connector is needed to retrieve any Paylocity data because the API handles pagination via the response header, which is not visible in PowerQuery in PowerBI Desktop or in Dataflows. 

The other option is a separate ETL process, in Python or something else that is a little more straightforward for handling HTTP requests. 

[Syndicate_Admin]

If we're seeking a connection to an intermediary or controller, would Tray.io be the best option for that?  We basically want to inform various internal parties once an employee change is triggered through Paylocity such as a position change, or employment termination, etc.  We are also seeking to set this up in the form of a webhook.  Thanks for the suggestions. 

[Anonymous]

Any luck here @Anonymous ? I'm trying to connect with Paylocity as well.

[Anonymous]

Hi @Anonymous 

I was unable to find a data connector for Paylocity. I found one option, tray.io, but did not test this. Paylocity does have an API, so if you have an deep understanding of those  (i did not), then that may work for you. I had reached out to one of our vendors (CData) and they quoted me around $10k to build a connecor. Depending on your needs, that may be worth it. 

https://www.paylocity.com/integrations/apis/

https://tray.io/connectors/microsoftpowerbi-paylocity-integrations

[v-yuezhe-msft]

@Anonymous,

You can review this blog about how to use Paylocity API to get data. To connect to the api in Power BI Desktop, you can use web connector or directly add Power Query code in Advanced Editor of a blank query, for more details, please review the following blog:

https://chris.koester.io/index.php/2015/07/16/get-data-from-twitter-api-with-power-query/

Regards,
Lydia

[edavila]

Had anyone able to connect to paylocity

[KF1]

We have connected Data Factory to Paylocity and extracting data ok.

[BA-Bandit]

Did you connect through the API calls? If so, can you help explain how did you connect?

[BA-Bandit]

I was able to connect to paylocity using a web call in an ADF Azure pipeline that loads the data into a sql server db then I have power bi connected to sql server db. I used postman to test the authentication and api calls. I can post some steps later if anyone is interested. 

[sirlanceohlott]

I'd be interested as well, woud love to incorproate ADF into our processes if it expedites connectivity with Paylocity for Power BI

[BA-Bandit]

I will need to create a blog post but here is the shortened version.

In my paylocity Pipeline I first call my RefreshToken pipeline 

It has 4 web API calls, the first two web calls get the ClientId and ClientSecret from Azure Keyvaults. The third call Post to 

https://api.paylocity.com/IdentityServer/connect/token passing in the clientID and ClientSecrets. The fourth web call updates the accesstoken in Keyvaults with the returned value of the third web call.

Once the token is refreshed I get the updated Access token from Keyvaults and use it in my copy data activity to get Emloyee id's and save them in a SQL DB.

I am not sure if this is the most efficient way but it is secure and i make sure to always check the Secure input and output. Once I have time to get this fully documented I wll add a link here.

[KF1]

We have successfully connected Paylocity to SQL Server / Power BI / MS Azure Analysis Services. Please let me know if you are interested in having us provide this integration effort. It was not easy but is now given we have done the effort a number of times.

[alexandreab]

I would be interested in this.  I am trying to help a client with this