How Does the Essbase Excel Add-in Work? (Part 2)

In part 1 of this series, I talked about the normal case of how Essbase Query by Example ("QBE") works. Determining the members represented at data intersections, or datapoints, in Essbase is generally very easy to understand. That being said, there are some potentially confusing layouts that bear some discussion.

The data intersection depicted in the part 1 of this series shows a block header layout that clearly represents all dimensions in the row and column of the data intersection itself. Sometimes, however, the intersection is not so clear. Those cases are best illustrated in the following examples.



What members are represented at this datapoint? Looking left from the datapoint, it is clear that South is the member that represents the Market dimension. Looking up from the datapoint, it is also clear that Qtr4 represents the Years dimension and Budget represents the Scenario dimension. However, the row and column have no members representing the Measures and Product dimensions. Page Fields represent the entire retrieval and thus Measures and Product represent the members from their dimensions regardless of their position on the grid.

Now, consider this layout and try to determine the members for the indicated datapoint.



In this case, the members West and Variance are the only two members in the row and column of the datapoint, so those two members are easy. Further, Sales is the only member from the Measures dimension and thus is a page field; it is automatically included. But what about the members from the Product and Year dimensions? The rules for those dimensions get a little more involved and are made more difficult. One reason it is so difficult is the rules for determining datapoint members for row and column fields are a bit inconsistent. The rules are:

• If the dimension is in row orientation, follow the row left to the column containing the dimension. If that cell does not contain a member, then look up through the rows in that column to find the first member. In the example above, look left to column ‘A’, then up to row ‘4’. That cell contains the member name Colas and thus Colas is the member for the Market dimension. Members in row orientation are very consistent and easy to determine; the same is not true for the columns.
  
• If the dimension is in column orientation, follow the column up to the row containing the dimension. If that cell does not contain a member, then first look one cell to the left in that row for a member. If that cell does not contain a member, look one cell to the right from the original column. If that cell does not contain a member, look two cells to the left from the original column. Continue this pattern until you find a member. In the example above, the cell F2 contains the member name Feb which represents the Years dimension in the datapoint.

This inconsistency can easily cause confusion with its ambiguous layout. Fortunately, there is an easy way to resolve it via the use of block headers. Block headers explicitly list the members for each intersection in each row and column. Below is the confusing grid shown above after being converted to use block headers. Note the correct variance is now retrieved in the indicated datapoint.

There is anecdotal evidence that an Essbase retrieve using block headers performs slightly slower than not using block headers (i.e. pyramid headers). However, the risk of a user relying on an incorrect interpretation of the numbers is a huge price to pay for a slight performance improvement.

In part 3 of this series, I will talk about how our Dodeca product leverages the best part of the Essbase Query by Example paradigm but eliminates many of the limitations of the classic Excel add-in.