Work with Multi-Source Session Variables

Oracle Analytics Server supports session variables that are populated from multiple data sources.

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.

You can use these multi-source session variables 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.

You explicitly define session variables for each source. Use the following format for the session variable names:

• <ms_variable_name>____<source>

You must use exactly four underscore characters as the separator.

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 Model Administration Tool, but the Expression Builder displays only the single multi-source session variable name, <ms_variable_name>.

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

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

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

Use these examples to learn how to create a multi-source session variable.

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, select New, select Session, and then select Initialization Block.

2. Create a row-wise initialization block called mvcountry_sebl_init with the following SQL for Default initialization string:

   select distinct 'MVCOUNTRY____SEBL', country from siebel_table
   

3. Create a second row-wise initialization block called mvcountry_orcl_init with the following SQL for Default initialization string:

   select distinct 'MVCOUNTRY____ORCL', country from oracle_table
   

4. Still in the Variable Manager, select Action, then New, then Session, and then Variable.

5. Create a session variable called MVCOUNTRY____SEBL, making sure to include four underscores between the variable name and the source name. For Initialization Block, select mvcountry_sebl_init.

6. Create a second session variable called MVCOUNTRY____ORCL, making sure to include four underscores between the variable name and the source name. For Initialization Block, select mvcountry_orcl_init.

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

Using the Multi-Source Session Variable in a Logical Query

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

For example:

select lastName, firstName, country from employee 
where country=VALUEOF(NQ_SESSION.MVCOUNTRY)

Using the Multi-Source Session Variable in a Data Filter

Perform the following steps to use the MVCOUNTRY multi-source session variable in a data filter:

1. In the Model 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:

   • Country=VALUEOF(NQ_SESSION.MVCOUNTRY)

   
   Description of the illustration mssv_perms.gif

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

Description of the illustration mssv_expr.gif