Three dashboard ideas for finance executives, a good read for those thinking of their organization’s strategy for the coming year…

On this holiday week I decided to dedicate this post to giving some thought to the challenges leaders of finance organizations face and to looking at them in terms of coming up with a short list of indicators that can be leveraged as they reflect on the ways to shape their team’s strategy in a way that maximizes their contribution and impact on the company this soon to come new year.

To keep things simple and avoid taking too much of your holiday time this post focuses on three dashboard ideas that align values and traits of high performance teams and that are built in response to governance and strategic challenges common to many financial organizations.

These dashboards can be easily implemented with Oracle Business Intelligence or other similar reporting tool. My recommendation is to have the first iteration of your dashboard be as simple and concise as possible and build upon it as your needs and processes evolve.

Discipline (IRR)

One of the organizational values that are hardest to measure is the discipline that your overall organization exhibits when making strategic decisions. Measuring the return on capital allocations is a process common in large conglomerates that isn’t always transparent or free of political maneuvering.

Deploying a simple dashboard that tracks the progress of large strategic projects in terms of both schedule and actual versus budgeted costs allows the office of the CFO to educate executives and managers in other areas on financial methodologies such as calculating the Internal Rate of Return (IRR) of a project or estimating the Total Cost of Ownership of a piece of equipment.

Having a dashboard with Key Performance Indicators that measure the rate of return, and actual versus projected performance in terms of cost and schedule will help your organization bring more transparency to the process of deciding which projects will be funded and will help your counterparts in other areas of the business in improving their financial decision making skills.

­­­­­Efficiency (Time To Close)

One other function that usually resides with the finance organization is the process of closing the books and preparing financial and management reports. This process which is, for the most part, simple and repetitive is implemented and managed very differently from company to company. These differences are often a result of industry specific accounting and differences in management style.

The best way to save your staff late nights and weekends while delivering analysis and reports to your management faster might be as unorthodox and simple as breaking your closing process in the different tasks that compose it and tracking the time each of them takes to complete each month. You can start simply by writing the list on a white board and later on make the transition to a dashboard page that can be shared with all of your team.

By understanding which components of your closing process take the most effort from your team you gain great visibility and thinker with the processes to shorten your cycle time. There have been corporations that implement daily reconciliation of their accounts therefore gaining the ability to detect unusual account activity much faster than if they had to wait for the bank statements to arrive in the mail.

You can use the data in your closing cycle dashboard as a development opportunity for your staff by encouraging them to detect processes that exhibit a lot of variance from month to month and coming up with ways to proactively eliminate the causes of this variance.

Value Creation

With the economy signaling it is finally turning around towards recovery and finance and accounting staff job satisfaction at the lowest level in years it is time to identify top performers and give them opportunities to feel challenged and excel.

It is in the best interest of finance leaders to create opportunities for their staff to get out of the routine of their day to day work and go work in teams within other functional areas of the organization by providing them with internal consulting on financial management and compliance best practices. This aligns very closely with the first dashboard idea, your staff can provide training and consulting to teams working on strategic projects being tracked on the IRR/Projects dashboard.

The value created by these select members of your team can be tracked by the number of projects they are consulting for and the number of training and consulting hours they provide to other areas of your organization. This in turn can be used as a tool in the justification of any proactive compensation activity you might decide to undertake as you try to retain high performing employees in your team.

Conclusion

When planning your finance organization’s strategy for the year that is about to begin you can use the dashboard ideas in this article as tools that will allow you to communicate to your staff and to other functional areas and fellow executives what improvement is being made in the internal processes of your area and; most important, how is it that your team can be engaged as a partner in value creation.

Please send your feedback and comments to Ignacio.delaTorre@biconsultinggroup.com, I’ll be very interested to hear your opinion and any additional ideas that might be useful to others reading this article.

Vintages - Time x Time

One of the more unique conceptual challenges when designing an Essbase or Planning model can revolve around handling multiple Time-basis dimensions. Many model requirements need granularity beyond the standard observation date that is typically defined as being the Time Period in a model. Consider the following:
A customer has a portfolio of investments, each having several characteristics related to some measurement of time.

• The origination date of the investment
• The maturity date of the investment
• An expected length of the investment, based from which the origination or maturation date can be interpreted
• An activity date

A single Time dimension (or two time dimensions that split portions of time apart, i.e. separating Years from Months and Quarters) can not accurately capture metrics in the previous example like the performance over time of all investments that originated in a given period, or how much return at a certain age of an investment can be expected.

Multiple Time Dimensions
Assuming the standard Essbase or Planning time dimensions represent the observation date, additional time-style dimensions can be created to provide more granularity. In the previous example, a dimension indicating the age of an investment can be utilized to analyse performance of investments based on how long ago they were originated. Likewise, identification of the starting or ending dates of investments allows analysis based on similar origination or maturation dates.

How much is too much granularity?
That all depends on the analysis requirements for a model. Adding more detail to a model will increase its size and complexity, but will provide a more detailed picture of time's effect on activity. All decisions regarding expanding a model require careful considerations of the benefits of additional complexity.



Other Time Time appears multiple Times (Time beyond core modelling requirements)

• Scenario revisions

First pass, second pass, etc: These are essentially observations or snapshots of a process at a point in time. Depending on how many of these snapshots are required, it may be advantageous to have a separate dimension identifying when the snapshot was presented. Consider the benefits carefully of disposing of forecast revisions. Often, one key long term goal to improve forecast reliability is the ability to track variances between revisions over time.

Hyperion Planning Data Forms

Hyperion Planning Data Forms are the main input mechanism to gather end user budgets and forecasts, and oftentimes, are the only component an end user interacts with in Planning. Because of this, there is a lot of functionality built into the forms that help them both mimic the use of a spreadsheet and enhance basic data entry and usability. Following are some of the features you will find when using data input forms.

Entering Data
Like a spreadsheet, you can copy and paste data into a cell, row, or column. If you enter data to a summary time period (quarter or year), values will automatically spread back into the months accordingly. If no months have data, values will be spread equally. If data does exist in the months, the summary time period spread will happen according to the previous spread pattern. You also can use the lock cells feature here to lock the spread from occurring in certain months. The result is that the spread would occur evenly to only the unlocked months.

Ad-Hoc Calculations
Data forms allow you to perform cell “what-if” scenarios to determine the impact of a number change. In a cell, you can add +, +-, *, /, or % along with a number and Planning will return an updated value. For example, if a cell contains 1,000 and you input “+500”, the cell would update to show 1,500. Or, you could enter “%40” and the result would be 400.

Supporting Detail
Supporting detail allows users to enter additional lines of detail below the lowest level of account contained in the application. For example, if you had a general Travel Expenses account, you could add additional rows of detail to itemize out airfare, meals, ground transportation, and hotel. Supporting detail can be added to a cell, cells, or an entire row. The supporting detail feature allows you to build hierarchies into the rows and contains row logic to add, subtract, multiply, divide, or ignore lines when rolling them up.

Adjust, Grid Spread, and Mass Allocation
The Adjust feature lets you adjust a cell or cells by a value or percent.

Grid Spread allows you to adjust a parent number by either a value or percent and then spread that value back to its descendants on the web form, either proportionally based on existing values, evenly, or filling all children with the parent value. The user need to have appropriate access to the target cells in order to write back to them. The administrator can also create other spreading options, if desired.

Mass Allocation is similar to Grid Spread, with the following differences:

• Data is allocated to all descendants of a member, even to those intersections not displayed on the web form.
• Access to the target cells is not required.
• Mass allocated values cannot be undone.

Exporting to a Spreadsheet
There are two options for moving a data form to Excel. There’s the spreadsheet export that copies the POV, page, row, and column members into an Excel spreadsheet, along with the data grid. The result is a file that is disconnected from Planning, although it could be used as a lock and send sheet by connecting through either the Essbase Add-In or Smart View. The second choice is the Open in Smart View option. This option opens the data form in Excel where you can essentially perform all the same functions as in Planning web, all while remaining connected to the system.

Notes and Annotations
Cell Text lets you add a textual note to a cell, similar to the Comments feature in Excel. This allows you to provide explanations for cell amounts and/or variances.

Account Annotations let you add descriptive information or a URL web link to an entire account row.

Instructions can be added to an entire data form to provide direction and information to an end user.

Other Functionality
With version 11, you now have the ability to add a document to a cell in a data form via a hyperlink or a Workspace object. You could either have a URL link to an Excel spreadsheet or Word document or provide a link to a Financial Reporting Studio report.

Another new feature in version 11 is the ability to drill from a data form cell down to FDM loaded source data. This allows a user to view data at a more granular level than is available in the Planning application.

Finally, calculations can be attached to and launched from a data form. For example, you could have a calculation that aggregates values up the hierarchy. A nice feature here is that the calculations can be set to run automatically, either upon opening a form or saving it.

Offline Budgeting and Planning

Have you ever been on a plane and said to yourself, “there aren’t enough Jack Daniel's miniatures aboard for me to have to sit through Santa Clause 3 again, but if I could just enter my Q4 forecast numbers, I’d be all set for the remainder of this ten hour flight.” Or, maybe you were driving down the Pacific Coast Highway thinking, “if I could only finish annotating my annual budget right now, my trip to the beach would be so much more enjoyable.” [Disclaimer here that I would never condone driving and budgeting or driving and doing anything else besides just driving, for that matter…] Or, maybe you’ve stayed at that Motel 17 out in nowhere with a download speed of 1 kb (+/- .5 kb) per hour and lost your budget updates no less than 20 times.

Ok, maybe not… But certainly, there is value in being able to access a budgeting system while disconnected from the system. Whether you’re on a plane or staying in a remote location, there’s inevitably a time when a deadline is looming and you just have to finish your work. Hyperion Planning provides a solution for this dilemma through the offline capabilities of the Smart View Excel add-in tool.

Smart View is a tool that allows you to pull Hyperion Planning data web forms into Excel. Once a form is in Excel, you can essentially perform all the same functions that you can in Planning web. With Smart View, you can input data and save it to Essbase, use the adjust data feature, enter supporting detail and cell text, and run business rules and calc scripts. You even have the same look and feel as a web form with expandable parent members, page drop-down boxes, green read-only cells, and yellow updated cells.

The process for taking a Planning data form offline in version 11 is fairly simple:

1. The application has to first be enabled for offline usage. Set this by going to Administration -> Manage Properties -> Application Properties and setting ENABLE_FOR_OFFLINE to true. This is the default setting.
2. Next, you need to enable individual data forms for offline usage. Edit a form, go to the Other Options tab, and click the Enable Offline Usage box.
3. Open the data form(s) in Smart View and click Take Offline. You can choose to take a single form, multiple forms, or an entire form folder and its contents offline.
4. Select the page dimension members to store offline. You can either choose to take all page members or just a subset.
5. Provide a connection name and click Finish.

At this point, the necessary application components are copied from the server to your local machine. Basically, a smaller version of the application is created on your system, including necessary outlines, calc scripts, dimension members, data, substitution variables, and web forms. The download can be quite large depending on the number of items you choose to include. To minimize the download time and system footprint, you will want to limit the web forms and page dimensions to only those that you will truly need.

Once all the components complete their download, you can work in your subset of the application to your heart’s content, all while being disconnected from the network. You can essentially perform the same operations as if you were still connected, with the following exceptions:

• When you save information, you are saving it to your local machine.
• When you log into the application in Smart View, you no longer log in through the Common Provider. You now go to Independent Provider Connections and open your offline connection there (named in step 5 above).
• Currency conversion is not supported for offline usage.

This offline connection can be accessed as many times as you’d like, continually opening, saving, and closing it until it’s synchronized back to the server.

Take the following steps to push any updates in your offline connection back up to the network server:

1. Open the offline connection.
2. Click Sync Back To Server.
3. Log into the application on the server.
4. Select which forms, folders, and page members to sync back.

After the upload is complete, I generally delete the offline Independent Provider Connection to remove the application files from my machine.

That's pretty much it in a nutshell. So, the next time you're in the middle of the Gobi Desert or standing on the edge of a crater rim on Mount Kilimanjaro, you'll have the comfort of knowing you can still get your work done. If, on the other hand, your motto is "when I'm away from work, I'm away from work", then just forget everything you just read.

How to search Planning Supporting Detail: Part 1 - Getting the data

Background Information

Supporting Detail is a powerful tool that helps planners build and communicate bottom-up values when planning such expenses as travel, salary, and projects, where you need to calculate aggregate values. Supporting detail can include text, values and operators that define how data aggregates.

This feature is particularly useful when budgeting when you have multiple expenses falling in the same line. However, one of the drawbacks is when you want to report on at the supporting detail level for particular line item. Suppose you want to look at the supporting detail for Facilities Expenses across multiple entities. You want to see all the entities that have the supporting detail item of Landscaping. This is rather difficult to do given the current mechanisms in planning for reporting on supporting detail.

One of the common complaints about supporting detail is this inability to report and search at the supporting detail level. However through the use of database objects such as views and Excel, this supporting detail can be searched and reported on. By no means do you have to use Excel to retrieve this data but it is the most common BI tool that all companies have. You can use any business intelligence reporting tools such as OBIEE to also provide the means to retrieve the supporting detail.

This blog entry will focus on setting up of the database to retrieve the data.

Overview

First, Supporting Detail is not stored in Essbase. This is the root of why you can’t get to it via the Add-In or Smartview. Supporting Detail is stored in the planning relational database in a couple of tables called HSP_COLUMN_DETAIL and HSP_COLUMN_DETAIL_ITEM. HSP_COLUMN_DETAIL provides the actual intersection where the supporting detail is stored whereas the actual supporting detail labels and values are stored in the HSP_COLUMN_DETAIL_ITEM table. The data is stored as keys so you also need to query the table HSP_OBJECTS and HSP_PLAN_TYPE to decode the key values to meaningful data as you would see in Planning.

The data that is stored in the HSP_COLUMN_DETAIL is stored as a member name so if you would like to see your supporting detail using the alias you will also need to include a mechanism to get the alias name from the HSP_ALIAS table.

There are many different ways to extract data from these tables ranging from stored procedures to database views. I prefer to use database views to give a real time look into the supporting detail but there are drawbacks and possible performance issues using this method.

For this blog entry I will focus on a simple method of extracting supporting detail. I will follow up this entry with additional blogs with methods for incorporating the alias and also converting the supporting detail value from the local currency to USD.

Example

For this example, assume you have a planning model that has 9 dimensions. The data is stored in HSP_COLUMN_DETAIL as DIM1, DIM2, etc ... so you will need to determine which dimension equates to the appropriate DIM column in the table. For this model the following table shows the dimensional mapping for the HSP_COLUMN_DETAIL table.


I have created a view using the following SQL. It is important to note that you will need to use outer join syntax to join HSP_COLUMN_DETAIL and HSP_OBJECT. HSP_COLUMN_DETAIL can have null values in the dimensional columns depending on your planning model.

select p.type_name PLAN_TYPE,
o1.object_name SCENARIO,
o2.object_name ACCOUNT,
o3.object_name DEPT,
o4.object_name PERIOD,
o5.object_name VERSION,
o6.object_name CURRENCY,
o7.object_name YEAR,
o8.object_name EMPLOYEE ,
o9.object_name PROJECT,
cdi.label SUPP_DETAIL,
cdi.value LOCAL_VALUE
from
hsp_column_detail cd inner join hsp_column_detail_item cdi on cd.detail_id=cdi.detail_id
left outer join hsp_object o1 on cd.dim1 = o1.object_id
left outer join hsp_object o2 on cd.dim2 = o2.object_id
left outer join hsp_object o3 on cd.dim3 = o3.object_id
left outer join hsp_object o4 on cd.dim4 = o4.object_id
left outer join hsp_object o5 on cd.dim5 = o5.object_id
left outer join hsp_object o6 on cd.dim6 = o6.object_id
left outer join hsp_object o7 on cd.dim7 = o7.object_id
left outer join hsp_object o8 on cd.dim8 = o8.object_id
left outer join hsp_object o9 on cd.dim9 = o9.object_id
left outer join hsp_plan_type p on p.plan_type = cd.plan_type;

By using the sql to create a view I am able to query the supporting detail as it is saved in planning. This view can then be queried via your business intelligence reporting tool. This view gives the user a the ability to look at the supporting detail that is stored in a planning model and the user can utilize the filter analysis functions of the reporting tool to take a close look at the supporting detail items.



I will follow up this blog entry with subsequent entries showing how to do currency conversion from local to USD with supporting detail, return the alias instead of a member name in the supporting detail record set, and utilize the planning security model to return specific supporting detail based on identity.

Division of Responsibilities Matrix for Hyperion Planning Implementations

I am often asked, "How should we delineate task responsibilities across the organization to support our Oracle Hyperion Planning implementation?" I have compiled an Excel workbook that I have shared through Microsoft Office Live Workspace which I have developed for clients in the past which provides my perspective on where tasks should reside throughout the organization.

By clicking on the title above, Division of Responsibilities Matrix for Hyperion Planning Implementations, you will be brought to the Microsoft Office Live Workspace that houses this spreadsheet for your review and use.

Please let me know your thoughts relating to this matrix, any comments are greatly appreciated

Building a Rolling Forecast in Hyperion Planning

In a time of economic uncertainty, businesses are looking for more ways to effectively plan and adapt to ever changing business climates. One more commonly occurring practice to combat this uncertainty is the implementation of a rolling forecast. A periodic “outlook” or “re-forecast” makes a lot of sense when, in a lot of ways, your annual budget essentially becomes outdated once the first month or quarter of actuals become available, maybe even before that…. The re-forecast allows you to update numbers based on more current information and assumptions, and thus, should give you the ability to make more informed decisions.

Generally, a rolling forecast is a new, forward-looking plan, based on the most current actual data. These forecasts are not done to the same level of detail as the annual budget, so the time to prepare them should be a bit shorter, as a result.

I’ve experienced both monthly and quarterly rolling forecasts. Some may say that re-forecasting on a monthly basis requires too much effort, while others may say that forecasting on a quarterly basis doesn’t give them timely enough information. My opinion would be that it depends on the business. If your industry is highly volatile and the environment changes frequently, then a monthly re-forecast might be a fit for you. However, if the business that you’re in is fairly stable and follows a cyclical path, quarterly forecasts may be all you need.

Building a rolling forecast in Hyperion Planning is a fairly common practice. As a user of Planning, that would affect both your data inputs (re-forecast of future periods) and your reporting (mix of actual months and forecast). As an application administrator, it’s a bit more complex as you would need to provide a means for dynamic dimension member updates, as well as create calculations that address the rolling functionality.

The first item to take care of is making sure the appropriate substitution variables are set up in Essbase. A substitution variable is a placeholder for members that change regularly. For example, you can set up variables for the current month and current year. Then, as those values change, you make the update in one location instead of having to go to every input form and every report to make the updates. In the case of a rolling forecast, this makes application maintenance much less time consuming.

The next item to address is the web data input forms. What I generally do is put the Year dimension in the page drop-down and all twelve months in the columns. This allows a user to toggle from one year to the next. A key to this design is to set the forecast scenario to the current period and year range so that only the forward-looking periods are open for input. One drawback to this method is that you don’t get a true rolling effect with actual periods rolling up to forecast. As a work-around, you could:

• Include the scenario dimension in the page with forecast and actual as choices.
• Create a composite web form with the second grid containing actual data.
• Create a custom menu link to a rolling forecast report.

Creating rolling forecast reports can get a little tricky, but that’s mostly due to the fact that they can accommodate more dynamic member selections compared to web forms. The column set up is the most complex portion as you have a mix of actual and forecast data, which oftentimes crosses multiple years. The report design will also be dependent on the start month for the forecast and how far out the forecast goes.

For a year-over-year forecast, you will essentially have multiple sets of columns which display or suppress depending on certain criteria, like whether or not the forecast start and current period are in the same year, for example. I generally use a substitution variable to make the criteria determination. Because of the many options in terms of forecast length and type, I won't go into detail on how to design the columns for these types of reports.

If your forecast is all contained within the same year, then one column would range from January to the current month variable and the second column would range from the current month variable offset by 1 to December.

Finally, you have calculations that need to be updated to accommodate the rolling forecast. Depending on how any trending you have is formulated, these calculations may need to incorporate cross dimensional operators to the appropriate scenario (actual, forecast). Also, when the calculations roll year-over-year, the formulas will need to appropriately account for the change from one year to the next. Usage of substitution variables is also extremely helpful here and is definitely a best practice.

When all is said and done, you should have multiple buckets for each of your planning processes. For example, you most likely would have a set of Annual Budget web forms and reports with their corresponding set of folders. You would also have a set of budget calculations. If you implemented a rolling forecast, that process would then have it’s own set of items as well. From an organization standpoint, it’s cleaner and it also makes it easier for end users to sift through.

In summary, this is generally the way I handle rolling forecasts in Hyperion Planning. Is it the only way to do it – no. I’m sure there are many other options. But, I’ve found this to be an efficient and effective way to implement the solution. If there are other ideas, alternatives, or opinions out there, I’d love to hear them. As a wise man once told me, “The best thing about Essbase calculation scripts is that there are a hundred ways to write the same formula. But, the worst thing about Essbase calculation scripts is that there are a hundred ways to write the same formula!” I guess something similar could be said about implementing rolling forecasts....

Direct Your Budgeting Process Using Hyperion Planning Task Lists

Ever wished you had more control over the budgeting process? Would you like to provide more direction to your budget managers/analysts? Then Hyperion Planning Task Lists might be just the thing for you.

Planning Task Lists guide end users through the budgeting process by directing them to perform specific tasks in a prioritized order. For example, you could start the process by having users read an instruction page that guides them through the system, details any drivers/assumptions, and provides pertinent milestone dates. After that, they could be directed to enter data for such items as new headcount, new capital, and discretionary expenses by being pointed to the appropriate web data input forms. You can also provide links to run calculations, review specific reports, and promote items up through the workflow process for approval.

Task lists are set up with links to each task and are prioritized as necessary. As part of the prioritization, tasks can also be set to be dependent on other tasks being completed first. So, as an example, prior to a user running a formula that calculates depreciation, they would first need to update a new capital web input form before they could proceed.

Other useful features of Task Lists include:

• Completion due dates and on-screen alerts: green for on schedule, yellow for approaching due date, red for overdue.
• Email alerts for tasks that are overdue. These alerts can be scheduled as frequently as every hour.
• The ability to view task status in summary and generate status reports.

Task Lists can also be viewed in two formats: Basic and Advanced modes. In Basic Mode, users only have the Task List to navigate through in the web interface. In Advanced Mode, users can view a Task List and can navigate through all the other areas in the Planning web interface.

Finally, you can set up different Task Lists for each functional area so that users responsible for sales, for example, will only see sales related tasks and users responsible for manufacturing will only see tasks relevant to them. Another nice feature is that you can set access to a Task List, preventing users from viewing other user’s lists. Task Lists can also be set up by planning process, so you could have a set for the annual budget, another set for the rolling forecast, and another for the long range plan.

Not all Hyperion Planning customers use Task Lists. But, if you are looking to provide your budget managers with more guidance and make the budgeting process more directed, implementing Task Lists might just do the trick.

Optimization of Established Planning Applications – Part 1

A common question that I am often asked when first arriving at a client with established Planning applications is, “How can I improve my applications performance.” Two short–term initiatives that can often provide more robust performance for established Planning environments are the following:
1. Remove unnecessary history
2. Reorder outlines

Remove Unnecessary History

Often an inordinate amount of history maintained within Planning applications to facilitate year over year reporting. Maintaining excessive history in a Planning application creates unneeded blocks, the greater the number of blocks, the greater the processing time for calculations. While year over year analysis capabilities undoubtedly have to be maintained, I often leverage the practice of creating a reporting application to facilitate Planning Optimization.

Native Essbase reporting databases are developed to archive historical data. These new reporting databases are based on the existing databases within the Planning applications. All data not associated with generating future budgets or forecasts from each of the Planning applications is moved to the reporting database through partitioning, xrefs or data extracts and loads. Planning data (i.e. Budgets, Forecasts, and Plans) would then be moved into the reporting databases at scheduled intervals to allow the year over year analysis, in addition to allowing for an optimal configuration of planning.

Basic steps to moving history out of Planning:

1. Create native essbase reporting cubes to archive historical data
2. These new reporting cubes should be based on the existing cubes in the Planning applications
3. Remove all data not associated with generating future budgets or forecasts from each of the Planning applications
4. Load all historical to the new reporting cubes
5. Remove dimension members pertaining to historical data and alternate hierarchies from the Planning applications
6. Integrate current year data from the Planning applications to the reporting cubes
   

Reordering of Outlines

Industry wide standards recommend that outlines be structured in an hourglass shape. From top to bottom; dense dimension members with the most stored members to the dense dimension with the smallest number of stored members. Then sparse dimension members with the least stored members to the sparse dimension with the largest number of stored members.

I acknowledge that this rule doesn’t apply to every model; however I do suggest that application administrators run multiple iterations of a baseline calculation reordering the dimension to ascertain the optimal outline order for calculation processing. Reordering outlines to increase the chance of achieving a single anchoring dimension with multiple bitmaps often result in achieving the optimal calculation performance.

Administrators can simulate calculations using SET MSG ONLY in a calculation script. A simulated calculation produces results that help you analyze the performance of a real calculation that is based on the same data and outline.

By running a simulated calculation with a command like SET NOTICE HIGH, you can mark the relative amount of time each sparse dimension takes to complete. Then, by performing a real calculation on one or more dimensions, you can estimate how long the full calculation will take, because the time a simulated calculation takes to run is proportional to the time that the actual calculation takes to run.

While these steps aren’t the be all, end all to optimization, these initial steps will help get you started.

In the next blog, we’ll address leveraging multiple plan types and calculation running in topdown and serial modes

Experiences with Hyperion Budgeting Applications

Boy, do I miss the annual budgeting process. Who can argue with long hours, strict deadlines, and countless iterations?! Ok, I may be stretching it a bit here, but I certainly have had plenty of experience with the corporate planning process in many shapes, forms, and flavors. Following is a recount of my experiences with the growth and evolution of the Hyperion planning tools.

As a corporate financial analyst in the early ‘90s, the company I worked for performed the annual budget, quarterly forecasts, and monthly reporting using Excel spreadsheets – a practice that was commonplace back then, and still is today, to a large degree. As we all know, Excel is a fantastic tool that serves many purposes. But, as part of the budgeting process, it definitely presents many challenges and leaves a lot to be desired.

Some difficulties I’ve experienced when using Excel for planning include:
• Having to consolidate a multitude of linked spreadsheets on a regular basis.
• Many sources of data.
• Time spent having to repair or update faulty links.
• A confusing maze of cell formulas.
• Trying to determine which file is the most current version.

In the mid-90s, we purchased a tool called Hyperion Pillar to facilitate the budgeting cycle. Pillar was a very good planning tool that was at the top of its class in its heyday. As a side note, the one distinct thing I remember about Pillar when I was first introduced to it was that administrator users were called Motherships and planner users were called Satellites. I think this set the product apart all by itself.

For all the things that were faulty about the naming conventions, the tool itself was very strong. You had a centralized place for standardized calculations (globals). Data was neatly categorized into different modules (expense, revenue, asset, etc.). Financial reports were built by default. There was a single, master file that consolidated in each planner’s file (yea, no more spreadsheet links!). And, it had built-in security down to the dimension level (depts, accts, etc.).

Pillar was a great tool (and still is), which aptly served its purpose for many organizations. But, like other financial applications, it also had its limitations and weaknesses. For example, the reporting aspect did not allow any real formatting or customization. The distribution and consolidation process (sending files to and collecting files from end users) was cumbersome and often resulted in mismatched data. There were limitations in dimensions, line items, and other application objects that made it unusable or inefficient for larger organizations.

I spent many years supporting Pillar while I worked in Hyperion Technical Support and one of the most frequent requests we heard went as follows:

Client: “Hi, can you please have the developers implement Print Preview”.
Me: “Um, yeah, we’ll get right on that….”

If you ever used Pillar, this was one of the biggest complaints about the product. “Every application has a Print Preview!!” For various reasons, Hyperion was not able to implement this functionality into Pillar until the latest releases. And, at that point, Pillar was in its twilight. But, again, for the few faults it had, I would still be the first to say, “I Loved Hyperion Pillar!”

Around the year 2000, Hyperion developed a new budgeting tool that was supposed to be the next generation version of Pillar. I was lucky enough to support the beta and 1.0 versions of Hyperion Planning. Now you want to talk about fun – this was fun! Well, not really…. Challenging might be a more appropriate word. As you can imagine, supporting any 1.0 version of a product is difficult. After supporting a stable, well-liked product like Pillar for many years, trying to support this new brain-child called Planning was downright impossible. We went from a propriety, self-contained program to an application that incorporated relational databases, web application servers, an OLAP engine (what the?!), separate reporting tools, calculation scripting, batching, scheduling, etc. All I could think was, “I want my mothership back!!”

But, as with most successful programs, things always get better. Having been a part of that initial release, I’ve been able to experience Planning’s tremendous growth and stability. You have a web-enabled tool that allows end users to enter their budgets over the web. No more spreadsheets, no more files - the only footprint an end user needs is a web browser. The tool incorporates built-in process management, through both the budgeting and approval process. Hyperion Planning is well integrated with its OLAP engine Essbase and we finally see the ability to perform most of the administrative functions in the web interface. And, for those who still love their spreadsheets (and I know there are many who do!) Planning and Essbase are fully incorporated with the Microsoft Office suite, using a tool called Smart View. This tool allows you to keep a dynamic link into the Hyperion database while working from the familiar Office environment.

Today, with the acquisition by Oracle, Hyperion Planning’s capabilities have expanded farther than ever before. Take reporting, for example. You have the use of the native tools, Financial Reporting Studio and Web Analysis. Or, you can take advantage of the Oracle Business Intelligence (OBIEE) suite of products. You also have more flexibility in data integration. Oracle has the tools that allow you to automate the loading of source data or metadata from your ERP, synchronize your headcount information from your HR system, or drill-down to detailed data in a data warehouse.

Going forward, I believe the Oracle acquisition will be good for Hyperion Planning as integration with other products and interfaces will become more seamless and user friendly. Who knows maybe the budgeting process can actually become pleasant? Ok, maybe the holiday punch I had was a little “strong”, but certainly the current release of Planning should be a tool that allows analysts to do more analyzing (what a concept!) and less number crunching.

What's the right level of detail to include in Hyperion Planning

When facilitating design sessions with clients, the question that invariably comes up is what level of detail should be included within their Hyperion Planning application.

Often, a clients first instinct is to include the level of detail that exists within their ERP system for actual data. When confronted with this desire/requirement, I like to encourage spirited conversation between project sponsors and subject matter experts by having them address the following two items.
1) Does a desire exist across the majority of the user base, to plan/budget at the same level of detail for which actual data occurs?
2) Does this level of detail for formulating a plan/budget coincide with management’s performance management objectives?

I have found that the best practice for the level of detail contained within a planning application or applications is that it should reflect management’s performance management objectives. Detail for detail’s sake wastes resources, but arbitrarily limiting the level of detail is not a best practice if it limits analysis against important factors that must be controlled.

If the driving desire to include this level of detail in the Planning application is only to facilitate analysis against actual data and business owners have no desire to perform data entry to each and every travel expense account for example, then other alternatives exist that will not encumber the Planning application(s).

Successful Planning implementations provide the level of detail within the planning model that business owners are accustom to and desire to formulate their Plans/Budgets. Meaningful reporting and analysis often may require further details then what users desire to plan to. This disconnect can be addressed through properly architected reporting applications, partitioning, or drill through.

When addressing the level of detail that is to be included within a Planning application, answering the two fundamental questions above has lead me to successfully architect and implement in excess of 30 Planning applications. More often than not, most clients when really pushed to consider what is really necessary from a Plan/Budget perspective versus what is necessary from a Reporting/Analysis perspective will arrive at a Planning application that is more summary level.

While meeting management’s performance management objectives for Reporting and Analysis shouldn’t be ignored, encumbering users with unnecessary levels of detail within a Plan/Budget only introduces potential for issues with performance, maintenance and end user satisfaction.

The mantra that I as well as many other seasoned EPM professionals subscribe to when asked to architect a Planning application is that, “Provide the level of detail necessary to formulate the Plan. Planning should be used as a means to facilitate Plan/Budget vs. Actual analysis, but if the level of detail that this analysis occurs at is beneath the level of Plan formulation then this analysis should be done outside of Planning (i.e. Essbase, partitioning to a reporting app, drill through).”

Different Options for Loading Data to Planning

One of the most asked questions I encounter is “How do I get my data into planning?” The answer is it depends.

For existing planning users, the old standby for loading data to planning was through the use of the Hyperion Application Link (HAL) utility. However, this utility has been phased out and is no longer supported by Oracle for use with Planning, therefore customers who are on older version of planning and want to upgrade face a brand new question: What is the HAL replacement? In most cases Oracle Data Integrator is the replacement tool for HAL, however, there are situations when a different tool is a better fit.

For new planning implementations the answer is a little more difficult due to the number of options available for loading data to planning.

There are multiple tools that are capable of loading data to planning. Each tool has its own pros and cons in regards to loading data to planning. The options for loading data into Planning are:

• Oracle Data Integrator (ODI)
• Data Integration Manager (DIM)
• Financial Data Quality Management (FDM)
• Enterprise Performance Management Architect (EPMA)
• Spreadsheet lock and send

I have worked with each of the tools listed and found that there is a lot of overlap in the tool choices so I decided to provide a brief description of the tool along with the strengths and weaknesses that I have found.

Oracle Data Integrator (ODI)

Oracle Data Integrator Enterprise Edition is a comprehensive data integration platform that covers all data integration requirements from high-volume, high-performance batches, to event-driven, trickle-feed integration processes, to SOA-enabled data services

Strengths

• High performance, E-LT architecture
• Does not require new servers/hardware; scales with existing source and target hardware
• Bundled with Hyperion Planning - No Extra Cost
• Oracle Strategic Tool for Data Integration
• Works with data and meta data

Weaknesses

• Usually IT Managed. There is a web application to enable business users to execute routines as needed.
• Hyperion Knowledge modules 1.0 release
• Not fully integrated with all Hyperion Products
• Smaller resource pool to pull from

Data Integration Manager (DIM)

Oracle Data Integration Management is a data integration platform that enables companies to access, integrate, transform, and move virtually any type of data between Hyperion and virtually any system. DIM is built off of the Informatica Platform.

Strengths

• Full scale ETL tool
• Flexible and Powerful
• Time proven track record for ETL excellence
• Based on Informatica - Lots of experience in Marketplace
• Works with data and meta data

Weaknesses

• IT Managed
• Additional Licensing Required
• Additional hardware required (unless PowerCenter already exists)

Financial Data Quality Management (FDM)

Financial Data Management helps to increase confidence in the numbers and lower costs of compliance by eliminating data integrity risks associated with the collection, mapping, and verifying by users of critical financial data from across the enterprise

Strengths

• Provides Business Mapping Layer
• Provides Drill back to transactional detail
• Provides drill through to select ERP systems
• Business Managed
• Auditable

Weaknesses

• Does not handle complex transformation
• Limited Functionality
• Very limited source and target capabilities
• Limited Automation capabilities
• Additional Licensing Required
• Only works with data

Enterprise Performance Management Architect (EPMA)

EPM Architect helps users configure common dimensions from an extensible library, link these dimensions to different applications, re-use or move artifacts from one application to another, and graphically manage data flows between applications. EPMA does have a data load mechanism but it is fairly limited.

Strengths

• Manages meta data
• Allows for data sharing between applications

Weaknesses

• Not a direct to source system - still requires data process to get data directly from data source
• Very limited source and target capabilities
• Limited automation capabilities

So the big question is which tool do I use. I mentioned earlier that it depends. Each tool will load data to planning but each tool has its strengths and weaknesses. When determining which tool is the right tool to choose, I consider the below questions.

1. What is the license enablement
2. Types of Data Sources – Flat files or direct database connections
3. Level of Automation Required for the load process
4. Data mapping requirements
5. Does the load process require auditing capabilities
6. Who will own the data load process - IT or Business Analysts

This will give you a start in selecting the right tool. Each tool has its own set of integration options so I have summarized the tools and the data load options in the below chart to help determine which tool is the right tool to use.

Conclusion

I usually lead with Oracle Data Integrator in new environment architecture and in most cases use ODI as the tool for loading data into planning. However, FDM does fit in many environments where there is less IT involvement and business will manage the entire load process. FDM is also the only tool that provides drill through from planning to transactional detail or to the ERP system. DIM is a great alternative if you have in house Informatica expertise and you don’t mind spending the extra licensing costs to enable DIM.