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
 |
| 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.
