Essbase Business Rules versus Calc Scripts – Which Way Do I Go?

 digg it |   delicious |   StumbleUpon |  reddit 

We've had a number of clients ask questions regarding Essbase business rules and calculation scripts - which one should I choose? It's a good question. They are very similar in nature and both can be launched via Essbase Administration Services console and the Planning web interface. Today, we will attempt to identify some subtle differences that you can utilize to help make the decision.

Scheduling Differences

It's common to create MaxL or Esscmd scripts to perform maintenance on Essbase applications. Many times, these consist of clearing and rebuilding an outline, loading some data, and then calculating the database. When this is the case, calculation scripts are the best choice. Business rules cannot be launched via Esscmd or MaxL.

However, if it is only a calculation that is required, business rules can be launched via a command line (CmdLnLauncher) and scheduled. So, in cases where you simply want to perform a calculation or series of calculations, business rules can be created and scheduled without the need to create additional MaxL or Esscmd scripts to schedule the calculation execution.

Business Rules Advantages

Macros and sequences - Business rules provide the option to use macros and sequences. Macros are mini rules that can be reused across multiple rules. Additionally, the macros and business rules can be sequenced to order the rules based on business requirements.

Run-time prompts - Business rules have the ability to use runtime prompts.  A value or dimension member name can be entered by a user via a prompt or read directly from a Planning form. The business rule then becomes dynamic in nature through the use of the run-time prompt.

Security - Many assume that business rules can only be utilized within the Planning product. This is incorrect. Business rules can also be executed against standalone Essbase cubes. An advantage of the business rule approach is that business rules can be included in a project within Shared Services, and security can be assigned and managed at the project level rather than against the individual business rules.

Calculation Script Advantages

Debugging - Calculation scripts provide a much better environment for debugging any errors that may occur. Calculation scripts provide much more detail in the error messages generated. Calculation scripts will provide the user and line number in the script where the error occurred. Business rules, on the other hand, will not show the user executing the rule, merely the user associated to the data source name.

Script Creation and Maintenance - It sounds simple, but the ability to perform search-and-replace functions in the calculation script editor is definitely better than the find only feature within the business rules editor. Also, you can't perform an Undo function within the business rules editor. Finally, validation is easier in the calculation script editor, since there is no requirement to save the script prior to validation.

Hopefully these tips will help you make a more informed choice the next time you have to.

Hyperion Planning

 digg it |   delicious |   StumbleUpon |  reddit 

Good news for Hyperion customers. Oracle will soon release Oracle Enterprise Performance Management System Release 11.1.2. And with it comes some great enhancements to the Oracle Hyperion Planning application-a core component of Oracle EPM.

Already a flexible web- and Microsoft Office-based planning application, Hyperion Planning supports enterprise-wide planning, budgeting, and forecasting. Hyperion Planning 11.1.2 offers  help for organizations by providing planning data in a fast, efficient way, ensuring that your entire organization is accountable for the submitted and approved planning numbers.

Here's a rundown on some of the latest features of Hyperion Planning:

Data Form Designer

• Web form designer features drag-and-drop technology
• Uses a composite form layout manager
• Layout Manager offers dynamic context-sensitive property setting
• Data validation rules allow data to be validated against thresholds

Process Management

• Enables users to add another tier dimension to the Planning Unit definition depending on where the user is in the workflow process
• Offers enhancement to associate business users for validation and submission
• Provides new promotional rules, delegation, and out-of-office assistant features

End-User Experience

• Features adhoc grids
• Provides the ability to save personal views
• Offers new sorting and filtering capabilities
• Allows display of consolidation operators

SmartView for Planning

• Full forms technology available in SmartView
• Features data validation
• Provides traffic lighting
• Offers task lists integrated into Outlook and Excel
• Provides composite forms using tabs
• Allows copying of versions

Interested in learning about Crown's Hyperion services and solutions, including how you can get the most from Oracle Hyperion EPM? Email us at info@crownpartners.com.

Best of both Worlds: An Essbase solution using both block and aggregate storage

 digg it |   delicious |   StumbleUpon |  reddit 

Written By: Dennis Hogan

Best of Both Worlds:
An Essbase Solution Using Block and Aggregate Storage

Crown Partners recently undertook a complex project where we actually combined the use of Block and Aggregate storage to accomplish our client's goal. Here's what happened:

The Mission 

1) Build a solution with 7 total dimensions (3 of which contain several thousand members);

2) Two of the dimensions share the same base level members. These cannot be alternate hierarchies-they must be presented to the user in separate dimensions;

3) Include complex calculation logic to perform allocations.

4) Include 10 scenarios that all need to have the calculations run against them;

5) Include 5 years of actual data for historical trending and one future year for plan and forecast results; and,

6) Process updates nightly in 4 hours or less.

The complex calculation logic suggests the need for the robust calculation engine that Block Storage (BSO) offers. But, the significant volume of historical data, dimension sizes, and processing window suggest the need for an Aggregate Storage (ASO) solution. Which one should you use? The answer is BOTH!

The Solution:

Here's a high-level view of how we made it happen.

Step 1: We created three BSO cubes. Each one contained a subset of the data:

        Cube A:  Current Year and Last Year - Actuals only

        Cube B:  All other History Years - Actuals only

        Cube C:  Forecast and Plan data for Current Year and Next Year.

Step 2: Each BSO cube contains one less dimension than defined in the mission. In the BSO cubes the two dimensions that share the same base are combined as alternate hierarchies. These BSO cubes are used for pre-processing only. Users do not see the combined dimension.

Step 3: We created one ASO cube. It contains all the data described above and includes separate dimensions for the items that were combined as alternate hierarchies in a single dimension for the BSO cubes.

Step 4: Only cube A processes nightly in the 4-hour window


Step 5: Cubes B and C are processed as needed when history, forecast, or plan data is revised. This processing includes all allocation logic. The results of the processing are then exported to text files using data export calculation scripts.

Step 6: Nightly processing runs Cube A and executes all of the complex allocation logic. Results are then exported out to text files using export calculation scripts. 


Step 7: The text files from the nightly processing are then loaded into the ASO cube along with the files from the other cubes that were created prior to the nightly process launching.

Step 8: As part of the load process, the alternate hierarchy used in the BSO pre-processing cubes is split into 2 separate dimensions for presentation to the users.

Step 9: The end-to-end processing time for the nightly process, including significant levels of aggregation in the ASO cube, is just 2 - 2.5 hours.

This is a high-level overview of our approach. Interested in the details? Feel free to contact me at dhogan@crownpartners.com.

Face to Face Networking in Ohio

 digg it |   delicious |   StumbleUpon |  reddit 

Written by: Dennis Hogan

Social and Professional networking sites are all the rage.  The list of sites is long including Facebook, Twitter and LinkedIn.   We use text messages and "IM" software to communicate with people in the same building, often as close as the next cubicle.   Blogs and Message boards offer great options for educating yourself or finding solutions to issues you may be experiencing.    Since you are reading this  blog entry, you obviously believe that these technologies offer us great advantages in communications.  With all of these communication options available, does it make sense to take time out of our busy schedules to engage in face to face networking opportunities like user groups?   Yes it does!

In the past I regularly attended and presented at Hyperion User Group meetings across Ohio.   These sessions were widely attended and produced great opportunities for information exchange between individuals using the Hyperion product line to meet business needs.     After the Oracle purchase of Hyperion, these conferences seemed to fade out of existence.   About a year ago I began working with Jon Riley of the Ohio Valley Oracle Application User Group (OVOAUG) in an effort to bring an EPM/Hyperion track into their meetings.     While the turnouts have not hit the level of the old Ohio Hyperion group meetings yet, we have had good numbers and expect to continue to grow the attendance numbers with each meeting.    

The content of the breakout session presentations for the EPM track at the OVOAUG meeting has been excellent.   However, what I believe is more important is the level of interaction that we have seen.   Virtually every presentation has gone "off-script" due to questions or comments from attendees in the audience.    This was so prevalent that our last meeting actually included a panel discussion where a handful of long-time Hyperion users opened the floor for any questions the audience wanted to tackle.     Some of the topics covered were lessons learned, best-practices, upgrade plans, product selection criteria, and complimentary products.    

Beyond the fact that the meeting itself offers great exchange of ideas, many of the attendees have established trusted advisor status with one another.   They now have peers that they can call or email to discuss a specific issue or to debate alternative approaches.  

Whether you agree with me that conferences offers a great opportunity for information sharing,
or you simply long for the "good-ole days" of face to face communications, please consider joining us for the next OVOAUG meeting in Cincinnati (details and link below).     See you there!

What:                    Ohio Valley Oracle Application User Group Meeting (OVOAUG)
When:                  February 19, 8:30 - 4:00
Where:                                 CrossRoads in Oakley

For more information go to http://ohio.oaug.org/index.html

The key to a happy life might just be a well designed Essbase Cube

 digg it |   delicious |   StumbleUpon |  reddit 

The Key to a Happy Life Might Just be a Well-Designed Essbase Cube.

A recent ‘Biggest Loser' contest in the office had me thinking about data. OK, pretty much everything in my life makes me think about the data-it's a job hazard. But this contest had me tracking things I'd never paid much attention to. I'm counting calories at every turn, weighing myself every morning, charting my progress in a spreadsheet, and forecasting my percentage of weight loss as well as that of my competitors.

And that got me to thinking...

Science and technology have us at a point that we can track virtually unlimited things about ourselves. We carry personal devices with computing power that makes even our desktop computers of 5 years ago seem silly. Devices that can track our location and the location of our friends ride around in our pocket like a pack of chewing gum (low-cal sugarless gum, of course...but I digress). Modern software has taken to whole new levels our ability to track our personal finances, personal schedule, blood sugar levels, the pace of our heart, the words we speak, the number of key strokes we type, and on and on. With very little effort, and with a high degree of accuracy, we could track where we spend out time (and with whom we spend it), the pattern and intensity of our speech, and more. (Btw-I'm guessing there are a wireless scale and an app to read and chart your weight. If not, there should be.)

So with some investment in portable data collection apps, we could track an endless amount of data about ourselves. Very cool! But what does it all mean? I don't know, but I can imagine. I can imagine the possibility of saving a few bucks personally (and tons of carbon emissions globally) when we pull the data together and realize that on days when our schedule is most busy, our gas mileage suffers most (triggering a schedule minder alert on busy days reminding us to not be so heavy on the pedal). Or even better, what if we could measure happiness? They used to have mood rings that purported to monitor our every mood. So what if we had something that actually quantified that mood, logged it, and correlated it against every other thing we could choose to measure-like how much sleep we get per night, how much time we spend with our loved ones, and so on. Sweet! Next step? Let's drop that data in an aggregate storage cube, layer on Visual Explorer, and suddenly Larry Ellison becomes our self-help guru and puts Tony Robbins out of business (look for project Data Utopia to kick off in the near future ;-)!

OK, so happiness isn't necessarily a practical measure to capture, but maybe there is something with a more immediate return. Perhaps through capturing our own personal metrics we can correlate our own productivity to our commute, time with family, or even the right number of calories! Or, perhaps to the horror of the ACLU, corporations will get involved and seek to identify statistical consistencies in their top performers. Who knows where the collective corporate imagination might lead. Perhaps they will ask their top sales people to self-collect data, looking for what common traits they possess in an effort to increase the productivity of the entire force. Or insurance companies will offer us substantial discounts for providing them with constant weight measurement. Perhaps they'll even quantify exactly what work-life balance really does produce the best result. 

Whether or not we ever reach such heights of data analysis, PDAs are becoming more powerful and ubiquitous every second-and the amount of data being collected about us is on an exponential growth curve. It seems only logical that the two collide.

Article written by Abe Combs
(Who, at time of authorship, was exactly 20lbs lighter than his starting weight. )

Getting the Most from Data Relationship Management

 digg it |   delicious |   StumbleUpon |  reddit 

Written by Kevin Barth

Crown customers are always looking for ways to leverage technology for improved business and IT operations. With a strategic use of hardware and software, you can cut costs, simplify processes, boost productivity, and on and on. When we see companies deploy new Oracle enterprise performance management systems, for example, they see immediate and widespread returns in the form of improved understanding of corporate finances and operational data. But hidden in the databases that store critical corporate data are opportunities for less obvious but nonetheless weighty business and process improvements.

We're talking about data relationship management. You might already be considering the Oracle Hyperion Data Relationship Management solution or may have even purchased it and are looking to implement it soon, or maybe you've already installed it. But regardless, Crown can show you specifically how the solution can help you proactively manage changes in master data across operational, analytical, and enterprise performance management (EPM) silos. We can also help you implement and maintain it. And for companies that have already deployed it, we can help you get the most from your investment.

When companies are benefiting from a strategic implementation, Oracle Hyperion Data Relationship Management builds consistency within master data assets. This is especially difficult to achieve for companies with endless changes occurring within the underlying transactional and analytical systems. Yet Oracle's solution-the first-ever data model-agnostic master data management (MDM) tool-delivers financial and analytical MDM in these dynamic business environments.

Here's how it works. At the highest level, the solution delivers accurate and timely master data to support day-to-day operations, EPM, and business agility. And that's whether you're processing analytical master data such as reporting structures and business dimensions, or financial master data such as accounts, legal entities, cost center, and other hierarchies.

Taking a closer look, it offers other important capabilities. Hierarchy management is simplified and improved, with drag-and-drop hierarchy maintenance, side-by-side hierarchy comparison, one-click navigation, and built-in referential integrity. Change management and control functions include reconciling cross-functional perspectives to a master record, with the flexibility for users to make changes through customized views. To meet audit and compliance requirements, you can query, view, and report on point-in-time or a full history of master data changes. It also allows hot-pluggable integration with workflow tools using web services.

So what does this mean to you in terms of business benefits? Users can easily make data changes using the solution. You can reduce or eliminate errors in data flow between analytical and operational systems, plus ensure consistent corporate definitions and metrics. You'll also save time and resources typically spent reconciling reports across business units. Manual IT data maintenance tasks are minimized while you maintain data integrity across systems and departments. You can even enhance data integrity by performing what-if scenarios and impact analyses. And importantly, you can achieve compliance with Sarbanes-Oxley and other industry regulations.

This all sounds great to companies and IT departments hoping to improve their MDM activities around EPM master data. But unless you're equipped with the best-practices know-how on implementing, using, and maintaining Oracle Hyperion Data Relationship Management, you'll be challenged to achieve its intended results. But that's where Crown Partners comes in. Whatever your stage of implementation-from still shopping to already purchased and installed-we can help. Some of the related services Crown offers include:

• Conducting a requirements review with selected business and IT users
• Analyzing existing EPM application metadata
• Developing a working DRM model to integrate the master data from your existing EPM system
• Producing relevant design documents
• Implementing account and entity hierarchies and reports
• And more

Whatever your needs around Oracle Hyperion Data Relationship Management, we offer the services to get you there. We're a Certified Oracle Partner with extensive experience in successfully delivering solutions for the Oracle Hyperion platform. With Crown's assistance, our customers achieve better ROI from their Oracle Hyperion solutions, while knowing they can depend on Crown for any future Oracle Hyperion needs.

What's next for Essbase?

 digg it |   delicious |   StumbleUpon |  reddit 

Whether it's new music or new software, you can rely on consumers looking down the road to the next big release while the current one is still warm in their hands. And so it is with Essbase 11.1.1.3. While Oracle hasn't yet committed to a release date, they did whet our appetites with some enhancements for 11.1.2 at OpenWorld in October.

It's pretty clear that the Aggregate Storage Option (ASO) figures heavily in 11.1.2. ASO will feature functionality like that of the procedural calc engine used now in the Block Storage Option (BSO). What does that mean for geeks like us? It means you can run a calc script in ASO (without MDX) and the data can be stored directly in the cube. There may be some limited functionality within the calc engine initially, but it's a heck of a start. ASO will also support the predefined allocation function. Lastly, the calc engine will be exposed within the Java API and MaxL.

From an enterprise-ready point of view, there are a few enhancements that, while they're not terribly sexy, are definite improvements from an infrastructure standpoint. These include process monitoring, status dashboards, identification of key metrics, centralized logging, and error identification. Oracle also revealed some Essbase Studio improvements, such as the ability to move, copy, and backup the import/export catalog.  Additionally, there is increased support for ODBC DSN sources and improved data source synchronization. 

If you're looking down the road already, these enhancements show all the promise of improved performance and usability.

Tech Spotlight: Configuring FDM & ASO

 digg it |   delicious |   StumbleUpon |  reddit 

-Written by Abe Combs  

FDM will work with aggregate storage (ASO). It also works with Internet Explorer, Adobe Acrobat, Notepad, and the newest KitchenAid food processor.

Okay, sarcasm aside, FDM has a little-used feature call mShellandWait-a subroutine that lets you call pretty much any external executable code, whether it's a batch file or an executable. Even a batch file you would use to call a MaxL process that builds or loads an aggregate storage cube.

But, the operative words here are "will work", which are different from "is supported". While FDM does not support aggregate storage, you could use the mapping and import formatting (both strengths of FDM) to get a file all the way through to export where you would then launch a MaxL process (via a batch file) that loads and aggregates an ASO cube.

No, it's not ideal; you're flying blind to a degree. You have no interface into the executable you launch, so you can't be sure it was successful. But, if calling MaxL is your goal, and you're a little creative, you can use MaxL's ability to write error logs and use FDM scripting to read the logs and send emails-and in the process make up for a lot of the shortfall.

If you're still with us, here's even more excitement: you can use FDM to call any executable-so in some ways you're limited only by your imagination. One of our favorite uses of the mShellandWait command is in conjunction with Hyperion Reports. Hyperion Reports' batch scheduler actually allows you to execute a report from a batch file on the fly, and email the results to a select audience. So once FDM has sent you the email confirming successful execution of your cube/HFM build process, you can send yourself an email, using mShellandWait and Hyperion Reports, that checks the database for validity. Did the balance sheet really balance? Do those alternate roll-ups tie out? Or, you can be a real hero and send the accounting department the first-look P&Ls during close week.

Oracle Hyperion Planning Guidelines: “Planning” before a Planning Implementation

 digg it |   delicious |   StumbleUpon |  reddit 

-Written by Chris Harris

Oracle Hyperion Planning is a great tool for the forecasting and budgeting process.  The ability to both gather and report a wide array of budget and forecast data in a central system is invaluable to an organization of any size.  Include the Capital Expenditures (Capex) and Workforce modules and Planning can handle nearly any requirement of a company's planning process.  However, if a company fails to properly plan for the new system it will be very difficult to realize the benefits offered by Oracle Hyperion Planning.  The following are crucial items to keep in mind when implementing a new planning system.

Define business processes prior to implementing the system - It is important to have a clear understanding of what the underlying business processes are going to be prior to the start of an implementation.  This may seem obvious for existing processes, but it also applies to new processes that will be implemented with the system.  If these processes aren't clearly defined and understood by both the business and technical professionals prior to the implementation the details could easily derail the project.

Identify inefficiencies with the current system and plan to change as part of the implementation - Oracle Hyperion Planning definitely facilitates the budgeting and forecasting cycle, but building a planning system around flawed processes won't fully leverage the tool.  Even worse, incorporating these items into the system can delay implementation and take away from the areas where Oracle Hyperion Planning excels. Many times these inefficiencies appear to be must haves because they have been part of the cycle for years, but close examination will probably show that these processes are costly and at the same time add little or no value.  Because these processes are so deeply engrained they can be the hardest to challenge, but doing so will ultimately improve the Hyperion Planning system.  The Oracle Hyperion Planning implementation should be more than an implementation of software, but also a refining of processes.

Focus on Pain Points Steps should also be taken to flag pain points and ask why they are problematic.  Many times the answer is collection and organization of the process.  This is where the out of the box functionality of Planning comes to the rescue.  However, a lot of these issues are a result of offline calculations and data gathering.  Many times this will require customizations to the system in the form of custom business rules and data forms.  It is crucial to identify these items prior to implementing.  Most often this is done by including the right players in the design process and sessions.

Don't try to do more than planning with the Planning system - When designing a planning system it is crucial to know "the right tool for the right job".  Depending on the complexity and requirements, it may be necessary to keep processes like allocations and management reporting downstream from the Planning system.  It is very important for an Oracle Hyperion Planning system to be quick and nimble.  Many users will simultaneously be using the system and the more extraneous processes incorporated the slower the system will be.  Identifying processes and reporting that is not essential to the budgeting and forecasting cycle and moving these into other tools will ultimately benefit both those processes and the Planning system.

Executive management buy in is key - It is crucial to involve executive management in the initial stages of an Oracle Hyperion Planning implementation and to keep them updated throughout the project.  In addition to sponsoring the project financially, these individuals will ultimately be the audience for the output of the system.  Involving this group early and often will ensure the system meets their needs and delivers value added output.

Implementing a new budgeting and forecasting system is a big undertaking that requires the input, effort, and cooperation of many players across the organization.  Planning ahead and considering the items above will contribute to the success of the Planning system implementation.

Essbase Partitioning: Divide and Conquer Your Processing Woes

 digg it |   delicious |   StumbleUpon |  reddit 

-Written by Kevin Barth 

A client of ours was having some issues with a completing a complex series of expense and revenue allocations against their budget and actual data (stored in Oracle Hyperion Planning) within a tight calculation window. This data is used to support P&L reporting at a business unit level on a monthly basis. During the close period, drivers are modified and a quick turnaround of results is needed and their current system design was not providing the necessary results fast enough.

Not to go into too much detail but the business is rather large with 1000s of cost centers and accounts. Allocations spread these dollars across three different dimensions: business solutions; products; and markets.  A small change in one business area can have an impact on any of the other dimensions within the application.

During our analysis, we were able to isolate "sets" of allocations that were able to run independently from the other allocations. We determined that trying to accomplish all of this within the Planning application probably would be difficult as there weren't a lot of efficiencies we could take advantage of and still maintain the dimensionality required for Planning. Instead, we decided to take advantage of the "isolation" levels of the data and use the power of Essbase to help solve our problem.

There were two obvious sets of allocations: the Business Solutions/Products and the Markets. We used a series of calc scripts and MaxL to extract the data required for the allocations from Planning and load the data into two smaller Essbase cubes designed specifically to handle each set of allocations. The  two new cubes were given dense/sparse configurations to allow the allocations to execute as quickly as possible. These cubes were able to run the allocations in parallel and complete in the required timeframe because the dimensionality and data sets were limited to those required for the allocations processing.

So the allocations now ran fast enough but we still needed to pull all of the data back together to allow P&L reporting to occur with as little confusion as possible for the user. The solution - partitioning. We created a transparent partition over the three cubes (Planning; Solutions/Products; and Markets). This provided a "one stop shop" for all the users reporting needs. Through the use of a "Source" dimension, this partitioned cube enables the users the flexibility to view the actual/budget data in raw form or after the allocations - or both!

The partitioned solution allows the users to run processes against any of the three source cubes without affecting the other two. Because it is a transparent partition, we are able to map different levels of data granularity to the reporting cube with no impact on processing time.

A little "out of the box" thinking allowed us to provide a solution that exceeded the client's requirements.