How to connect PolyBase to Paylocity

PolyBase for SQL Server allows you to query external data by using the same Transact-SQL syntax used to query a database table. When paired with the CData ODBC Driver for Paylocity, you get access to your Paylocity data directly alongside your SQL Server data. This article describes creating an external data source and external tables to grant access to live Paylocity data using T-SQL queries.

NOTE: PolyBase is only available on SQL Server 19 and above, and only for Standard SQL Server.

The CData ODBC drivers offer unmatched performance for interacting with live Paylocity data using PolyBase due to optimized data processing built into the driver. When you issue complex SQL queries from SQL Server to Paylocity, the driver pushes down supported SQL operations, like filters and aggregations, directly to Paylocity and utilizes the embedded SQL engine to process unsupported operations (often SQL functions and JOIN operations) client-side. And with PolyBase, you can also join SQL Server data with Paylocity data, using a single query to pull data from distributed sources.

Connect to Paylocity

If you have not already, first specify connection properties in an ODBC DSN (data source name). This is the last step of the driver installation. You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. To create an external data source in SQL Server using PolyBase, configure a System DSN (CData Paylocity Sys is created automatically).

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.

Click "Test Connection" to ensure that the DSN is connected to Paylocity properly. Navigate to the Tables tab to review the table definitions for Paylocity.

Create an External Data Source for Paylocity Data

After configuring the connection, you need to create a master encryption key and a credential database for the external data source.

Creating a Master Encryption Key

Execute the following SQL command to create a new master key, 'ENCRYPTION,' to encrypt the credentials for the external data source.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

Creating a Credential Database

Execute the following SQL command to create credentials for the external data source connected to Paylocity data.

NOTE: Since Paylocity does not require a User or Password to authenticate, you may use whatever values you wish for IDENTITY and SECRET.

CREATE DATABASE SCOPED CREDENTIAL paylocity_creds
WITH IDENTITY = 'username', SECRET = 'password';

Create an External Data Source for Paylocity

Execute a CREATE EXTERNAL DATA SOURCE SQL command to create an external data source for Paylocity with PolyBase:

• Set the LOCATION parameter , using the DSN and credentials configured earlier.

For Paylocity, set SERVERNAME to the URL or address for your server (e.g. 'localhost' or '127.0.0.1' for local servers; the remote URL for remote servers). Leave PORT empty. PUSHDOWN is set to ON by default, meaning the ODBC Driver can leverage server-side processing for complex queries.

CREATE EXTERNAL DATA SOURCE cdata_paylocity_source
WITH ( 
  LOCATION = 'odbc://SERVER_URL',
  CONNECTION_OPTIONS = 'DSN=CData Paylocity Sys',
  -- PUSHDOWN = ON | OFF,
  CREDENTIAL = paylocity_creds
);

Create External Tables for Paylocity

After creating the external data source, use CREATE EXTERNAL TABLE statements to link to Paylocity data from your SQL Server instance. The table column definitions must match those exposed by the CData ODBC Driver for Paylocity. You can refer to the Tables tab of the DSN Configuration Wizard to see the table definition.

Sample CREATE TABLE Statement

The statement to create an external table based on a Paylocity Employee would look similar to the following:

CREATE EXTERNAL TABLE Employee(
  FirstName [nvarchar](255) NULL,
  LastName [nvarchar](255) NULL,
  ...
) WITH ( 
  LOCATION='Employee',
  DATA_SOURCE=cdata_paylocity_source
);

Having created external tables for Paylocity in your SQL Server instance, you are now able to query local and remote data simultaneously. Thanks to built-in query processing in the CData ODBC Driver, you know that as much query processing as possible is being pushed to Paylocity, freeing up local resources and computing power. Download a free, 30-day trial of the ODBC Driver for Paylocity and start working with live Paylocity data alongside your SQL Server data today.