Relational Functionality of Dodeca 5.0

Dodeca 5.0 shipped about a month ago, so I will expand on some of the new features in a few blog posts. I will start with the most expansive new capability which is the comprehensive new relational functionality.

Back in the old days with Essbase, relational technology was almost considered a dirty word. In fact, the Arbor product manager who I worked for on my first Essbase project, which was demoware for Arbor, told me I couldn't use a relational database on the project and, in fact, never to mention the word relational in their office! Of course, they were competing heavily against relational systems at the time so I can understand his position. Still, he didn't have a good answer for me when I asked where he would recommend storing addresses in Essbase. Of course, at the time it was not uncommon to see dummy Essbase outlines with this type of information but it was probably one of the worst cases of 'when all you have is a hammer, everything looks like a nail' that I have ever seen. These days, with Oracle in the picture, there is much more opportunity to use Essbase for what it does best, storing and aggregating dimensional data, and to use relational technology for what it does best which is storing and retrieving transactional data for running businesses.

As the technology has evolved over the years, there have been a number of ways to bring together the power of Essbase with the details stored in relational databases. The first major effort to accomplish this task was Essbase Integration Server ("EIS"). By my rough estimate, EIS was used by less than 10% of Essbase customers to provide drill-through. Other Hyperion/Oracle products such as Financial Data Quality Management ("FDQM"), Essbase Studio and the new ERPi provide drill-through capability as well but have some limitation, version requirements or require a number of Oracle products, including Oracle eBusiness Suite in the case of ERPi, to work. While I am thrilled to see Oracle innovate in these ways, my thought is that is should be easier, faster and less expensive to provide drill-through to customers. This is what led us to invest so much time in relational technology for Dodeca.

The idea for drill-through in Dodeca is that you can target any relational database directly from any Essbase database. With this design goal in mind, we set out to make it reality and what we ended up with exceeds even my expectations. Some of the features we implemented include:

• One or more queries on a worksheet
• Automatic grouping based on column values or expressions
• Use any Excel function in subtotal header/footer blocks
• Write back data to the relational database (optional)
• Relational query ranges on the same sheet as Essbase data ranges
• Cascade based on relational values

Here is a screenshot of a relational view that shows a view with 2 different worksheets containing the results of 2 different queries. The first tab has data grouped by product and the second tab is grouped by salesperson. The grouping automatically included the Excel grouping controls which were configured to be collapsed; I expanded open the 'Classic Vest, L' group to display the details. The selected subtotal cell is an Excel SUM() function that was placed in the template by the view developer and whose range was automatically expanded when the data rows were inserted into the view. The performance, though, is the really amazing thing. This view, which runs a query for each sheet that together return over 120,000 rows, generated and displayed in 25 seconds. When it displays in Dodeca, the data and formatting is in the native Excel xls or xlsx format, so 'exporting' to Excel means that Dodeca saves the Excel file it is displaying as a temp file, then opens it in Excel.

Dodeca Relational View (click to enlarge)

Though this screenshot does not show an Essbase drill-through, the Intelligent Navigation feature of Dodeca makes drill-through almost trivial and I will show an example in an upcoming blog post. I also plan a post or two to talk about the technical aspects of how the relational functionality works.