Tuesday, January 13, 2009

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.

11 comments:

Chaitanya Sagar said...

The article is really very interesting and it give a complete knowledge about Essbase Excel, its benefits as well as its pitfalls Can you please show me how to write code that will initiate the Retreive Data function automatically.

Tim Tow said...

There are many, many examples on the Network54 board. If you want to make it really easy, look at our Dodeca product which completely automates the process.

Tim

Anonymous said...

Is Essbase 7.x Add-In compatible with Excel 7.0?

Tim Tow said...

Essbase 7x is not compatible with Excel 7 which was also known as Excel 95. Essbase 7.0, according to the install guide esb_install.pdf, page 27, supports Excel 97, Excel 2000, and Excel XP.

Tim

Tim Tow said...

Essbase 7x is not compatible with Excel 7 which was also known as Excel 95. Essbase 7.0, according to the install guide esb_install.pdf, page 27, supports Excel 97, Excel 2000, and Excel XP.

Tim

Amrit said...

Is essbase Version 7x Compatible with Windows 7 ??

Tim Tow said...

I haven't tried Essbase 7 on Windows 7.. Sorry!

Tim

Anonymous said...

Does Dodeca connect to other OLAP products besides Essbase such as Microsoft OLAP?

Tim Tow said...

Dodeca is targeted specifically at Essbase. That being said, we wrote a framework first before writing the product, so we could add MS OLAP if we see a significant market.

vc reddy said...

hi the new features of dodeca v.5 are amazing...
where we can download trail version if so can u post the link plez
thanq

Tim Tow said...

Qualified companies can obtain an evaluation copy by contacting us via email at sales@appliedolap.com.

Tim