OBIEE 11g Release 11.1.1.7 –Multi-Source Session Variables

OBIEE 11g Release 11.1.1.7 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. The multi-source session variable can hold more than one values, there is no restriction on number of values.

Is there any need to have multi-source session variable?  What is a use case of having multi source session variable?  

Multi-Source Session Variables

It is very common for BAW to have data from multiple sources. A regular session variable is populated from one data source. Session variables are generally attribute values which are sourced from data sources at run time. In case of multiple sources there is a use case to get different values from data sources for chosen attribute e.g. country – values might be different in different data sources (e.g. Siebel Source System and E-Business Source System)   

The creation of multi source session variables includes followings

# Create row-wise initialization blocks for each source

# Define session variable for each source in format - <ms_varabile_name>____<source>, where separator is exactly four underscore characters

This will automatically creates a single multi-source session variable - <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>).

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

Creation of Multi-Source Session Variables

 

Admin Tool >> Variable Manager>> Action >> New >> Session >> Initialization Block - Create following two row-wise initialization block mvcountry_sebl_init and mvcountry_orcl_init

Admin Tool >> Variable Manager>> Action >> New >> Session >>variable

Sr.No	Variable	Initialization Block	SQL
1	MVCOUNTRY____SEBL	mvcountry_sebl_init	Select distinct 'MVCOUNTRY____SEBL'from siebel_table
2	MVCOUNTRY____ORCL	mvcountry_orcl_init	select distinct 'MVCOUNTRY____ORCL', country from oracle_table

Session Variable – Sibel Source System

Session Variable – ORCL Source System 

 

 

Usage of Multi-Source Session Variables

The multi-source session variables created appear in the Variable Manager, the multi source session variable that has been created will appear in Expression Builder as shown below.

 

The logical expression which uses the multisource session variable is as follows.
 select lastName, firstName, country from employee where country=VALUEOF(NQ_SESSION.MVCOUNTRY)

Inference

Oracle BI EE 11g now supports session variables that can be populated from multiple data sources and retain values from all source systems. 

I hope you find this information useful.