Workday Query Language

Write queries using the Workday Query Language (WQL) to bring data into Visier from Workday.

The Workday Query Language (WQL) connector provides an additional, efficient way to retrieve data from Workday. WQL allows you to write custom queries to get aggregated and filtered data from any Workday table. This method efficiently retrieves custom fields, providing an alternative to generating Reports-as-a-Service (RaaS) reports.

Create a WQL connector

1. Add OAuth 2.0 credentials in Studio.

   Note: WQL only supports OAuth 2.0 for authentication. For instructions on how to create OAuth 2.0 credentials in Visier, see Workday.

2. In a project, on the navigation bar, click Data > Connectors. Create a connector and select the Workday WQL connector type. For instructions, see Set Up Data Connectors.

Create a table

To extract data, you must create a table and link your WQL query to it. To explore the tables and columns you can use when writing queries, look at the WQL schema in the Request Sandbox tab. For more information, see Get the WQL schema.

1. In the Customize tab of the connector, click Create Table.
2. In the Create table dialog, type a name for your table. You cannot rename the table after it has been created.

3. Click Create.

4. After the table is created, enter a WQL query to define the data that is extracted from the table in the Query box. For more information, see Write the WQL query.
5. You can configure additional settings such as whether to generate a new source or include the table in the extraction.
6. Optional: If you want the connector to retrieve additional data, add another table by clicking the Create button at the top of the Query based tables list.

7. Click Preview to see the query results before you run an extraction job.

   Tip: Paginate results when querying a large data set

   • Use the the Limit and Offset options to control the number of records returned in one query. For example, if the estimated number of records is approximately 10,000. Set the limit to 1000, and offset to 0 to get the first 1000 records, then limit to 1000 and offset to 1000 for the next 1000 records, and so on.
   • By default the limit is 100.
8. When finished run the extraction job, Run a Data Connector Extraction Job.

Write the WQL query

Use the following code samples as guidance when writing your WQL queries. For more information about the WQL syntax, see WQL Reference.

Queries can be static or dynamic. Static queries remain the same whenever they're executed, while dynamic queries change each time they are executed based on parameters and runtime variables. Dynamic queries offer flexibility by allowing you to use parameters like ${currentExtractionDate}, ${lastExtractionDate}, and ${snapshotDate} to retrieve data from a specified date range or a single date, providing the ability to extract time-specific information as needed.

Static query

For example, this query returns the current number of workers every time the extraction is performed.

SELECT COUNT() FROM allWorkers

This query retrieves worker base data (ID and descriptor), employee first name, last name, and employee ID for all employees with a termination date between March 1, 2025 and January 1, 2020.

SELECT worker, firstName, lastName, employeeID FROM allWorkers WHERE terminationDate <= "2025-03-01" and terminationDate > "2020-01-01"

Dynamic query

You can use the following parameters in your queries: ${currentExtractionDate}, ${lastExtractionDate}, and ${snapshotDate} to retrieve incremental updates or a snapshot of all your data at a point-in-time. When the query is executed, the parameters will be updated based on the extraction job settings.

These parameters can be used across all tables in a connector. Be aware when you schedule extraction jobs for specific tables at different times, the dynamic queries will resolve parameter values based on the most recent extraction job for that connector. To ensure you're extracting data for your desired time range, use the quick extraction options Limit records from and Limit records to to define the start and end dates.

Incremental extraction query

Use the ${currentExtractionDate} and ${lastExtractionDate} and parameters to retrieve new or changed records since the last extraction.

This query retrieves worker base data (ID and descriptor), first name, last name, and employee ID of employees that were terminated since the last extraction job.

1
SELECT worker, firstName, lastName, employeeID FROM allWorkers WHERE terminationDate <= ${currentExtractionDate} and terminationDate > ${lastExtractionDate}

By default, the ${lastExtractionDate} parameter is replaced with the date and time of the last extraction job (lower bound) and ${currentExtractionDate} is replaced with the current date and time (upper bound). If the start and end dates of the job are defined in the quick extraction options, those dates will be used instead.

Snapshot extraction query

Use the ${snapshotDate} parameter to retrieve a set of records at a point-in-time.

This query retrieves the employee ID, first name, and last name of all active employees as of the extraction date.

1
Select employeeID, firstName, lastName FROM allActiveEmployees (effectiveAsOfDate = ${snapshotDate})

By default, the query extracts monthly snapshots from the date and time of the last extraction job (lower bound) to the current date and time (upper bound). If the start and end dates of the job are defined in the quick extraction options, those dates will be used instead. For example, if the current date is March 31, 2025, and the last extraction was on December 31, 2024, the query will retrieve data for all active employees as of March 31, 2025. It will also include snapshots of their records for January and February 2025.

Aggregate query

Headcount query

This query retrieves 1 month of Headcount data up to October 31, 2024. It does not use any group bys or filters. This is equivalent to the Headcount metric for October, 2024.

1
select count() from allActiveEmployees (effectiveAsOfDate = "2024-10-31")

Headcount query with group by

This query retrieves 1 month of Headcount data grouped by Gender up to October 31, 2024.

1
2
select count(), gender from allActiveEmployees (effectiveAsOfDate = "2024-10-31")
group by gender

This query retrieves 1 month of Headcount data grouped by Gender and Employee Type up to October 31, 2024.

1
2
select count(), gender, cf_EmployeeTypeSI from allActiveEmployees (effectiveAsOfDate = "2024-10-31")
group by gender, cf_EmployeeTypeSI

List query

Basic list query

This query retrieves the employee ID, first name, last name for all employees who were active as of October 31, 2024. It does not use any group bys or filters. The effectiveAsOfDate clause allows you to set the time parameter of the query.

1
select employeeID, firstName, lastName from allActiveEmployees (effectiveAsOfDate = "2024-10-31")

List query with filter

Use the where clause to filter records.

This query retrieves the employee ID, gender, and employee type for employee 210001.

1
2
select workdayID, employeeID, gender, cf_EmployeeTypeSI from allActiveEmployees (effectiveAsOfDate = "2024-10-31")
where employeeID = "21001"

This query retrieves the employee ID, gender, and employee type for regular, women employees who were active as of October 31, 2024.

1
2
select workdayID, employeeID, gender, cf_EmployeeTypeSI from allActiveEmployees (effectiveAsOfDate = "2024-10-31")
where cf_employeeTypeText = "Regular" and cf_Gender1Letter = "F"

Event query

If you're querying an event table, such as termination, use the proper event date in the where clause.

This query retrieves all employee terminations for 2022.

1
2
select employeeID, terminationDate from allTerminatedEmployees 
where terminationDate <= "2022-12-31" and terminationDate >= "2022-01-01"

Validation

You can also use the WQL connector to validate the data you're retrieving from the Workday connectors. Write aggregate queries in WQL and compare the results with the metrics in Visier. Then, write list queries in WQL and compare the result with the Detailed View visual in Visier to investigate the root cause of any discrepancies. Validate by getting the WQL schema and writing queries in the Request Sandbox tab of the connector.

Get the WQL schema

See a list of tables and columns available for querying.

1. After the connector is created, click the Request Sandbox tab.
2. In the Request Sandbox tab, select Data from the Type list.

   Result: A list of tables available for querying will appear. This may take some time.

3. Explore the schema to see the tables you can query from and their columns.

   Tip:  

   • Use search to quickly find tables and columns.
   • To view columns in a table, click the Expand button .
   • To insert tables or columns to your query, click the Add button .
4. Write a query and click Send to run it.