Choose and Indexcol functions in OBIEE

You can use the choose statement to dynamically choose different columns in a request  depending on the group a user belongs to:

Let us look at  the following request:


Based on the USER logging in we need to choose different columns from the EMPLOYEE_HIER table.

We can do this using the CHOOSE function.

The first step is to create groups in the RPD  and assign USERS to their respective groups.


Next in the Presentation layer of the RPD select  on of the columns on which you need to apply the CHOOSE function.

Image-0005 Image-0004

Click on the Permissions button and give rRead permission to the group whose memebers have Read access to that column.

In the aboive example I have chosen the REGION_MANAGER_NAME column and assigned tread permission for this column to REGIONAL MGR group.

In answers, edit the formula of the column on which you want to apply the CHOOSE function.


Now when a USER logs into the application, the appropriate column is chosen from the EMPLOYEE_HIER table.


IndexCol Function

We can get a similar functionality using  the Indexcol function. The syntax for this function is:

IndexCol( integer value , column1, column2, … )

integervalue can take integral values like 0,1,2 etc and
column1,column2 etc can be any column in the Business Layer

The column is chosen depending on the integer value that gets assigned when a user logs in.  If integer value is 0, then column1 is chose, if it is1 then column2 is chosen and so on.

So in order to implement this function let I create a database table that will contain all the user login ID and a LEVEL column that will will have the integer value associated with that user. Now using  an initialization block and an associated session variable, I can get the integer value associated with the user.

username              level

EMP1                        0

EMP2                        0

MGR1                       1

AM1                           2

RM1                          3


I will use the LEVEL session variable to assign the integer value to the indexcol function.

Now I will choose the column in the BM layer on which I want set the indexcol function, and use the following expression in the expression builder:





Basically what this  does is,

when EMP1 logs in the value of session variable LEVEL is zero and the column LOGIN is chosen

When MGR1 logs in the value of LEVEL is 1 and the column SALES_MGR_NAME is chosen and so on.

Displaying Charts as Images instead of Flash in OBIEE

Recently, one of my clients wanted to spin up Oracle Business indicators, an application to provide Apple Iphone users with real time, secure business information generated by OBIEE and other Oracle Applications.

However, viewing the reports generated by OBIEE on the Iphone requires further configuration of the OBIEE presentation server as  the Iphone presently does not support Flash displayed on web pages.  So reports that contain Flash components like charts  are not displayed correctly.

We  can modify the instanceconfig.xml file to display charts in a custom graphic format  like PNG or JPEG instead of flash.

Include the following in <Server Instance> section of instanceconfig.xml


It is recommended that you configure a separate presentation server to cater to the mobile device users as displaying charts in PNG or JPEG disables some of the interactive features offered by Flash.

Passing Time Series Functions in OBIEE to ESSBASE

In OBIEE we have the AGO and TODATE Time Series Functions in order to compare measures across time periods. Let us see whether we can make use of these functions when using an Essbase Cube as our Data Source.

Let us first test out the AGO function. The AGO  function calculates the aggregated value as of some time period shifted from the current time.

In order to use these time series functions in OBIEE, the first step is to identify a dimension as the Time Dimension. Double click the Time Dimensional Hierarchy and mark it as a Time Dimension.


Next, expand the Time Dimension hierarchy to its lowest detail level. Double Click the lowest detail level and check the Chronological key.

Image-0001 Image-0002

To create a Month Ago measure, right click on the Fact Table in BMM layer and create a new Logical column. I have named this column Month Ago Sales.


Now check ” Use Existing Logical Column as the source and then click on the Ellipsis to open up the Expression Builder box.


In the Expression Builder, Select  Functions > Time Series Functions > Ago and click insert.

The AGO function requires 3 arguments:

Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)

1. Measure—-Select the appropriate measure from the Fact Table( Sale_Amount)

2. Level——-  Select the required level in the Time Dimension (Quarter)

3. Number of Periods——Is an integer number (# of Periods you want to go back) The AGO function will calculate the Amount sold n Quarters prior to current Quarter if this value is n.

I have shown the parameters chosen for the Month Ago Sales Measure.


Close the expression builder, drag the newly created measure column into the Presentation Layer and check in changes.

Now create a test report in Answers and verify your results


MS plug-in for OBIEE

The new office plug-in from Oracle for MS Office integration with OBIEE looks pretty cool on the surface. But the Power point plug-in is very rudimentary and almost not usable. Here are some major draw backs of the product from a first pass.

1) Pivot tables are not supported.

2) Formatting specified in the OBIEE reports/charts would not get migrated to PPT when imported using the BI Plug-in. The charts can be formatted within Power Point and refreshes would preserve the format (specified in PPT).

3) Sorting based on hidden columns in a chart would get messed up when the chart is displayed in PPT using the BI Plug-in. The values (x-axis) are always sorted alphabetically. There is a work around for this though. Push the sorting back to the RPD. Set the hidden column used for sorting in the OBIEE report as the sort key for the column displayed, in the RPD.

4) Object security in the web catalog is not reflected in the Plug-in. For example if a user doesn’t have access to a certain report in the webcat, the user would still see the report in the BI Plug-in pane. But if the user clicks on the report an error pops up and user would not be able to see the data in the report. So there is not data security breach as such but still this is very annoying. The users would be able to see all the reports irrespective of is they have access or not. The users would not be able run the reports that they don’t have access to, the report names would be visible on the BI plug-in pane.

Incorporating Attribute Dimensions in Essbase into OBIEE

Attribute dimensions that you define in Essbase are imported into the OBIEE RPD when you import the cube. However, they are not automatically assigned to the correct dimensional level during the import process. Lets us see how we can model attribute dimensions and associate them with the correct level.

In this blog we will see how we can create attribute dimensions in Essbase and then how we go about incorporating it in OBIEE.

Let us first start with the OLAP model. I want to create an attribute dimension called UNIT PRICE at the PROD_NAME Level in the PRODUCT Dimension.


In the table properties for PRODUCT table select the UNIT PRICE column and click on Attribute to define UNIT PRICE as an Attribute Dimension. Save the OLAP model and open the Metaoutline for this model.Image-0002

In the Metaoutline, drag the UNIT PRICE Attribute Dimension to right pane. Image-0003

Double click the UNIT PRICE attribute dimension and set the association to the correct level. In this case it is the PROD_NAME LEVEL.


Save the Metaoutline and then load the cube.

After you have built the cube import this into the Physical Layer of the OBIEE RPD and then drag it into the BMM Layer. You can see that the UNIT PRICE Attribute Dimension is not associated with the PRODUCT Dimension.


We will have to manually add the attribute Dimension column to the PRODUT_D logical table and also associate it to the correct hierarchical level in the PRODUCT hierarchy.


You can now log into Answers and verify that the Attribute Dimensions give the correct results.


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.


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.



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.



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.


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


Cache management in a OBIEE clustered environment

Let us look at cache management in a clustered environment.

Consider a scenario in which I have two BI servers in a cluster – server A and server B and  I want seed the cache for users who have different data visibility.  A common approach to seed the cache is to use ibots. You could run an ibot as an administrator and  add the users as the recipients  to seed their cache. However, in this case because of the data-level security restrictions,  when you try to seed the cache for a particular user you might run into cache-seeding errors. Only users with administrator privileges in the RPD can seed the cache using ibots.

Another issue is that a request scheduled using ibots may be assigned to any one of the clustered BI servers. Even if a cache entry is made,  it will exist only in the node that processed the request. As a result, we will see a cache hit only if a query gets directed to the same BI server which has the cache entry.

A workaround to these issues is to use the NQCMD command line utility to seed the cache on all the clustered BI servers.  NQCMD needs to be run on all the nodes separately to seed the cache.

First we need to create a non clustered DSN entry in all the nodes in the cluster.


First step would be to purge the cache in all the nodes in the cluster.

Purging all Cache:

Open a text file and enter the following text:

Call SAPurgeAllCache()

Save this file as purgecompletecache.txt

Open another text file and enter the following:

nqcmd -d non_cluster_DSN -u Administrator -p Administrator -s purgecompletecache.txt

Save this file as purgecache.bat

When you execute purgecache.bat all the cache entries will be purged.

Purging Cache for a particular table:

Save the following in a text file:


Call this file in the batch file and execution of the batch file will clear all the cache entries associated with the physical table TABLENAME. Note that the catalog, schema and tablename are  the values in OBIEE and not the name in the database. If the CATALOG value is null then use ”.

Repeat this process on all the nodes and verify that the cache entries have been purged.

The next step is to seed the cache for the different users. The following steps outline the steps involved in this:

Seeding the cache:

Copy the Logical SQL for the request you want to cache from cache manager and paste it into a notepad file.

I have named this notepad file: sql_1.

Now open another notepad file and enter the following text:

nqcmd -d non_cluster_DSN -u EMP4 -p EMP4 -s sql_1.txt -o output.txt

You should use the username and password of a user who has the same data visibility as the users whose cache you want to seed.

Save this file with a .bat extension. I have named it seedcache.bat.

When you run this windows batch file, the the following events occur:

1. call the nqcmd utilty

2. Log into OBIEE server using the non-clustered DSN  with username: EMP4 and password:EMP4

3. Execute the logical sql in file sql_1.txt

4. Write the output in file: output.txt.

Execute this batch file in every node of your cluster and verify that a cache entry has been made in your RPD for user : EMP4