Query Source Data

Learn how to query records from your data sources.

Overview

You can investigate your source data in Visier with the source query tool. Sources in Visier store data before processing; a source contains the raw data from any data files that have the same content and structure. You can write SQL-like queries to retrieve specific records from your sources, such as finding duplicate records or finding which files contain a specific record. To access the source query room, do one of the following:

• In the solution experience, in a visualization, click the Info button > Metric lineage > Metric Validation button > Launch Source Query.
• In the studio experience, on the global navigation bar, click Data > Query.

Prerequisites

To retrieve records using source query, your sources must contain data. For more information, see Sources.

Write a query

In the Query tab, you can write a query to retrieve records from your sources in Visier. You can use SQL syntax to write queries in Visier. For more information about supported functions and keywords, see Query functions.

Query format

In a query, insert source data using one of the following formats:

• Source_Name.Column_Name

  For example, Starts_Events.Position_Code identifies the Starts_Events source and the Position_Code column within that source.

• `Source Name`.`Column Name`

  For example, `Exit Events`.`Fiscal Period` identifies the Exit Events source and the Fiscal Period column within that source. If your source or column name contains spaces, you must use this format.

To use features like auto-complete and syntax highlighting, we recommend always using `Source_Name`.`Column_Name` format. Type ` in the query box to see a list of all sources that you can insert into the query with auto-complete.

Sources and columns may have more than one name in Visier, such as an object name and a display name. When writing a query, be sure to use the right name type:

• Source: Use the display name. This can be found in the Basic Information tab of a source in the Display Name field.
• Column: Use the original name. This can be found in the Columns tab of a source in the Original Name column.

Sample queries

The following section offers a selection of sample queries that cover different source query use cases.

Query multiple sources to create a single table

Let's say you want to select columns from the Employee and Employee Exit sources to identify employees that exited the organization. Using the source explorer, you can query both sources to create a single table that contains the information that you want to review.

To retrieve that data, you can write the following query: SELECT Employee.EmployeeID, Employee.Title, Employee.Email_Address, `Employee Exit`.`Fiscal Period` FROM Employee INNER JOIN `Employee Exit` ON Employee.EmployeeID = `Employee Exit`.EmployeeID

The above statement tells Visier to retrieve three columns from the Employee source: Employee ID, Title, and Email Address, and one column from the Employee Exit source: Fiscal Period. The statement then tells the platform to join the Employee and Employee Exit sources on the column EmployeeID, which appears in both sources.

The table that's returned includes data from both sources and combines the EmployeeID column so that it isn’t duplicated, as shown in the following screenshot.

Query using snapshot dates

The following query uses SQL to retrieve records that have snapshot dates.

select `Snapshot Date`, `Employee ID`, `Employment Status` from `Employee Historical` where `Employee ID` = "M8L1GQPWT" and to_date(`Snapshot Date`, "MM/dd/yyyy") < to_date("12/31/2021", "MM/dd/yyyy")

order by to_date(`Snapshot Date`, "MM/dd/yyyy")

Query starts, exits, and employee profile in one statement

The following query gets the first employee record and then gets the start and exit information. This query runs even if there are no starts or exits for the employee ID. In this query, UPI is equal to EmployeeID.

select first(employment_Profile.RECORD_DATE), employment_Profile.UPI, employment_Profile.STATUS, employment_start.HIRE_DATE, employment_start.Employment Start Type, employee_exit.TERMINATION_DATE, employee_exit.TERMINATION_REASON from employment_Profile Left outer Join employment_start on employment_Profile.UPI = employment_start.UPI left outer join employee_exit on employment_Profile.UPI = employee_exit.UPI where employment_Profile.UPI in ("15103121", "15085148", "15087173", "15092691", "151003877") group by employment_Profile.UPI, employment_Profile.STATUS, employment_start.HIRE_DATE, employment_start.Employment Start Type, employee_exit.TERMINATION_DATE, employee_exit.TERMINATION_REASON

Identify uploaded files that contain a specific record

The following query identifies which uploaded files contain a specific record.

select distinct(right(getFileName(), getUploadTime())) from `Employee Performance` where `Employee ID` = "111487"

In this example, the query returns the following string.

20230118_1952251888_8550ced3-29e7-4315-8d8-6e3361405e74_upload/Filename.ext.parquet

• 20230118: The date the file was uploaded.
• 8550ced3-29e7-4315-8d8-6e3361405e74: The file's upload ID.
• Filename.ext: The uploaded file's display name.

Identify duplicate records

The following query returns Employee_IDs with duplicate records.

select Count (Employee_id), Employee_id from SourceFile group by Employee_id having count(Employee_id) > 1

Identify the number of duplicate records

The following query returns the total number of duplicate records. If the numbers are the same, there are no duplicates. If the numbers are different, duplicates is count - Distinct count. The previous Identify duplicate records query returns which values are duplicated.

select count(DISTINCT EmployeeID), count(EmployeeID), SnapshotDate

from Employment_Start

group by SnapshotDate

Validate new files to ensure loads are correct

The following query gets the number of records for a value.

select count(EmployeeID), EmploymentStatus f1 from EmployeeProfile group by f1

You can use this query for any field by replacing the dimension in the query. The following query is the same as the above query, but replaces EmploymentStatus with ContractType.

select count(EmployeeID), ContractType f1 from EmployeeProfile group by f1

Count unique records by year, month

The following query counts unique records by year and month. This is useful for starts and exits.

select count(distinct EmployeeID), date_part('year', `EventDate`) Year, date_part('Month', `EventDate`) Month from Employment_Start_UKG group by Year, Month, order by Year, Month

Count ratings by rating for active records only

The following query uses an anti join to exclude all records from the left side of the join when there is a record from the right side. If a record appears in the terminations or exits files, it isn't included in the list.

select Count(distinct `Employee Performance`.`Employee ID`) Records, `Performance Cycle`, `Performance Rating Code` Code, `Performance Rating Display Name` Rating from `Employee Performance` anti join `Employee_Exit_DS` on `Employee Performance`.`Employee ID` = Employee_Exit_DS.`Employee ID` where `Performance Year` = "2022" group by `Performance Cycle`,`Performance Rating Code`, `Performance Rating Display Name` order by `Performance Cycle`,`Performance Rating Code`, `Performance Rating Display Name`

Check event dates

The following query checks dates.

select * from employee_paychanges where date_part('year', `Visier Event Date`) ="2023" and cast(`Visier Event Date` as date) > cast('2023-03-22' as date)

Check for negative values

The following query checks for negative values.

select count(`Employee ID`), Date_part_('year', `Visier Event Date`) Year from employee_paychanges where date_part('year', `Visier Event Date`) = "2023" and cast(`Change Amount` as decimal) < 0 group by year

Check if the direct manager in employee profile is terminated

The following query checks if the direct manager in an employee profile is terminated. This query uses aliases to join employee profile to employee profile.

select employment_Profile.UPI, employment_Profile.MANAGER_UPI, MAN.STATUS from employment_Profile inner join employment_Profile MAN on employment_Profile.MANAGER_UPI=MAN.UPI where employment_Profile.STATUS!= "Terminated" and MAN.STATUS = "Terminated" order by employment_Profile.MANAGER_UPI

Limit the number of records returned

The following query limits the numbers of records returned when testing queries. In the following example, the query returns 5 records.

select * from Worker_Stock_WD_TestV2 limit 5

Subtract dates

The following query subtracts dates using Spark SQL.

select Count(Distinct `Employee ID`), `Performance Year` from `Employee Performance` where cast(months_between(to_date(`Evaluation Period End Date`), to_date(`Evaluation Period Start Date`)) as INT) > 24 group by `Performance Year`

Summarize totals as an integer

The following query summarizes totals as an integer using cast and sorts the results in descending order.

select sum(cast (Amount as decimal)), Company Code from revenue_sample group by Company Code order by sum(cast (Amount as decimal)) desc