Watching Sales

More efficient handling of sparse data and converging web and windows clients mark new release of OLAP tool

Improvements

OSA MOLAP applications can now support up to 10 dimensions per data measure, increased from six in version 6.2.1; this is now consistent with OSA ROLAP applications. When preaggregating data to summary levels (always the most time-consuming step when building any MOLAP application) the Data Loader Utilities can now use the Aggregation Management facility that was first introduced in OES 6.3. The Aggregation Management facility supersedes the older, slower Rollup command and is much more efficient at handling sparse data. The Aggregation Management facility can summarize multiple measures (provided they share the same dimensionality) across all dimensions in a single command, whereas Rollup requires a separate command to summarize along each dimension of each measure.

Using real-world data, I witnessed performance improvements of more than 300 percent compared to version 6.2.1. In general, the sparser the data and the more stored measures there are, the greater the performance improvements. It's easy to modify existing applications to take advantage of these improvements without having to reload all the data.

Aggregation Management is enhanced in OES 6.3.2 (the version of Express that supports 11i) to add support for nonadditive aggregation methods such as average, weighted average, and weighted sum. Dynamic aggregation at run time is also supported. With Dynamic aggregation you can specify full, partial, or no aggregation for each individual data measure in order to trade off query response time with database build time. This flexibility can be very useful when you are trying to fit the database build within an allocated time window that is, say, part of a nightly update process. For example, you could partially aggregate so that the most frequently accessed higher-level information is preaggregated (for a fast response time), and the infrequently accessed information is dynamically aggregated only when the user requests it (resulting in a slower response time). Out of the box, OSA does not take advantage of partial aggregation or nonadditive aggregation methods. However, these methods require only simple customization to implement.

New Web Features

In the previous release of OSA, 6.3, the end-user functionality available on the Web client was nearly identical to that of the Windows client, apart from some of the minor reporting formatting options.

OSA (like OFA) now takes advantage of the enhancements in the OES 6.3.2 Oracle Web Developers Toolkit to improve the manipulation of the document tree, provide report formatting, and support dynamic color-coding. In addition, language selection on the Web is now determined at run time and users can access reports directly from other applications. See the November 10 issue for more discussion of these features.

OSA users have long been able to create and save their own custom calculations (known as Custom Measures) by selecting from 18 different types, such as time series, arithmetic, and share. In prior versions, the only way you could modify a custom measure was to delete and then re-create it; this led to problems opening existing reports containing this measure, because the application held a different internal name. In this latest release (on the Web only) you can edit Custom Measures as well as use them in the Web Analysis Library reports.

Users must still create ranking and exception reports through a Windows client, but can view and manipulate them on the Web. Report row and column calculations, available on Windows clients, will not appear on the Web. This will only be an issue if the reporting requirements call for individual columns or rows to be derived from other report columns or rows; however, Custom Measures and Custom Aggregates can sometimes be used instead.

With the performance improvements (particularly regarding MOLAP aggregation) and increased Web functionality, OSA deserves a look. It should receive interest from companies seeking a robust, general-purpose business-intelligence application that can be deployed standalone or as the end-user analytical component of a data mart or warehouse implementation.

Paul Dean is vice president of OLAP Consulting Services at Braun Consulting and can be reached at 312-984-7160 or pdean@braunconsult.com.