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 some 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 Super User 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:

• Internal session variables
• External session variables
• Filter expression session variable
• The $user system variable

Supported global variable usage

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

• Formula Columns
  • Incorta Analyzer View formula column
  • Business Schema View formula column
  • Insight formula column
• 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) (if the query result is a scalar single value)
• Other global variables
• The Analyzer formula individual filters in insights and Incorta Analyzer Views
• Dashboard Filter options
• Dashboard Applied filters
• Presentation variables

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, in supported contexts, 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.

Copy
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.

Copy
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.

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

Global variables in MV scripts

Starting with 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.

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.

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

Unsupported global variable usage

• Join filters
• Load filters
• Incorta SQL Tables
• The value in Analyzer column individual filters
• Incorta Analyzer Table formula column

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

Copy
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.

Copy
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.

Copy
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.

Copy
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.

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

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

Copy
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.

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 an 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.

On-demand migration

Starting the 2022.7.0 release, a migration script is available to update all the global variables with old syntax in your tenant to the new syntax that uses the query and queryDistinct expressions. Contact Incorta Support to have the migration script run on your tenant.

The result of the migration will be as follows: