Row wise initialized variable in OBIEE 11g and VALUELISTOF function

OBIEE up to 10g had a limitation around session variables which are row wise initialized ,in that you cannot use that variable to initialize another subsequent session variable.  Good news!! 11g seems to have a fix for this.

For e.g. lets say you have a session (row wise initialized)  variable called “REGION” initialized as follows

select ‘REGION_LIST’ ,region_id from region_table.

Now lets say you want to initialize another row wise initialized variable based on the ‘REGION_LIST’ variable you had initialized earlier. For e.g. all the accounts belonging to the ‘REGION_LIST’ variable.

Here is the new way of doing it in  11g

select ‘ACCOUNT_LIST’ ,account_id from accounts where

region_id in (VALUELISTOF(NQ_SESSION.REGION_LIST)).

Keep in mind this will go back to the database as an in list, coma separated and the values would be in single quotes.

Advertisements

Data Visibility in an OBIEE/Essbase Integrated Environment

In my previous blog we seamlessly integrated an Essbase cube with OBIEE.  In this blog  we will see how a simple data-visibility scheme can be implemented for this OBIEE, Essbase integration.

In my SALES_F fact table you can see that every transaction is assigned to one of 5 employees (1 through 5) joined to the EMPLOYEE_D table by the EMPLOYEE_WID foreign key.  Our goal is to set up data visibility such that when an employee logs in to the OBIEE application, he can see only the transaction details that are assigned to him.

Image-0000

First, we create 5 users in the RPD using Security Manager. Image-0001

Next, create a new security group (Employee Group) in the RPD and assign the 5

users into this security group.

Image-0002

Image-0003

Now we need to assign permissions for this security group. Click the permissions button and click on Filters tab.

Click add and and select the fact table (SALES_F) on which you want to apply the filter.Image-0005

Click on the Business Model Filter to open up the expression builder window.

Image-0007

Image-0008

In the expression builder enter the filter condition: CUBE_DATA.EMPLOYEE_D.LOGIN =  VALUEOF(NQ_SESSION.”USER”) .

This filters the contents of the fact table to the employee logging in to the OBIEE application. We are able to achieve seamless data level security implementation between OBIEE and Essbase  as the Essbase Cube AND OBIEE have the same data model and the login column is mapped to the cube as well as to the  EMPLOYEE_D table in the oracle DB.

Image-0011

Now you can log into Answers and verify that your Data visibility scheme gives you the right results.

Image-0010