Query Source Data

Learn how to query records from your data sources.

Access requirements

Custom profile with these capabilities: Data (Read, Detailed)

Data required: Access to all data such as the Super Admin permission

Reach out to your administrator for access.

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 Studio, on the global navigation bar, click Data > Query.

The following screenshot shows the Query room in the global workspace.

Search: Find a source or source column in the source directory.
Source directory: A list of available sources to query. Expand a source to see its columns and column data types. Hover over a source or column and then click the Add button to insert it into the query.
Filter: Filter the source directory to only sources that have records from included files.
Sort: Sort the source directory alphabetically in ascending or descending order.
Include records from excluded files: Option to include records in the query from data files that are excluded from the source.
Query box: The query statement.
Time picker: Select the time range against which to run the query, such as Last 30 Days or a custom date range. The time picker selects files based on their snapshot time.
Run Query: Execute the query.
Results actions: Options for the results table. You can also search for records in the results table.
- Information: More information about the source query tool, such as query result limits and how to interact with the results.
- Enter full screen: View the results in full screen.
- Download: Download the results in CSV format.
Results: The query results. Each query retrieves up to 1000 records.

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:

Copy

Format 1

1
Source_Name.Column_Name

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

Copy

Format 2

1
`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.

To simplify your queries, use aliases for the field names and table names. This is useful when using joins. For example, consider the following query.

Copy

1
SELECT Employee.EmployeeID, Employee.Title, Employee.Email_Address, `Employee Exit`.`Fiscal Period` FROM Employee INNER JOIN `Employee Exit` ON Employee.EmployeeID = `Employee Exit`.EmployeeID

You can simplify the above query by adding an alias to the tables, as shown next.

Copy

1
SELECT Emp.EmployeeID, Emp.Title, Emp.Email_Address, Exit.`Fiscal Period` FROM Employee Emp INNER JOIN `Employee Exit` Exit ON Emp.EmployeeID = `Exit`.EmployeeID

Source directory

Use the source directory in the Query room to view all available sources. In the source directory, you can:

Expand a source to see its column and column data types.
Hover over the Source icon to see information about source records and files.
Insert a source or column into your query.
Filter the list of sources to only show sources that has records (loaded data).
Sort the list of sources alphabetically in ascending or descending order.

The following screenshot shows the source directory in the Query room.

Query functions

Note: Source query supports read-only functions. You cannot modify source tables using insert, transform, or other functions.

Visier supports the following functions in the source query tool. For examples of query statements, see Sample queries.

Aggregate functions

Function	Description
count	Returns the number of rows in a group.
count_if	Returns the number of rows in a group where a condition is true.
first	Returns the first value in a group.
first_value	Returns the first value in an ordered group.
last_value	Returns the last value in an ordered group.
max	Returns the largest value in a group.
mean	Returns the average value in a group.
median	Returns the middle value in a group.
min	Returns the smallest value in a group.
sum	Returns the total of all values in a group.

Bitwise functions

Function	Description
&	Performs a bitwise AND on two integer values.

Collection functions

Function	Description
exists	Returns true if a specified condition matches at least one row or element.
concat	Joins two or more strings together.

Conditional functions

Function	Description
case and when	Returns different values depending on one or more conditions.
coalesce	Returns the first non-null value in a list of values.
if	Returns one value if a condition is true and another value if it's false.

Conversion functions

Function	Description
cast	Converts a value to a specified data type.
date	Converts a value to a date.
decimal	Data type for a fixed-point number. Use with `cast` to convert a value to this type.
double	Data type for a double-precision floating-point number. Use with `cast` to convert a value to this type.
float	Data type for a single-precision floating-point number. Use with `cast` to convert a value to this type.
int	Data type for an integer. Use with `cast` to convert a value to this type.
string	Data type for text. Use with `cast` to convert a value to this type.
timestamp	Converts a value to a timestamp.
tinyint	Data type for a small integer. Use with `cast` to convert a value to this type.

Custom functions

Function	Description
getFileName	Returns the file name of the file being read.
getJsonWithPath	Returns the string representation of the JSON object at the given JSON path.
getUploadTime	Returns the upload time of the file being read.

Datetime functions

Function	Description
add_months	Adds a number of months to a date.
date_add, dateadd	Adds a number of days to a date.
date_diff, datediff	Returns the number of days between two dates.
date_format	Formats a date or timestamp as a string using a specified pattern.
date_from_unix_date	Converts a number of days since January 1, 1970 into a date.
date_part, datepart	Extracts a specified part, such as year or month, from a date.
date_sub	Subtracts a number of days from a date.
date_trunc	Truncates a date or timestamp to a specified unit, such as month or year.
day, dayofmonth	Returns the day of the month from a date.
dayofweek	Returns the day of the week from a date.
dayofyear	Returns the day of the year from a date.
hour	Returns the hour from a timestamp.
last_day	Returns the last day of the month for a given date.
minute	Returns the minute from a timestamp.
month	Returns the month from a date.
months_between	Returns the number of months between two dates.
now	Returns the current timestamp.
second	Returns the second from a timestamp.
timestamp_millis	Converts a number of milliseconds since January 1, 1970 into a timestamp.
timestamp_seconds	Converts a number of seconds since January 1, 1970 into a timestamp.
to_date	Converts a string to a date using a specified format.
to_time	Converts a string to a time value.
to_timestamp	Converts a string to a timestamp using a specified format.
to_unix_timestamp	Converts a date or timestamp to the number of seconds since January 1, 1970.
to_utc_timestamp	Converts a timestamp in a specified time zone to UTC.
trunc	Returns a date truncated to the unit specified by the format model.
unix_date	Returns the number of days since January 1, 1970 for a given date.
unix_micros	Returns the number of microseconds since January 1, 1970 for a given timestamp.
unix_millis	Returns the number of milliseconds since January 1, 1970 for a given timestamp.
unix_seconds	Returns the number of seconds since January 1, 1970 for a given timestamp.
unix_timestamp	Returns the current time, or a specified time, as the number of seconds since January 1, 1970.
weekday	Returns the day of the week as a number, starting from Monday.
weekofyear	Returns the week of the year for a given date.
year	Returns the year from a date.

Hash functions

Function	Description
xxhash64	Returns a 64-bit hash value for one or more input values.

Math functions

Function	Description
%	Returns the remainder after dividing the left value by the right value.
*	Multiplies two values.
+	Adds two values.
-	Subtracts the right value from the left value.
/	Divides the left value by the right value.
cbrt	Returns the cube root of a number.
ceil, ceiling	Rounds a number up to the nearest integer.
exp	Returns e raised to the power of a number.
pow, power	Raises a number to a specified power.
round	Rounds a number to a specified number of decimal places.
sqrt	Returns the square root of a number.

Predicate functions

Function	Description
!, not	Reverses a Boolean value.
!=	Returns true if the two values aren't equal.
<	Returns true if the left value is less than the right value.
<=	Returns true if the left value is less than or equal to the right value.
=, ==	Returns true if the two values are equal.
>	Returns true if the left value is greater than the right value.
>=	Returns true if the left value is greater than or equal to the right value.
ilike	Returns true if a string matches a pattern, ignoring case.
in	Returns true if a value matches any value in a specified list.
like	Returns true if a string matches a specified pattern.
or	Returns true if either condition is true.
rlike	Returns true if a string matches a specified regular expression.

String functions

Function	Description
char, chr	Returns the character for a given numeric code.
char_length, character_length, len, length	Returns the number of characters in a string.
chr	Returns the character for a given numeric code.
concat_ws	Joins two or more strings together using a specified separator.
contains	Returns true if a string contains a specified substring.
decode	Returns a result based on matching a value against a set of values.
endswith	Returns true if a string ends with a specified substring.
format_number	Formats a number as a string with a specified number of decimal places and thousands separators.
format_string	Formats a string using a specified pattern and set of values.
instr	Returns the position of the first occurrence of a substring within a string.
lcase, lower	Converts a string to lowercase.
left	Returns a specified number of characters from the start of a string.
lpad	Pads a string on the left to a specified length with a specified character.
ltrim	Removes leading spaces, or a specified set of characters, from a string.
replace	Replaces every occurrence of a substring within a string with another substring.
right	Returns a specified number of characters from the end of a string.
rpad	Pads a string on the right to a specified length with a specified character.
rtrim	Removes trailing spaces, or a specified set of characters, from a string.
split	Splits a string into an array using a specified delimiter.
split_part	Returns a specified part of a string split by a delimiter.
startswith	Returns true if a string starts with a specified substring.
substr, substring	Returns a substring of a string.
substring_index	Returns the substring before a specified number of occurrences of a delimiter.
to_number	Converts a string to a number using a specified format.
trim	Removes leading and trailing spaces, or a specified set of characters, from a string.
ucase, upper	Converts a string to uppercase.

Window functions

Function	Description
lag	Returns the value from a previous row in the result set.
lead	Returns the value from a following row in the result set.
rank	Returns the rank of the current row within its partition, leaving gaps after ties.
row_number	Returns a unique sequential number for each row within its partition.

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:

Copy

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.

Copy

select `Snapshot Date`, `Employee ID`, `Employment Status` from `Employee Historical` where `Employee ID` = "M8L1GQPWT" and date_format(cast(`Snapshot Date` as date), "MM/dd/yyyy") < date_format(cast("12/31/2021" as date), "MM/dd/yyyy") order by date_format(cast(`Snapshot Date` as 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.

Copy

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.

Copy

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

In this example, the query returns the following string.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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.

Copy

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

Convert string to a date

This following query converts a string in the Snapshot Date column into a date in the yyyy-MM-dd format.

Copy

select date_format(cast(`Snapshot Date` as date), "yyyy-MM-dd") from `Employee Historical`

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.

Copy

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.

Copy

select * from Worker_Stock_WD_TestV2 limit 5

Subtract dates

The following query subtracts dates using Spark SQL.

Copy

select Count(Distinct `Employee ID`), `Performance Year` from `Employee Performance` where cast(months_between(date_format(cast(`Evaluation Period End Date` as date), "MM/dd/yyyy"), date_format(cast(`Evaluation Period Start Date` as date), "MM/dd/yyyy") 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.

Copy

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

Truncate a timestamp

Use the date_trunc function to truncate a date, time, or timestamp to a specified level of granularity, such as year, quarter, month, week, day, hour, minute, second, or microsecond. The following query truncates each employee's date of birth to the first day of the year. If the date of birth for an employee is 1992-05-15, the function returns 1992-01-01.

Copy

select date_trunc('year', date_format(cast(`DOB` as date), "yyyy-MM-dd")) from `Employee Historical`