I was going to do a nice in-depth post to follow up on my discussion of the relational cache outline extraction method/improvements on the Next Generation Outline Extractor, but someone already beat me to the punch. It turns out that the tool’s primary author, Tim Tow, blogged about the technique and the tables for ODTUG a couple of years back.
I’ll just add on one thing that I wanted to highlight, though: the general technique behind most relational extractions from an Essbase outline is to generate a single table, with such common columns as
CONSOLIDATION, and so on. If you think about this, it tends to imply a number of limitations that might make this technique unfeasible for you:
- The table can only hold one extraction at a time
- You can only get the member name and a certain alias table alias at a time
- The columns in the table are variable based on the attribute dimensions that may be associated with the dimension
- More than one UDA: ¯\_(ツ)_/¯
Since the relational cache extraction method was designed for robust, flexible, and quick use with Dodeca, it is structured a little differently than traditional extracts. The additional aliases, attribute associations, and UDAs are all in separate tables, allowing for arbitrary and complete extractions of such data (in tables
Additionally, each version of an outline is extracted and given a unique ID as well, meaning that you can extract an outline over and over again to the same tables without having to necessarily clear the table beforehand. This again stands in contrast to the typical extraction format that is essentially one extraction in a table at a time (although if you choose to then merge that in to a separate table with a unique ID, you can).
In any case, I keep finding myself surprised at just how many people are using the relational extraction technique as part of their automation and day to day solutions, including techniques related to automated outline change comparisons, quasi-DRM solutions, and shuffling data around the enterprise for consumption in other solutions.