Essbase on Unix: Tips and Tricks

Oracle's Hyperion Essbase is a multidimensional database primarily utilized for providing robust analytical capabilities. Real-time slicing and dicing of data, exploration of KPIs and their material basis, and decision making assistance make Essbase an indispensable financial engine for organizations of all sizes. This blog addresses some of the lesser known tips and tricks with running Essbase on Unix. Linux, AIX, Solaris, and HP UX are all supported, see the Oracle web site for a full list of supported operating systems, including specific release notes.

1. Essbase startup
Oracle Essbase has a preference for running under a C or Bourne shell. Often, this conflicts with the default shell on many installations (Korn). Ensure that your startup script for Essbase begins with "/bin/sh". Also, avoid using "nohup" (it is not necessary for a C or Bourne shell; it is only needed for a Korn shell)- nohup has been shown to cause a race condition in larger installations when making changes to the security file. For some reason, the default startup script included with the version 11 release does not follow the documented convention to start Essbase; if you're having strange problems with your installation I would recommend making changes to this script to make it similar to the documented startup script. For an example startup script see the Essbase Technical Reference chapter "Maintaining Essbase", section "Running Essbase Servers, Applications, and Databases." (the example script also shows how to hid the console password from nosey users on HPUX/Solaris systems)

2. Environment variables
Oracle Essbase requires certain environment variables to be preconfigured for correct operation. These variables are typically documented in a file named "hyperionenv.doc" on the Unix server during installation, however the convention utilized does not export the variables. Ensure that the processes that start Essbase, as well as any scripts calling Esscmd or Maxl scripts, have their environment configured by using this script as a template (remember to export the variable settings).

3. Filesystems
Essbase doesn't like NFS. It gets all whiny and fussy, like a 6 year old that can't have cake. In all seriousness, Essbase's IO requirements and file locking characteristics don't mesh well with filesystems mounted via NFS. This problem is particularly troublesome for ASO database, but affects BSO databases as well. To sum it up, using NFS for Essbase is not supported, so save yourself the headache- don't do it.

4. Stopping Essbase
No one runs Essbase in the foreground, so you can't just type "exit" to stop Essbase. Essbase can be stopped via Administration Services by manually selecting the server and selecting Stop. Alternatively, scripts can be written on the Unix server utilizing either Maxl or Esscmd to stop the server. The relevant Maxl statement is "alter system shutdown" (after logging in to the server) or "shutdownserver" in Esscmd (no login necessary, as the login information is used as a part of the shutdownserver command syntax). Whatever you do, do not "kill" the Essbase server- at worst you're risking serious database corruption, at best you're applications will need to go through free space recovery on startup- a time consuming operation that is unavoidable when the server is killed and which prevents users from being able to connect to their applications and be productive. Note- the version 11 installation does not include a shutdown script; you'll have to write your own, and add it to the appropriate place in the EPM shutdown script to properly stop your server.

5. Best Practices (not necessarily for Unix, but for Essbase administration in general)
Consider carefully the tradeoffs with any of these recommendations. They're all made with the assumption that their advantages outweigh their disadvantages.

• Database restructures (particularly for read/write applications) - performing periodic full database restructures (using Maxl statement "alter database application.database force restructure;") will improve performance, and reduce free space recovery time if a crash does occur.
• Place the application in read-only mode before shutting the server down - this forces the Essbase server to write all open files held in memory to the disk, in particular the Free Space file.
• Fully shutdown the server before performing a backup. Applications can be backed up in read-only mode while the server is running, however the Essbase security file can not. Shut the server completely down to make a backup of this critical file.

Video: Maximizing Oracle Business Intelligence Adoption: Amway and LCRA

A customer panel at a recent conference analyzed different approaches to maximizing user adoption for Oracle Business Intelligence. Business Intelligence Consulting Group moderates this customer panel, which features representatives from Amway, Lower Colorado River Authority (LCRA), and BICG.

Financial Reporting Studio Report Design & Development Considerations

As a standard part on any EPM implementation the output from the culmination of the all project work (requirements, design, development, testing, etc.) is reporting. Typically, prior to an EPM implementation for Planning or Essbase, client reporting is typically performed in Excel spreadsheets. Thru the use of Excel spreadsheets reports are developed and consolidated into a reporting package. Utilizing Excel spreadsheets for financial reporting is fraught with many pain points. Making changes to multiple worksheets within a single workbook is often a tedious and time consuming task as well as the potential for human error in terms of incorrect cell references and worksheet links. I’ve worked with clients who have had to change dozens and dozens of worksheets in preparation for the next budget cycle. This process took weeks and weeks to complete and the owner of this process was never sure that all the spreadsheets were absolutely correct. The lack of flexibility with an Excel-based reporting is significant.

Oracle’s Hyperion Financial Reporting Studio (FRS) relieves companies of this problem. We’ll be discussing high-level reporting strategies to consider when creating reports using Oracle’s FRS.

Report Design Considerations

1) Determine the set of reports to be developed in FRS – this is a critical aspect of report development and should be determined during the requirements gathering process of a project but should be revisited as you move closer to the report development cycle to ensure the report set is still in alignment with the project requirements. In working with clients, the most successful report development phase of projects has been where client have been able to provide a full set of static reports not only for the current cycle but reports for a full year. This will provide you with the ability to determine if there any anomalies or differences between the reporting cycles.

2) Dynamic Reports – the objective should always be to develop dynamic reports because of their inherent ability to reduce maintenance. Dynamic reports should include:

a. Use of Essbase substitution variables. The benefits of utilizing substitution variables include reduced go-forward report maintenance and provides client with the ability to reuse a set of reports for different reporting cycles.

b. Use of relationship members rather than hard-coding dimension members. This will reduce report maintenance when new accounts, dept members, etc. are added to the Essbase outline. Instead of going into every report to update the particular row or column, the new member will automatically incorporate appear in reports. For clients that have a significant number of FRS reports the potential on-going time-savings is considerable.

c. Use of FRS functions. FRS provides a series a pre-built functions that enable report developers to develop reports that are dynamic in nature.

3) Report Consistency – reports should be developed with a consistent “look and feel”. Rows and columns should have a consistent height and width, spacing rows and columns widths should be consistent as well. Fonts should be consistent across reports. Headers and footers should be consistent across all reports.

By following these guidelines during the report developed process, clients will have the ability utilize FRS to its full effectiveness and have in place dynamic flexible reporting that will create more time for analysis of the data rather than the ticking and tying process. In addition, the ability to reusability of dynamic reporting and reduced maintenance and preparation for the future reporting cycles will enable will provide organizations the insight to their business that may not have had previously as well as provide consistent reporting themes across the organization.

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....

Article Features Kaleida Health’s Use of OBIEE to Combat the Flu

Kaleida Health’s Flu Monitoring Dashboard was developed quickly using Oracle Business Intelligence Enterprise Edition to provide insightful reporting regarding patients with the flu, including H1N1.

The innovative use of Oracle Business Intelligence was recently detailed in a SearchOracle.com article. Kaleida used their Oracle Business Intelligence Enterprise Edition (OBIEE) dashboard technology for analyzing emergency room visits where people arrived at a clinic with flu-like symptoms, tracking various metrics that included what time of day or night the visit took place. With this information, Kaleida was able to effectively calculate when a spike may occur so they could start diverting patients with flu-like symptoms to another clinic, minimizing the possibility of flu patients infecting others in a crowded waiting room.

Read More...

A Better Average

Past Performance is no guarantee of the Future

How many of you have a metric based on an average over time (i.e. Average Sales for the past 12 months)? Simple mathematical averages are a great tool to quickly compare results to an expected result, based on historical performance. Unfortunately, in their simplicity also lies a key problem: They assume results will be flat, whereas real-world results will often plot onto a curve. Often, changes are not linear either due to seasonality, or an inherent exponential factor underlying a result.


Seasonality

Few, if any, businesses do not have any seasonality. Retail sales are greatest toward the end of the year, tourist destinations have the highest bookings during their respective high season, and real estate transactions occur more frequently in the spring. Activity for these businesses, at least in part, is affected by external factors causing results to fit a curve.


Exponential factors

While not necessarily readily apparent, often activity within an organization will change in an exponential manner.

• A simple example is interest income: Over time interest earned on an investment will exponentially increase due to compounded returns (this assumes no drawdown of the investment, as well as reinvestment of returns). Depending on the size of the investment, over a short period it can be feasible to assume a linear growth, but this will introduce greater and greater variances over time.
• A more complex example can be seen in revenues. Often, a portion of revenues are reinvested into generating greater revenues. Conceptually, this scenario is similar to the compounded interest scenario. This differs in that it is often difficult to determine a rate of growth based on an amount reinvested in a business.

Essbase to the rescue!

Among Essbase's many built-in functions is @TREND. In layman's terms, @TREND calculated a weighted average of a series of values. Options allow for fourdifferent weighting algorithms:

• Linear Regression - Standard linear regression (similar to a typical average), with an option to assign priority to points to adjust the importance of certain events.
• Single Exponential Smoothing - Weighting system giving more importance to earlier values than later values. Allows for an adjustment to how much more weight is applied to earlier values.
• Double Exponential Smoothing - Similar to Single Exponential Smoothing, but includes an additional adjustment to influence the resulting slope, or curve, of the result.
• Triple Exponential Smoothing - Builds on Double Exponential Smoothing with a third influence factor. This algorithm is particularly useful for seasonal values.

Some points to consider:

• #MISSING values: some algorithms remove #MISSINGs from the list of values (i.e. they are not treated as zeros), other algorithms do not allow any values to be #MISSING.
• Usage: The function may only be used in a calculation script; it may not be placed within a member formula. Inside of a calculation script the function must be associated with a member.
• The order of members passed to the function will influence the result, since weights are applied differently depending on where in the list a value appears. Consider carefully the order of members in the outline, whether that order will change, and what impact that will have. It may be useful to utilize the @LIST function to hardcode a specific order.

For more information on @TREND, see the "Trend Calculation Function" in the Essbase Technical Reference (this is available via the Oracle website if it is not installed on your system).

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.