Category Archives: OBIEE

OBIEE – Variables

Oracle Business Intelligence Enterprise Edition (OBIEE) allows us to use several types of variables in the different places of components like analyses, dashboards, KPI’s, conditions… etc.

There are four main types of variables each having a different use area and syntax. These are:

  • Session Variables
  • Repository Variables
  • Presentation Variables
  • Request Variable

 

Session Variables

A session variable is a variable as its name implies, that is initialized at the time of user login for each user. When a new user logins to the BI Server, a new instance of session variables are created, initialized and assigned to that user.

There are two types of session variables:

  • System Session Variable: A session variable that the Oracle BI Server and Oracle BI Presentation Services use for specific purposes. System session variables have reserved names that CAN NOT be used for other kinds of variables.
  • Non-System Session Variables: A session variable that the administrator user creates and names in the repository us,ng the Administration tool.

Syntax:

@{biServer.variables[‘NQ_SESSION.variablename‘]}

Sample:

@{biServer.variables[‘NQ_SESSION.USER’]}

 

 

Repository Variables

Repository variables are created by the BI Administration Tool in the repository by the admin user. There are two types of repository variables:

  • Static Repository Variable: Static repository variables are constant and they do not change until the administrator user changes them in the repository.
  • Dynamic Repository Variable: Dynamic repository variables values are refreshed by data returned from queries.

Syntax:

@{biServer.variables.variablename} or @{biServer.variables[‘variablename‘]}

Sample:

@{biServer.variables.country} or @{biServer.variables[‘country’]}

 

 

Presentation Variables

A presentation variable is a variable that users in the BI Answer can create as creating dashboard prompts as:

  • Column prompt: A presentation variable created as part of a column prompt is associated with a column, and the values that it can take come from the column values. To create a presentation variable as part of a column prompt, in the “New Prompt dialog” (or Edit Prompt dialog), you have to select Presentation Variable in the Set a variable field and then enter a name for the variable in the Variable Name field.
  • Variable prompt: A presentation variable created as part of a variable prompt is not associated with any column, and you define the values that it can take. To create a presentation variable as part of a variable prompt, in the “New Prompt dialog” (or Edit Prompt dialog), you have to select Presentation Variable in the Prompt for field and then enter a name for the variable in the Variable Name field.

The value of a presentation variable is populated by the column or variable prompt with which it was created. That is, 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.

Syntax:

@{[variables.]variablename}[format]{defaultvalue} or @{scope.variables[‘variablename‘]}

Sample:

@{[variables.]Country}{COUNTRY NOT SELECTED} or @{dashboard.variables[‘Country’]}

  • variablename is the name of the presentation or request variable
  • (optional) format is any valid format mask dependent on the data type of the variable (#,##0, MM/DD/YY hh:mm:ss.) Note that the format is not applied to the default value
  • (optional) defaultvalue is a constant or variable reference indicating a value to be used if the variable referenced by variablename is not set
  • scope identifies the qualifiers for the variable. You must specify the scope when a variable is used at multiple levels (analyses, dashboard pages, and dashboards) and you want to access a specific value. (If you do not specify the scope, then the order of precedence is analyses, dashboard pages, and dashboards.)

 

Request Variables

A request variable lets you override the value of a session variable but only for the duration of a database request initiated from a column prompt. You can create a request variable as part of the process of creating a column prompt.
A request variable that is created as part of a column prompt is associated with a column, and the values that it can take come from the column values.

To create a request variable as part of a column prompt, in the “New Prompt dialog” (or Edit Prompt dialog), you have to select Request Variable in the Set a variable field and then enter the name of the session variable to override in the Variable Name field.

The value of a request variable is populated by the column prompt with which it was created. That is, each time a user selects a value in the column prompt, the value of the request variable is set to the value that the user selects. The value, however, is in effect only from the time the user presses the Go button for the prompt until the analysis results are returned to the dashboard.

Syntax:

@{[variables.]variablename}[format]{defaultvalue} or @{scope.variables[‘variablename‘]}

Sample:

@{[variables.]Country}{COUNTRY NOT SELECTED} or @{dashboard.variables[‘Country’]}

  • variablename is the name of the presentation or request variable
  • (optional) format is any valid format mask dependent on the data type of the variable (#,##0, MM/DD/YY hh:mm:ss.) Note that the format is not applied to the default value
  • (optional) defaultvalue is a constant or variable reference indicating a value to be used if the variable referenced by variablename is not set
  • scope identifies the qualifiers for the variable. You must specify the scope when a variable is used at multiple levels (analyses, dashboard pages, and dashboards) and you want to access a specific value. (If you do not specify the scope, then the order of precedence is analyses, dashboard pages, and dashboards.)

 

 

 

 

References:

http://obieetutorialguide.blogspot.com.tr/2012/02/using-variables-in-obiee.html

http://docs.oracle.com/cd/E12096_01/books/AnyUser/AnyUser_Format8.html

Advertisements

OBIEE – Direct Database Request: Using Presentation Variables

In the previous post, we have mentioned about direct database requests (DDR), and positive and negative properties of them. If you are interested in those topics please take some time and read corresponding post:

OBIEE – Direct Database Requests

In this article, we will try to explain how to use presentation variables in these requests.

 

OBIEE – Direct Database Request: User Permissions

In the previous post, we have mentioned about direct database requests (DDR), and positive and negative properties of them. If you are interested in those topics please take some time and read corresponding post:

OBIEE – Direct Database Requests

In this article, we will try to explain which permissions are required for DDR’s and how to assign then to a specific user.

First of all, we need to configure some settings in the repository part using Administration tool:

  • Open the Administration Tool and go to Manage -> Identity
  • Select BI Repository and on the right side, select Users or Application Roles tab
  • Under the selected tab, select the desired user or application role name and double click
  • Select the permissions button on the Application Role popup window
  • In the Query Limits tabs, change Execute Direct Database Requests from Ignore to Allow
  • Save and close windows.

Then we need to configure some settings in the BI Server:

  • Using your web browser, open BI Server (analytics) and login as an administrator (weblogic user)
  • Click on the Administration link placed at the right side of the top most part of the page
  • Click on the Manage Privileges link located under Security part
  • In the Answers section (list is not alphabetically sorted), for Edit Direct Database Analysis and Execute Direct Database Analysis sections click on the default value to include desired users and groups to allow these features.

 

OBIEE – Direct Database Request

The ability to execute SQL statements directly against the physical data source is called as direct database request (DDR). Direct database requests is available in Oracle Business Intelligence Enterprise Edition (OBIEE) but have some limitations, which are:

  • SQL statement is always executed on the physical data source.
  • SQL statement is not optimized by Oracle Business Intelligence (Oracle BI Server) Server engine.

Users with the appropriate permissions can create and issue a direct database request directly to a physical back end database. The results of the request can be displayed and manipulated within Oracle BI Answers, and subsequently incorporated into Oracle BI Interactive dashboard.

The Oracle BI Server sends original unprocessed, user-entered, physical SQL directly to an underlying database. The returned results set is then treated as an Oracle BI request.

Direct database requests completely bypass the Oracle BI Server mechanisms like security and logical business model. And one must BE CAREFUL, since in addition to SELECT queries, direct database requests also able to execute CREATE, or even DELETE and DROP statements on physical data store.

All above statements lists the the negative characteristics of direct database requests, but this feature has some benefits. These can be listed as:

  • DDR allows users to write and execute complex SQL queries
  • DDR allows to check physical connectivity to the database and can be used to determine performance issues.

How to Create a Direct Database Request:

In order to create a direct database request, open your web browser and navigate to the BI Answers (http://localhost:9704/analytics) to login with using an appropriate user (weblogic) having required permissions.

Select Analysis from the New menu at the the top-right corner of the page and Click Create Direct Database Request link at the bottom.

On the criteria tab, provide the Connection Pool name (the same as in the repository created by the Administration Tool) and SQL Statement (database specific SQL syntax is required) and then click Validate SQL and Retrieve Columns button.

If everything is OK, then you should have column names listed below the Result Columns part. Be sure to write connection pool name exactly the same as the name in the repository (with speces etc.) otherwise you may end up with Unresolved Connection Pool object:”..” error.

After successfully executed the SQL statement and get the result columns, click the Result tab and build your analysis as usual.

 

References:

http://gerardnico.com/wiki/dat/obiee/direct_database_request

https://santoshbidw.wordpress.com/category/obiee-10g/obiee-10g-answers/direct-database-request/