Hyperion Essbase & Planning Drill-Through

Drill-Through from an Essbase cube or a Planning application is a fairly common requirement. There are a couple of ways to do it: EIS, Essbase Studio, FDM, etc. The main drawback of most (probably all) of these tools is that the cube/application needs to be built from the ground up using one of these tools. There are a couple more drawbacks in some of the tools like upper level drill-through, retrieval speed and format of the returned data so there is some kind of compromise that has to be made. Business users do not necessarily see eye to eye with the consultants or hyp-admins when it comes to compromises.
Drill-through can also be done in reporting products like Web Analysis and OBIEE however the link between users and Excel cannot be broken easily.

Drill-Through from Hyperion Planning applications to transaction data is especially important if you do not want to transform you're planning application into an detailed actual reporting application. Also an audit will usually result in the requirement that transaction data should be made available.

Making a BSO cube include detailed actuals is not always the best of ideas however it is fairly common practice. Mainly because a Planning Plus licence doesn't allow the use of ASO cubes, getting the ASO option does not allow the use of Financial Reporting with ASO, getting both ASO+FR might be too expensive. Mixing a BSO forecast/budget with and ASO reporting cube is a really good idea and will(should) result in good performance.

One good way to get drill-through from a Planning app or an Essbase cube is to use the drill-through definition provided by Essbase. There is not a lot of documentation about this that I could find. Also some of the examples that I could find do not work. See Oracle Essbase Addendum.
The Essbase drill-through definition allows to add drill-through capabilities to any cube or Planning application without having to modify the existing ETL or sending users to web reports. I think that is also easy and fast to implement.

The Essbase drill-through definition can be used together with a simple custom made web app to allow drill-through from Excel/SmartView or Planning Forms. The result of the drill-through can be anything, a list of transactions based on the POV delivered as html, xls, pdf, cvs, avi or a parametrised redirect to something like an OBIEE report.

Once the Essbase drill-through definition is created the drill-through is available in Excel (Smart View) or Planning forms and looks like this:

Once the user clicks the drill-through button the default browser will be opened and a post request will be made to the URL specified in the drill-through definition. A CSV file or whatever the users like will be returned.
The drill-through can be initiated from Excel / Smart View using either the "Drill-through" button located on the ribbon or by right clicking  on the data cell and selecting "Smart View -> Drill-through".
Two or more drill-through definition can be specified for the same data cell and the users will have to choose which one wants to execute.

To create a drill through definition MaxL, Admin Services Console or the Essbase API (Essbase C API as I couldn't find this in the Java API) can be used. MaxL is the easiest option to show an example in:

1. Create the drill-through definition's XML file, name it "xml.xml" and save it on the C drive:

<?xml version="1.0" encoding="UTF-8"?>

<foldercontents path="/">

  <resource name="Show transactions" description="" type="application/x-hyperion-applicationbuilder-report">

    <attribute name="name" type="string" xml:lang="es" value="Drill through" />

    <action name="Display HTML" description="Launch HTML display of Content" shortdesc="HTML">

      <url>

        <![CDATA[http://webserver/page.html?context=$CONTEXT$&$ATTR(ds,id,pos,gen,level.edge)$]]>

      </url>

    </action>

  </resource>

</foldercontents>

2. Create the drill-through  definition in Essbase with MaxL:

create drillthrough sample.basic."Show transactions" from xml_file 'C:\xml.xml' on {'@DESCENDANTS("Measures"), "100", "200", "300"'};

Done. All users that have access to those particular members will see the drill-through. Note that SSO_TOKEN parameters can also be used in the URL specified in the XML file so that the users credentials can be verified.

The Essbase API can be used to manage drill-through definitions. In a custom build app this might be the way to go.

When a user selects the drill-through the default web browser is opened and a POST request is sent to the URL defined in the drill-through definition. The web application receiving the request is responsible for parsing the request and doing something about it. The request will contain all the info that the users sees on the screen: all selected members (POV) at any level, the level of the members, where is the drill through coming from, server name, app name and sso token.

Here is an example of the Post request coming from Essbase:
context=$CONTEXT$&attribute=Market.id.New+York&attribute=Scenario.id.Actual&attribute=Measures.id.Marketing&attribute=Product.id.100&attribute=Year.id.Jan&attribute=Market.edge.col&attribute=Market.pos.0&attribute=Market.level.0&attribute=Market.gen.3&attribute=Scenario.edge.col&attribute=Scenario.pos.1&attribute=Scenario.level.0&attribute=Scenario.gen.2&attribute=Measures.edge.col&attribute=Measures.pos.2&attribute=Measures.level.0&attribute=Measures.gen.4&attribute=Product.edge.row&attribute=Product.pos.0&attribute=Product.level.1&attribute=Product.gen.2&attribute=Year.edge.row&attribute=Year.pos.1&attribute=Year.level.0&attribute=Year.gen.3&attribute=system.ds.essbase&attribute=server.ds.demoepm1112&attribute=app.ds.Sample&attribute=database.ds.Basic&attribute=alias.ds.Default&applicationtype=officeAddin

An easy way to see how the request from Essbase looks like is to put http://www.hashemian.com/tools/form-post-tester.php/drillthroughtest as the URL in the drill -through definition. This page will show the POST data sent by Essbase.

The most common requirements is to show the user a list of transactions in CSV format based on the members available on the users Excel spread sheet or the members on the Planning form.
A common flow will look like this:
- the request coming from Essbase is parsed to get the Dimenion/Member selected by the user and the sso token.
- the sso token is validated. The css api, essbase api or a direct http request to the workspace web page (this is not officially supported) can be used to validate the sso token.
- decide what members will be used in the SQL query that will get the transaction data. If an upper level member is selected by the user and transaction data sits at bottom level then the level 0 descendants will be needed. To get this info the Essbase outline can be queried or any other data source that contains this info (planning db)
- build the select statement or call an stored procedure to get transactions out of the dw
- format the transaction data as CSV and send it to the user. The web page that was opened by the drill- through will be automatically closed and the CSV file will be opened in Excel.

The above can be done in ASP, JSP, PHP and any web developer will be able to do it. Because the request is received by web app that is completely under the developers controls the outcome of the drill-through could be virtually anything:
- a file with anything in it: transactions, changes/merges over time for a particular business unit, sections, debtor
- a redirect to OBIEE, E-Business Suite
- an e-mail to an user saying this value needs attention.
- a trigger to an ETL process to start refreshing data for a particular entity