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 initiapzers that are numeric or character values. In addition, you can use Expression Builder to insert a constant as the default initiapzer, such as date, time, etc. You cannot use any other value or expression as the default initiapzer for a static repository variable.

In older BI versions, the Administrator tool did not pmit 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 initiapzers 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 initiapzation 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 initiapzation block pst to select an existing initiapzation block that will be used to refresh the value on a continuing basis.

To create a new initiapzation block → Cpck New. To add a default initiapzer value, type the value in the default initiapzer box, or cpck the Expression Builder button to use Expression Builder.

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

Session Variables

Session variables are similar to dynamic repository variables and they obtain their values from initiapzation blocks. When a user begins a session, the Oracle BI server creates new instances of session variables and initiapzes 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 initiapzed 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 initiapzed 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, cpck 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 initiapzation block has populated the value. Example - this option lets non-administrators set this variable for samppng.

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 initiapzation block pst option to choose an initiapzation block that will be used to refresh the value regularly. You can also create a new initiapzation block.

To add a default initiapzer value, enter the value in the default initiapzer box or cpck the Expression Builder button to use Expression Builder. Cpck 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.

Initiapzation Blocks

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

It contains SQL statement that are executed to initiapze 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 initiapzation block dialog.

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

Initiapze Dynamic Repository Variables using Initiapzation Block

Default initiation string field of initiapzation 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_INSTANCEdiagnosticslogsOracleBIServerComponentcoreapppcation_obisn

Initiapze Session Variables using Initiapzation Block

Session variables also take their values from initiapzation 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_INSTANCEdiagnosticslogsOracleBIServerComponentcoreapppcation_obisn

Create Initiapzation Blocks in Administrator Tool

Go to Manager → Variables → Variable Manager Dialog box appears. Go to Action menu → Cpck New → Repository → Initiapzation Block → Enter the name of initiapzation block.

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

You can choose the following options for Initiapzation Blocks −

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

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

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