Adding Drill Through to Your Hyperion Implementation
This is part one of a two part blog where I will discuss a general overview of how within the framework of a Hyperion Planning or Hyperion Essbase implementation, organizations may configure Hyperion to provide drill through to transaction details. In this first blog, I will discuss implementation background, and part two will provide information related to implementation approaches.
The final output of most Hyperion Planning implementations is the generation of a Financial Plan. While various “sub-ledger” plan types may exist to support the generation of this plan (Revenues by Customer and Product; Salary Expense By Employee; Capital Expenditures by Asset Class), the fundamental level of granularity is usually equivalent to the various chart fields in the organization’s general ledger, summarized on a MTD basis. For certain ERPs, this corresponds to the GL Account Balances table, or an aggregate of the transaction details. Depending on your ERP package, this can be one of the following:
- Lawson – gl_trans
- Oracle E-Business – gl_je_lines
- Peoplesoft – PS_Ledger
- JD Edwards – F0902
- SAP R/3 –
o Profit Center Accounting – GLPCT
o Cost Center Accounting - COSP
- Microsoft Great Plains - GL11110
For financial implementations, you are usually looking at the following standard chart fields:
- Fiscal Period
- Fiscal Year
- Legal Entity or Company
- Management Chart Fields
o Department or Cost Center
o Product or Profit Center
o Geography or Location
Inherent within the Hyperion environment is an ability to drill to a lower level of detail, usually to answer a question. This type of functionality manifests itself in several forms, and the following terminology is used:
Drill Down: Drilling from one level to another of more detail in one dimension of a cube (for example, expanding quarters into months)
Drill Up: Drill from one level to another of less detail in one dimension of a cube (for example, collapsing quarters into year)
Drill Thru: Drilling out of an Essbase cube into a RDBMS source system (for example, expanding months into days)
Drill Across: Drilling from one Essbase cube into another, normally to explode dimensionality that exists there (for example, on a MTD basis, while looking at Revenue in the Financial Cube, drilling across to expose Customer Detail in a Customer Cube)
The actual drill process from a user interface could be as simple as expanding the rows on the user’s grid (as in a drill down), to launching a new grid which creates a new point of view in a drill-thru and drill-across operation.
Much like the relationship between Planning sub-types and the primary financial cube, there is a relationship between the General ledger, and various sub-ledgers that feed it. Primarily, detailed information is summarized prior to being posted. For example, individual employee payroll data may get aggregated by department and posted as one line into the ledger. Or Ship-To Customer and individual product SKUs may get aggregated into Channels and Profit Centers prior to be posted. In any event, when talking about exposing additional granularity in Financial cubes, it is critical to define what types of details the user wants to see, as there may be multiple steps in that process. Because of this, it is very rare that a simple drill through into a general ledger will yield all of the results a user wants to see, since many of the results, while at least expanded from a monthly to a daily basis, will still be summarized across the other dimensions a user may wish to see (such as Vendor). This leads to the design of two new components:
- Delivery of additional operational cubes, that expose details (such as Employee, Customer, and Vendor) for Drill-Across operations
- Development of custom-data marts that create hybrid views integrating different levels of granularity of multiple data sources.
The overall schematic might look something like the workflow below:
In the example above, a new vendor “view” or table has been created to support the drill through view requested by the user, in a top down fashion. Notice that the table actually pulls from two sources:
- GL Trans – the general ledger transaction table where individual journals are posted
- The Accounts payable table, where individual transactions exist
The need for both tables occurs because accruals may be made to the accounts in question, and those entries are generally made directly in the GL Transaction table. So for the detailed drill through table to tie to the financials, it must contain both elements. However, since the AP element exists in both (detailed in AP, summarized in GL), special care must be made to avoid double-counting the entries that come back. Since many GLs contain a field that identifies the source system (AP, AR, etc), or has a journal mask to accomplish the same, this is usually readily available.
In my next blog (part 2), I will discuss practical techniques for implementing the above.
Mike Killeen - Ranzal and Associates
For more than eleven years, Mike Killeen has scoped, designed, and implemented Planning & Essbase applications for Ranzal & Associates, an award winning, preferred consulting partner of Oracle Hyperion. Currently serving as practice director, Mike provides guidance to Hyperion product management, Ranzal customers, and internal consulting resources alike on best practices related to the implementation of Hyperion System 9. Mike is a certified Hyperion Professional, with certifications in Essbase, Planning, and HFM. He graduated cum laude from Cornell University with a degree in engineering, and received his MBA from the University of Connecticut.