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

Essbase C API with C# - Login & Database List

After the Essbase C API has been initialised successfully a pointer to an essbase api instance handle is returned. The instance handle should be preserved as it will be needed throughout the program. If building a wrapper in C# the instance handle should be kept. The instance handle will be used in function like EssTerm, EssAlloc, EssLogin, EssFree and so on.
As initialising the Essbase API is discussed in another post I won't show it here however it is needed.

Here are the steps to login to essbase and get the accessible list of databases of the logged-in user:

Prerequisites
1. Add a few constants

public class NativeConstants

{

    public const int ESS_DBNAMELEN_CHARACTER = 30;

    public const int ESS_BYTES_PER_CHARACTER = 4;

    public const int ESS_BYTES_FOR_TRAILING_NULL = 5;

    public const int ESS_APPNAMELEN = ((30 * NativeConstants.ESS_BYTES_PER_CHARACTER) + NativeConstants.ESS_BYTES_FOR_TRAILING_NULL);

    public const int ESS_DBNAMELEN = ((NativeConstants.ESS_DBNAMELEN_CHARACTER * NativeConstants.ESS_BYTES_PER_CHARACTER)+ NativeConstants.ESS_BYTES_FOR_TRAILING_NULL);

}

2. Create the ESS_APPDB_T structure. This is needed to get the accessible list of database in case of a successful login.

[StructLayout(LayoutKind.Sequential, Pack = 1)]

public class ESS_APPDB_T

{

    /// <summary>

    /// Application name

    /// </summary>

    [MarshalAs(UnmanagedType.ByValTStr, SizeConst = NativeConstants.ESS_APPNAMELEN)]

    public string AppName;

    /// <summary>

    /// Database name

    /// </summary>

    [MarshalAs(UnmanagedType.ByValTStr, SizeConst = NativeConstants.ESS_DBNAMELEN)]

    public string DbName;

}

3. Add more functions to the EssbaseTypes class

public class EssbaseTypes

{

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssInit(ref ESS_INIT_T EssInitStruct, ref uint EssHInst);

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssTerm(uint EssHInst);

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssLogin(uint EssHInst, string EssSvrName, string EssUserName, string EssPassword, ref ushort EssDBCount, ref IntPtr EssDBList, ref uint EssHCtx);

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssLogout(uint EssHCtx);

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssFree(uint EssHInst, IntPtr EssMemBlock);

}

Code
1. Initialise the Essbase API and get the instance handle. How to initialise the Essbase C API
2. Call the login function

Here are a few functions that can be used:
EssLogin - login with db list
EssAutoLogin - login with EssSetActive added to it
EssLoginEx - login with sso_token. Really useful when the token is passed from another Hyperion product as it eliminates the need to ask for credentials.

We'll use EssLogin as it is a bit more interesting.

info needed:
- user and password: "admin"/"password" is my favourite as most production env are using it.
- Essbase server: Can be either server:port or the url of the essbase cluster. If only the server name is used then the default port is used.

info returned:
- essDBCount: the number or accessible database
- essDBList: a pointer to array of ESS_APPDB_T structures
- essHCtx: context handle. Should be kept because it will be needed in almost all api calls.

// Connection details

string essServer = "server";

string username = "admin";

string password = "password";

// Out values of EssLogin method

ushort essDBCount = 0;

IntPtr essDBList = new IntPtr();

uint essHCtx = 0;

// essHCtx is to be user in subsequent essbase api calls

uint errNum2 = EssbaseTypes.EssLogin(essHInst, essServer, username, password, ref essDBCount, ref essDBList, ref essHCtx);

3. Get the list of accessible databases
What we get back from EssLogin is the number of databases and the a pointer to a list of databases

if (essDBCount > 0)

{

    // Get the database list

    ESS_APPDB_T[] appdbs = new ESS_APPDB_T[essDBCount];

    for (int i = 0; i < essDBCount; i++)

    {

        appdbs[i] = (ESS_APPDB_T)Marshal.PtrToStructure(new IntPtr((essDBList.ToInt32() + i * Marshal.SizeOf(typeof(ESS_APPDB_T)))), typeof(ESS_APPDB_T));

    }

}

4. Free memory


// Free memory allocated to the app/db list

uint errNum3 = EssbaseTypes.EssFree(essHInst, essDBList);

5. Logout

// Log out

uint errNum4 = EssbaseTypes.EssLogout(essHCtx);

6. Terminate the API

// Terminate

uint errNum5 = EssbaseTypes.EssTerm(essHInst);

The C# code above is only an example on how to use the Essbase C API. Building a proper C# wrapper requires a bit more work, especially around UTF8 compatibility.
One very important aspect of using the essbase api is freeing memory. Memory leaks can sneak in and slowly kill you application.

To get the Essbase C API working the API version in the ESS_INIT_T should be set to something like 0x00070000 otherwise it will create problems later on. I do not know exactly why this is happening but some people have been know to spend days figuring this thing out.

Using the Essbase C API with C# - Initialisation

Essbase C API and .Net does not appear to be the hottest topic on the net, probably for obvious reasons, but somebody might find this useful. Working with the Essbase C API is fairly intuitive (after a while) and if imagination helps great products can come out.

Here are the steps to get the Essbase C API working in C# :

1.Install the Essbase Client and set the ESSBASE_PATH environment variable to “X:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient”

2.Alternatively copy the Essbase API to a folder and set the ESSBASE_PATH to point to that folder. This makes for an easier deployment.

3.Create a new project in VS and add the following classes:

public class EssbaseTypes

{

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssInit(ref ESS_INIT_T EssInitStruct, ref uint EssHInst);

[DllImport("essapinu.dll", CharSet = CharSet.Ansi, SetLastError = true, ExactSpelling = true)]

static internal extern uint EssTerm(uint EssHInst);

}

public delegate uint FcnPtr();

[StructLayout(LayoutKind.Sequential, Pack = 1)]

public struct ESS_INIT_T

{

   public uint Version;

   public uint UserContext;

   public ushort MaxHandles;

   public uint MaxBuffer;
   public string LocalPath;

   public string MessageFile;

   public FcnPtr AllocFunc;

   public FcnPtr ReallocFunc;

   public FcnPtr FreeFunc;

   public FcnPtr MessageFunc;

   public string HelpFile;

   public uint Ess_System;

   public uint usApiType;

}

4.Using the code:

string essApiPath = @"C:\Oracle\Middleware\EPMSystem11R1\products\Essbase\EssbaseClient\bin\";

           

// Create initialisation structure

ESS_INIT_T essInit = new ESS_INIT_T();

essInit.Version = 0x000B1200;

essInit.UserContext = 0;

essInit.MaxHandles = 0;

essInit.MaxBuffer = 0;

essInit.LocalPath = essApiPath;

essInit.MessageFile = essApiPath + "essbase.mdb";

essInit.AllocFunc = null;

essInit.ReallocFunc = null;

essInit.FreeFunc = null;

essInit.MessageFunc = null;

essInit.HelpFile = essApiPath + "essapiw.hlp";

essInit.Ess_System = 0;

essInit.usApiType = 0x0002;

// instance handle

uint essHInst = 0;

// The errNum is 0 when successful

uint errNum1 = EssbaseTypes.EssInit(ref essInit, ref essHInst);

if (errNum1 == 0)

{

// Code goes here

uint errNum2 = EssbaseTypes.EssTerm(essHInst);

}

5. The errNum should be 0 in case of a successful initialisation. Any other code indicates an error. Errors are usually related to the essbase api path and the ESSBASE_PATH variable.

The value for Version in ESS_INIT_T can be found in file essapi.h. For 11.1.2 the version is 0x000B1200.