When automating Business Rules through batch scripts, an XML file is needed to state the runtime prompts. This is how the batch script will know which members to run the business rule for. Is there an easy way to create these files? Read more
Tag Archive for: XML
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.
I started my career as an accountant and never had any aspirations of doing the same thing all day, every day. While I struggled through what I considered monotonous job functions, I developed a knack for finding ways to automate my job. As a result, I didn’t have to do repetitive tasks and I had more time to learn the business. Don’t get me wrong, accountants possess a unique set of skills and talent that I respect trumendously. It is a critical function of any business. So, kudos to you accountants!
When I get involved with building new applications with Hyperion, or updating existing models, it pains me to see accounting, finance, and the staff who support Hyperion continue to perform repetitive tasks that dominate their time. It can drive talented people to look for employment elsewhere. It inflates salaries and jeopardizes credibility with an increase in human error. It also deteriorates the quality of business analysis, introducing a greater risk of poor decisions. Inflated expenses and poor management decisions can be catastrophic to any business.
Automation in accounting and finance areas is critical to productivity. Being able to support the constant push from management to become better and faster with less resources is always challenging. If your Hyperion environment is supported outside of finance, IT areas are under just as much scrutiny. How much of your time, or staff, is spent generating reports? How much more time could be spent helping analyze the business and adding value to management decisions? From an IT prospective, how much of your time is spent supporting the environment and responding to requests where answers could be automatically generated? If 20% of your reparative tasks were eliminated, how much more effective you would you be? How much more experience would you gain? How much more marketable would you be both internally and externally?
Many of the possibilities for automation are never discussed. Most people don’t even realize how much time they spend performing repetitive tasks that could be automated. Some think it would be impossible to automate and others think it would be too expensive. The examples below were both accomplished in a matter of weeks. The investment had a positive return within months. The non-monitory gain was felt immediately.
Don’t think of why it can’t be done. Think of a solution without constraints and ask, “How can we get there?” With the proper guidance and background, massive improvements can be accomplished with minimal effort.
To spark some thought, think about these situations.
Monitoring Essbase jobs and keeping users informed of system status
Are you responsible for managing all the jobs that run on Essbase server(s) and are constantly asked if something has completed, or when something will complete, by your users? Some organizations have a person dedicated to managing this information flow.
I implemented a solution at a large financial institution to conquer this problem. The result was a solution that required zero effort to maintain and provided a summary of over 50 processes in one web page. It gave the status of the process, when it last executed, if there were any errors, and a link to the log and error files if they were required. Access was granted to all the Essbase administrators. Another page was available for all users that displayed the status of the application, when it was last loaded, when it was last calculated, and several other useful sources of information.
The days of searching through folders on multiple servers are now long gone for system administrators. Users are more informed and support tickets diminished substantially. The estimated time savings was 4-6 hours per day.
This solution was built using existing technologies, limited to Maxl, Windows scripting, ASP.NET, and access to an IIS Server to host the website. It was 100% maintenance free and built dynamically enough so that new applications could be added and applications could be renamed or deleted. All this is possible without changing any code or processes.
Distribution of reports
A large international organization distributed over 150 reporting templates to an equal amount of people in the US and abroad. These templates were distributed daily through the monthly close of business. The daily adjustment cycle finished updating the reporting Essbase application around 2 AM. When a finance staff member arrived around 8 AM, the work began. The template was refreshed and saved for each of the 150 business entities. Emails were then sent to each of the 150 people with their respective report. This process took about 6 hours every day it was performed.
Using existing technology, a process was created to traverse through a spreadsheet that had 2 columns, which was maintained by finance. The first was the business unit, followed by the email the report was to be sent to. Using the Essbase toolkit and Excel, a process was initiated as soon as the database was updated that opened a spreadsheet that included the template, changed the business unit, refreshed the template, saved it, and emailed to the intended recipient. This process took less than 1 hour and all the reports were distributed before 4 AM. Customers received their reports earlier (those in Asia a day early), no human errors were made, and the finance staff now had an additional 6 hours to add value.