Variables

VinayR · ‎06-08-2022

Introduction

A variable stores a value of a specific data type. For example the variable X is an integer and stores only positive and negative whole numbers. A variable typically has a default value for initialization, for example, X=1.

Some variables are mutable, meaning the values they store can change during the scope of a user session. There are two types of mutable variables in Incorta:

Presentation Variables
Session Variables.

Variables that are immutable, meaning the Incorta Analytics service initializes the variable with an initial value that cannot be changed, are:

System Variables
Global Variables

What you should know before reading this article

We recommend that you be familiar with these Incorta concepts before exploring this topic further:

Applies To

These concepts apply to all releases of Incorta.

Let's Go

Presentation Variables

Defined in Dashboard Filters, presentation variables only exist within the scope of the given dashboard and the user interacting with the dashboard. Presentation variables are mutable by design, meaning user interactions can set the value of the variables. When a user leaves the dashboard, the Analytics Service evicts the presentation variable from memory.

Presentation Variables exhibit behavior similar to Prompts in the Filter bar. The default behavior is for the dashboard to override the existing column filter list with the list of presentation variables for the dashboard filters. Unlike Prompts, only the Filter dialog shows the presentation variables as column filters.

Presentation Variables Usage

A presentation accepts an input from the dashboard user and alters the data shown on the dashboard. Here are the differences between using a presentation variable and using a dashboard prompt.

- A presentation variable does not have to be based on a specific column used in the dashboard query. It can be validated against a table that has no relationship with the tables used in the query. The only purpose of having an associated table column is to limit the possible values shown in the list of values.

- A presentation variable can affect the selected insights or measures, not the entire dashboard. It has no effect on the insight until you use it in an applied filter, in an insight filter, or in a formula.

Session Variables

The user session defines the scope of a Session variable.

When a user signs in to a tenant, the Incorta Analytics Service creates a user session. A user session has access to all System Variables, including the special user system variable. The user system variable is a special system variable that stores the login name for the user.

Only when a user interaction invokes a session variable, such as when a user opens a dashboard that references a session variable, does the Analytics Service assign a value to the invoked session variable.

The session variable, and its value, only exists within the scope of the user session. When a user signs out of Incorta, the Analytics Service evicts all related user session variables from memory.

Session Variables Usage

As a default value to a default prompt

Users would like to see the data in the current period, and switch to a prior period only as necessary.

A session variable is created to get the Current Period.

As a default value to a presentation variable

Example: A presentation variable is used to select the currency code used to see the converted amount in a given currency.

The user would like to see the data in their own currency where the user’s division is located. Create a session variable that uses the user's profile to find their division and the currency used by the division. The session variable is then used as a default value in the presentation variable.

Use in a runtime security filter

A runtime security filter enforces data security at the row level. By using a session variable that is initialized based on the $user system variable, we can limit data access by users.

Use in a dashboard applied filter or insight filter

Dashboard applied filters and insight filters are filters that are defined to limit the data available in a dashboard or in a specific insight. By using user specific session variables, you provide a personalized view of the data and introduce data security at the dashboard level. Please note that the difference between implementing data security using a runtime security filter and implementing it using an applied filter or an insight filter is that runtime security enforces the secured data access to an Analyzer as well. If a user has an Analyzer role and can make a copy of a dashboard and customize the dashboard, the dashboard filter or the insight filter can be easily removed and thus cannot be used to provide secure data access.

Session Variables Best Practices

Best Practice 1

Using session variables to provide a personalized view or secured data view is recommended compared with creating separate content for different users.

Incorta supports external variables and internal variables.

Initializing an internal variable requires the data to be loaded to Incorta first and it means that a schema table has to be defined. It also means that the data may be delayed. However, it is more reliable and is not affected by network and external system availability.

Best Practice 2

If there is no real-time refresh requirement, Incorta recommends using internal variable. If the external system is not available, the data loaded previously will be used.

Initializing an external variable is supported against a database source such as Oracle or MySQL database. We write a SQL statement that will fetch the data from an external environment when the variable is being accessed via an Incorta dashboard during a user session. Once it is fetched from the source, it remains throughout the user session. If the external system is not available, and the session variable is used as a data security filter, the user will not be able to access the secured data.

Best Practice 3

If data security is a strong concern, we can use external variables since it is initialized with the real time data. However, please ensure that you have a sufficient connection pool available and a reliable network and data source exists.

Best Practice 4

A user-defined session variable returns the value as the data type of String. When the session variable is being used in a formula, we need to explicitly convert the data type.

Best Practice 5

Although a user-defined session variable is available as a string, it can be used as a default to a presentation variable with the data type of Date. We need to convert the string with the format as yyyy-MM-dd.

System Variables

With the exception of the user system variable, all exposed System Variables are of the type Date. As System Variables, the system date variables have the widest scope of all three types of variables. The scope of system variables is for all users and all services.

When the Incorta Service starts, the services initialize all system date variables. System Variables are immutable in the sense that only the Incorta services can assign a value to a System Variable.

Global Variables

Unlike other objects in Incorta, a global variable is available to all tenant users. A global variable has a name, description, type, and value. A global variable is a static variable.

It is not possible for the value of a global variable to reference another variable, such as an external session variable, internal session variable, or another global variable for dynamic evaluation. It is possible, however, to edit the value of an existing global variable.

Only the tenant super user, users that belong to a group assigned the SuperRole role, or users that belong to a group assigned the Schema Manager role, can create and manage global variables for a given tenant.

The Name of a global variable must:

begin with an alpha character
besides the first character, can contain alphanumeric characters and underscores
be between 1 and 250 characters
be unique

The Type of a global variable is an Incorta data type:

date
double
integer
long
string
timestamp

The Value of a global variable must match the Type.

Global Variables Usage

The supported usage of a global variable is in the predicate, the WHERE clause of a SQL query, for a 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 example below, the $$gvar_TenantName has a string value of demo:

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