Business Intelligence – Oracle

BI EE – Hyperion Essbase Security – Zeroes for No Access

Posted by Venkatakrishnan J on May 19, 2008

Both Hyperion Essbase and BI EE have very good security models. Now that we have the connectivity to Essbase from BI EE, i thought i would test out the security integration between the two. One of the first things that i wanted to test was to find out how the Essbase Security propagates into the BI EE security model. Lets start the test with a simple cube/database having 2 dimensions LOB and YEAR and a measure AMOUNT. Now, lets define one more user in our Essbase server (user1) who would have read access to this cube.



As a next step lets define a filter in the Essbase cube which would restrict access to the LOB dimension and all its members. This filter can be achieved using the below maxL script.

create or replace filter 'POA'.'Poa'.'filter2'
no_access on '@DESCENDANTS ("LOB")'

Now, assign this filter to the user user1.


Once this is done, lets first see what happens when we try to view the cube’s data as user1 from with the excel add-in.



As you see, since this user does not have access to the other dimension members of the LOB dimension we get “No Access” message for all the members of the LOB dimension. But for the YEAR dimension alone, for which user1 has access, we get the data. Lets see what happens in BI EE. The first step is to import the cube as an Admin user (or an user with Application read/write privilege). Once this done, change the username in the connection pool as user1.


Now create BM and PL layers for this imported cube and try creating a simple report using this cube.


Strange that we are getting zeroes here instead of an error message (which i was expecting). But data is shown correctly for other dimensions for which this user1 has access to.


This is something you would have to be aware of while using the BI EE-Essbase connectivity. Not every user/management would be happy if you show them zeroes instead of a No Access message. But this can be circumvented by assigning one more layer of BI EE security on top of the Essbase dimensions.

5 Responses to “BI EE – Hyperion Essbase Security – Zeroes for No Access”

  1. […] original here: BI EE – Hyperion Essbase Security – Zeroes for No Access microsoft office patch microsoft office […]

  2. sahil said

    Hi , im using siebel analytics there are 3columns in my report EIN,OUC,EIN KEY and i want to export the report to excel.
    But in excel i want only 2columns EIN,OUC to be shown when imported.
    How can we do dis??

  3. Deepak Karanwal said

    We are using OBIEE to report against the Essbase Cube.
    We are authenticating our users against the Essbase cube.
    We had applied security filters at Essbase , when we see data using Essbase plug in, or smart view we see correct data with all security applied , but with OBIEE we are getting correct data.

    For eg

    we created a filter fdk_test1 like
    create or replace filter plnaso.plnaso.fdk_test1 read on ‘@IDESC (“844:Electrical and Comp”)’;
    alter filter plnaso.plnaso.fdk_test1 add no_access on ‘@IDESC (“All Orgs”) ‘;

    and assigned this filter to user “USER1”
    Org 844 is at the lowest level of Org Hierrachy.

    When USER1 log to Essbase cube through Essbase addin or smartview and run the query
    he get

    Org Amount
    700 #No Access#
    844 1000
    855 # No Access

    which is perfect as we defined our filter to access data only related to org 844.

    But when USER1 run same query via OBIEE he get

    Org Amount
    700 0 (which I assume is correct as no access will be replaced by 0)
    844 1000(this is correct)
    855 1000 (this is wrong)

    As soon as it find any non zero value , all other member following that will be assigned this value , which is wrong.

    Appreciate your help.


  4. […] the 2 components. Currently, BI EE cannot leverage the Essbase security. Though i had stated here that it does work, that test was done for a specific use case. But the same does not work for all […]

  5. […] test out the security integration of BI EE and Essbase. If you have read my blog entries here and here, you would have noticed that there was a known bug around the version of BI EE. Though […]

Leave a Reply

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

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

Google photo

You are commenting using your Google 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 )

Connecting to %s

%d bloggers like this: