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 your 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 OK.
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.