Table of Contents
Summary
The Problem
The original detail data that is consolidated and loaded to an Essbase cube is not directly available to users querying the cube with tools such as Smart View™, Hyperion Planning, and Financial Reporting – but there is a way to get to it. Drill-through is the process in which the original detail data behind each cell in Essbase is queried and viewed. The original detail data for a cell in Essbase typically comes from a relational data source.
The Solution
Drillbridge is an innovative technology for implementing drill-through that doesn’t require any changes to existing automation or administration. It is easy to deploy and utilizes data sources as they already exist, with few, if any changes. Organizations with an Essbase cube and a relational database with data already have everything they need in order to implement drill-through.
Request a Drillbridge product demo
The Design of Drillbridge
Modern versions of Essbase support third-party drill-through providers. Drillbridge is one such provider. Drillbridge is a drill-through provider that maps from dimensional intersections to data stored in any relational database.
In this paradigm, the existing cube and its management processes stays the same, including the automation, load rules, and calc scripts. In fact, only one thing will change and that is that some cells will be marked as drillable so that tools like Smart View can drill on them.
When a drillable cell is drilled, Drillbridge will process the current point of view (POV), construct a SQL query (written in the backend data store language, such as Oracle, Microsoft SQL Server, DB2 and more), and display the results in a web browser.
The trick, then, is this: How do we map from the members in the database to data in a relational table? Quite often, the members in the cube represent a transformation versus the data in the original relational table.
From Dimension Members to Relational Data
It’s common for an Essbase cube member name to be slightly different than how it looks in the database. The data in the cube represents what has been loaded in with a load rule, which is designed to split, prefix, suffix, and otherwise change the data in order to map it in to the cube. Common transformation examples include mapping a month name like January to a numerical equivalent (01 or 1), stripping a prefix from an account number, and stripping a suffix from a business unit.
In looking at these transformations, several common operations become apparent:
- Remove prefix: Removing a prefix would be necessary in order to transform the member name A_1234 into 1234.
- Remove suffix: Removing a suffix is necessary to go from a member name of 201BU to a column value of 201.
- Transform text: A transform is needed to go from FY21 to 2021 in the database. This is achieved with a remove prefix combined with adding a prefix.
- Convert month to numeric value: In this case, January is converted to “01”, February would be converted to 02, and so on.
- No change in data: The data is the same in the relational field as well as the outline (such as Department being 001 in both).
- No mapping applicable: The cube contains a Scenario dimension with members such as Actual and Budget, but the source data is only actuals (budget data is either entered into the cube itself or is loaded from another source).
Drill from Upper Level Members, Excel Output, and Enhanced Security
Drillbridge offers robust and user-friendly built-in methods capable of efficiently managing a wide range of transformations and mappings essential for associating member names with their counterparts in a relational database. The system’s potent mapping capabilities extend to upper-level members, allowing users, in the context of hyperion reporting, to drill into a member such as Quarter 1 to retrieve data for January, February, and March. Drillbridge dynamically loads the descendants of the drilled member, constructing a query using these member names while seamlessly applying any necessary transformations.
Drillbridge provides a number of other features that enhance the drill-through user and administrative experiences. On the user side, detailed reports can be easily downloaded as Excel sheets with the click of a button. On the administrative side, enhanced view security prevents users from displaying data they do not have access to through Essbase security.
Conclusion
Organizations frequently develop Essbase functionality based on consolidating their existing transactional data somehow. That original detailed data is incredibly useful for a variety of tasks, but all too often is impossible or cost prohibitive to easily get to – until now. Drillbridge’s flexible architecture allows for quickly augmenting solutions with little to no redevelopment – providing users convenient access to their detailed data.
With Drillbridge, organizations can finally deliver the benefits of drill-through using a powerful solution that unlocks the value of their transactional detail and enhances their existing Essbase cubes, all without changing the way they build and manage their existing processes and environment.
About the Author
He is passionate about performance tuning, optimization, programming solutions for the EPM community, and sharing his knowledge with others.
Jason is the creator of the innovative Drillbridge product for easily implementing drill-through to relational databases from Oracle EPM Cloud, Smart View, Planning, and Financial Reporting.
