Oracle Business Intelligence Cloud Services – Variables
Sunil Guddanti

Variables are useful to handle dynamic scenarios. We have five types of Variables.

(a) Session variables:

1. Session variable will populate separately for each & every session or user.

2. It persist only while a user’s session is active.

3. It receive values when users establish their sessions.

4. Session variables exist for each user for the duration of their browsing session and expire when the user closes the browser or signs out.

Syntax:VALUEOF (NQ_SESSION.Variablename)

System session variables:

1. These are pre-defined session variables and used by oracle BI server for specific purpose such as authenticating users.

2. We have below system variables. [Case-sensitive must be in CAPITAL]

• USER.
• PASSWORD
• DISPLAYNAME.
• GROUP.
• WEBGROUP.
• LOGLEVEL.
• ROLES.
• PERMISSIONS.
• USERLOCALE.
• TIMEZONE.
• PORTALPATH.

3. These variables are useful in special cases such as authenticating user. These variables should not use for any other purpose.

Non-system session variables:

1. Non-system session variables are application specific customized variables.

2. These variables required Session initialization blocks.

3. Session initialization blocks will executed whenever user login into analytics application.

Ex: Data Security or row level security.

(b) Repository variables:

1. Repository variables also called as Oracle BI Server variables.

2. The variable values will updated whenever Oracle BI Server started or restarted based on schedule.

Syntax: VALUEOF (Variable name)

Dynamic:

• Dynamic variables are associated with initialization block.
• A dynamic repository variable has a value that it should refreshed by data returned from queries.
• An SQL query is called as initialization block.
• The initialization blocks will executed with oracle BI server refresh and based on the schedule of initialization block.
• The Schedule option is available only for dynamic repository variable initialization blocks.

Static:

• If we want to use a constant value at ‘n’ no. of places then we will go for static variables.
• It has a value that persists and does not change until the administrator changes it.
• Ex: Database name, username of connection pools
• Static variable values are initialized in the Static Repository Variable dialog box
• We have three static predefined syntax variables for date: Date, Time and Timestamp.

Ex: To Capture Max Year and Max Month from Database.

(C) Presentation variables:

• Presentation Variables are useful to capture “User Response”
• Presentation variable is a variable that can be created either column prompt or a variable prompt.

Syntax: @ {<variable name>} {<value>} [<format>]

Here <value< and <format< are optional.

• Variable Name is the name of the Presentation Variable.
• Value is by default value
• Format is to convert one format to other format.

(d) Request variables:

Request variable enables you to override the value of a session variable and it happens only during a database request initiated from a column prompt. It is useful to override session variable values

• The name of request variable should be same as session variable
• Request variable are defined at Dashboard Prompts
• These variables are useful to send values from report to RPD.

(e) Global variables:

• It is a column created by combining a specific data type with a value. The value can be a Date, Date and Time, Number, Text, and Time.
• It will evaluate the time the analysis is exececution, and the value of the global variable is substituted appropriately.
• Users with the BI Advanced Content Author role can manage (add, edit, and delete) global variables.
• Global variable can saved in CatLog and made available to other analysis.

Creating Global Variables:

By creating global variables, we can reuse it in different analyses. Simply creating a global variable, there is no need to create separate column in Data model.

1. Open the analysis for editing column formula.
2. In the below Selected Columns pane, click Gear Option beside the column name.



3. Click on Edit Formula to display the Column Formula tab. You can create a custom header for the global variable by using the tab.
4. Click Variables and select Global to display the Insert Global Variable dialog box.
5. Click on Add New Global Variable to display the New Global Variable dialog box.



6. Enter the value for the Name.
   Ex: gv_region, time_and_date_global_variable.
   Note:
   The name of global variable should be fully qualified while referencing the global variable, and it prefixed by text “global.variables".
   for example, a global variable set to calculate revenue is displayed in the Column Formula dialog as follows:
   "Base Facts"."1- Revenue"*@{global.variables.gv_qualified}
   
7. Enter values for the Type and Value.
   Note:
   If we are selecting "Date and Time" as data type, then enter the value as in the following example: 03/25/2004 12:00:00 AM
   if you are entering an expression or a calculation as a value, then you must use the Text data type, as in the following example: "Base Facts"."1- Revenue"*3.1415
   
8. Click OK. Then the new global variable is added to the Insert Global Variable dialog.
9. Add the new global variable just created and click OK. The Column Edit Formula dialog is displayed with global variable inserted in column formula pane. Then Custom Headings check box is automatically selected.
10. Enter the new name for the column to which you have assigned a global variable to more accurately reflect the variable.
11. Click OK.

Syntax for Referencing Variables:

Refer variables in analyses and dashboards.

However, referencing a variable depends on the task that you are performing. For tasks while defining in the fields in dialog you must specify only the type and name of the variable (not the full syntax), for example, referencing a variable in a filter definition.

Referencing Variables in Expressions: