Many of the Hyperion Planning and Essbase users still prefer to use the Essbase Add-In in conjunction with, or in place of, SmartView. As you probably already know, deploying the Essbase Add-In in version 11 has challenges. There is over 2GB of data that is required and the installtool.cmd file is not a simple installation that most users can administer without help. Because of the size, deploying it in a distributed package is extremely challenging. There are some instructions on various BLOGs that explain a way to deploy it manually, with edits to the registry. Any time I work with a client and mention editing the registry outside an automated install, this option is quickly disregarded.

In version 11.1.2, Oracle|Hyperion has added a self contained executable for the Essbase Add-In! The download is located in the Hyperion Essbase’s download page.

Regardless of whether the perception of using SmartView for large queries is good or bad, the reality is that finance and accounting users require the ability to pull large volumes of information out of Essbase.  The only limit that I am aware of in the days of the Excel Add-In was the maximum number of rows Excel would allow (assuming the Essbase application cache settings were high enough to support it).  With SmartView, there is a limit.  The limit is controllable very easily, however.  The error that users may question an administrator follows.

“Cannot perform cube view operation. OLAP error (1020011): Maximum number of rows [5000] exceeded.”

To increase the maximum number of rows a user can retrieve, or submit, edit the service.olap.dataQuery.grid.maxRows property in the essbase.properties file.  The default is 5000. While editing this property, it may be benefitial to evaluate the size if the columns (.olap.dataQuery.grid.maxColumns), which is set to 255 by default.

Once this is updated, restart the Hyperion services.

The location of the essbase.properties file is dependent on the version of Essbase installed.  Start by going to the server with APS installed.

Location for version 9.3
%HYPERION_HOME%\AnalyticProviderServices\bin directory

Location for version 11
%HYPERION_HOME%\products\Essbase\aps\bin\

Reporting solutions often require companies to filter out a top range of Key Performance Indicators; for example, the top 10 expenses related to marketing. Hyperion Financial Reporting makes this type of reporting easy for developers by providing the “Top” properties checkbox. The difficulty arises when a company requires a solution to display the bottom 10 – those 10 expenses that account for a majority of marketing related expense. Hyperion Financial Reporting has nothing built to provide this type of information.

As you might expect – knowing your smaller expenses is important but knowing the largest; those where you can improve margin, is vital. A solution to display the bottom 10 is detailed below; this solution displays the 10 largest negative values vs. displaying the 10 largest positive values.

The high-level solution includes the following functionality:
a.    Inserting a “Rank” column.
b.    Sorting on the “Rank” column.
c.    Adding conditional suppression for bottom 10.

Step 1:

Create a report grid with a formula column as the first column (Column A below).

Step 2:

Insert the “Rank” function on the Formula Column. Be sure to choose the “Ascending” property. Adding “Rank” will order the rows from High-to-Low based on the data returned. The example below provides ranking off of Column ‘A’. The ranking is used on Step 4 when adding conditional suppression.

Step 3:

Apply row “Sort” to the grid. You find the “Sort” property by placing focus on the entire grid (left clicking the upper left-most cell). Choose to apply sorting to the “Rows”, Sort by “Column A”, and sort in “Ascending” order. Sorting will determine the order in which the data is displayed, Ascending or Descending. The Sorting is used on Step 4 when adding conditional suppression.

Step 4:

Add Conditional Suppression to the row(s). This logic will determine which data rows are ultimately displayed to the user. To add conditional suppression, highlight the row and click “Advanced Options”. Because the requirement is to show the bottom 10, suppression should hide any row with a “Rank” value greater than 10 (You will also want to suppress rows where “No Data” is returned).

When this report is run, only the bottom 10 will be displayed to the user… those marketing expenses with the largest negative values. The solution above will essentially do what a “Bottom” checkbox would have provided had Hyperion programmed this functionality into the application.