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\

The ability to import font types into Hyperion Financial Reporting is a common request by many companies, typically a request resulting from corporate reporting standards. Not only is this possible, it’s a quick and easy exercise that is detailed out below.

Step 1: Locate the Font Folder.

The font type files (normally identified by a .TTF or .ttf extension) can be found in the “Fonts” folder located in your Windows directory (Likely on your ‘C’ drive). The key here is locating this folder on the server where Hyperion Financial Reporting has been installed.

Step 2: Copying& Pasting the New Font File.

This is as easy as it sounds… just copy and paste the new file into this directory.

Step 3: Creating a Report.

Opening Hyperion Financial Reporting and create/modify a report. When selecting the font type, notice the new Font Type that was just added in Step 2 above. Note that it’s best to close the Financial Reporting client before importing the new Font file onto the server; this will insure that the client will recognize the new file.

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.

Step 4: Adding ‘Advanced Suppression’ to each of the Year & Period columns.

Step 4 in the development of this report contains a majority of the logic to be setup which will allow a range of periods to be displayed to users. The idea behind the logic in this section is to move the range of periods displayed to users based on the Period selected in the User POV. The "Range Matrix" below will shed some light on what should be displayed based on what is selected.

Just as Conditional Suppression was setup for the trigger columns, Conditional Suppression will need setup for these Year/Period columns. The difference between the "Trigger" section and the "Year/Period" section resides on how columns are chosen to be suppressed. As the name suggests, the "Trigger" section added in steps 1 & 2 will drive the conditional logic, and thus the range of Periods displayed to users. The examples below display a high-level subset of the column logic.

Example 1:

• User selects "Jan" as the Period.
  • Which Periods will be displayed?
  • Sep (Prior Year)
  • Oct (Prior Year)
  • Nov (Prior Year)
  • Dec (Prior Year)
  • Jan (Current Year)
  • Which Periods will be hidden (suppressed)?
  • Feb-Dec (Current Year)

Example 2:

• User selects "Sep" as the Period.
  • Which Periods will be displayed?
  • May (Current Year)
  • Jun (Current Year)
  • Jul (Current Year)
  • Aug (Current Year)
  • Sep (Current Year)
  • Which Periods will be hidden (suppressed)?
  • Sep-Dec (Prior Year)
  • Jan-Apr (Current Year)
  • Oct-Dec (Current Year)

When adding columns to a report, each column will be tagged with an alphanumeric value that identifies the column number. Staying true to the rolling 5-month solution, columns "A" through "L" of your report identify the "Trigger" section (Jan equals "A", Feb equals "B"… Dec equals "L"). The "Year & Period" section is identified by columns "M" through "AB" of your report (Sep of Prior Year equals "M", Oct of prior year equals "N"… Dec of current year equals "AB"). When setting up the "Year & Period" Conditional Suppression, it is imperative that you know and understand which Periods correlate to which column numbers.

"Trigger" Section:

"Year & Period" Section:

The Conditional Suppression will need added to all "Year & Period" section columns (columns "M" through "AB" in the above images). Column "M" (which correlates to "Sep" of prior year) will need displayed to the user ONLY when the user selects "Jan" for the current POV of the Period dimension. By selecting "Jan", the user is requesting to see data for Sep-Dec of the Prior Year, and Jan of the current year (as shown above in the "Range Matrix"). A subset of the Hyperion Reporting logic is shown in the image below. Similar logic is required for the remaining columns of the "Year & Period" section (columns "N" through "AB") with the only difference being the suppressed "Trigger" columns selected.

Hyperion Reporting – Conditional Suppression Logic:

Year & Period Suppression Logic:

As stated before, the "Trigger" section of the report drives what is ultimately displayed to the user, and this is based on what the user selects in the User POV for Period. If a report requirement exists for something other than a 5-month rolling view, the number of "Year & Period" section columns would need adjusted, as would the Conditional Suppression logic, but the "Trigger" section will not need adjusted. The overall idea of how to implement this solution remains intact. Please feel free to contact me directly with any questions on implementing a solution such as this, I’m happy to assist when possible.

Step 3:  Adding Year and Period columns.

The columns added here will be those which are displayed to the users. The Trigger section added in steps 1 and 2 above will determine which range of Periods will ultimately be displayed to the users. The key to adding columns in this section of the report is to include ALL possible Periods that could be displayed to the end-user. The Trigger section of the report will essentially move & display a subset range of Periods. For example, If a user selected “Jan” as their current Period, The report will need to display Sep-Dec of the prior year.

Keys:

• These columns will be those displayed to the end-users.
• These columns MUST be Data columns.
• A rolling 5-month report will display Sep, Oct, Nov, Dec and Jan IF Jan is selected by the end-user.
• Either ‘Substitution Variables’ or the ‘RelativeMember’ function can be utilized for the Year dimension (ie. CurrentYear, CurrentYear-1, etc.).
• The Period members can be “hard-coded” into the report (don’t use the POV Period option here).