Concepts → Global Variable

About a Global Variable

Unlike other objects in Incorta, global variables are available to all tenant users as they are not subject to user access rights. A global variable has a name, description, and value. Starting the 2022.4.0 release, the value of a global variable is not only a static value anymore. It can be the outcome of a query or queryDistinct expression, which can be a scalar value or an array of scalar values.

Global variables are evaluated when invoked in the related context, whereas session variables are evaluated when the user session starts. Being unlimited by the user session allows global variables to be beneficial in different use cases. Although the first use case of global variables was to leverage them in the queries and scripts of physical schema tables and materialized views (MVs), they can be used in formula columns and individual filters as well starting the 2022.4.0 release.

Managing Global Variables

Using the Schema Manager, you can create and manage global variables. The tenant SuperUser Admin and users that belong to a group assigned the SuperRole role or the Schema Manager role can create and manage global variables for a given tenant.

For information about how to create and manage global variables, refer to Schema Manager actions for global variables.

Referenceable variables

When you create a global variable, you use the Formula Builder to specify the query expression of the global variable value. Starting the 2022.4.0 release, a global variable can reference another global variable. In addition, it can reference date system variables.

Date system variables

The following date system variables are available to use in the global variable query expression:

  • $currentDate
  • $currentDay
  • $currentMonth
  • $currentMonthEnd
  • $currentMonthStart
  • $currentQuarter
  • $currentQuarterStart
  • $currentTime
  • $currentWeek
  • $currentWeekStart
  • $currentYear
  • $currentYearStart
  • $dayAgo
  • $lastMonth
  • $lastMonthStart
  • $lastQuarter
  • $lastQuarterStart
  • $lastWeek
  • $lastWeekStart
  • $lastYear
  • $lastYearStart
  • $monthAgo
  • $nextMonthStart
  • $quarterAgo
  • $weekAgo
  • $yearAgo

Non-referenceable variables

It is not possible for the value of a global variable to reference the following variables:

Supported global variable usage

With the introduction of the 2022.4.0 release, you can reference a global variable in multiple contexts as follows:

Note

The syntax to reference a global variable varies according to the context.

Global variables in the Formula Builder

To reference a global variable in the Formula Builder, whether when creating a formula column or an individual filter, precede the global variable name with a single dollar sign $, for example, $global_variable_name.

The following is an example of a formula individual filter in an insight. The GLVar_Region global variable returns each region where at least one of its countries has revenue that exceeds 2 M$. Thus, this filter will show the records related to these regions only.

inList(
SALES.COUNTRIES.COUNTRY_REGION,
$GLVar_Region
)

The following example showcases using the global variable $fiscal_offset_months in an Incorta Analyzer view formula column. The $fiscal_offset_months returns a value of -6, which represents the offset in months when a fiscal calendar is different from a Gregorian calendar. Here, for each row, the formula column will show the fiscal date, which is 6 months before the sales date, in the defined format.

formatDate(
addMonths(
SALES.SALES.TIME_ID,
$fiscal_offset_months
),
"DD-MM-YY"
)

Global variables in extraction queries

Before the 2022.1.0 release, the only supported usage of a global variable is in a physical schema table of the type SQL database as the data source. The usage supports both Query and Update Query configurations.

In this context, the syntax to reference a global variable is $$global_variable_name. In the following example, the gvar_TenantName variable returns the string value demo.

SELECT `ID`, `NAME`, `LOADMODE`, `PATH`, `DESCRIPTION`, `ENABLED`
FROM
`db_incorta_metadata`.`TENANT`
WHERE `NAME` = $$gvar_TenantName

Global variables in MV scripts

Starting the 2022.1.0 release, you can reference global variables in MVs when you use the Notebook Editor or the Query Builder to add or edit the MV script or incremental script using any supported language.

Warning

You must reference only global variables that are evaluated to a single scalar value. MVs do not support global variables evaluated to an array of values.

To reference it in an MV, precede the global variable name with a double dollar sign $$, for example, $$global_variable_name. In addition, when referencing string, date, and timestamp global variables, you must use single or double quotes depending upon the MV language. For Spark Scala, use double quotes, for other languages, use single quotes, for example '$$string_gvar_name'.

In the following example, the gvar_CustomerType returns a string value of S, and the gvar_Quarter returns an integer value of 3.

SELECT
$$gvar_Quarter As Quarter,
OnlineStore.customer.CustomerID,
OnlineStore.customer.CustomerType,
OnlineStore.customer.ModifiedDate
FROM
OnlineStore.customer
WHERE
OnlineStore.customer.CustomerType = '$$gvar_CustomerType'
Important

Global variables in MVs are evaluated when validating the MV script and when loading them. Thus, when you edit the value of a global variable, you must perform a full load of the related MVs.

Unsupported global variable usage

Although it may be possible to achieve, the usage of global variables in the following contexts is not supported.

Note

Referencing a global variable that is resolved to an array of values in MV scripts does not work properly in the meantime.

Additional considerations

Global variable data type

The data type of a global variable value is determined by the query syntax and referenced objects and columns. The following are examples of global variables with different data types.

Date global variable

query(
SALES.SALES1.TIME_ID,
between(
SALES.SALES1.TIME_ID,
date(
"2010-01-01"
),
date(
"2010-04-01"
)
)
)

String global variable

The result of the following global variable is an array of the country names, which are of a string data type.

query(
SALES.COUNTRIES.COUNTRY_NAME,
contains(
SALES.COUNTRIES.COUNTRY_NAME,
"S"
)
)

The result of the following global variable is an array of the region names, which are of a string data type.

query(
SALES.COUNTRIES.COUNTRY_REGION,
SALES.COUNTRIES.AMOUNT_SOLD > 2000000
)

The result of the following global variable is of a string data type because of the double-quotes.

query("1234")

Integer global variable

The result of the following global variable is an array of the Customer IDs, which are of an integer data type.

queryDistinct(
Online_Store.Customers.CustomerID,
Online_Store.Customers.Rank < 4
)

The result of the following global variable is of an integer data type too.

query(1234)

Global variable name

The following are the rules for naming a global variable:

  • Must be between 1 and 250 characters in length
  • Must begin with an alpha character (lower or upper case) or an underscore (_)
  • After the first character, can contain zero or more alphanumeric characters in lower, upper, or mixed case
  • Besides underscores (_), cannot contain special characters, symbols, or spaces
  • Must be unique in the tenant
  • Cannot share the same name with an internal or external session variable
  • Is case-sensitive

Once defined, the value of the Name property of a global variable cannot be changed.

Note

If you have a global variable and a presentation variable that share the same name, the presentation variable will take precedence over the global variable.

Limitations and known issues

  • No validation on functions is triggered in the Formula Builder when creating global variables.
  • Global Variables will not appear on the list of variables when referenced in a column individual filter, filter option, or applied filter, that is when you type$ in the search values box. However, you can add the global variable manually, if applicable. In Query, Contains, and Starts With are examples of functions that accept a global variable as a filter value to be added manually.
  • Global Variables that return a list of values are not functioning properly when referenced in MVs or individual filters.
  • Global variables do not support aggregation functions for now.