Business Intelligence – Oracle

Oracle BI EE 10.1.3.4.1 – Hyperion Essbase Security Integration – Resolved

Posted by Venkatakrishnan J on April 27, 2009

Now that 10.1.3.4.1 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 10.1.3.4 version of BI EE. Though there was a patch that was out earlier to resolve this, i never got around to install the patch. 10.1.3.4.1 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.

With
  set [Channel2]  as '[Channel].Generations(2).members'
  set [Product2]  as '[Product].Generations(2).members'
  set [Time2]  as '[Time].Generations(2).members'
select
  { [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"';

 

With
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]}'
select
{ [Measures].[Forecast Units], [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel2]},crossjoin ({[Product3]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

       

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.

About these ads

2 Responses to “Oracle BI EE 10.1.3.4.1 – Hyperion Essbase Security Integration – Resolved”

  1. Kaus m said

    Thanks Venkat ,

    This should really be of help. Do you think you can figure something out for alternate sort orders in OBIEE reports for Essbase cube based data?

    Thanks

  2. […] Oracle BI EE 10.1.3.4.1 – Hyperion Essbase Security Integration … […]

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: