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 the studio experience, 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 the studio experience.

   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.

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.

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"