1. Building Semantic Models in Oracle Analytics Cloud
  2. Create and Build Your Model
  3. Create and Use Variables in a Semantic Model
  4. Define Session Variables

Define Session Variables

This topic describes what you need to know to understand and define session variables for use in data filters and expressions.

About Session Variables

Use a session variable when you need a variable with a value specific to a user's session and is set when a user logs into Oracle Analytics. Use session variables to set filters and permissions for the session.

Session variables dynamically modify metadata content to adjust to a changing data environment. For example, suppose User1 belongs to Department1 and User2 belongs to Department2. These users must access only the data for their respective departments. In this case you can create and use the DEPARTMENT_NUMBER variable to store the appropriate values for User1 and User2. You can then use this variable to filter data by Department2 for User1 and Department2 for User2.

To define a session variable, you create or use an existing initialization block to contain one or more session variables. The initialization block contains a default initialization query and connection pool to access the data source and return results to populate the session variables that you define. The session variables are then available for you to add to the semantic model's data filters or expressions.

Unlike global variables, the initialization of session variables isn't scheduled. When a user begins a session, Oracle Analytics creates new instances of session variables and initializes them. Session variable values remain unchanged for the duration of the session.

There are as many instances of a session variable as there are active sessions on Oracle Analytics. You can initialize each instance of a session variable to a different value.

Initialization blocks that contain many session variables can slow performance. You can defer the processing of session variable initialization blocks during session logon until their associated session variables are actually accessed within the session. See Defer Session Variable Processing.

About Multi-Source Session Variables

Create and use multi-source session variables when you need a variable to provide values from more than one data source. You can use multi-source session variables in data filters and expressions.

There is no restriction to the number of values that the multi-source session variable can hold.

In a session initialization block, you use the following format to create a session variables for each source. This format contains four underscore characters as the separator between the variable name and the source.

<ms_variable_name>____<source>

The multi-source system variable definitions that you created are listed in the saved session initialization block's definition (for example, MVCOUNTRY____ORCL and MVCOUNTRY____SNFL). But when you create expressions that include the multi-source session variable name, the Expression Editor's Variables tab displays the variable name (for example, MVCOUNTRY).

For an example of how to create multi-source session variables, see Create a Multi-Source Session Variable.

You can add values to the multi-source session variable from other component initialization blocks that return values. The multi-source session variable fails if all of the component initialization blocks return null values.

You can set processing dependencies and deferred processing for multi-source session variables, similar to regular session variables.

Create a Session Variable

Create a session variable when you need the variable's value to be specific to a user's session and set when a user logs into Oracle Analytics. For example, user department or sales region.

After you define and save session variables, they're available for you to add to data filters and expressions.

In a semantic model, you create and define a session variable within an initialization block. You can't create and define a standalone session variable and then later associate it with an initialization block.

Consider the following information when creating a session variable:

  • If you add more than one variable to the initialization block, then the variables must match the column order in the initialization query. This is so each variable receives the proper value when the query is run.

  • The initialization query used to populate the variables must reference the physical tables needed for the variable values. You don't have to include the physical tables that supply the variable values in the semantic model's physical layer.

  • The Enable any user to set the value option allows any user to set the variable's value in an analyses or dashboard (for example, a What If analysis). The user-specified variable value is passed to the Oracle Analytics query engine and used in the underlying calculation.

  • The Security Sensitive option identifies the variable as sensitive to security when using a row-level database security strategy such as Virtual Private Database (VPD). When this option and the database's Virtual Private Database data source property are selected, then the Oracle Analytics query engine matches a list of security-sensitive variables to each prospective cache hit. Cache hits occur only on cache entries that include and match all security-sensitive variables.

  • If you're creating a variable to override selection steps in a hierarchy column, then use JSON syntax to write the initialization query. See Initialization Queries Used in Variables to Override Selection Steps.

  • Because object permissions don't apply to variables, the values in variables aren’t secure and anyone who knows or can guess the name of the variable can use it in an expression. Because of this, Oracle recommends that you don’t put sensitive data like passwords in variables.

Follow these steps to create a session variable:

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Variables.
  4. Click Create and then click Create Initialization Block.
  5. In Create Initialization Block, go to the Name field and enter an initialization block name.
  6. Go to the Type field and select Session. Click Add.
  7. Confirm that the Variables tab is displayed, and then go to the Select Statement: DEFAULT field and enter the initialization query.
  8. Go to Connection Pool and click Select to browse for and select a connection pool specifically for use in initialization blocks.
  9. Click Add Variable and enter a unique name.
  10. Use one of the following options to specify a default value.
    • Go to the Value field and enter a default value.
    • Leave the Value blank if you want null as the default value.
    • Click Detail View and click Open Expression Editor to create an expression that determines the default value.
  11. Optional: Select Enable any user to set the value to allow the user to set the variable's value in an analyses or dashboard (for example, a What If analysis).
  12. Optional: Select Security Sensitive to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD).
  13. Optional: Click Add Variable to add another variable, and use the Move Up and Move Down to position the variables in the correct order.
  14. Optional: Click Test Query to review the variable values returned by the initialization query and to confirm that the variables are receiving the correct values.
  15. Click Save.

Example - Create and Use a Multi-Source Session Variable

This topic provides an example of how to create and use the MVCOUNTRY multi-source session variable.

Topics:

Create a Multi-Source Session Variable

This topic explains how to create the MVCOUNTRY multi-source session variable. When you add the MVCOUNTRY variable to an expression or data filter, it returns data from the Oracle and Snowflake data sources.

After you create a multi-source session variable, the variables definitions are listed in the initialization block's definition (for example, MVCOUNTRY____ORCL and MVCOUNTRY____SNFL). But in the Expression Editor, the multi-source session variable name is displayed (for example, MVCOUNTRY).

See About Multi-Source Session Variables.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Create the first variable.
    1. Click Variables.
    2. Click Create and then click Create Initialization Block.
    3. In Create Initialization Block, go to the Name field and enter mvcountry_orcl_init.
    4. Go to the Type field and select Session. Click Add.
    5. Confirm that the Variables tab is displayed, and then go to the Query Returns field and select Variable names and values.
    6. Go to the Select Statement: DEFAULT field and enter the following initialization query, using four underscores between the variable name (MVCOUNTRY) and the source (ORCL):
      select distinct 'MVCOUNTRY____ORCL', country from oracle_table
    7. Go to Connection Pool and click Select to browse for and select a connection pool.
    8. Click Add Variable and enter the name MVCOUNTRY____ORCL. You need to add four underscores between the variable name and the source name.
    9. Click Save.
  4. Create the second variable.
    1. Click Variables.
    2. Click Create and then click Create Initialization Block.
    3. In Create Initialization Block, go to the Name field and enter mvcountry_snfl_init.
    4. Go to the Type field and select Session. Click Add.
    5. Confirm that the Variables tab is displayed, and then go to the Query Returns field and select Variable names and values.
    6. Go to the Select Statement: DEFAULT field and enter the following initialization query, using four underscores between the variable name (MVCOUNTRY) and the source (SNFL):
      select distinct 'MVCOUNTRY____SNFL', country from snowflake_table
    7. Go to Connection Pool and click Select to browse for and select a connection pool.
    8. Click Add Variable and enter the name MVCOUNTRY____SNFL. You need to add four underscores between the variable name and the source name.
    9. Click Save.

Use a Multi-Source Session Variable in an Expression

After you create the MVCOUNTRY multi-source session variable, you can use it in an expression.

For information about the MVCOUNTRY session variable used in this example, see Create a Multi-Source Session Variable.

This is an example of how to use the multi-source session variable in an expression: 

select lastName, firstName, country from employee 
where country=VALUEOF(NQ_SESSION.MVCOUNTRY)

Use a Multi-Source Session Variable in a Data Filter

After you create the MVCOUNTRY multi-source session variable, you can use it in a data filter.

The MVCOUNTRY multi-source session variable is displayed in the Expression Editor's Variables tab. For information about the MVCOUNTRY session variable used in this example, see Create a Multi-Source Session Variable.

  1. On the Home page, click Navigator and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Browse for and open a logical or presentation table.
  4. Click the Data Filters tab.
  5. Go to the Add field, enter the application role that you want to set the data filter for, click Search by Role Name, and from the list select the application role.
  6. In the Role Name list, click the role you added to highlight it, and then click Open Expression Editor.
  7. Enter this expression:
    Country=VALUEOF(NQ_SESSION.MVCOUNTRY)
  8. Optional: Click Validate.
  9. Click Save to save the expression.
  10. Click Save to save the semantic model.