Tag Archives: presentation variables

Multi-Source Session Variables in OBIEE11g RPD – Get values from multiple sources

In addition to supporting regular session variables that are populated from one data source, In version, the Oracle Business Intelligence also supports session variables that can be populated from multiple data sources. These multi-source session variables can be used in logical queries or in repository data filters, and contain the union of values from the different data sources. There is no restriction on the number of values that the multi-source session variable can hold. To create a multi-source session variable, you first create row-wise initialization blocks for each source.

Then, you explicitly define session variables for each source. The format for the session variable names must be:

  • <ms_variable_name>____<source>

where the separator must be exactly four underscore characters.

This automatically creates a single multi-source session variable, named:

  • <ms_variable_name>

The component session variable names (<ms_variable_name>____<source>) appear separately in the Variable Manager in the Administration Tool, but the Expression Builder displays only the single multi-source session variable name (<ms_variable_name>).


While the main focus of this section is on the definition and usage of multi-source session variables, you may also select the VALUEOF the component session variables in logical queries and data filters.

If any of the row-wise initialization blocks returns null results, this is logged in the Oracle BI Server log, nqserver.log. Values can still be added to the multi-source session variable from other component initialization blocks that succeed in returning values. The multi-source session variable will fail only if all of the component initialization blocks return null values.

You can set execution precedence and deferred execution with multi-source session variables, similar to regular session variables.


Example to Illustrate the Creation and Usage of Multi-Source Session Variables

The following example illustrates how to create and use a multi-source session variable:

  1. In the Variable Manager in the Administration Tool, select Action > New > Session > Initialization Block.
  2. Create a row-wise initialization block called ‘Extra_products_orcl_init’ with the following SQL for Default initialization string:

               select distinct ‘MSVPRODUCTS____orcl’,PROD_NAME from extra_products

  1. Create a second row-wise initialization block called ‘SampleApp_Products_init’ with the following SQL for Default initialization string:

              select distinct ‘MSVPRODUCTS____OBI‘,”Sample App Lite Data”…”D10 Product”.”Prod_Dsc” AS “Prod_Dsc” from “Sample App Lite Data”…”D10 Product”

  1. Still in the Variable Manager, select Action > New > Session > Variable.
  2. Create a session variable called MSVPRODUCTS____orcl, making sure to include four underscores between the variable name and the source name. And select the initialization block called Extra_products_orcl_init.
  3. Create a second session variable called MSVPRODUCTS____OBI, making sure to include four underscores between the variable name and the source name. And select the initialization block called SampleApp_Products_init.

While the component session variables appear in the Variable Manager, the multi-source session variable that has been created, MSVPRODUCTS, will appear in Expression Builder.


Using the Multi-Source Session Variable in a Logical Query:

You can now use the multi-source session variable MSVPRODUCTS in a logical query.

For example:
Select “SampleApp Lite”.”D1 Products”.”Product Type”,”SampleApp Lite”.”D1 Products”.”Brand”,”SampleApp Lite”.”D1 Products”.”Product” from “SampleApp Lite”.”D1 Products” where “SampleApp Lite”.”D1 Products”.”Product”=VALUEOF(NQ_SESSION.MSVPRODUCTS)

Using the Multi-Source Session Variable in a Data Filter:

To use the multi-source session variable MSVPRODUCTS in a data filter, perform the following steps:

  1. In the Administration Tool, select Manage, then select Identity.
  2. In the Identity Manager dialog, in the tree pane, select BI Repository.
  3. In the right pane, select the Application Roles tab, then double-click the application role for which you want to set data filters.
  4. In the Application Role dialog, click Permissions.
  5. In the User/Application Role Permissions dialog, click the Data Filters tab.
  6. In the Data Filters tab, create the data filter expression:

             “SampleApp Lite”.”D1 Products”.”Product” = VALUEOF(NQ_SESSION.MSVPRODUCTS)


Note that the Expression Builder, as shown in the image that follows, displays only the multi-source session variable MSVPRODUCTS, and not the regular session variables that were used during the creation of the multi-source session variable.



Shiva Molabanti


Posted by on April 17, 2013 in BI Publisher, OBI EE, OBIEE 11g, Weblogic


Tags: , , , , , , , , , , , , , , , , ,

RCU Schema Configuration failed with the error “The specified prefix already exists”

We all know that we need to create RCU schemas before starting OBIEE 11G (In fact, for any Fusion Middleware application) Installation. Because it is the base store for FMW.

Recently I came across a scenario where my customer created RCU schemas with Oracle Repository Creation Utility (RCU) version and Installed OBIEE Now after new OBIEE release he wants to upgrade his application from But he is not interested at the In-place upgrade. So we uninstalled weblogic and OBIEE and started RCU to drop the _BIPLATFORM and _MDS schemas.

The RCU shown up with successful message and so we thought everything is cleaned up and it’s good to create schemas again. I started RCU and selected Oracle Business Intelligence component and given prefix as DEV. But am not able to assign this prefix and got a message like “The specified prefix already exists”.  Though I got successful dropped schemas message, just for cross check I looked at database for the DEV_BIPLATFORM and DEV_MDS schemas. There I am not seeing any of them.

Then I checked at dropped schemas log generated by RCU and realized that there is an entry in System.SCHEMA_VERSION_REGISTRY$ table which is stopping me to assign the same DEV prefix which we used for earlier version schemas.

Actually the RCU should drop schemas using script. This script has capability to do cascade delete and will delete entries in System.SCHEMA_VERSION_REGISTRY$ table.

To make sure this log into RCU DATABASE as sys and run the following SQL Query:

Select * from System.SCHEMA_VERSION_REGISTRY$;

If this query returns any record then it means the RCU didn’t drop the schemas properly.

Actually what happened in this case is the RCU schemas were not dropped using script, it’s just dropped with normal schema delete and the resulting repository record for the schema is not clean. Keep in mind, this is just in case if you want to use same prefix. Otherwise RCU will let you proceed to create new prefixed Schemas.

If the RCU schemas have been deleted incorrectly using the RCU drop user command, then We need to clean these entries before starting new repository schemas creation.

To do this, run the following SQL*Plus command as sys to complete the removal of the RCU schemas records in the repository database:

delete from System.SCHEMA_VERSION_REGISTRY$;

Once you confirmed there are no entries in System.SCHEMA_VERSION_REGISTRY$  table and start the RCU to create repository schemas with the prefix (DEV in my case). The RCU will go smooth and wont complain with any error.

Hope this helps you in your RCU issues troubleshooting.





Posted by on November 16, 2011 in BI Publisher, OBI EE, OBIEE 11g


Tags: , , , , , , , , , , , , ,

Predefined Presentation Variables in OBIEE 11G

The OBIEE has couple of predefined presentation variables which we can reference in analyses, dashboards, KPIs, and agents. we can take advantage of these varaibles and can be used in analysis for better presentation.

The below table contains a list of the predefined presentation variables. For time zone variables, the time zone for a user must be set to a value other than Default in order for this variable to work. (Users set their preferred time zone in the “My Account dialog: Preferences tab”.)

Note that the syntax for these predefined presentation variables omits the variables qualifier. 
for example, @{session.locale} rather than @{session.variables.locale}.

Hope this post helps you in your report developments.

Leave a comment

Posted by on October 23, 2011 in BI Publisher, OBI EE, OBIEE 11g


Tags: , , , , , , , ,