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.