Developer Studio: Hierarchical Reporting

By Harry Lotrowski

One of the key concepts in Business Intelligence is multidimensional analysis: examining the enterprise from all necessary combinations of dimensions. A dimension is defined as "organized hierarchies of categories and (levels) that describe similar sets of members." Among the most commonly specified dimensions are time, geography, products, organizations and financial charts of accounts, but the potential dimensions are as endless as the varieties of enterprise activity. The events or entities associated with a particular set of dimension values are usually referred to as measures.

These measures might be sales or purchases in units or currency, profits, customer counts, production volumes, or anything else considered worth tracking. In multidimensional databases, these measures are pre-aggregated along the hierarchies for query performance.

WebFOCUS Developer Studio release 7.1.3 has many built-in elements that address some of the complexity of interacting with multidimensional databases. The release will include new functionality to capitalize on the hierarchical presentation and pre-aggregation within cubes. It also will contain mechanisms to adapt to the rules required by the different cube data sources.

You now have the ability to display information in the same sequence as the hierarchy itself. This includes the indentation usually associated with hierarchies. The actual hierarchy members (and the resulting paths) can be selected by screening on their properties.

All types of filter tests are supported, and this allows including hierarchical parents and children in the report for context purposes. The report shows the context of each selected hierarchy member up to the root of the hierarchy.

The set of members selected for a report by a WHEN test is augmented so that every member of the hierarchy is shown within its hierarchical context. The WHEN statement controls which hierarchy members are shown on the report while the WHERE test controls which data is included in the totals for the displayed members.

The displayed sub-tree of hierarchy members is a contiguous tree whose root is the root of the whole hierarchy. The added "context" members are shown without totals. The application controls the method of display for hierarchy members.

Metadata Support

Multidimensional data sources differ from traditional relational data sources when it comes to reporting. Different cubes restrict operations, which may (in an exact error message of one of the cubes) result in a nonsensical report. Also, different vendors have different rules.

To attain the knowledge of operations permitted by the individual cube, the metadata, produced by the "create synonym" process, is recognized by Developer Studio, which will use it to determine how to display the object inspector (Figure 1), and how to display the information within the dimension itself. Certain operations may be restricted based on the type of field (measure, dimension member, attribute). These are requirements of the individual cube. In addition, the front end enforces certain required operations (BW mandatory variables). Hierarchy trees are used to display the structure of file metadata as well as those areas that display the actual data within the hierarchy. These features enable a developer to provide reports utilizing hierarchy trees for both output and pick-list selection.

The same type of control used in FML for parent/child selection is now available for parameter/values selection where a cube is involved. The levels selection dialog, which completes the criteria for displaying the cube, is the final piece of the specification.

WebFOCUS has always had summarization capabilities built in. However, in the multidimensional world, where aggregates are generally stored, we have to choose which values to show the end user. Totals on the report can be either the amounts that are pre-aggregated in the cube or the totals for that data that passed the specified WHERE/WHEN tests. We allow for both methods.

Visual totals for each node are composed from the totals of its hierarchical children shown on the same report. Full total displays are the value found in the cube data source. This value may not reflect the sum of its displayed descendants if some descendants are eliminated from the report output based on the WHEN and SHOW options (Figure 2).

Our goal is to extend the Developer Studio paradigm from relational data sources to multidimensional data sources without changing the skill set required by any substantial amount. Now the same tool that is used against any of the existing data sources can address this new multidimensional paradigm.