---

In OBIEE, there are  styles of variables which are normally used −

• Repository variables
• Session variables

Apart from this you could additionally define Presentation and Request variables.

Repository Variables

A Repository variable has a single fee at any point of time. Repository variables are defined the usage of Oracle BI Administration tool. Repository variables may be utilized in area of constants in Expression Builder Wizard.

There are  kinds of Repository variables −

• Static repository variables
• Dynamic repository variables

Static repository variables are described in variable conversation box and their price exists till they may be changed via the administrator.

Static repository variables contain default initializers which can be numeric or person values. In addition, you could use Expression Builder to insert a constant as the default initializer, such as date, time, and so on. You cannot use every other price or expression because the default initializer for a static repository variable.

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

Dynamic repository variables are same as static variables however the values are refreshed by means of data lower back from queries. When defining a dynamic repository variable, you create an initialization block or use a preexisting one which contains a SQL query. You can also installation a time table that the Oracle BI Server will follow to execute the question and refresh the price of the variable periodically.

When the cost of a dynamic repository variable adjustments, all cache entries associated with a commercial enterprise version are deleted routinely.

Each query can refresh numerous variables: one variable for each column inside the question. You schedule these queries to be done through the Oracle BI server.

Dynamic repository variables are beneficial for outlining the content material of logical table sources. For example, think you have got  resources for statistics about orders. One source consists of modern-day orders and the other carries ancient statistics.

Create Repository Variables

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

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

If you pick out dynamic variable, use the initialization block listing to choose an current initialization block with a purpose to be used to refresh the cost on a persevering with foundation.

To create a brand new initialization block → Click New. To upload a default initializer value, kind the fee in the default initializer box, or click the Expression Builder button to apply Expression Builder.

For static repository variables, the price you specify within the default initializer window persists. It will no longer trade unless you change it. If you initialize a variable using a man or woman string, enclose the string in unmarried charges. Static repository variables must have default initializers which can be constant values → Click OK to shut the dialog field.

Session Variables

Session variables are similar to dynamic repository variables and that they gain their values from initialization blocks. When a consumer 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 classes at the Oracle BI server. Each example of a session variable can be initialized to a distinct price.

There are two forms of Session variables −

• System consultation variables
• Non-system consultation variables

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

USER	This variable holds the value the user enters with login name. This variable is typically populated from the LDAP profile of the user.
USERGUID	This variable contains the Global Unique Identifier (GUID) of the user and it is populated from the LDAP profile of the user.
GROUP	It contains the groups to which the user belongs. When a user belongs to multiple groups, include the group names in the same column, separated by semicolons (Example - GroupA;GroupB;GroupC). If a semicolon must be included as part of a group name, precede the semicolon with a backslash character (\).
ROLES	This variable contains the application roles to which the user belongs. When a user belongs to multiple roles, include the role names in the same column, separated by semicolons (Example - RoleA;RoleB;RoleC). If a semicolon must be included as part of a role name, precede the semicolon with a backslash character (\).
ROLEGUIDS	It contains the GUIDs for the application roles to which the user belongs. GUIDs for application roles are the same as the application role names.
PERMISSIONS	It contains the permissions held by the user. Example - oracle.bi.server.manageRepositories.

Non-gadget session variables are used for setting the user filters. Example, you could outline a non-system variable referred to as Sale_Region that might be initialized to the name of the sale_region of the consumer.

Create Session Variables

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

In the Variable Manager conversation, click on Action → New → Session → Variable.

In the Session Variable conversation, enter variable name (Names for all variables need to be precise and names of system consultation variables are reserved and can not be used for other styles of variables).

For consultation variables, you can choose the following options −

• Enable any consumer to set the price − This choice is used to set session variables after the initialization block has populated the cost. Example - this option we could non-administrators set this variable for sampling.
• Security sensitive − This is used to identify the variable as touchy to protection while using a row-level database safety method, which includes a Virtual Private Database (VPD).

You can use the initialization block list choice to choose an initialization block a good way to be used to refresh the value frequently. You can also create a new initialization block.

To upload a default initializer cost, input the value in the default initializer field or click on the Expression Builder button to apply Expression Builder. Click OK to close the dialog field.

The administrator can create non-gadget consultation variables the usage of Oracle BI Administration device.

Presentation Variables

Presentation variables are created with introduction of Dashboard activates. There are  types of dashboard activates that may be used −

Column Prompt

Presentation variable created with column spark off is related to a column, and the values that it is able to take comes from the column values.

To create a presentation variable visit New Prompt dialog or Edit Prompt dialog → Select Presentation Variable within the Set of a variable field → Enter the call for the variable.

Variable Prompt

Presentation variable created as variable spark off isn't always related to any column and also you need to outline its values.

To create a presentation variable as a part of a variable spark off, in the New Prompt conversation or Edit Prompt dialog → Select Presentation Variable within the Prompt for subject → Enter the name for the variable.

The fee of a presentation variable is populated through the column or variable set off with which it's miles created. Each time a user selects a cost inside the column or variable spark off, the value of the presentation variable is ready to the price that the user selects.

Initialization Blocks

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

It incorporates SQL assertion which might be finished to initialize or refresh the variables related to that block. The SQL declaration which are executed points to physical tables that may be accessed the usage of the relationship 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 pick a database kind for that question.

Initialize Dynamic Repository Variables the usage of Initialization Block

Default initiation string area of initialization block is used to set fee of dynamic repository variables. You additionally define a time table that's followed by way of Oracle BI server to execute the question and refresh the cost of variable. If you place the logging stage to two or higher, log data for all SQL queries performed to retrieve the fee of variable is saved in nqquery.Log report.

Location of this file on BI Server −

ORACLE_INSTANCEdiagnosticslogsOracleBIServerComponentcoreapplication_obisn

Initialize Session Variables the use of Initialization Block

Session variables also take their values from initialization block but their cost never changes with time periods. When a person begins a brand new consultation, Oracle BI server creates a new instance of consultation variables.

All SQL queries completed to retrieve consultation variable statistics via BI server if the logging stage is about to 2 or higher in the Identity Manager User object or the LOGLEVEL machine session variable is ready to 2 or better in the Variable Manager is saved in nqquery.Log document.

Location of this document on BI Server −

ORACLE_INSTANCEdiagnosticslogsOracleBIServerComponentcoreapplication_obisn

Create Initialization Blocks in Administrator Tool

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

Go to Schedule tab → Select begin date and time and refresh c language.

You can pick out the following options for Initialization Blocks −

• Disable − If you select this option, initialization block is disabled. To allow an initialization block, right-click on an existing initialization block inside the Variable Manager and pick out Enable. This option allows you to change this belongings without opening the initialization block conversation.
• Allow deferred execution − This lets in you to defer the execution of the initialization block till an associated consultation variable is accessed for the first time throughout the consultation.
• Required for authentication − If you select this, initialization block have to execute for customers to log in. Users are denied access to Oracle BI if the initialization block doesn’t execute.