OBIEE – Variables

In OBIEE, there are two types of variables that are commonly used −

• Repository variables
• Session variables

Apart from this you can also define Presentation and Request variables.

Repository Variables

A Repository variable has a single value at any point of time. Repository variables are defined using Oracle BI Administration tool. Repository variables can be used in place of constants in Expression Builder Wizard.

There are two types of Repository variables −

• Static repository variables
• Dynamic repository variables

Static repository variables are defined in variable dialog box and their value exists until they are changed by the administrator.

Static repository variables contain default initializers that are numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initializer, such as date, time, etc. You cannot use any other value or expression as the default initializer for a static repository variable.

In older BI versions, the Administrator tool did not limit value of static repository variables. You may get warning in consistency check if your repository has been upgraded from older versions. In such case, update the static repository variables so that default initializers have a constant value.

Dynamic repository variables are same as static variables but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one that contains a SQL query. You can also set up a schedule that the Oracle BI Server will follow to execute the query and refresh the value of the variable periodically.

When the value of a dynamic repository variable changes, all cache entries associated with a business model are deleted automatically.

Each query can refresh several variables: one variable for each column in the query. You schedule these queries to be executed by the Oracle BI server.

Dynamic repository variables are useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains current orders and the other contains historical data.

Create Repository Variables

In the Administration Tool → Go to Manage → Select Variables → Variable Manager → Go to Action → New → Repository > Variable.

In the Variable dialog, type a name for the variable (Names for all variables should be unique) → Select the type of variable - Static or Dynamic.

If you select dynamic variable, use the initialization block list to select an existing initialization block that will be used to refresh the value on a continuing basis.

To create a new initialization block → Click New. To add a default initializer value, type the value in the default initializer box, or click the Expression Builder button to use Expression Builder.

For static repository variables, the value you specify in the default initializer window persists. It will not change unless you change it. If you initialize a variable using a character string, enclose the string in single quotes. Static repository variables must have default initializers that are constant values → Click OK to close the dialog box.

Session Variables

Session variables are similar to dynamic repository variables and they obtain their values from initialization blocks. When a user begins a session, the Oracle BI server creates new instances of session variables and initializes them.

There are as many instances of a session variable as there are active sessions on the Oracle BI server. Each instance of a session variable could be initialized to a different value.

There are two types of Session variables −

• System session variables
• Non-system session variables

System session variables are used by Oracle BI and Presentation server for specific purposes. They have predefined reserved names which can’t be used by other variables.

Non-system session variables are used for setting the user filters. Example, you could define a non-system variable called Sale_Region that would be initialized to the name of the sale_region of the user.

Create Session Variables

In the Administration Tool → Go to Manage → Select Variables.

In the Variable Manager dialog, click Action → New → Session → Variable.

In the Session Variable dialog, enter variable name (Names for all variables should be unique and names of system session variables are reserved and cannot be used for other types of variables).

For session variables, you can select the following options −

• Enable any user to set the value − This option is used to set session variables after the initialization block has populated the value. Example - this option lets non-administrators set this variable for sampling.

• Security sensitive − This is used to identify the variable as sensitive to security when using a row-level database security strategy, such as a Virtual Private Database (VPD).

You can use the initialization block list option to choose an initialization block that will be used to refresh the value regularly. You can also create a new initialization block.

To add a default initializer value, enter the value in the default initializer box or click the Expression Builder button to use Expression Builder. Click OK to close the dialog box.

The administrator can create non-system session variables using Oracle BI Administration tool.

Presentation Variables

Presentation variables are created with creation of Dashboard prompts. There are two types of dashboard prompts that can be used −

Column Prompt

Presentation variable created with column prompt is associated with a column, and the values that it can take comes from the column values.

To create a presentation variable go to New Prompt dialog or Edit Prompt dialog → Select Presentation Variable in the Set of a variable field → Enter the name for the variable.

Variable Prompt

Presentation variable created as variable prompt is not associated with any column and you need to define its values.

To create a presentation variable as part of a variable prompt, in the New Prompt dialog or Edit Prompt dialog → Select Presentation Variable in the Prompt for field → Enter the name for the variable.

The value of a presentation variable is populated by the column or variable prompt with which it is created. Each time a user selects a value in the column or variable prompt, the value of the presentation variable is set to the value that the user selects.

Initialization Blocks

Initialization blocks are used to initialize OBIEE variables: Dynamic Repository variables, system session variables and non-system session variables.

It contains SQL statement that are executed to initialize or refresh the variables associated with that block. The SQL statement that are executed points to physical tables that can be accessed using the connection pool. Connection pool is defined in the initialization block dialog.

If you want the query for an initialization block to have database-specific SQL, you can select a database type for that query.

Initialize Dynamic Repository Variables using Initialization Block

Default initiation string field of initialization block is used to set value of dynamic repository variables. You also define a schedule which is followed by Oracle BI server to execute the query and refresh the value of variable. If you set the logging level to 2 or higher, log information for all SQL queries executed to retrieve the value of variable is saved in nqquery.log file.

Location of this file on BI Server −

ORACLE_INSTANCE\diagnostics\logs\OracleBIServerComponent\coreapplication_obisn

Initialize Session Variables using Initialization Block

Session variables also take their values from initialization block but their value never changes with time intervals. When a user begins a new session, Oracle BI server creates a new instance of session variables.

All SQL queries executed to retrieve session variable information by BI server if the logging level is set to 2 or higher in the Identity Manager User object or the LOGLEVEL system session variable is set to 2 or higher in the Variable Manager is saved in nqquery.log file.

Location of this file on BI Server −

ORACLE_INSTANCE\diagnostics\logs\OracleBIServerComponent\coreapplication_obisn

Create Initialization Blocks in Administrator Tool

Go to Manager → Variables → Variable Manager Dialog box appears. Go to Action menu → Click New → Repository → Initialization Block → Enter the name of initialization block.

Go to Schedule tab → Select start date and time and refresh interval.

You can choose the following options for Initialization Blocks −

• Disable − If you select this option, initialization block is disabled. To enable an initialization block, right-click an existing initialization block in the Variable Manager and choose Enable. This option enables you to change this property without opening the initialization block dialog.

• Allow deferred execution − This allows you to defer the execution of the initialization block until an associated session variable is accessed for the first time during the session.

• Required for authentication − If you select this, initialization block must execute for users to log in. Users are denied access to Oracle BI if the initialization block doesn’t execute.