Sunday, 29 December 2013

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.

1 comment:

  1. Good one,


    In your post, it states need to create row-wise init blocks. But while creating init blocks shown with example of a variable created instead using row-wise.

    That is small observation... please ignore, it it doesn't make sense..

    ReplyDelete