VOOZH about

URL: https://www.cdata.com/kb/tech/workday-jdbc-pentaho-data-integration.rst

⇱ Integrate Workday Data in Pentaho Data Integration

Integrate Workday Data in Pentaho Data Integration

👁 Jerod Johnson
Jerod Johnson
Director, Technology Evangelism
Build ETL pipelines based on Workday data in the Pentaho Data Integration tool.

The CData JDBC Driver for Workday enables access to live data from data pipelines. Pentaho Data Integration is an Extraction, Transformation, and Loading (ETL) engine that data, cleanses the data, and stores data using a uniform format that is accessible.This article shows how to connect to Workday data as a JDBC data source and build jobs and transformations based on Workday data in Pentaho Data Integration.

About Workday Data Integration

CData provides the easiest way to access and integrate live data from Workday. Customers use CData connectivity to:

  • Access the tables and datasets you create in Prism Analytics Data Catalog, working with the native Workday data hub without compromising the fidelity of your Workday system.
  • Access Workday Reports-as-a-Service to surface data from departmental datasets not available from Prism and datasets larger than Prism allows.
  • Access base data objects with WQL, REST, or SOAP, getting more granular, detailed access but with the potential need for Workday admins or IT to help craft queries.

Users frequently integrate Workday with analytics tools such as Tableau, Power BI, and Excel, and leverage our tools to replicate Workday data to databases or data warehouses. Access is secured at the user level, based on the authenticated user's identity and role.

For more information on configuring Workday to work with CData, refer to our Knowledge Base articles: Comprehensive Workday Connectivity through Workday WQL and Reports-as-a-Service & Workday + CData: Connection & Integration Best Practices.


Getting Started


Configure to Workday Connectivity

To connect to Workday, users need to find the Tenant and BaseURL and then select their API type.

Obtaining the BaseURL and Tenant

To obtain the BaseURL and Tenant properties, log into Workday and search for "View API Clients." On this screen, you'll find the Workday REST API Endpoint, a URL that includes both the BaseURL and Tenant.

The format of the REST API Endpoint is: https://domain.com/subdirectories/mycompany, where:

  • https://domain.com/subdirectories/ is the BaseURL.
  • mycompany (the portion of the url after the very last slash) is the Tenant.
For example, in the REST API endpoint https://wd3-impl-services1.workday.com/ccx/api/v1/mycompany, the BaseURL is https://wd3-impl-services1.workday.com and the Tenant is mycompany.

Using ConnectionType to Select the API

The value you use for the ConnectionType property determines which Workday API you use. See our Community Article for more information on Workday connectivity options and best practices.

APIConnectionType Value
WQLWQL
Reports as a ServiceReports
RESTREST
SOAPSOAP

Authentication

Your method of authentication depends on which API you are using.

  • WQL, Reports as a Service, REST: Use OAuth authentication.
  • SOAP: Use Basic or OAuth authentication.

See the Help documentation for more information on configuring OAuth with Workday.

Built-in Connection String Designer

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

java -jar cdata.jdbc.workday.jar

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

👁 Using the built-in connection string designer to generate a JDBC URL (Salesforce is shown.)

When you configure the JDBC URL, you may also want to set the Max Rows connection property. This will limit the number of rows returned, which is especially helpful for improving performance when designing reports and visualizations.

Below is a typical JDBC URL:

jdbc:workday:User=myuser;Password=mypassword;Tenant=mycompany_gm1;BaseURL=https://wd3-impl-services1.workday.com;ConnectionType=WQL;InitiateOAuth=GETANDREFRESH;

Save your connection string for use in Pentaho Data Integration.

Connect to Workday from Pentaho DI

Open Pentaho Data Integration and select "Database Connection" to configure a connection to the CData JDBC Driver for Workday

  1. Click "General"
  2. Set Connection name (e.g. Workday Connection)
  3. Set Connection type to "Generic database"
  4. Set Access to "Native (JDBC)"
  5. Set Custom connection URL to your Workday connection string (e.g.
    jdbc:workday:User=myuser;Password=mypassword;Tenant=mycompany_gm1;BaseURL=https://wd3-impl-services1.workday.com;ConnectionType=WQL;InitiateOAuth=GETANDREFRESH;
  6. Set Custom driver class name to "cdata.jdbc.workday.WorkdayDriver" 👁 Configuring the JDBC Driver connection.
  7. Test the connection and click "OK" to save. 👁 Saving the connection.

Create a Data Pipeline for Workday

Once the connection to Workday is configured using the CData JDBC Driver, you are ready to create a new transformation or job.

  1. Click "File" >> "New" >> "Transformation/job"
  2. Drag a "Table input" object into the workflow panel and select your Workday connection. 👁 Configuring the Table input object
  3. Click "Get SQL select statement" and use the Database Explorer to view the available tables and views. 👁 Selecting data (QuickBooks Online is shown).
  4. Select a table and optionally preview the data for verification. 👁 Previewing the data (QuickBooks Online is shown).

At this point, you can continue your transformation or jb by selecting a suitable destination and adding any transformations to modify, filter, or otherwise alter the data during replication.

👁 A completed transformation/job.

Free Trial & More Information

Download a free, 30-day trial of the CData JDBC Driver for Workday and start working with your live Workday data in Pentaho Data Integration today.