Where can we use repository variables in obiee

We can use repository variables in three places :

• in BI Server
• in BI Presentation Service
• in Delivers

Syntax

VALUEOF("NQ_SESSION.Variable Name")

1)In BI Server:

Connection Pool

The picture below show a repository variable call but you can use in the same way the session variable syntax.

Opaque View / Select Statement

In the bookshel Version 10.1.3.2, it's written that Only repository variables can be used in the definition. An error will generate if a session variable is used in the view definition.

  

           With the version 10.1.3.4, a warning is generated:

WARNINGS: GLOBAL: The repository variable, 
NQ_SESSION.MYYEAR, has no value definition.

But BI Server process it without problem.

-------------------- SQL Request:
SET VARIABLE QUERY_SRC_CD='Report',SAW_DASHBOARD='/users/administrator/_portal',SAW_DASHBOARD_PG='MyYear',
SAW_SRC_PATH='/users/administrator/MyYear/Answers',MYYEAR='2001';SELECT TIMES_VIEW.calendar_year saw_0 FROM Test 
ORDER BY saw_0


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- General Query Info:
Repository: Star, Subject Area: Test, Presentation: Test


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Logical Request (before navigation):

RqList  distinct 
    TIMES_VIEW.calendar_year as c1 GB
OrderBy: c1 asc


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Execution plan:

RqList <<150282>> [for database 3023:156:orcl SH,31] distinct 
    TIMES_VIEW.calendar_year as c1 GB [for database 3023:156,31]
Child Nodes (RqJoinSpec): <<150293>> [for database 3023:156:orcl SH,31]
    (select time_id, calendar_year 
from times
where
calendar_year > VALUEOF(NQ_SESSION.MYYEAR)) as T6563
OrderBy: c1 asc [for database 3023:156,31]


+++Administrator:2e0000:2e0008:----17/12/2009 06:19:16

-------------------- Sending query to database named orcl SH (id: <<150282>>):

select distinct T6563.calendar_year as c1
from 
     (select time_id, calendar_year 
from times
where
calendar_year > 2001) T6563
order by c1

2) Presentation Services

Logical SQL in Answers

OBIEE - Logical Sql:

VALUEOF(NQ_SESSION."Variable Name")

Other places

You can call a server variable by using this syntax :

biServer.variables['VariableName']

Example in a dashboard text object:

3)In Delivers

@{NQ_SESSION.Variable Name}

Presentation Variables in OBIEE and where can we use them

The Presentation Variable is the only variable offered by the Presentation service. It will take the datatype of the presentation column on which the prompt is created.

Syntax: @{variables.<variableName>}{<default>}[format]

variables: Optional

variableName: It refers to the variable which should be different from the existed variable names.

Default: The default value of the variable.

Format: It represents the format in which the variable values need to be displayed.

You can reference presentation variables in the following areas 

• Answers :
  • Title Views
  • Narrative Views
  • Column Filters
  • Column Formulas
  • Conditional Formatting conditions
  • Chart scale markers.
  • Gauge range settings.
  • Static text.
• Direct Database Requests
• Dashboard prompts
• iBot Headlines and text

Some examples are given below with a presentation variable “Year” and as default value the max of the year :

@{Year}{max(Calendar."Calendar Year" by) }

Column Formula:

You can also set it in a formula. Example :

CASE 
   WHEN @{Year}{max(Calendar."Calendar Year" BY) } = '2001' THEN 'It''s the year 2001' 
   ELSE 'You choose an other year' end

or

CASE  
WHEN  @{Year}{max(Calendar."Calendar Year" BY) } <  Calendar."Calendar Year" 
   THEN 'The Year is greater than the presentation variable ' || 
       cast(@{Year}{max(Calendar."Calendar Year" BY) }  AS char)   
WHEN @{Year}{max(Calendar."Calendar Year" BY) } =  Calendar."Calendar Year" 
   THEN 'The Year is equal to the presentation variable ' || 
       cast(@{Year}{max(Calendar."Calendar Year" BY) }  AS char)  
ELSE 'The Year is smaller than the presentation variable ' || 
      cast(@{Year}{max(Calendar."Calendar Year" BY) }  AS char) end

Filter:

Title View:

Session Variables in OBIEE

Session variables are similar to dynamic repository variables in that they obtain their values from initialization blocks. Unlike dynamic repository variables, however, the initialization of session variables is not scheduled. When a user begins a session, the Oracle BI Server creates new instances of session variables and initializes them.

Session variables are primarily used when authenticating users against external sources such as database tables or LDAP servers. If a user is authenticated successfully, session variables can be used to set filters and permissions for that session.

System Session Variables

System session variables are session variables that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names, that cannot be used for other kinds of variables.

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable LOGLEVEL set the filter to the Variable NQ_SESSION.LOGLEVEL.

The available system session variables are.

DISPLAYNAME:

Used for Oracle BI Presentation Services. It contains the name that will be displayed to the user in the greeting in the Oracle BI Presentation Services user interface. It is also saved as the author field for catalog objects. For internal Oracle BI repository users (nondatabase users), this variable is populated with the user's full name.

EMAIL:

Contains the user's default email address for use with Answers. If the delivery option of Answers is enabled, an email device using this address will be created for the user upon first log in. Users can override this address by changing their account settings in Oracle BI Presentation Services.

GROUP:

Contains the groups to which the user belongs. These are used by both the Oracle BI Server and Oracle BI Presentation Services.When a user belongs to multiple groups, separate the group names with semicolons. Do not delimit text (for example, do not surround the text with single or double quotes). Use a Varchar column in a database table to contain the group memberships.

For example, if a user belonged to groups called Sales US, Sales UK, QA and Dev, and Doc, the text entered into a Varchar data type column in a database table would be:

Sales US;Sales UK;QA and Dev;Doc

Note: The Oracle BI Presentation Services Administrator needs to make sure that the names of Presentation Services groups are different from any user IDs that will be used to log on to Oracle BI Presentation Services. If a user and a Presentation Services group share the same name, the user will receive an Invalid Account message when attempting to log on to Oracle BI Presentation Services.

LAST_SYNCH_TIME and THIS_SYNCH_TIME:

These two variables are set and tracked by Oracle BI Presentation Services to manage the synchronization of Oracle BI Disconnected Analytics. For more information, refer to Oracle Business Intelligence Disconnected Analytics Administration and Configuration Guide.

LOGLEVEL:

The value of LOGLEVEL (a number between 0 and 5) determines the Logging level that the Oracle BI Server will use for user queries.

This system session variable overrides a variable defined in the Users object. If the Administrators Users object has a Logging level defined as 4 and the session variable LOGLEVEL defined in the repository has a value of 0 (zero), the value of 0 applies.

PORTALPATH:

Used for Oracle BI Presentation Services. It identifies the default dashboard the user sees when logging in (the user can override this preference after logged on).

REQUESTKEY:

Used for Oracle BI Presentation Services. Any users with the same nonblank request key will share the same Presentation Server cache entries. This tells Oracle BI Presentation Services that these users have identical content filters and security in the Oracle BI Server. Sharing Presentation Server cache entries is a way to minimize unnecessary communication with the Oracle BI Server.

SKIN:

Determines certain elements of the look and feel of the Oracle BI Presentation Services user interface. The user can alter some elements of the user interface by picking a style when logged on to Oracle BI Presentation Services. The SKIN variable points to an Oracle BI Presentation Services folder that contains the nonalterable elements (for example, graphics such as GIF files). Such directories begin with sk_. For example, if a folder were called sk_companyx, the SKIN variable would be set to companyx.

USER:

Holds the value the user enters as his or her logon name.

WEBGROUPS:

Specifies additional groups specific to Oracle BI Presentation Services, if any. The use of Presentation Services groups provides a mechanism for more granular content control.

Non-system Session Variables

The procedure for defining nonsystem session variables is the same as for system session variables.

A common use for nonsystem session variables is setting user filters. For example, you could define a nonsystem variable called SalesRegion that would be initialized to the name of the user's sales region.

You could then set a security filter for all members of a group that would allow them to view only data pertinent to their region.

When you use these variables for Oracle BI Presentation Services, preface their names with NQ_SESSION. For example, to filter a column on the value of the variable SalesRegion set the filter to the Variable NQ_SESSION.SalesRegion.

Step by step process of creating session variables..

     1.  From the Administration Tool menu bar, choose Manage > Variables.

    2.  In the Variable Manager dialog box, from the menu bar, choose Action>New>Session>Variable.

3. In the Session Variable dialog box, type a variable name.
   Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables.

     For session variables, you can select the following check boxes:

4. --Enable any user to set the value:
   Check box that allows you to set the session variables after the initialization block has populated the value (at user login) by calling the ODBC store procedure NQSSetSessionValue(). For example, this allows non-Oracle BI Administrators to set this variable for sampling.
   --Security Sensitive:
   Check box that identifies the variable as sensitive to security for virtual physical databases (VPD). When filtering cache table matches, the Oracle BI Server looks at the parent database of each column or table that is referenced in the logical request projection list. If the physical database source is a VPD, the Oracle BI Server matches a list of security-sensitive variables to each prospective cache hit. Cache hits would only occur on cache entries that included and matched all security-sensitive variables.
5. Use the Initialization Block drop-down list to select an initialization block that will be used to refresh the value on a continuing basis.
   
   
   
   To create a new initialization block, click New.
6. To add a Default initializer value, perform one of the following steps:
   • To use the Expression Builder, click the ellipsis button to the right of the Default initializer work space.
   • Type the value into the Default initializer text box.
7. Click OK.   

Repository Variables in OBIEE

A repository Variable has a single value at any point of time. There are two types of Repository variables. Static and Dynamic.

          Repository variables can be used instead of literals or constants in expression builders in the Administration Tool. The Oracle BI Server will substitute the value of the repository variable for the variable itself in the metadata.

Static Repository Variables:

The value of a static repository value is initialized in the Variable dialog box. This value persists, and does not change until an Oracle BI Administrator decides to change it. Example Suppose you want to create an expression to group times of day into different day segments. If Prime Time were one of those segments and corresponded to the hours between 5:00 PM and 10:00 PM, you could create a CASE statement like the following: CASE WHEN "Hour" >= 17 AND "Hour" < 23 THEN 'Prime Time' WHEN... ELSE...END where Hour is a logical column, perhaps mapped to a timestamp physical column using the date-and-time Hour(<<timeExpr>>) function. Rather than entering the numbers 17 and 23 into this expression as constants, you could use the Variable tab of the Variable dialog box to set up a static repository variable named prime_begin and initialize it to a value of 17, and create another variable named prime_end and initialize it to a value of 23. After created, variables are available for use in expression builders. In an expression builder, click on the Repository Variables folder in the left pane to display all repository variables (both static and dynamic) in the middle pane by name. Variables should be used as arguments of the function VALUEOF( ). For example, the following CASE statement is identical to the one explained in the preceding example except that variables have been substituted for the constants. CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < ALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END. Dynamic Repository Variables You initialize dynamic repository variables in the same way as static variables, but the values are refreshed by data returned from queries. When defining a dynamic repository variable, you will create an initialization block or use a pre-existing one that contains a SQL query. You will also set up a schedule that the Oracle BI Server will follow to execute the query and periodically refresh the value of the variable. Example Dynamic repository variables are very useful for defining the content of logical table sources. For example, suppose you have two sources for information about orders. One source contains recent orders and the other source contains historical data. You need to describe the content of these sources on the Content tab of the Logical Table Source dialog box. Without using dynamic repository variables, you would describe the content of the source containing recent data with an expression such as: Orders.OrderDates."Order Date" >= TIMESTAMP '2001-06-02 00:00:00' This content statement will become invalid as new data is added to the recent source and older data is moved to the historical source. To accurately reflect the new content of the recent source, you would have to modify the fragmentation content description manually. Dynamic repository values can be set up to do it automatically. Another suggested use for dynamic repository values is in WHERE clause filters of logical table sources, that are defined on the Content tab of the Logical Table Source dialog box. The values of dynamic repository variables are set by queries defined in Variable Initialization blocks. A common use of these variables is to set filters for use in Oracle BI Presentation Services. For example, to filter a column on the value of the dynamic repository variable CurrentMonth, set the filter to the Variable CurrentMonth. Step by step process of creating repository variable    1.  From the Administration Tool menu bar, choose Manage > Variables. Now the variable manager dialogue box appers... In the Variable Manager dialog box, from the menu bar, choose Action>New>Repository>Variable. Repository variable dialogue box is as follows... In the Variable dialog box, type a Variable name. Names for all variables should be unique. The names of system session variables are reserved and cannot be used for other types of variables. In the Variables dialog box, select the type of variable: Static or Dynamic. The name of the dialog box changes to reflect the type of variable that you select. (Dynamic repository variables) Use the Initialization Block drop-down 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.     6.   (Dynamic or static variables) To add a Default initializer value, perform one of the following steps:  To use the Expression Builder, click the ellipsis button to the right of the Default initializer work space. Type the value into the Default initializer text box.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 ( ' ). Click OK.