Analyze Paylocity Data in R via JDBC

Access Paylocity data with pure R script and standard SQL on any machine where R and Java can be installed. You can use the CData JDBC Driver for Paylocity and the RJDBC package to work with remote Paylocity data in R. By using the CData Driver, you are leveraging a driver written for industry-proven standards to access your data in the popular, open-source R language. This article shows how to use the driver to execute SQL queries to Paylocity and visualize Paylocity data by calling standard R functions.

Install R

You can match the driver's performance gains from multi-threading and managed code by running the multithreaded Microsoft R Open or by running open R linked with the BLAS/LAPACK libraries. This article uses Microsoft R Open 3.2.3, which is preconfigured to install packages from the Jan. 1, 2016 snapshot of the CRAN repository. This snapshot ensures reproducibility.

Load the RJDBC Package

To use the driver, download the RJDBC package. After installing the RJDBC package, the following line loads the package:

library(RJDBC)

Connect to Paylocity as a JDBC Data Source

You will need the following information to connect to Paylocity as a JDBC data source:

• Driver Class: Set this to cdata.jdbc.paylocity.PaylocityDriver
• Classpath: Set this to the location of the driver JAR. By default this is the lib subfolder of the installation folder.

The DBI functions, such as dbConnect and dbSendQuery, provide a unified interface for writing data access code in R. Use the following line to initialize a DBI driver that can make JDBC requests to the CData JDBC Driver for Paylocity:

driver <- JDBC(driverClass = "cdata.jdbc.paylocity.PaylocityDriver", classPath = "MyInstallationDir\lib\cdata.jdbc.paylocity.jar", identifier.quote = "'") 

You can now use DBI functions to connect to Paylocity and execute SQL queries. Initialize the JDBC connection with the dbConnect function.

Set the following to establish a connection to Paylocity:

• RSAPublicKey: Set this to the RSA Key associated with your Paylocity, if the RSA Encryption is enabled in the Paylocity account.

  This property is required for executing Insert and Update statements, and it is not required if the feature is disabled.

• UseSandbox: Set to true if you are using sandbox account.
• CustomFieldsCategory: Set this to the Customfields category. This is required when IncludeCustomFields is set to true. The default value for this property is PayrollAndHR.
• Key: The AES symmetric key(base 64 encoded) encrypted with the Paylocity Public Key. It is the key used to encrypt the content.

  Paylocity will decrypt the AES key using RSA decryption.
  It is an optional property if the IV value not provided, The driver will generate a key internally.

• IV: The AES IV (base 64 encoded) used when encrypting the content. It is an optional property if the Key value not provided, The driver will generate an IV internally.

Connect Using OAuth Authentication

You must use OAuth to authenticate with Paylocity. OAuth requires the authenticating user to interact with Paylocity using the browser. For more information, refer to the OAuth section in the Help documentation.

The Pay Entry API

The Pay Entry API is completely separate from the rest of the Paylocity API. It uses a separate Client ID and Secret, and must be explicitly requested from Paylocity for access to be granted for an account. The Pay Entry API allows you to automatically submit payroll information for individual employees, and little else. Due to the extremely limited nature of what is offered by the Pay Entry API, we have elected not to give it a separate schema, but it may be enabled via the UsePayEntryAPI connection property.

Please be aware that when setting UsePayEntryAPI to true, you may only use the CreatePayEntryImportBatch & MergePayEntryImportBatchgtable stored procedures, the InputTimeEntry table, and the OAuth stored procedures. Attempts to use other features of the product will result in an error. You must also store your OAuthAccessToken separately, which often means setting a different OAuthSettingsLocation when using this connection property.

Built-in Connection String Designer

For assistance in constructing the JDBC URL, use the connection string designer built into the Paylocity JDBC Driver. Either double-click the JAR file or execute the jar file from the command-line.

java -jar cdata.jdbc.paylocity.jar

Fill in the connection properties and copy the connection string to the clipboard.

Below is a sample dbConnect call, including a typical JDBC connection string:

conn <- dbConnect(driver,"jdbc:paylocity:OAuthClientID=YourClientId;OAuthClientSecret=YourClientSecret;RSAPublicKey=YourRSAPubKey;Key=YourKey;IV=YourIV;InitiateOAuth=GETANDREFRESH;")

Schema Discovery

The driver models Paylocity APIs as relational tables, views, and stored procedures. Use the following line to retrieve the list of tables:

dbListTables(conn)

Execute SQL Queries

You can use the dbGetQuery function to execute any SQL query supported by the Paylocity API:

employee <- dbGetQuery(conn,"SELECT FirstName, LastName FROM Employee WHERE EmployeeId = '1234'")

You can view the results in a data viewer window with the following command:

View(employee)

Plot Paylocity Data

You can now analyze Paylocity data with any of the data visualization packages available in the CRAN repository. You can create simple bar plots with the built-in bar plot function:

par(las=2,ps=10,mar=c(5,15,4,2))
barplot(employee$LastName, main="Paylocity Employee", names.arg = employee$FirstName, horiz=TRUE)