Replicate Workday WQL Data to Snowflake using Workday Prompts (without errors)

Replicating Workday Data: The Challenges

Replicating Workday HCM and Financials data can be a challenging task in itself, especially when the Workday APIs are so vast, support a variety of data models, and offer different types of authentication mechanisms.

With CData Workday connectivity solutions, we simplify the complexities of the backend APIs and offer you a relational view of all your Workday that can be queried using SQL. CData Workday connectors support a variety of Workday connections through:

• Workday SOAP APIs
• Workday REST APIs
• Workday RaaS (Reports-as-a-Service)
• Workday WQL

To learn more about the seamless Workday connectivity, CData offers, please refer here: https://cdn.cdata.com/help/ASK/sync/Workday-Source.html#connect-to-workday.

Powerful Querying through Workday WQL

The Workday WQL service enables high-performance querying of Workday data. Each CData Workday connectivity solution has a built-in SQL Engine that is optimized to work with Workday WQL. As such, CData solutions can push the complexities of queries for data down to Workday for processing. This results in faster responses to queries and less reliance on client-side resources for data processing.

To learn more about connectivity through Workday WQL, please refer here: https://www.cdata.com/kb/articles/workday-wql-raas.rst.

Workday Prompts

The entities available from WQL are often large models and require prompts to retrieve data. Prompts are fields that allow you to refine the results of a data request. If you've ever exported data from Workday, you've seen these prompts in the form(s) you use to download the data.

Challenges: However, Workday Prompts can be tricky, and can often land you with a series of hits and trials before you replicate your data successfully.

In the article, we will learn how to replicate data from Workday WQL using Workday Prompts to Snowflake.

Replicate Data from Workday to Snowflake (using CData Sync)

To get started, download and install the CData Sync from https://www.cdata.com/sync/trial/. Once done, please follow the steps here:

1.) Create a Destination connection - Snowflake:

To configure a Destination connection to Snowflake, please refer to the Help documentation here: https://cdn.cdata.com/help/ASK/sync/Snowflake-Destination.html.

2.) Create a Source connection - Workday (using WQL):

To configure a Source connection to Workday, please refer to the Help documentation here: https://cdn.cdata.com/help/ASK/sync/Workday-Source.html.

Please note that I am using Connection Type=WQL and Auth Scheme=OAuthISU.

3.) Create a Job:

Once you have the source and destination connection set, let’s create a job:

4.) Add a Task:

Once the job is created, you can select a Task - the Workday Object you wish to replicate. We are using ‘Journals’ data here: 

Once done, follow the step-by-step guide below to work with Workday Prompts.

Working with Workday Prompts: Step-by-step Guide:

1.) Identify Workday Prompts for Table:

• First of all, we will try to identify the Workday Prompt columns for the table/view we have selected. In this case, ‘Journals’. To do this, navigate to the task level and then to the Query tab to modify your query:

REPLICATE [journals] 
SELECT * FROM sys_tablecolumns WHERE 
TableName = 'Journals' AND 
ColumnName LIKE '%prompt%'

• We are querying a system table sys_tablecolumn to fetch the columns with ‘prompt’ in its name. This is the best way to identify a prompt column in your Workday objects.
• Now go ahead and preview the data:

This will list all the prompt columns for the ‘Journals’ table. As we can see, here are the prompt columns:

1. accountingDateOnOrAfter_Prompt
2. accountingDateOnOrBefore_Prompt
3. company_Prompt

Once you know the available prompts, you can query the related tables to retrieve the appropriate values (typically an "id" field) for subsequent prompts.

The first two are DateTime Columns that define a range for which you wish to fetch the journal's data. The third one is a company ID that is fetched from the ‘Companies’ view.

2.) Get Company ID:

• In your job, add a Task for Companies:

• Click on the Task Name - 'Companies' and navigate to Preview, and Execute to fetch data:

• This should give you the Company Name and respective Company ID. Pick the Company ID you wish to fetch the data from.

3.) Filter Calculated Fields:

• Navigate to Workday connection, go to the Advanced section, under Miscellaneous, and find 'Metadata Filters' and set it to 'Calculated'.

• This will filter all the Calculated Fields when fetching metadata: https://cdn.cdata.com/help/JWJ/synch/default.htm#RSBWorkday_p_MetadataFilters

• This will avoid the error you were getting similar to this: 

   

4.) Replicate Journals Data:

• Modify the task for ‘Journals’.

• Modify the Query to the following:

REPLICATE [journals] SELECT * FROM journals WHERE 
company_Prompt = 'cb550da820584750aae8f807882fa79a' AND
accountingDateOnOrAfter_Prompt = '2023-01-01' AND 
accountingDateOnOrBefore_Prompt = CURRENT_TIMESTAMP()

• This query uses three prompts:

  • Company_Prompt = Company ID we fetched from Step 2

  • accountingDateOnOrAfter_Prompt = Start Date when you wish to fetch Journals data from.

  • accountingDateOnOrBefore_Prompt = End Date when you wish to fetch Journals data. I have set it to CURRENT_TIMESTAMP() which is a dynamic value for today's date.

• Preview:

5.) Execute the Job:

• Once the queries have been modified correctly, execute the job:

• Preview in Snowflake:

More Information & Free Trial 

The tips above will help you get the most out of our CData connections to Workday, improving performance and following the integration recommendations of Workday experts.

To get more information about working with your Workday data through CData's connectivity solutions, check out our Workday integration page.

Sign up for a free trial of Sync or download a trial of any of our drivers or connectors to get started with easier access to your Workday data today!