How Does the Essbase Excel Add-in Work? (Part 3: Why Dodeca is Easier and Better)

In the first two parts of this series, I discussed the basics of the Essbase Query by Example query engine, some of it's benefits and some of it's limitations. Fortunately the Query by Example engine is exposed to developers as part of the Java API which gave us the opportunity to leverage the best of QBE within our Dodeca product but also allowed us to remove some of the limitations.

Dodeca removes or minimizes the effects of the following limitations found in the Excel Essbase add-in:

• More than one retrieval range per sheet is allowed.
• Each worksheet may retrieve data from multiple Essbase databases.
• Extraneous text can be ignored.

Dodeca accomplishes this functionality via the use of retrieval ranges. These ranges, which use reserved range names, define both the cell range that is to be retrieved and, optionally, the database connection to use for the retrieval. Further, you can have a virtually unlimited number of retrieval ranges per worksheet. By contrast, to overcome these limitations in the Essbase Excel add-in, users must manually select the retrieval range by selecting the Retrieve option from the Essbase menu. Alternatively, this process may be automated in the Essbase Excel add-in by writing complex VBA code to retrieve each range. In other words, it is easier and faster to implement multiple retrieve ranges in Dodeca.

The first step is to create the range name. This is accomplished in the Excel template using the Define Names dialog:

Dodeca uses the range name format Ess.Retrieve.Range.x where x is a number. When the administrator uses the template in a Dodeca view, they choose how Dodeca will interpret the worksheet to determine the retrieval range. In this case, the RetrievePolicy needs to be set to RetrieveRanges.

At runtime, Dodeca automatically cycles through the range names that are defined and retrieves each one separately. As I posted in an earlier blog post, one of our customers is using this functionality to retrieve over 250 different retrieve ranges in a single workbook.

Similarly, if the administrator wants to associate that retrieve range with a specific database connection, they would use a similar range name. In Dodeca, Essbase connections are defined as an object in one of the built-in Dodeca metadata editors. Here is how a typical Essbase connection may be look in the metadata editor:

The connection ID, as circled above, is used in the range name to indicate the connection to use for the corresponding retrieval range:



The connection range name is optional in Dodeca. If a range name is not present, the Excel template will be connected to the ConnectionID defined at for the view level:



In this series, I have examined how the Essbase Query by Example concept works and have talked about its benefits, its pitfalls and some solutions. I hope you learned some information that will help you get the most out of Essbase.