Hyperion Essbase has this cool tool called Drill-Through Reports that allows you to drill into leaf level data in the cube. The tool comes standard with Version 9 and above. Users love drill-through because it is a natural extension of the process of analysis. After a balance has been identified, drill-through allows the user to see the transactions that comprise it.

However, sometimes you can’t use the built-in feature. For example, when data is fed into Essbase via a flat file, a data warehouse Drill-Through can only drill back into the source data. In the above example, Drill-Through will go into the source and simply load the transaction that makes up the balance, resulting in the same information. Another example is if data undergoes several transformations before it is loaded into the Essbase cube.

There is another way: develop a completely customized tool that can recognize the data coordinates and launch an ODBC call into the source database to retrieve the transactions. I have developed such a tool. It takes some time to build, but when it’s done, it’s great!

The idea is simple. When users are in ad-hoc analysis mode in Excel, they can:

1. Use the EssVGetDataPoint API function to identify the coordinates for a given data point.

2. Use the EssVGetMemberInfo to API function to get information about the data point.

3. Run validations to make sure the data point is drillable. These validations will be specific to each cube and data type. For example, make sure the element is a leaf level element.

4. Construct a SQL query using the coordinates collected in Step 1.

5. Open a temporary ODBC connection with the source transaction database.

6. Get the transaction data and paste it into a new sheet in Excel.

These steps are captured in a sequence of VBA calls and packaged in a friendly user interface (I use the cell context menu—see figure below) for easy access.

Well, it all sounds easy enough, but there are four things that need to be done before we even start dealing with the data and the metadata:

1. Check to see if Essbase is installed. If not, it won’t show the Drill menu.

2. Check to see if the user has an active connection to Essbase. If not, same as above.

3. Check to see if the user is in an ad-hoc analysis grid.

4. Check to see if the selected cell is a valid data cell.

To get things moving along, you must customize the process to test for specific conditions in the metadata. Depending on the configuration of the Essbase application, these conditions can include identifying cubes, tables, members, aliases, and even calculation rules. The nice thing is that the API provides the tools to deal with almost any situation. The wide variety of functions available through the Essbase API opens up endless possibilities for customization. The only limitation is the developer’s imagination.

In one environment, I created a solution that had to distinguish between two separate cubes. The cube structures were different so the solution had to execute different metadata tests and ODBC calls. The nice thing about this specific solution was that the two cubes were related. Drill-Through from one cube resulted in detailed balances from the other cube. After drilling through from the first cube, the user could continue to drill through from the second cube to get transaction level detail data from the source.

Of course, you can’t use this approach in every case. In the above example, the mapping between the Essbase data and the source data was very similar. This meant a relatively simple SQL statement and fairly straightforward validation tests. In other instances, the mapping may be extremely complex. In these instances, using this approach may be impractical.

Advantages of the Essbase tool:

• Cross Platform (Essbase, Planning, HFM)

• Integrated security

• Built-in functionality

Drawbacks of the Essbase tool:

• Depends on ETL process

• Requires lots of customization

• "One Size Fits All" functionality

Advantages of the custom VBA/API approach:

• Completely customizable

• Independent of ETL process

• Can be adapted for other OLAP platforms and any data source

Disadvantages of the custom VBA/API approach:

• Completely customizable

• Not native to Essbase

• Only works in Excel

When is it good to use the custom VBA/API approach?

• Data source does not have transaction level data

• Simple mapping between data source and Essbase

• Multiple hops to get to transaction level data

Anyone out there trying this? I’d love to hear your thoughts!