Write SQL-Like Queries

Learn how to author queries using Visier's SQL-like language.

Overview

Visier offers a dialect of Structured Query Language (SQL) that allows you to query data in Visier more easily. SQL is an existing domain-specific language that lets users write human-readable queries. Visier provides a SQL-like querying option for you to perform operational data activities, including querying a dataset and ingesting data.

You can use SQL-like with Visier APIs or through our SQL-like shell on GitHub.

Visier's SQL offering is "SQL-like" because it's not constrained by the American Notation Standards Institute (ANSI) SQL standard, but it's similar enough to ANSI SQL that users who are already familiar with SQL won't face any syntactic or structural barriers when using Visier's SQL-like querying.

You can use SQL-like in Visier to query data in your Visier tenant, including subjects, events, metrics, dimensions, and some concept types.

The following table catalogs the SQL-like supported and unsupported objects in Visier's data model. For more information about Visier's data model, see Understand Visier's Analytic Model.

Clauses and time handling

SQL-like querying uses a selection of standard SQL's projection clauses, and Visier's own unique time handling method.

FROM

The FROM clause specifies an analytic object in Visier and declares the relationship between underlying data objects. The analytic object is the table object from which to select data.

In the following example, the FROM clause specifies that the correct analytic object is Employee.

1
2
SELECT employeeCount(), level(Disability_Status, 'Disability_Status') AS Status
FROM Employee

SELECT

The SELECT clause defines the shape of the result set. You can assign aliases to the fields in the SELECT clause.

If the data source is a metric, the SELECT clause turns the query into an aggregate query that returns numeric values (as opposed to a list query). Metrics have an intrinsic aggregation function; as such, metrics act like an aggregation function in SQL-like statements. Though metrics look different from simple aggregation functions like count or sum, the same principle applies.

In the following example, the SELECT clause is calling the employeeCount metric. Because employeeCount is a metric, this SQL-like query is equivalent to an aggregate query.

1
2
SELECT employeeCount() AS "Headcount", Union_Status
FROM Employee

If the data source is a dimension, use level to select specific dimension levels. If a level isn't specified, the SELECT clause references members on any level.

In the following example, the level function selects the Location dimension's Country level. This returns all members of the Country level, such as Canada, Brazil, and Japan.

1
2
SELECT employeeCount(), level(Location, 'Country') AS Location
FROM Employee

In the following example, the query doesn't define a level for Location. This returns all members at the top-level of the dimension, such as North America, South America, and APAC.

1
2
SELECT exitCount(), Employee.Location
FROM Employee

In the following example, the query doesn't define a level for Age_Ranges because it does not have multiple levels. This returns a level 0 summary line representing the only level of the dimension.

1
2
SELECT employeeCount(), Age_Ranges
FROM Employee

View sample response

Copy

1
2
3
4
5
6
7
8
9
10
11
12
13
employeeCount|Age_Ranges                  |Visier_Time             
-------------------------------------------------------------------
4360         |                            |2023-04-01T00:00:00.000Z
69           |Age_Ranges.Months_240_To_300|2023-04-01T00:00:00.000Z
1991         |Age_Ranges.Months_300_To_360|2023-04-01T00:00:00.000Z
1123         |Age_Ranges.Months_360_To_420|2023-04-01T00:00:00.000Z
536          |Age_Ranges.Months_420_To_480|2023-04-01T00:00:00.000Z
214          |Age_Ranges.Months_480_To_540|2023-04-01T00:00:00.000Z
188          |Age_Ranges.Months_540_To_600|2023-04-01T00:00:00.000Z
113          |Age_Ranges.Months_600_To_660|2023-04-01T00:00:00.000Z
56           |Age_Ranges.Months_660_To_720|2023-04-01T00:00:00.000Z
37           |Age_Ranges.Months_720_To_780|2023-04-01T00:00:00.000Z
33           |Age_Ranges.Months_780_To_Inf|2023-04-01T00:00:00.000Z

SET

SQL provides the SET clause to allow database implementors to implement session-state modification. Visier supports using SET in our SQL-like shell to switch between the analytic schema and the staging (or data load) schema; for example SET schema TO staging.

WHERE

There WHERE clause evaluates the data using an operator. In Visier's SQL-like language, use the WHERE clause to filter your data.

If the data source is a leveled dimension, use path qualifiers through dot notation to select specific levels. In the following example, the WHERE clause uses dot notation to select the Location Canada in the North America path.

1
WHERE Location IN ('[North America].[Canada]', '[EMEA].[Germany].[Bavaria]')

If the data source is a selection concept, use ANSI SQL syntax. In the following example, the WHERE clauses uses ANSI SQL syntax to assert the selection concept isHREmployee is TRUE.

1
WHERE isHREmployee = TRUE

Visier_Time

To handle time in your SQL-like queries, use the Visier_Time symbol.

Use Visier_Time in aggregate queries

When writing an aggregate query, you can specify the time period date in YYYY-mm-dd format, the number of periods in the interval, the period count, the period type, and the direction of the data. In aggregations, the query time is required to aggregate the right data values.

In the following example, Visier_Time is defined as starting on April 1, 2020 (the time period date) for 4 periods (the number of periods in the interval). The period count is 3 and the period type is Month. The direction is not specified, so the default Backward is used.

1
Visier_Time IN periods(date("2020-04-01"), 4, period(3, Month))

If you omit the Visier_Time symbol, the default time is one month of data backward from the day you submit the query.

If using Visier_Time with periods, you must define the start date. If using Visier_Time with period, you must define the number of periods. The default period type is Month.

In the following example, the query defines 4 periods starting from April 1, 2020. Each period is 3 months long. The default direction is Backward.

1
2
3
4
5
SELECT
    employeeCount() AS Employee_Count,
    Visier_Time AS Reporting_Period
FROM Employee
    WHERE Visier_Time IN periods(date("2020-04-01"), 4, period(3, Month))

Use Visier_Time in list queries

When writing a list query, you can use a BETWEEN clause to specify that dates between which to execute the query. If you omit the Visier_Time symbol, the default time is one month of data from the day you submit the query.

In the following example, the query defines the time between January 1, 2020 and April 1, 2020.

1
2
3
4
5
6
SELECT
    EmployeeID,
    level(Gender, "Gender") AS Gender
FROM Employee
WHERE
    Visier_Time BETWEEN date("2020-01-01") AND date("2020-04-01") 

Aggregate queries

An aggregate query retrieves values over a period of time, such as multiple months. You can group and filter your data in an aggregation query to retrieve detailed information.

In the following example, the query is using the employeeCount metric and grouping the data by Gender. Because Visier_Time is not defined, the query uses the default time period. The response will return the number of employees grouped by gender in the last month.

1
SELECT employeeCount(), GenderFROM Employee

List queries

A list query provides information about values for selected data points, and is not an aggregated value. As non-aggregated entities, list queries must come from a single analytic object and the source cannot be a metric.

In the following example, the query is selecting Birth_Date and Gender from the Employee object. Because Visier_Time is not defined, the query uses the default time period. The response will return the birth dates and genders for each employee in the last month.

1
2
SELECT Birth_Date, Gender
FROM Employee

Frequently asked questions

How does SQL-like handle multiple analytic objects?

In some situations, like with derived metrics, a query contains more than one analytic object. The following table describes how SQL-like handles queries that contain more than one analytic object.

Can I use joins and references?

Visier doesn't support multiple driving tables in one query. Additionally, Visier's data model defines joins through "references". References qualify symbols in SQL-like using a dot-notation. SQL-like uses dot notation to eliminate the need for explicit JOIN syntax.

In the following example, the metric activeApplicantCount is implicitly qualified on Applicant, so Applicant_Stage doesn't require reference qualification. The hiring manager's gender is expressed using dot notation to reference the analytic object Requisition. In this case, Gender traverses two forward-references through the dot notation Requisition.Hiring_Manager to arrive at the appropriate object.

1
2
SELECT activeApplicantCount() AS "Total Count", Applicant_Stage, Requisition.Hiring_Manager.Gender
FROM Applicant

Can I define parameters in a query?

Yes! In the following example, the query defines a numeric parameter of 55 for the Retirement_Eligibility metric.

1
2
SELECT Retirement_Eligibility(Retirement_Age_Parameter = 55)
FROM Employee

In the following example, the query defines the member parameters to include all of Canada except Ontario for the empCountByLocation metric.

1
2
3
SELECT empCountByLocation(Emp_Loc_Param IN ('[North America].[Canada]'),
    Emp_Loc_Param NOT IN ('[North America].[Canada].[Ontario]'))
FROM Employee

How do I set aliases?

In projection clauses, like SELECT, aliases provide more human-readable column headers, as shown next.

1
2
3
4
5
6
7
8
SELECT 
    level(Location, "Location_1") AS Country,
    level(Gender, "Gender") AS Gender,
    level(Union_Status, "Union_Status") AS Is_Union_Member,
    employeeCount() AS "Employee Count"
FROM Employee 
WHERE 
    Visier_Time IN periods(date("2020-04-01"), 2, period(3, Month)) 

How do I set pagination?

Pagination isn't supported in SQL-like.