So I have been told I blog with the frequency of a solar eclipse, and with respect to this two-part series, several interested parties via e-mal have indicated I have done the equivalent of a network TV season ending episode – “just wait the whole summer folks for the answer to these questions”. Thanks to all of you who “gently” pinged me to remind me to complete the blog.
So for example, take the following view one might see at the bottom level (level zero, or base level members) of an Essbase cube:
Notice the accrual for Ranzal services that is visible, and multiple transactions are available at the weekly level. A further explosion into AP would reveal the identity of the vendors, and other relevant info looking something like this:
So as can be seen from the above example, relevant data from multiple tables is required from the drill through view. With the accruals that are made, there is relevant information in the journal descriptions; for the AP system there is vendor detail that is valid. The solution to this drill through is the creation of a view (called VendorFact in the above schematic) that is a union of the two data sources, filtering on data source. The query might look something like this:
CREATE VIEW ‘VendorFact’ AS
Company, Dept, FY, Per, Acct, JournalID, JournalDesc as Desc, PostingDate as MyDate, ‘00000’ as Vendor_ID, ‘00000 as Doc_Nbr, ‘0’ Line_Nbr, PostedBaseAmt as Amt
Source <> ‘AP’
Company, Dept, Year(Trans_Date) as FY, Month(Trans_Date) as Per, Acct, ‘AP’ as Source, ‘NONE’ as JournalID, LineDesc as Desc, TransDate as MyDate, Vendor_ID, Doc_Nbr, Line_Nbr, Trans_Amt
The following view might result:
With the required view in place, a drill through query could then be created in Hyperion Web Analysis, using the relevant GL chart fields (Company, Department, Fiscal Year, Fiscal_Period, and Account) as filters in the selection query as follows:
SELECT * from VendorFact
Where Company = [CompanyToken] and Dept = [DeptToken] and Account = [AccountToken] and FY = [FYToken] and Per = [Month]
Once this drill through query had been created, a drill-link could be added as a menu item on the Summary Expense analyzer view that was directly querying the Essbase database. When the user had drilled down to the bottom of the cube, the next action would open up the new Web Analysis report, pass the relevant parameters to the query, and display a relational grid like the one above.
To give you a flavor of what this might look like, check out the example below from another type of application…..
Step 1 – Create the Summary “Essbase” View, and establish Drill Links to target detail report
- The report is setup so that all dimensions except for currency must be selected at the lowest level. This is done as a sample and to put the constraint on the report so that it returns only expected rows.
- To drill through, first select data down to level 0 for all dimensions. Next double click the actual data cell you want to drill into.
- The drill through report will be initialized with the parameters passed. If dimensions are not at lowest level, a “no data available” message will appear on the drill through.
- Drill Link Options Configuration
This is the configuration to point the report to the Drill Through Report. It is accessed by opening the report and right clicking in a cell selecting “Drill” and “Drill Link Options…”
- The drill link options page pops up. You specify the name of the report you want to open, and what to pass as the “WHERE” clause to the next report.
Step 2 – Create the detailed Web Analysis view accessing the relational data
- The drill through report is accessed by the FinPlan report based on the dimensionally that is picked. There’s a 10,000 row limit on drill-throughs, as any set of detailed transactions that can be drilled to should never exceed even a few hundred records. If this setting was not enabled, opening the drill through report directly would fail as too many records would be loaded.
- SQL Query Configuration
This screen is accessed by right clicking on the Drill Through Report Grid and selecting “Edit Query…”
- The JDBC-ODBC bridge driver is used to connect back to the hyperion data mart housing the drill through data. Drill through users will leverage a shared drill through connection..The row limit and fetch size are also configured here.
- Click the “Query Builder” button in the screen above and another dialog box opens. This is where the drill through mappings are configured. Click Mappings and notice that all dimensions are configured to their drill through counterparts in the vw_GL_Transactions_DT view.
- When executing the drill through, an output like this can be generated.
- As a final note, in constructing the view, particular care must be paid to the structure of the member names in the Essbase outline. For, example, core chart fields such as Departments and Accounts are often prefixed or suffixed to make them unique (e.g. instead of “000610” one might have “Dept_000610”). In constructing the drill through view, the member names must match those exactly as defined in Essbase, in order for the filtering aspects of the queries to execute properly.
- Other Drill Through Choices
So one problem with Essbase is that it has multiple ways to do everything. For example, how many ways are there to load data? (Answer: For block storage cubes, flat file via rules file; sql interface with rules file; EIS data load via ODB connection; lock and send via Excel; lock and send via smartview; planning web forms; FDM adapter; DIM adapter; ODI apdater; HAL adapter; I am sure I missed a few). There are 2 command line automation interfaces for goodness sake (MaxL and Esscmd). Transaction drill through is no different. Approach wise, the one consistent thought is this: never drill back to the live transaction tables. Ok, so never is a pretty strong word, but let’s just say as a general rule of thumb, doing so is not a good idea for the following reasons: •
- Your Essbase cube is a snapshot of data from a load. Querying a live database for open periods implies the sum of your details may not equal your account balances
- Performance – transaction databases are optimized for write based operations, and highly normalized. As we have defined in the above example, creating denormalized views for specific queries will make your DBAs and end users happier.
Now, technology gets better (hardware and software), and with some of the new items coming from Oracle, out of the box, they are trying to get you to a place where EPM apps are more real time, so the rules above won’t always apply, but for now, it’s a good starting point.
In general when deciding on drill through options, you want to ask the following questions:
- What products do I need to drill through from?
o Web Analysis
o Interactive Reporting
o Financial Reports o Excel Add-In
o Smart View
- What is the type of content I want to drill through two?
o Transactions in a relational database
o Some other content served up via a URL (for example, very clever way to serve up document images)
- What level of the source cube do I need to drill through from?
o Either level zero, or the top of a dimension
o Every level of a non-ragged hierarchy
o Custom groups and ragged alternate rollups
- What release of the software are you using (Fusion vs. 9 vs 7.x)
For example, if you want to enable drill through from every product, and need to support ragged or standard hierarchies, you are looking at an Essbase Integration Services or Essbase Studio solution. Ranzal has some creative techniques for enabling drill through via EIS on cubes not built with EIS (thanks to our handy outline extractor), which makes it useful for Planning, but once your concern over “newness” has abetted, Essbase Studio allows you to enable drill through the right way – build your cube first, then add the drill through later on. Not the other way around.
On the other hand, if you are looking at something quick and dirty, a Web Analysis or IR view in the short term can get you where you need to be.
Either way, Oracle, particularly with its continued emphasis on OBIEE/Essbase integration, is looking to provide more out of the box options to enable drill through. For example, if you haven’t seen it, check out the Ranzal webinar on Financial Data Management; the latest releases are geared to provide drill back from Planning to FDM, and then ultimately if you load from Oracle Financials, drill all the way back to the ERP.
This blog was meant to give you an idea of options and approaches for enabling drill through on your Hyperion application. While its great to have options, remember the golden rule – when you make your choice, know WHY you chose that path. Design is basically the optimization of an approach against one set of requirements over another.
Until next time, good luck.
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.