Business Intelligence – Oracle

Oracle BI EE, BI Publisher 10.1.3.4 & Hyperion Essbase Connectivity – Security Integration

Posted by Venkatakrishnan J on December 22, 2008

One of the major letdowns (if i can call that) of the BI EE and Essbase connectivity is the security integration between 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 the other major scenarios. This blog entry basically gives a brief on all my findings on the BI EE and Security Integration. We shall take the same Demo->Basic cube for all our testing. To start with let us use below report on the Demo->Basic cube.

       

As you see it is a very simple report containing all the dimensions in the Demo->Basic Cube. Now, let us create a new user in Essbase called as Demo1. And this user would only be given a filter access to this cube.

       

Let us create a new filter in Essbase which would have access to only the Profit Account and all the other members of other dimensions. So the filter specification would be as shown below

@IDESCENDANTS ("Year"),@IDESCENDANTS ("Market"),@IDESCENDANTS ("Product"),@IDESCENDANTS ("Scenario"),"Profit"

       

Then assign this filter to the Demo1 user.

       

Lets change the user in the connection pool to Demo1.

       

Once this is done, let us take a look at the report.

       

If you look at the report closely, the data itself is completely wrong. We are not supposed to get any data for accounts other than Profit. But in our case not only are we getting data, it is juxtaposed with numbers from prior members. Now, let us take a look at the MDX to see whether the filter that we setup above works correctly.

With
set [Accounts2] as '[Accounts].Generations(2).members'
set [Market2] as '[Market].Generations(2).members'
set [Product2] as '[Product].Generations(2).members'
set [Year2] as '[Year].Generations(2).members'
select { [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Accounts2]},crossjoin ({[Market2]},crossjoin ({[Product2]},{[Year2]})))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

Now, let us fire the above MDX query directly in Essbase as the Demo1 user. This will let us know whether the filter has been applied properly on the cube.

       

As you see above, the MDX does generate No Access label for all the Non-Profit account intersections. Hence the problem actually lies in the BI EE Server wherein the conversion from this MDX output to a BI EE specific answers output does not happen correctly. I believe this is probably because, since the ACTUAL measure is numeric, the UI expects only numeric values in the output MDX. But if there is any non-numeric output then rendering of the output would be wrong. This prompted me to understand another important question. How does BI EE handle the #Missing label? If you see, BI EE will always pass the NON EMPTY clause in your MDX output. Hence there is no way #Missing can come in the output. The only thing that i can conclude as of now is that #No Acces label in not handled in the BI EE Server and hence it is bound to produce wrong results. Now, lets investigate further to see whether this is actually due to the BI EE UI. One is to test the MDX directly in BI Publisher and the other is to fire the logical SQL directly. Let us take the BI Publisher approach first. Let us log into BI Publisher first and then fire the same MDX Query as the admin user first.

       

As you see we get all the accounts including Profit and Non-Profit accounts. But in the above output we do not have the dimensions as part of the rowset. To investigate this in itself is a seperate blog entry which i shall do later. But for now, we are sure that the above works for the admin user. Now, lets try the same report as the Demo1 user.

       

So far so good. Looks like BI Publisher does understand the No Access label as it has filtered all the no-access rows. I believe this is either getting filtered in the UI or BI Publisher engine modifies the MDX queries in such a way that it does not display the No Access Rows. This is again a bit strange as there are no out of the box properties to filter the No Access rows in BI EE. In order to test this completely, let us remove the NON EMPTY property and also add one more scenario to the query as shown below

With
set [Accounts2] as '[Accounts].Generations(2).members'
set [Market2] as '[Market].Generations(2).members'
set [Product2] as '[Product].Generations(2).members'
set [Year2] as '[Year].Generations(2).members'
select { [Scenario].[Actual], [Scenario].[Budget] } on columns, {crossjoin ({[Accounts2]},crossjoin ({[Market2]},crossjoin ({[Product2]},{[Year2]})))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

       

       

Though we thought the security did work fine above, this is not the case anymore when we added the Budget scenario as well to the query. So, what has happened is BI Publisher has converted all the No Access labels to #Missing label. But again this is not applicable everywhere as we get #Missing for the second rowset. One thing that i can conclude is that security integration to Essbase is very primitive in this current release of both BIP and BI EE.(i just hope someone proves me wrong here). And, I just hope that this is addressed properly in 11g.

As a last effort let us try to pull in the data from the same report in answers to BI Publisher.

       

       

As you see, we still get wrong data. So, the only way to handle secure Essbase data sources is from within BI EE using the same approach as relational data sources.

One Response to “Oracle BI EE, BI Publisher 10.1.3.4 & Hyperion Essbase Connectivity – Security Integration”

  1. […] 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. […]

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: