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.

For those of you who still enjoy the flexibility of the Hyperion Excel add-in and have upgraded to Microsoft Office 2007, you are well aware that Excel no longer has toolbars or menus.  SmartView integrates with the ribbon methodology well, but the Hyperion Essbase Excel add-in doesn’t.  What used to be the Hyperion menu is stuck in the add-ins ribbon as a drop down menu.  It still functions the same, but users loose the quick access to functions that the ribbon methodology adds.

In2Hyperion developed a ribbon add-in that can be used in conjunction with the Excel add-in.  Nearly all the functionality exists and makes the options easier to use and are embedded in their own ribbon!  This still requires the Hyperion Essbase add-in to be installed.  It simply uses the existing functions and associates them with a ribbon button.

We have also added a few additional functions that make common tasks a little quicker to perform.

  • Drill To Bottom:  Use this option to drill down to level 0 of the selected member.
  • Drill To All:  Use this option to drill down to all descendants of the selected member. 
  • Restore Defaults:  Use this option to set all the global settings back to the default settings when installed.
  • Excel Mouse:  Use this option to set the mouse functionality to the Excel options.  This disables the right click and double click Hyperion Essbase functionality.
  • Hyperion Essbase Mouse:  Use this option to set the mouse functional so the double click and right click functions interact with Hyperion Essbase.
  • Lock And Send:  Use this option if you do not want to select lock, followed by selecting send.

To install the add-in, unzip In2Hyperion Essbase Ribbon.zip to any location and follow these steps. 

  1. Click on the office button in the top left corner of Microsoft Excel. 
  2. Click the Excel Options button located in the bottom right area of the window. 
  3. Select the Add-Ins menu.
  4. Select Excel Add-Ins in the Manage drop down, and click Go. 
  5. Click the browse button, navigate to the location where you unzipped the downloaded file, and click the OK button.

 

 

 


Whether in a finance organization or a technical role, most of us have had the need to create sample data to use to test Hyperion systems.  In2Hyperion is sharing a tool to make this process more efficient.  By defining a numeric range, the number of columns, and the number of rows, an excel spreadsheet will be generated with the appropriate random data.

This tool can be access at http://www.in2hyperion.com/Tools/RandomNumbers.aspx.

Many people use Custom Lists in Excel – sometimes without even knowing.  If you have ever typed January into a cell and used autofill (click the dark plus sign, and drag across other cells) to create February through December, you have used Custom Lists.

Excel has a few Custom Lists setup for users when it is installed. Select the Tools / Options menu, and display the Custom Lists tab to view them.  Users can create their own Custom Lists in this dialog box by entering a list separated by commas or importing a range of cells that already includes a list.

For Essbase users who use the Hyperion Spreadsheet Add-In or SmartView, this can become a valuable tool.  Many times Essbase users will want to display a specific list of accounts, measures, products, etc.  Rather than selecting these from the member selection, or typing them, Custom Lists can be created and used to reduce the effort.

Let’s assume a user is responsible for a subset of the existing products and those products are only sold in a few of the markets.  The user may spend a lot of time creating the market list every time they create a new retrieve.  If the user creates a Custom List, they can automate this selection process.  A Custom List might include the following members.

Columbus,Cincinnati,Los Angeles,Tempe,Dallas,Austin,Seattle,Denver,Nashville

All the user has to do now is type Columbus in the first cell and use the autofill to list the rest of the markets.  This function can save those who frequently create add hoc reports a lot of time.

Custom Lists can be created for just about anything, are easy and quick to create, and are useful in a variety of situations.  www.In2Hyperion.com is not just for those in a technical capacity.  User related ideas, such as using Custom Lists, will become more prevalent on this site.  Sign up for our newsletter and receive notifications when more Excel tips for Essbase users become available.

Users of Essbase have some control over the performance of a database and how responsive it is when retrieving data.  With a basic understanding of how Essbase stores data, users can optimize performance by changing the order of the dimensions and members in a report.

It might be helpful to read our article on sparse and dense dimensions.  Here is a brief overview:

An Essbase database is comprised of thousands, if not millions or billions, of data blocks.  Each block of data, and its size, is defined by the dense dimensions in the Essbase outline.  The volume of blocks is dictated by the unique combinations of sparse dimension members.  If Time and Accounts are dense, each block created would hold all the months for every account.  If Organization and Product are sparse dimensions, there would be a block for each unique combination of Organization and Product.  A block would exist for Center 10 / Product A, as well as Total Organization / Total Product.  If the outline has 20 members in Organization and 15 members in Products, the database could have up to 300 independent blocks.

If a report is written to show an entire income statement for all 12 months for Total Product and Total Organization, how many blocks would have to be queried?  Remember, there is a block for each unique member combination of Organization and Product.  The answer is one, because there is a block for Total Organization/Total Product that includes every account and every member in the time dimension.

How many blocks would be accessed if a report pulled Total Sales (a member in the Accounts dimension) in January for every product?  Since the Product dimension is sparse and there are 15 products, 15 blocks would have to be opened to return the results.

Here is where your understanding of what sparse and dense represents will help you improve your reports.  Opening a data block, reading the contents, and closing it, is similar to opening, reading, and closing a spreadsheet.  It is much faster to open one spreadsheet, or block, than 15 spreadsheets.  So, if the database retrieves are written in such a way to minimize the number of blocks that need to be accessed, or the order in which they are accessed, performance can improve.

I will agree that if data for all 15 products is needed for the report, all 15 blocks have to be opened.  There is no way around that.  That said, often times users will build one worksheet for income statement and one worksheet for balance sheet.  This means that the report is making two passes on the same blocks.  In theory, it takes twice as long to open/read/close a data block 2 times than it does once.  It is faster to have the income statement and the balance sheet accounts in one worksheet, which only makes one pass on the required blocks.  One worksheet for Income Statement and one for Balance Sheet can be created with cell references to the worksheet that has the retrieved data, if 2 separate reports are required.

I frequently see another example of a report requiring multiple passes to the same data block.  Using our example dimensions above, assume product information is required in a report for multiple accounts.

 

 

Jan

Feb

Mar

Income

Product A

 

 

 

Income

Product B

 

 

 

Income

Product C

 

 

 

Income

Product D

 

 

 

Expense

Product A

 

 

 

Expense

Product B

 

 

 

Expense

Product C

 

 

 

Expense

Product D

 

 

 

The Essbase retrieve above would start from the top of the spreadsheet and move down the rows to retrieve the data from Essbase.  This cycle would open the Product A block, then B, C, and D, and retrieve the associated income for each.  It would then have to reopen the same 4 blocks to access expenses. 

The following example, again going from top to bottom, would access both income and expense while the block is open.  The way this retrieve is setup, it eliminates the need to access the same block multiple times, yet still pulls the required information.

 

 

Jan

Feb

Mar

Income

Product A

 

 

 

Expense

Product A

 

 

 

Income

Product B

 

 

 

Expense

Product B

 

 

 

Income

Product C

 

 

 

Expense

Product C

 

 

 

Income

Product D

 

 

 

Expense

Product D

 

 

 

These examples are very small.  In a real world example, a report of this size would not produce significant variances in the time it takes to retrieve them.  Users often have spreadsheets that are hundreds of rows long and take minutes to retrieve.  In these situations, eliminating the need to access the same block multiple times can produce notable improvements in the time it takes to retrieve data from Essbase.

With a basic understanding of how your database is setup, users of Essbase can help themselves with some simple changes to the format of the retrieve worksheet.  If access to the dimension properties in your database is unavailable, ask your system administrator to supply them for you.