There are times when planning and forecasting databases grow for apparently no reason at all. The static data (YTD actuals) that is loaded hasn’t changed and the users say they aren’t doing anything different.

If you load budgets or forecasts to Essbase, you probably do what I’m about to tell you. If you are a systems administrator and have never seen how finance does a budget or forecast, this might be an education.

The culprit?  More data!

Budgets and forecasts are not always completed at the bottom of the hierarchy and rolled up. I don’t mean technically, as you might be thinking, Yes they do, they load to level 0 members and it gets consolidated up the outline. When it comes to budgets and forecasts, they are largely done in a top down approach. What this means is that finance is given a goal, or number, they have to hit, and they have to PUSH it down to lower business groups. The way a financial analyst creates a top-down budget, many times, is to allocate a value based on a metric, like headcount or sales.

Assume a budget for desktop support services is required. Let’s say management has mandated that the expense doesn’t grow from last year. Since this cost is to support the people in the business, the expense is divided by the expected headcount and allocated evenly. If a business unit has 20% of the people, that unit will get 20% of the expense. Since the expense to be allocated isn’t going to change, but the headcount will, the following will be the result:

Because the analyst doesn’t want to worry about missing any changes to the headcount forecast, he or she will create a data retrieve with headcount for every cost center, whether it has headcount or not. A lock and send sheet now takes the percentage of headcount each cost center has and multiplies that factor by the total expense. As headcount gets re-forecasted, this expense has to be reallocated. With this methodology, all the user has to do is retrieve the sheet with all the headcount forecast. The math does the allocation and the result is sent back to the database.

Easy, right?

This makes a ton of sense for an accurate forecast or budget with minimal effort. Not so fast, as this has two major flaws.

First, the volume of data loaded may be drastically higher than it needs to be. Assume the worksheet has 500 cost centers (500 rows). If half of these have no headcount, there are an additional 250 blocks created that hold zeros (assuming the cost center/organization hierarchy is sparse). This method, although very efficient for updating the numbers for the analyst when headcount changes, is causing the database to grow substantially. In this isolated example, there is twice as much data than is required.

Secondly, since the data has to be loaded at level 0, the analyst thinks loading at every cost center is a requirement. The materiality of the data at this level is often irrelevant. Let’s say that the analyst is really forecasting at the region, but loading data at the cost center because it is required to be loaded at level 0. Assume there are 10 regions in which these 500 cost centers exist. A forecast at the 250 cost centers that have headcount is not required. The forecast only needs to be loaded for 10 cost centers, one for each region. If this method were used, we would only create 10 blocks, rather than the 250, or 500 originally. When the system has hundreds of users, and thousands of accounts, you can see how the size of the database would grow substantially. This also provides no additional value and huge performance problems. In the example above, the number of blocks can be reduced from 500 to 10. It is far quicker to calculate 10 blocks than 500.

Even if the data needs to be at the cost center, many times the allocation is so small, the result of the allocation is pennies, or dollars. You would be hard-pressed to find a budget where a few dollars is material. In situations like this, the users have to ask themselves if the detail is worth the performance impact.

Users, Help Yourselves

Educate your users and co-workers on the impacts of performing these types of allocations. If loading data at every cost center is required, change your formula. Rather than calculating the expense as

=headcount / total headcount * Total Expense

add an IF statement so when the retrieve has no headcount, the calculation produces a #MI,

rather than a 0. This would be more efficient

=IF(headcount=0,”#MI”, headcount / total headcount * Total Expense)

If this is not necessary, change the way the data is loaded. Rather than picking all the cost centers, retrieve the headcount from the regions and build the send template to load to one cost center for each region.

The Real World

I worked for a large financial institution with a 100 Billion dollar budget. More than 70% of all the data was less than 10 dollars, and 30% was equal to zero! The budget was never looked at below region, which was 4 levels deep in an organization hierarchy that included more than 30,000 cost centers.

After consolidating the insignificant data and educating the users, the calc times decreased from 50 minutes to less than 5. All aspects of performance were better.

Easily Find Out How This is Impacting Your Application

There are a lot of ways to see if this phenomenon impacts your database. If the database is small, the export could be loaded to Excel. With some basic IF statements, the number of cells that were higher or lower than an identified threshold could be determined. Because I regularly work in a lot of different environments with large amounts of data, I wrote an application to traverse through an Essbase export to produce statistics on the data. The application is attached for download. Make sure you have the .NET libraries installed or this will not execute.  Version 3.5 or higher is required, and can be found by searching download .net framework.  There is a good chance it is already installed.

This is a simple application that I developed quickly to help me understand the degree to which a database is impacted by the example explained above. It will traverse through roughly 25,000 lines every second, and will provide the following metrics:

• the number and percentage of values above a threshold entered
• the number and percentage of values below a threshold entered
• the number and percentage of values that are 0
• the number and percentage of values that are #Missing, or Null
• The number of lines in the export and the number of seconds it took to process

To use this, export the database at level 0 and choose column format. You will be prompted for the path and file name of the export, and the threshold to evaluate.

Download Essbase Export Analysis, and give it a try.

There are several ways to export data from Essbase on a large scale. Pulling it via Excel (Smart View or the Essbase Add-In) is not the best way to get large amounts of data when the goal is to move the data somewhere else, so this option will not be covered.

Database Export

The easiest method is to export all the data from a database by exporting the database.  This can be done in EAS.  This method is easy to automate with Maxl, but has little flexibility with formatting and the only option is to export all the data.  It can be exported in column format so the data can easily be loaded into another data repository.  If the data needs to be queried, or manipulated, this is a good option.  

Essbase Report Script

The classic way, prior to version 9, was to write a report script. This provides a tremendous amount of flexibility in formatting, but most admins struggle with this method, as it requires an in-depth knowledge of how data is most effectively queried.  If there isn’t a strong understanding, then extracting data is extremely slow.  Report scripts also offer the ability to export specific data, which is often required.

Calculation Script or Business Rule

Now, there is an alternate method that somewhat combines the best of the previous 2 methods.  A calculation script, or business rule, can now extract data in column format with a predefined column delimiter. It can filter the result to specific criteria, and doesn’t require the knowledge reports scripts does to make them efficient.

/* Export to text file */
DATAEXPORT "File" "delimiter" "fileName" "missingChar"
  
/* Export to relational database */
DATAEXPORT "DSN" "dsnName" "tableName" "userName" "password" 
  
When DATAEXPORT is used, there are a series of settings that can be applied.
  
SET DATAEXPORTOPTIONS
{
/* set the level of data to be extracted – same as a database export from EAS */
DataExportLevel ALL | LEVEL0 | INPUT;
  
/* Turning this on will export any member within the fix statement that is a dynamically calculated member that produces a value */
DataExportDynamicCalc ON | OFF;
  
/* A value between 0 and 16 – if no value is supplied, the number of decimals positions in the data, or the DataExportPrecision will be used */
DataExportDecimal n;
  
/* The number of positions numeric data is exported with, emphasizes data accuracy
NOTE: if DataExportDicimal is set, it will override this setting */
DataExportPrecision n;
  
/* When set to ON, every column will have a member name and they will be repeated from row to row */
DataExportColFormat ON | OFF;
  
/* Specifies the dense dimension used for columns – typically the months, or periods, are the most logical choice */
DataExportColHeader dimensionName;
  
/* When set to ON, the dimension names will preceed the data as column headers */
DataExportDimHeader ON | OFF;
  
/* When set to ON, data will be in column format and all members names will be repeated, and no column header will be included.  Missing and invalid data will be skipped producing successive dilimiters */
DataExportRelationalFile ON | OFF;
  
/* When set to ON, if the file specified already exists, it will be overwritten */
DataExportOverwriteFile ON | OFF;
  
/* When set to ON, the file exported will include additional details about the export
     - Summary of data export settings
    - Info, Warning, and Error messages
     - Exact number of blocks to be exported
     - Estimated time, excluding I/O time. */
DataExportDryRun ON | OFF;
};

Each method has its advantages.  I see the DATAEXPORT function becoming the primary export method because of its flexibility and ease of use.

A Word of Caution

When exporting data, always remember the population that has access to the export. Exporting data is a very popular method for backing up data, but often times it includes total company financials, employee salaries, and other sensitive data.

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.  

Below are two examples.  Neither is difficult, but they will provide you with a taste of what you can do with custom functions.  The first example calculates a better/worse value based on three inputs (prior period, current period, and expense vs. revenue).  The second concatenates columns together with a user specified delimiter and the option to use quotes around the values.

Background on Custom Functions

Custom functions are Visual Basic for Applications (VBA) code snippets that are stored in modules in a workbook.  This is the same place macros are stored, so it may be familiar.  To open the VBA window, use ALT F11.  Once the window opens, right-click the workbook you want to add the function to in the VBAProject window and select Insert->Module.  A new window will open named Module1.  Custom functions have to be in a module to be accessed in a workbook.

Each function has a function name, input arguments that pass data to the function, and return a value.

A very simple example shows these pieces.  “Test” is the function name.  “Input” is one argument passed to the function.  The function returns a numeric value, which is the input value multiplied by ten.

Function Test(input as double) as Double
    Test=input * 10
End Function 

To use this function, return to your worksheet and enter “=Test(5)” in a cell.  This function can also be found in the Insert Function option by selecting User Defined in the Select A Category dropdown box.  The input parameter doesn’t have to be a value.  A cell reference can be used, just like any other Excel function.  The result should return 50.

Example:  Better(Worse) Calculation

For you finance folks, you will almost always have a better/worse calculation in a spreadsheet that compares two periods.  For revenue, the current period is subtracted from the prior period.  For expense, it is the inverse. 

To accomplish this, we will have a function with 4 parameters. 

1. Prior Period
2. Current Period
3. Whether the numbers being evaluated should be calculated as an expense or revenue
4. Whether the result returned is in the form of a dollar value or percentage change

Function BetterWorse(Prior_Period As Double, Current_Period As Double, Expense As Boolean, Return_Dollar As Boolean) As Double
    If Expense = True Then 'Calculate as an expense
        If Return_Dollar = True Then  'Return a dollar value
            BetterWorse = Prior_Period - Current_Period
        Else  'Return a percentage
            BetterWorse = (Prior_Period - Current_Period) / Prior_Period
      End If
    Else  'Calculate as a revenue
        If Return_Dollar = True Then  'Return a percentage
            BetterWorse = Current_Period - Prior_Period
        Else  'Return a percentage
            BetterWorse = (Current_Period - Prior_Period) / Current_Period
      End If
    End If
End Function

Below is an example of this function being used.  The result of the custom function resides in column D and E.  Revenue is lower in the current year, resulting in a negative variance.  Expenses are also lower, but result in a positive variance.

The formulas that exist in columns D and E are as follows.

Example:  Concatenation

The need to create a delimited file from Excel is very common.  The problem with doing this is that the entire worksheet is extracted.  If the worksheet had data in rows or columns that are now blank, Excel still exports those blank cells.  One way to overcome this is to create a function that concatenates a range into one cell.  Then, the concatenated values can be copied and pasted to a text file.  Many times this is very handy.  This can obviously be done with a cell formula, but gets time consuming to create when many cells are required.  It is further complicated when quotes around the fields are necessary. 

Function ConcatForExport(InRange As Range, Delimiter As String, UseQuotes As Boolean) As String
    Dim TheCount As Integer
    TheCount = 0
    For Each cell In InRange
        If TheCount = 0 Then
           If UseQuotes = True Then
              strString = Chr(34) & cell.Value & Chr(34)
           Else
              strString = cell.Value
           End If
        Else
           If UseQuotes = True Then
               strString = strString & Delimiter & Chr(34) & cell.Value & Chr(34)
           Else
               strString = strString & Delimiter & cell.Value
           End If
        End If
        TheCount = TheCount   1
    Next cell
    ConcatForExport = strString
End Function

To expand on the variance example above, an additional column has been added to show the use of this function.  Each row passes different parameters.  Columns B through E are concatenated together into one cell.  The delimiter is altered in row 5, and no quotes are around the value in row 4.

The corresponding formulas are below.

There are a wealth of opportunities that open up using custom functions.  Adding functionality and automating tasks like the examples above are just the start of what can be done. 

Almost every planning or forecasting application will have some type of allocation based on a driver or rate that is loaded at a global level.  Sometimes these rates are a textbook example of moving data from one department to another based on a driver, and sometimes they are far more complicated. Many times, whether it is an allocation, or a calculation, rates are entered (or loaded) at a higher level than the data it is being applied to.

A very simple example of this would be a tax rate.  In most situations, the tax rate is loaded globally and applied to all the departments and business units (as well as level 0 members of the other dimensions).  It may be loaded to “No Department”, “No Business Unit”, and a generic member in the other custom dimensions that exist.

If a user needs the tax rate, in the example above, they have to pull “No Department” and “No Business Unit.”  Typically, users don’t want to take different members in the dimension to get a rate that corresponds to the data (Total Department for taxes, and No Department for the rate).  They want to see the tax rate at Total Department, Total Business Unit, and everywhere in-between.

There are a number of ways to improve the experience for the user.  An effective solution is to have two members for each rate.  One is stored and one is dynamic.  There is no adverse effect on the number of blocks, or the block size.  The input members can be grouped in a hierarchy that is rarely accessed, and the dynamic member can be housed in a statistics hierarchy.

Using tax rate in the example above, create a “Tax Rate Input” member.  Add this to a hierarchy called “Rate Input Members”.  Any time data is loaded for the tax rate; it is loaded to Tax Rate Input, No Department, No Business Unit, etc.  Under the statistics/memo hierarchy, create a dynamic member called “Tax Rate”.  “Tax Rate” would be the member referenced in reports.  The formula for this includes a cross-dimensional reference to the “Tax Rate Input” member, and would look something like this.

“No Department”->”No Business Unit”->”Tax Rate Input”;

When a user retrieves “Tax Rate”, it always returns the rate that is loaded to “No Department,” “No Business Unit,” and “Tax Rate Input,” no matter what department or business unit the report is set to.  The effort involved in creating reports in Financial Reporting or Smart View now becomes easier!

There is an added bonus for the system administrators.  Any calculation that uses the rate (you know, the ones with multi-line cross-dimensional references to the rates) is a whole lot easier to write, and a whole lot easier to read because the cross-dimensional references no longer exist.

Before you move the application to production, make sure to set the input rates consolidation method to “Never.”  Don’t expect this change to make great improvements in performance, but it will cause the aggregations to ignore these members when consolidating the hierarchies.  A more important benefit is that users won’t be confused if they ever do look at the input rates at a rolled up level.  The ONLY time they would see the rate would be at level 0, and would be an accurate reflection of the rate.

Note:  It is recommended to create member names without spaces.  The examples above ignored this rule in an effort to create an article that is more readable.

1. SSL Offloading
2. 2-way SSL deployment
3. SSL termination at the web server

1. Additional information in migration status reports, including source and destination details.
2. Users must be provisioned with the Shared Services Administrator role to work with the Deployment Metadata tool.
3. The Calc Manager is supported, and has its own node under Foundation. As a result, business rules can now be migrated to classic HFM and Planning applications.

1. Manage Taskflows – This role allows users to create and edit a taskflow
2. Run Taskflows – This role permits users to view and run a taskflow, but they cannot create or edit taskflows

Improved Support for Essbase

1. Utilizing the Reorder Children dialog box, a new sort order can now be created to reorder members in the hierarchy.
2. Performance settings for dimensions can now be modified in EPMA
3. Dynamic Time Series (DTS) is now supported on the period dimension (BSO cubes)
4. The ability to add Typed Measures and members with a Date Format has also been included.
   1. Varying Attributes are still not supported in this release

1. Select the Financial Management app. It will then be copied as a Classic application. Once this has been done, the EPMA upgrade feature can be uses
2. Alternatively, the LCM tool can be used to migrate the application. Once this is done, the Copy Application Utility can be utilized to move the data.

1. Login through a proxy is now supported
2. Single Sign On (SSO) login is also supported

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.

1. SSL Offloading
2. 2-way SSL deployment
3. SSL termination at the web server

1. Additional information in migration status reports, including source and destination details.
2. Users must be provisioned with the Shared Services Administrator role to work with the Deployment Metadata tool.
3. The Calc Manager is supported, and has its own node under Foundation. As a result, business rules can now be migrated to classic HFM and Planning applications.

1. Manage Taskflows – This role allows users to create and edit a taskflow
2. Run Taskflows – This role permits users to view and run a taskflow, but they cannot create or edit taskflows

If you have recently upgraded your web browser to IE8 and attempted to use Hyperion Workspace, you’ve likely encountered difficulty in navigating the interface. When a user clicks on the wheel in the upper left hand corner, selects “Applications,” they can’t see the menu as it apearrs to be condensed. 

This issue can be quickly resolved by making a browser modification to the IE security. It is best to run this settings change past your IT department to ensure it will not open holes in your company’s security profile.

To correct this issue, 

1.       Open IE8

2.       select “Tools”->”Internet Options”

3.       Select the “Security” tab and “Custom Level”.

4.       You will have 4 zones where security changes can be performed (Internet, Local Internet, Trusted sites, Restricted sites). The change can be made to each of these 4 zones if necessary, but it’s possible only one zone needs modified. Test the combination that works for your environment and fits your companies IT/security profile.

5.       Select the “Internet” zone and scroll down the menu of options to find “Allow script-initiated windows without size or position constraints”. This will likely be set to “Disable”… select “Enable” and click “OK”. (If prompted, accept the change just made to the zone security).

6.       Select “OK” from the security tab to finalize the modification.

7.       Test the modification to verify the change worked as intended. As noted above, you might need to enable this setting on multiple zones in order for the setting to take effect.

Audit logs, or SSAUDIT, are a crucial component of backing up Hyperion Essbase applications in many environments.  It is the equivalent of a transaction log in a relational database.  To use this effectively, the audit log has to consistently log database changes.

If the audit feature in Hyperion Essbase is used, the following information is absolutely critical to know to effectively manage this feature.  If the application is on a shared environment where multiple groups/people are administering the applications, it is critical that everybody understands this, and plays nicely together!

The audit logs are turned off without any notification when the following actions occur on an Essbase server.  To turn the audit feature back on, the Essbase application in question has to be stopped and started.  It is not required to cycle the Essbase service.

• Any operation that causes a database restructure.
• The creation of a new application
• The creation a new database
• Copying a database
• Renaming a database

After any of these operations occurs on the server, stop and start all applications that use the audit feature.