Concepts → 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
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.
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.
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.
The following date system variables are available to use in the global variable query expression:
It is not possible for the value of a global variable to reference the following variables:
- Internal session variables
- External session variables
- Filter expression session variable
With the introduction of the 2022.4.0 release, you can reference a global variable in multiple contexts as follows:
- Formula Columns
- The extraction Query and Update Query of a physical schema table of the type SQL database as the data source table
- The Script and Incremental Script of a materialized view (MV) (in case the query result is a scalar single value)
- Other global variables
- The Analyzer formula individual filters
- Dashboard Filter options
- Dashboard Applied filters
- Presentation variables
The syntax to reference a global variable varies according to the context.
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,
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.
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.
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
SELECT `ID`, `NAME`, `LOADMODE`, `PATH`, `DESCRIPTION`, `ENABLED`FROM`db_incorta_metadata`.`TENANT`WHERE `NAME` = $$gvar_TenantName
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.
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
In the following example, the
gvar_CustomerType returns a string value of
S, and the
gvar_Quarter returns an integer value of
SELECT$$gvar_Quarter As Quarter,OnlineStore.customer.CustomerID,OnlineStore.customer.CustomerType,OnlineStore.customer.ModifiedDateFROMOnlineStore.customerWHEREOnlineStore.customer.CustomerType = '$$gvar_CustomerType'
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.
Although it may be possible to achieve, the usage of global variables in the following contexts is not supported.
- Dashboard runtime filters
- Join filters
- Internal session variables
- External session variables
- Runtime security filters for a table or MV
- Load filters for a table or MV
- Dashboard Prompts
- Incorta SQL Tables
- Incorta SQL Views
- The value in Analyzer column individual filters
Referencing a global variable that is resolved to an array of values in MV scripts does not work properly in the meantime.
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
String global variable
The result of the following global variable is an array of the country names, which are of a string data type.
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.
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.
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.
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.
- 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.
Starts Withare 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.