Import Workday Data Using Azure Data Factory

Dibyendu Datta
Lead Technology Evangelist

Use CData Connect AI to connect to Workday Data from Azure Data Factory and import live Workday data.

Microsoft Azure Data Factory (ADF) is a completely managed, serverless data integration service. When combined with CData Connect AI, ADF enables immediate cloud-to-cloud access to Workday data within data flows. This article outlines the process of connecting to Workday through Connect AI and accessing Workday data within ADF.

CData Connect AI offers a cloud-to-cloud interface tailored for Workday, granting you the ability to access live data from Workday data within Azure Data Factory without the need for data replication to a natively supported database. Equipped with optimized data processing capabilities by default, CData Connect AI seamlessly channels all supported SQL operations, including filters and JOINs, directly to Workday. This harnesses server-side processing to expedite the retrieval of the desired Workday data.

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 Workday Connectivity for ADF

Connectivity to Workday from Azure Data Factory is made possible through CData Connect AI. To work with Workday data from Azure Data Factory, we start by creating and configuring a Workday connection.

CData Connect AI uses a straightforward, point-and-click interface to connect to data sources.

Log into Connect AI, click Sources, and then click Add Connection

👁 Adding a Connection

Select "Workday" from the Add Connection panel

👁 Selecting a data source

Enter the necessary authentication properties to connect to Workday.
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.

API ConnectionType Value
WQL WQL

Reports as a Service Reports

REST REST

SOAP SOAP

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.
👁 Configuring a connection (Salesforce is shown)
Click Save & Test
Navigate to the Permissions tab in the Add Workday Connection page and update the User-based permissions. 👁 Updating permissions

API	ConnectionType Value
WQL	WQL
Reports as a Service	Reports
REST	REST
SOAP	SOAP

Add a Personal Access Token

When connecting to Connect AI through the REST API, the OData API, or the Virtual SQL Server, a Personal Access Token (PAT) is used to authenticate the connection to Connect AI. It is best practice to create a separate PAT for each service to maintain granularity of access.

Click on the Gear icon () at the top right of the Connect AI app to open the settings page.
On the Settings page, go to the Access Tokens section and click Create PAT.
Give the PAT a name and click Create. 👁 Creating a new PAT
The personal access token is only visible at creation, so be sure to copy it and store it securely for future use.

With the connection configured and a PAT generated, you are ready to connect to Workday data from Azure Data Factory.

Access Live Workday Data in Azure Data Factory

To establish a connection from Azure Data Factory to the CData Connect AI Virtual SQL Server API, follow these steps.

👁 Logging in to ADF

If you have not yet created a Data Factory, Click New -> Dataset.

👁 Creating new data factory

In the search bar, enter SQL Server and select it when it appears. On the following screen, enter a name for the server. In the Linked service field, select New.

👁 Selecting SQL Server

Enter the connection settings.
- Name - enter a name of your choice.
- Server name - enter the Virtual SQL Server endpoint and port separated by a comma: tds.cdata.com,14333
- Database name - enter the Connection Name of the CData Connect AI data source you want to connect to (for example, Workday1).
- User Name - enter your CData Connect AI username. This is displayed in the top-right corner of the CData Connect AI interface. For example, [email protected].
- Password - select Password (not Azure Key Vault) and enter the PAT you generated on the Settings page.
- Click Create.

👁 Configuring new linked service

In Set properties, set the Name, choose the Linked service we just created, select a Table name from those available, and Import schema from connection/store. Click OK.