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

A lot of people use the Essbase Excel add-in but don't really know all of the rules of how it detects Essbase members on the worksheet using the standard Essbase Query By Example (”QBE”) paradigm. We have a nice summary explanation of how it works in the Dodeca Administrators Guide and I thought I would share it with everyone. There is enough in our summary that I am going to split the post into a two part series.

Query By Example is a very powerful concept as it makes it very easy for business users to layout an Excel worksheet to get data into the format they need. QBE templates are created by typing the Essbase member names into the worksheet in a specific layout. QBE does follow some rules the users will need to be familiar with before they start creating templates. Here are some basic Essbase retrieval rules and terminology used in this guide:

• One retrieval range per sheet is allowed.
  
• Each worksheet may only be connected to one Essbase database at a time.
  
• All dimensions in the database must be represented or any missing dimensions will be inserted automatically.
  
• At least one Essbase dimension must be represented in row orientation.
• No extraneous text is allowed that may be confused with Essbase member name.
• Numeric member names must be preceded with a single quote to make them look like text rather than numbers.

Note: Information about removing some of these limitations with Dodeca is available later in this section.

Consider the following simple Essbase retrieval in Excel from the Sample Basic database which contains five dimensions. In this example, 3 dimensions are oriented in page orientation, 1 dimension is oriented in column orientation and 1 dimension is oriented in row orientation. Collectively, dimensions in an orientation are often referred to as fields as in Page Fields, Column Fields and Row Fields.

When the Essbase engine parses the grid, it looks at the cells selected in the worksheet and, if no cells are selected, looks at the entire used range of the worksheet. When it does the parsing, it looks for members for each dimension in the connected database and follows these rules:

• At least one dimension must be represented in row orientation. The reason is that Essbase returns data at the data intersections represented by a member in each dimension. If no dimension is represented in Row orientation, then it is impossible to have an intersection represented.
  
  
  
  The cell intersection depicted here is the intersection of the West, Qtr1, Scenario, Product and Measures members from the Market, Year, Scenario, Product and Measures dimensions, respectively.
• Multiple members in any single column must be from the same dimension unless the Use Both Members and Alias option is selected. In that case, the multiple members from a group of two adjacent columns must be from the same dimension. Members in this orientation are referred to as Row Fields as they form the row headers for the data retrieval.
• Multiple members in any single row must be from the same dimension if there is more than one member from that dimension represented on the grid. These members are referred to as Column Fields as they form the column headers for the data retrieval.
• Dimensions that contain only a single member may appear in any cell above and to the left of the first data cell as long as they don’t appear in any row or column used by the Row
  Fields or Column Fields. These dimensions effectively act as filters for the data retrieval and are referred to as ‘Page Fields’.
• Extraneous text may appear within the retrieval as long as it doesn’t resemble an Essbase member name. If Essbase confuses the text with a member name, the parsing algorithm used in the QBE query engine will get confused and will return a Member Out of Place error. Similarly, if a numeric member name is not entered as text by pre-pending the member name with a single quote, the parsing engine will get confused and return a Data item found before member error.

Determining the members represented at data intersections, or datapoints, in Essbase are generally very easy to understand. That being said, there are some potentially confusing layouts that bear some discussion. I will pick up with that point in the second part of this post.