Current Articles | RSS Feed RSS Feed

Essbase Partitioning: Divide and Conquer Your Processing Woes

Posted by Kevin Sahnd on Thu, Sep 17, 2009 @ 12:21 PM
Submit to Digg digg it |  Add to delicious  delicious |  Submit to StumbleUpon StumbleUpon | Submit to Reddit 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.

Questions or comments? info@crownpartners.com
Tags: , , ,

Comments

Currently, there are no comments. Be the first to post one!
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics

Current Articles | RSS Feed RSS Feed

Product Focus: Oracle EPM Fusion Edition (11x)

Posted by Doug OKeefe on Tue, Jun 23, 2009 @ 01:48 PM
Submit to Digg digg it |  Add to delicious  delicious |  Submit to StumbleUpon StumbleUpon | Submit to Reddit reddit 
When Hyperion System 9 was released almost four years ago, it was touted as a true Enterprise Performance Management (EPM) system. It was eagerly awaited as the Hyperion software package that would finally unite the various non-communicative Hyperion products. In truth, the initial release (9.0) was somewhat underwhelming. Shared Services and the Workspace were introduced as a common administration console and repository for reporting objects, but the applications themselves were fairly independent and void of many enhancements. Subsequent releases (9.2, 9.3) also didn't improve the user or developer experience as much as initially thought.

Version 9.3.1 introduced many functionality changes and enhancements, and the software largely "worked" as anticipated. In spite of this, installation of the software was cumbersome since each component contained its own installation base. This resulted in the need to install and configure the packages in a specified order. Despite following specified order, installation problems were abundant and were often tough to troubleshoot. I remember a colleague of mine saying "Wouldn't it be nice if they could develop an install package where you could just check off what you wanted and the installer software took care of the rest?"

Well, that day has finally arrived. Oracle EPM Fusion Edition was released in mid-2008 and we are finally starting to see some adoption of the software in the marketplace. Many of our clients feared the bleeding edge and were reluctant to make the plunge (some are still upgrading to the 9.3.1 edition). The magnitude of the improvements in 11x are immediately evident, even when perusing the software's release notes (I will highlight some of these later in this post).

The first improvement in this release simplifies the software installation process. The EPM System Installer is similar to other software installers you are probably familiar with. The installation process will first perform a system readiness check to determine if your machine is ready for the software (so far I haven't found one that isn't, so I can't comment on how well that check works). Next, it offers the ability to select the software components individually or by tier (Client, Web Application, Services) and performs the installations in the proper manner depending on which items you have checked. Once the installation has completed, the EPM System Configurator enables the user to activate the products, register with Shared Services, configure the databases and common settings, and configure and deploy the application servers.

The Fusion Edition also offers helpful utilities to help manage the various services that run on the servers to keep Hyperion up and running. I tested the included "Start EPM System" and "Stop EPM System" utilities, which seemed to work as expected. The package also contains a diagnostic tool that checks the various tiers and components for their working status and offers suggestions for rectifying any issues. A simple, yet, great improvement is that ALL logs are now stored in a common place, simplifying the troubleshooting process.

Wow - so far, so good! So what's new with the software? Here is an overview of some of the new features in the various products:

Essbase 11.1 Fusion Edition New Features

  • Lifecycle Management - Provides a consistent way to migrate applications and related objects
  • Typed Measures - Text and date types are now supported
  • Format Strings - Numbers can now be formatted so they appear as Text, Date or other types. For example, you can use a String format to display the values "Low", "Medium" or "High" rather than the actual value of the numerical data.
  • Varying Attributes - In previous versions, an attribute was a one-to-one relation with the base dimension. Essbase could calculate numbers based on the attribute assigned, but if the attribute varied over time (i.e. geographical markets changed), the attribute could not support the change. In Essbase 11x, attributes now have the ability to vary, providing useful functionality in a number of practical applications. For instance, a sales person can now look at profitability for a region that has changed. The attribute "Region" that is assigned varies over time, so the users can perform a query to determine the change in the Region from January to April.
  • Increased options for backup and restore - Automated backup with transaction logging is now available, providing the ability to restore and apply transactions from a log in order to restore current database states. The traditional methods of backup and restore can still be utilized.
  • ASO Data Clear - Allows an ASO database to have "regions" of data deleted while retaining the rest of the database.
  • Transparent Partition Response Time Logging - An Essbase.cfg setting now enables logging of the transaction response time for query requests from a data source to a transparent partition target.
  • XOLAP (eXtended Online Analytical Processing) - XOLAP is a variation of OLAP where only the metadata is "in" Essbase. The data resides in a relational database and is retrieved at query time. This provides the power of the Essbase analytic capabilities (functions, etc.) with the scalability of a relational database.
  • New Calculation Functions - Various new calculation functions (all detailed in the read me) are available to support some of the functionality described above. There are functions to handle the new Date data type and also some handy functions to generate hierarchy member lists including @PREVSIBLING, @NEXTSIBLING and @SHIFTSIBLING.

Additionally, there are numerous new functionality available in MDX, MaxL and the Essbase API.

Planning Fusion Edition New Features

  • Lifecycle Management - Provides a consistent way to migrate applications and related objects
  • Cell Level Documents - Users can attach Oracle EPM Workspace, Fusion Edition documents to a data form (i.e. Excel spreadsheets or PDFs) as supporting information for the cell value
  • Display Member Formula option - On a data form, selecting this option allows users to view the formula associated with the member
  • Row Suppression - Users can right click on a data form and suppress missing or zero rows
  • Calendar - Dates can now be selected from a calendar
  • Clear Cell Details - Previously, a SQL routine was required to clear cell details. This enhancement enables administrators and interactive users to clear annotations, supporting detail, cell text, and cell-level documents (see above).
  • Attributes - Support for Boolean, date, and numeric attribute types with hierarchies

Reporting and Analysis

  • Workspace changes
  • Look and Feel - The look and feel of Workspace is now much cleaner with new background colors and improved graphics
  • Masthead - Stacked menu and toolbar replace the single tier
    Search Services - Enables search capabilities for reports, dashboards, and documents from any repository
  • Shortcut keys - Shortcut keys have been enabled (Yes!)
    Integration of OBIEE products

Financial Reporting

  • Annotations - Comprehensive annotation functionality enables users to freely annotate Financial Reports and save the annotations with the reports.
  • Rank Function - Enhanced to allow the develop more flexibility in dealing with equal values and rankings
  • Books - External files can now be added to books

These are just some of the highlights of Oracle 11x. There are many more features, functions, and enhancements that were not included in this high-level overview. For more information on this release, refer to the product release notes or visit http://www.oracle.com

0 Comments Click here to read/write comments
All Posts