Business Intelligence – Oracle

Archive for April 10th, 2008

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

 from  SH_BSO1.Shblog Shblog,

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",
 from  SH_BSO1.Shblog Shblog,
 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!!!

Posted in All Posts, Bi Publisher, Hyperion Essbase, OBI EE Plus | 1 Comment »