Validate Data Versions Using the Workday Query Language

Write queries using the Workday Query Language (WQL) to validate data versions produced by Visier's Workday connector.

Overview

The Workday Query Language (WQL) is a query language used to interact with and retrieve data from the Workday system. In Studio, you can write WQL queries to validate the data versions produced by the Workday connector. Use the WQL to validate your Workday data as you configure the mappings and rules in Visier during initial onboarding.

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.

Get the WQL schema

  1. After the connector is created, click Request Sandbox.
  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. Select the table you want to query and look at its 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 .

Write the WQL query

Visier offers a dialect of Structured Query Language (SQL) that allows you to query data in Visier more easily. For more information, see Write SQL-Like Queries. To validate your data, 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.

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

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.

Copy 
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.

Copy 
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.

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

Note: Group by does not support multi-instance columns. employeeType is one example. Because of how this data is structured, you must use its corresponding calculated field 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.

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

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.

List query with filter

Use the where clause to filter records.

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

Copy 
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.

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

Note: Filter only support data types such as text, boolean, or numeric. Multi-instance and single instance types cannot be used in a where clause. gender is one example. Because of how the data is structured, you must use its corresponding text field cf_Gender1Letter.

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.

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