Oracle Essbase 11.1.1.3 – Drill-through to BI EE from Smart View

One of the nice little advantages of using Oracle Essbase Studio is the fact that it manages the relational drill through much better than the Essbase Integration Services. With the advent of Essbase studio, all the metadata for relational drill through is pushed back to the Studio server. So, when one looks at an Essbase outline, there is no way of telling whether the specific database has a drill through set up or not. Apart from the metadata push, one other interesting piece of integration is the ability to drill through from Smart View directly to BI EE. This is another interesting piece especially if you have a mix of Smart-view and BI EE users in your reporting group.

In a prior blog entry here, i had covered the integration to Smart View through a new smart view protocol that was introduced in the EPM 11 release. The smart view protocol basically provides ability to setup integration from BI EE to Smart-view (one can make BI EE to open up excel automatically along with the smart-view add-in and also passing parameters). The relational drill through opens up the reverse integration methodology wherein one can click on an Essbase cell which will in turn open with a BI EE URL along with the parameters.

To set the drills, the pre-requisite is the fact that the Essbase outline would have to be maintained completely only by Essbase Studio. What this means is, if you have a cube that you have created directly using Essbase Administration Services it is not possible to set up drill-through on them. Though this is somewhat limiting but there is a way around it which i shall be covering in a future blog entry.

Lets start with building a SH cube from Essbase Studio. The idea is to build an ASO cube using the hierarchies shown below

Once the hierarchies have been built in the Essbase studio, create an Essbase Model and then deploy the model as an Essbase database.

Once the cube is deployed, create a drill through report from Essbase studio as shown below. Include all the hierarchies whose dimension attributes you would like to pass as values to your BI EE report. In my case, i would like to just pass down time dimension value as a filter to my report

In the report contents tab, instead of SQL, use a drill through link. One very good feature is Essbase Studio can create a BI EE template URL which will pass the cell’s time attribute value through the URL as a URL parameter automatically

Change the above url to a Go URL (if you want to pass the value to a dashboard use the PortalPages url parameter which is the default for Essbase studio)

obi:http://localhost:9704/analytics/saw.dll?Go&
Path=/shared/Sample%20Sales/SmartviewReport&Options=rmf&
Action=Navigate&P0=1&P1=eq&P2=Times.FISCAL_YEAR&P3=$$Times-VALUE$$

Now, go to the associations tab and then associate the drill through report to the model. That is all needed to enable the drill through from smart view. This should now navigate automatically from smart-view to BIEE.

One excellent thing about this integration is the fact that nothing is done to the Essbase server. So, drill through reports can always be setup externally by reporting users rather than Essbase admin users. In addition, this provides a lot of analysis options directly within smart-view.