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? Read more
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.
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. Read more
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. Read more
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.
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
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.
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.
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.
After any of these operations occurs on the server, stop and start all applications that use the audit feature.