Business Intelligence – Oracle

Archive for April 27th, 2009

Oracle BI EE – Hyperion Essbase Security Integration – Resolved

Posted by Venkatakrishnan J on April 27, 2009

Now that is out, i thought i would 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 there was a patch that was out earlier to resolve this, i never got around to install the patch. is a patch release, so all the intermediary patches should have been bundled with this release as well.

To see how this has been resolved, lets use the Global cube that we had built in one of the blog entries before. We shall be testing it with 2 users. One is the admin and the other would be admin1. admin would be the super user. admin1 would just have access to the Global cube. And a filter would be assigned to this user. So to start lets first create a filter in the Global Cube as shown below

create filter 'Global'.'Global'.'Sample'
read on '"1999","Units","All Channels","Total Product"';

Once this filter is created, assign the filter to the admin1 user.


So basically, admin1 has access to only one intersection. Every other intersection would produce #NoAccess in Excel addin. As a next step lets create a report out of the Global cube as the admin user.


Now lets create a report in BI EE out of this global with admin user.


Lets switch this user to admin1 in the connection pool and try viewing the same report.


Thats so much better. So, by default BI EE does displays the #NoAccess intersections as null. The MDX generated still remains the same.

  set [Channel2]  as '[Channel].Generations(2).members'
  set [Product2]  as '[Product].Generations(2).members'
  set [Time2]  as '[Time].Generations(2).members'
  { [Measures].[Units]
  } on columns,
  NON EMPTY {crossjoin ({[Channel2]},crossjoin ({[Product2]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Global.Global]


Lets change the filter a little bit as shown below to include more intersections for admin1’s access.

create or replace filter 'Global'.'Global'.'Sample'
read on '@IDESCENDANTS("Time"),"Units",@IDESCENDANTS("All Channels"),"Total Product"';


set [Channel2] as '{[Channel].[All Channels]}'
set [Product2] as '{[Product].[Total Product]}'
set [Product3] as 'Generate({[Product2]}, Descendants([Product].currentmember, [Product].Generations(3),SELF), ALL)'
set [Time2] as '{[Time].[1998]}'
{ [Measures].[Forecast Units], [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel2]},crossjoin ({[Product3]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows


It does look like this indeed works. Well this makes it more interesting. We now have a complete solution and sure opens up lot more opportunities for integration.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 2 Comments »