Business Intelligence – Oracle

Hyperion Essbase 9.3.1 and BI Publisher Connectivity – Using BI EE as a data Bridge

Posted by Venkatakrishnan J on April 10, 2008

As i was testing the BI EE and Hyperion Essbase connectivity yesterday, i thought i would test out the connectivity of BI Publisher and Hyperion Essbase through BI EE metadata and Answers. I started out under the impression that the performance would either be slow or there would be some hiccups on the way. But to my surprise, the connectivity was so seamless that it gave me the impression that BIP itself is using XMLA to connect to Essbase( i am not sure whether there are plans for supporting XMLA directly from BIP). I used the same cube that i had built yesterday here which contained a ragged hierarchy on the products dimension. As you would probably know, there are 2 ways of connectivity from BI Publisher to BI EE. One is by firing a query on the BI Server and the other is by using an existing Answers report. Lets try both of them today to find out how the connectivity actually works. Lets start first with the BI Server connectivity. So, create a simple BI Publisher report and in the data source use “Oracle BI EE”. And in the query use “Sql Query”.

    

Click on the Query builder and choose your Subject Area ( in our case the SA containing the Hyperion objects). Choose the 3rd level and the measure

    

If you notice, you would get a query like this

 select  PRODUCTS.Gen3,PRODUCTS as Gen3,PRODUCTS,
  Shblog.AMOUNT_SOLD as AMOUNT_SOLD
 from  SH_BSO1.Shblog Shblog,
  SH_BSO1.PRODUCTS PRODUCTS

Unfortunately, BI Publisher is not aware of Hyperion Essbase modelling in BI Server and hence does not encapsulate all the columns within double quotes. The above query would only give an error. So, change the above query to the one below

 select  "PRODUCTS"."Gen3,PRODUCTS" as "Gen3,PRODUCTS",
  Shblog.AMOUNT_SOLD as AMOUNT_SOLD
 from  SH_BSO1.Shblog Shblog,
  SH_BSO1.PRODUCTS PRODUCTS
 where "PRODUCTS"."Gen4,PRODUCTS" is not null

Now, try viewing the data. So far so good.

    

Now all we need to do is to add templates to this report and it works like a breeze.

    

    

Lets try the other means of connectivity which is by using an Answers request as the data source.

    

Add a template to this data source and there you go. A simple BIP report using Essbase as the data source.

    

Very nice!!!

About these ads

One Response to “Hyperion Essbase 9.3.1 and BI Publisher Connectivity – Using BI EE as a data Bridge”

  1. rodney weinstein said

    Folks – One thing to note that we ran into that can be an issue for you as well is that there is a bug with BI Publisher using Answers as a data source and using dashboard global filters with session/presentation variables. Our fallback is to use the OBIEE RPD as a source, but it is certainly preferred to use Answers so we don’t have to recreate the reports from scratch.

    Rodney

    BUG 6903436 – BI PUBLISHER REPORT DOESN’T PRINT CORRECT INFORMATION FROM OBIEE ANSWERS REQUEST

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 151 other followers

%d bloggers like this: