Working With FML and Essbase

By Harry Lotrowski

Hyperion Solutions Corp.’s Essbase Business Intelligence platform is one of the newer data technologies that WebFOCUS can access. Although tabular reporting is available against Essbase cubes, WebFOCUS 5 enhances the process by incorporating its Financial Modeling Language with the cube hierarchies.

Reporting from a cube is somewhat different from reporting from almost any other data source that WebFOCUS can use. Certain features are enhanced by use of a cube, while others are curtailed by the nature of the cube itself. This article outlines some things that users must know to make the report creation experience flow smoothly.

Let’s start with a definition. What is a cube? One definition says it’s a multi-dimensional matrix of data that has multiple dimensions (independent variables) and measures (dependent variables). Each dimension may be organized into a hierarchy with multiple levels. The intersection of two or more dimensional categories is referred to as a cell (see image below).

The first thing to keep in mind is that you will need to use the WebFOCUS console and not the Developer Studio synonym wizard to create your synonym (file description). The synonym created by WebFOCUS enumerates all the levels in each of the hierarchies.

To emulate the file description the Essbase administrator provides, we have included a web application to provide an alternate file description. This Master File Description may be better suited for end users. If used in this fashion, you may want to open the Master File Description in WebFOCUS and add headings and descriptions for the end user. This application, ibessfmt.fex, resides in the IBISAMP folder. Look for further information on this in the release notes.

One benefit of cube technology is its ability to store "measures" or information of interest in a manner that lends itself to being consolidated at one or more points within those dimensions (rollups). In addition, retrieval is done rather quickly considering the amount of data involved. Within the WebFOCUS arena, this translates to ensuring that you have specified "where" clauses on as many of the dimensions as possible. This defines the "intersection" among the dimensions where the data exists.

Essbase, by nature, lends itself to Financial Reporting. This requires the ability to handle synchronization not only within the data, but formatted reports that need to recognize these changes. The new features available in the FML Painter deal with these amply. They allow for parent/child relationships to be resolved dynamically and will allow you to control both the starting point and depth of your hierarchies at which calculations are performed.

Data that is referenced in a "where" clause may be selected from drop-down menus dynamically populated from the cube. Temporary or custom hierarchies may be created for specific reports. The FML Painter allows you to drag multiple entries (either consolidated or enumerated) and create subtotals and other calculations on groupings that do not exist within the cube. While you can browse the hierarchy for the entries you want, there is a search menu that allows you to directly select entries for the report matrix.

The Get Children functionality of WebFOCUS lets you have the report procedure refresh its view of the hierarchy at run time. This ensures that reports be constantly in sync with the chart of accounts without manual maintenance.

As an example, a company’s chart of accounts can stretch out into thousands of entries. Other dimensions may grow so large or infrequently used that there is a need to filter out this information from the user interface. In WebFOCUS this can be accomplished with the FILTER facility. This facility allows you to limit or filter the values available for reporting or report creation, thereby decreasing the use of resources and providing faster processing.

Another approach is to use the MAXROWS parameter. This parameter limits the number of records returned from Essbase and is useful when testing against large cubes.

Note: WebFOCUS defaults this parameter to 10,000 to ensure it is overridden for production reporting.

Essbase provides an arena where FML can add value to the reporting process. FML is the tool of choice because of the ease of customized grouping, dynamic chart of account recognition, and the ability to make financial reporting easy from a large data mart. Add in the functionality of WebFOCUS and you have presentation and distribution capabilities second to none.