Whether you play a technical role or are a financial analyst, Excel is likely a major asset in your toolbox. Whether it is the SUM function, the VLOOKUP function, or one of the many others, we have all used Excel functions for a plethora of reasons. 

There is a lot of potential hidden in Excel that you may not be aware of.  Excel offers the ability to create your own user defined functions, and it's not hard to create them.  With a little ingenuity and strategic thinking, custom Excel functions can be a huge asset.  More...

Working with people new to Essbase every three to six months, I am always looking for ways to show users their hierarchies effectively. Many of them don't have access to Essbase administration services or EPMA.  So, I always fall back to excel as a distribution method, as well as documentation, to show hierarchies. 

Expanding hierarchies to all descendants is a great way to show small hierarchies, but, I am always asked to make it a collapsible hierarchy using the Excel grouping feature. The challenge of doing this manually to a hierarchy with thousands of members is that it is extremely time consuming and very error prone. 

The following script can be added to any workbook to automate this effort. 

Sub CreateOutline()
    Dim cell As Range
    Dim iCount As Integer
    For Each cell In Selection
        'Check the number of spaces in front of the member name 
        'and divide by 5 (one level)
        iCount = (Len(cell.Value) - Len(Trim(cell.Value))) / 5
        'Only execute if the row is indented
        If iCount <> 0 Then cell.EntireRow.OutlineLevel = iCount
    Next cell
    MsgBox "Completed"
End Sub

Setup

First, this sub routine has to be added to a workbook.  Open up the visual basic editor. Right click on the workbook in the project explorer window and add a new module. Paste the code above in the new module.  The editor is in different places in different version.  In Excel 2007 and 2010, the Developer ribbon is not visible by default.  To make it visible, go to the navigator wheel and click Excel Options.  There is a checkbox named Show Developer Ribbon that will make this developer ribbon viewable.

How To Use

First, open the member selection option in the Essbase add-in or smart view and select the parent.  Add all its descendants.  Alternately, change the drill type to all descendants and zoom in on the member of the hierarchy.

Retrieve, or refresh, the data, and make sure the indent is set so the children are indented.  Now, highlight the range of cells that has the hierarchy/dimension that the grouping should be applied. This should include cells in one column of the worksheet.  Open the code editor and place the cursor inside the sub routine you added from above and click the green play triangle in the toolbar to execute the script.  When this is finished, go back to the worksheet with the hierarchy and it will have the hierarchy grouped.

Excel limits the level of groupings to eight. If the hierarchy has more than eight levels, they will be ignored. Now, the hierarchy can be expanded and collapsed for viewing.  

Shortcut keys or toolbar buttons can be assigned to execute this function if it is used frequently. If you are interested in doing this, there are a plethora of how-to articles on this topic.  This Google search will get you started if you choose to go down that path.   

So, the next time you need to explain a hierarchy in Essbase, or distribute it in a common format, hopefully this script will help.

A number of minor enhancements and fixes have been included.

Replace 0 Option

The replace 0 option with #Missing has been a huge hit!  The option worked great on smaller worksheets.  On sheets where there are thousands of cells to check, the time to perform this operation was unacceptable to some.  After some research, a faster way to process this request was identified.  More than 40 times faster, we have finalized the change and are now making it available.

Connection Manager Not Functioning Properly

A number of users have had issues with the connection manager menu.  With the help of those involved, we have identified an issue when using &, >, or < characters in the name of the connection.  When adding connections, those keys are no longer available for use.

Retrieve All

The Retrieve All function now operates as expected.

For those who need help installing this as an addin, please read the installation instructions here.

Download Version 3.3

A lot of users like to see hierarchies in Excel and build groupings around these hierarchies so they can be collapsed and expanded easily.  It is not a huge deal to do this for things that don’t change a lot, like months rolling to a quarter, but it can be extremely cumbersome to maintain for organizational or account hierarchies that are large or change frequently. 

By adding some VBA code (a macro) to your workbook, managing groupings can be completely automated.  This can be customized for a plethora of different scenarios.  Below are 2 examples that Hyperion users will encounter.  One caveat to this is that Excel limits the number of grouping levels to 8.  If the worksheet has more than 8 levels, the following logic would not provide the expected result.

Creating a Hierarchy Based On Excel Indents

If a spreadsheet exists where the hierarchy is created with the indent (not multiple columns) feature of Excel, select the range for the groupings to be applied.  Execute the following script.  Basically, this loops through the cells you have selected and will create the groupings based on the number of indents in the cell.

Sub CreateGroupingsOnIndents()

Dim cell As Range
For Each cell In Selection
    If cell.IndentLevel <> 0 Then
        cell.EntireRow.OutlineLevel = cell.IndentLevel
    Else
        cell.EntireRow.ClearOutline
    End If
Next

End Sub

Creating a Hierarchy Based On SmartView/Excel Add-In Indents

When retrieving from Essbase, cells are indented by adding 5 spaces to the member name.  By getting the length of the cell, subtracting the number of spaces preceding the member name, and dividing the result by 5, the level of the indent is identified.  Select the cells with the member names and execute the following.

Sub CreateGroupingsOnSpaces()

Dim cell As Range
Dim iLength1 As Integer
Dim iLength2 As Integer
Dim iIndent As Integer

For Each cell In Selection
    iLength1 = Len(cell.Value)
    iLength2 = Len(LTrim(cell.Value))
    iIndent = (iLength1 - iLength2) / 5
    If iIndent <> 0 Then
        cell.EntireRow.OutlineLevel = iIndent
    Else
        cell.EntireRow.ClearOutline
    End If
Next

End Sub

Setup a Module

If you are unfamiliar with adding custom code to an Excel workbook, follow the steps below.

Excel 2000 and below

1. Select Tools/Macro/Visual Basic Editor
2. Right click on the workbook in the Project window, and select Insert/Module
3. Expand the module folder and open the new module (likely module1)
4. Paste the example above in this window to the right
5. Execute it by clicking F5 or the green play triangle in the toolbar

Excel 2003 and greater

1. Select the Navigation Wheel, and check the “Show Developer tab in the Ribbon” checkbox in the Popular tab
2. Select the Developer Ribbon and click Visual Basic
3. Right click on the workbook in the Project window, and select Insert/Module
4. Expand the module folder and open the new module (likely module1)
5. Paste the example above in this window to the right
6. Execute it by clicking F5 or the green play triangle in the toolbar

These can also be associated to a custom menu or toolbar if you choose to take the extra step!

We have been working diligently to add the requests from our community. We hope you enjoy the following improvements.

Manage Quick Connections

The most important enhancement is the Manage Quick Connection under the Quick Connect menu.  Now, passwords can be quickly changed for multiple connections.  This is an important update for those of you who have to change your password frequently.  Saved connections can easily be removed.

Eliminating unnecessary database growth

Every worksheet now has the option to replace zeros with #Missing when Lock/Send is used.  When this option is turned on, all zeros are replaced with #Missing prior to sending the data to Essbase.  This helps keep the database size from growing unnecessarily and improves performance on the server.  After the data is sent, #Missing is replaced with a blank to maintain visual appeal.

Other minor tweaks

• The ribbon includes the Indent Level option.
• There are multiple disconnection options - disconnect the active worksheet, disconnect all connections on the active workbook, and disconnect all connections in all open workbooks.

For those who need help installing this as an addin, please read the installation instructions here.

Download Version 3.1.

UPDATE ON FEB 18, 2011

A bug was discovered today by one of our members and was gracious enough to help us debug it.  Member names in a spreadsheet that are numbers, formatted as text, were replaces with numeric values when using the option to replace zeros with #MI.  This obviously causes errors as Essbase only recognizes textual member names.  If you downloaded version 3.1, please download version 3.2.  Thanks to those bringing this to our attention.

Download Version 3.2.

As an Essbase user, you have more power to improve performance than you think.  How many times do you lock and send data through Excel, SmartView, or web forms, that include zeros?  How many times do you allocate data to a finite level out of convenience?  Understanding what this does to Essbase is critical to understanding how a user can negatively impact performance without adding any value to the analysis or the results the database produces.

I analyzed a planning database used in one of the largest financial institutions in the world.  Over 60% of the values entered were zero.  Another 20% of the values were less than 1 dollar.  By eliminating the zeros, the total calculation time of the planning application was under 20 minutes.  With the zeros, it was nearly 2 hours.

There are two reasons for this.  First, there is a different between empty and zero.  Empty consumes no space to store whereas a zero consumes the same space as 1 billion.  Think of this as a grocery bag.  If you fill a grocery bag with nothing, it takes up no space.  If you fill it with empty cans (a zero), it consumes the same amount of space as if those cans were full (1 billion).

The example below is very common.  Assume that a forecast needs to be done for the last 3 months of the year.  Frequently, a spreadsheet would hold zeros for the first 9 months.  18 cells have zero and 6 cells have a positive value.  That means that 75% of your data could be eliminated by not loading zeros.

The same load with #Missing is more effective.

I highly recommend reading the article explaining dense and sparse to understand what a block is and what it represents before you continue this article. 

There is also another very significant factor in loading zeros.  Loading a zero that creates a block just to hold a value of zero can explode the size of the database, as well as the time it takes to consolidate and execute business rules.  The more blocks that have to be loaded and consolidated, the longer it takes to finish.  If each block was a spreadsheet and you had to do this manually, you would have to open each spreadsheet and enter the number into a calculator to consolidate.  If 75% of the blocks you opened were zero, it wouldn’t change your total, but it would drastically increase the time it takes because you still have to open each spreadsheet.   If an Essbase database has 1,000 blocks, and 75% of them only hold zeros, it will likely take 2 or 3 times longer to calc the zeros because it still has to open the block and add the zero.  Remember, a zero acts no differently than a value of 100.

As an example to the above, the following example would create a block for South and West, inflating the database size.

Users can significantly reduce this unnecessary explosion in size by loading a blank as apposed to a zero.  If zeros are already in the database, leaving the cell blank will NOT overwrite the zeros.  If zeros are loaded inadvertently, a #Missing has to be used to remove them. 

For all you users loading data, it can be a hassle removing the zeros.  Being responsible can significantly improve your experience with Essbase.  To make it easier, take a look at the function in the In2Hyperion Excel Ribbon that replaces all zeros with #Missing.