Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Using IndexCol Function – Varying Underlying Columns based on User Login

Posted by Venkatakrishnan J on November 16, 2007

One of the interesting features of BI EE is its ability to bring up different Business Model columns for a single Presentation Layer column based on different user logins. For example, say if you are creating the following report (all columns point to presentation layer columns),

GEOGRAPHY            SALES

you can specify which column in the BM/Physical layer should be used for the query in the place of GEOGRAPHY Presentation column (CEO’s can see region level data. Manager’s can see country level data etc), based on a user login. This can be done by using a simple function called as IndexCol. Before moving on to the syntax of this function lets try to understand what we are trying to do in the example below. Our aim is to create a report like the one below.

      

Now, whenever the user Region logs in, he must get a report containing the AMOUNT for all the regions (GEOGRAPHY_LEVEL will get the data from the column REGION_NAME) like the one below.

      

And, whenever the user Country logs in, for the same report he must get the data containing the AMOUNT for all the countries( GEOGRAPHY_LEVEL will get the data from the column COUNTRY_NAME ) like the one below.

      

Now lets look at the IndexCol function which forms the basis of what we are trying to do. Syntax of this function is

SYNTAX:      IndexCol( integer literal, expr1, expr2, … )

   Where
         Integer Literal can take integral values like 0,1,2 etc and
         expr can have different BM Columns.

What this function basically does is, it dynamically chooses different columns based on the Integer Literal value. Lets consider the example below

       INDEXCOL( VALUEOF(NQ_SESSION.”LEVEL”), BISE1_TUTORIALWH.GEOGRAPHY.REGION_NAME, BISE1_TUTORIALWH.GEOGRAPHY.COUNTRY_NAME)

In the above function, the INDEXCOL function looks for the LEVEL session variable. If the value of this variable is 0, it will choose the REGION_NAME column. If the value is 1 then it will choose the COUNTRY_NAME column. So, to implement the above example, we shall create a simple table in the database which would have 2 columns as shown below

      

And lets populate it with the username values and the corresponding integer literal values. So, our idea is to populate the LEVEL variable from a init block. So, the user Region, when he logs in will set LEVEL to 0 and hence the REGION_NAME column will be used in the report. Similarly for user Country, when he logs, the variable will be set to 1 and hence the COUNTRY_NAME column will be chosen.

      

      

The LevelInit init block will set the LEVEL variable as shown below.

      

Once this is done, your report should reflect the 2 columns based on the user who has logged in.

As user Region

      

As user Country

      

The above is not the best of examples. But this should atleast give an idea of what IndexCol can do.

Advertisements

2 Responses to “Oracle BI EE 10.1.3.3/2 – Using IndexCol Function – Varying Underlying Columns based on User Login”

  1. Vivek said

    Very helpful!

    Thanks,
    Vivek.

  2. Saraswathi said

    It was really very helpful.Thanks a lot.

    Regards,
    Saraswathi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: