Oracle Sales Analyzer (OSA) 11i is one of two Oracle Analytical Applications that use Oracle Express Server (OES) as their multidimensional OLAP data store. As with Oracle Financial Analyzer (OFA) 11i, you can access OSA via either a Windows or Web client.

Version 11i of the Analytical Applications, which replaced 6.3 in August, takes advantage of recent improvements to OES and has added new Web features. This article focuses on OSA only. For a review of OFA 11i, see the November 10, 2000 issue.

OSA is a read-only application for performing advanced analysis on sales, marketing, and other corporate data. OSA contains inherent intelligence about sales and marketing data, such as predefined calculation types. It also contains customizable aggregates that let users build their own groupings on dimensional information based on their product or industry knowledge. However, despite its name, OSA is not restricted to analyzing just sales and marketing information. You can use it to analyze virtually any information that can be described in a multidimensional way: business facts (measures) that mimic the business perspectives (dimensions) by which users analyze these business facts.

Architecture

You can deploy OSA in any OLAP mode: multidimensional, relational, or hybrid (MOLAP, ROLAP, and HOLAP, respectively). HOLAP is a variation of ROLAP wherein some of the data, typically high-level summary, is preloaded into OES. (For more information on these different OLAP architectures, see the April 10, 2000 issue for the OES review Steve Elkins and I cowrote.)

To implement ROLAP you must have a relational data warehouse or mart with a star or snowflake schema. You use MOLAP when the source data has to be extracted from legacy applications or operational data stores.

Figure 1 outlines the different architecture and application components. Here's an overview of how they fit together.

An OSA Application consists of three Express databases. The Data database holds the dimensions, hierarchies, and data measures (or in the case of ROLAP, caching information and rules about how to fetch data from the relational database). The Master database holds configuration information about the OSA application. The Public database stores (in libraries) shared objects such as reports, graphs, forecasts, user-defined data measures, and so on.

You can set privileges by user group to libraries created in the Public database. For example, you might create a library for the marketing department that is available to all users, whereas you might more tightly restrict one created for finance.

Each user has a Personal database for storing any user-defined objects (reports, graphs, forecasts, data measures, and so on). Users can share Personal objects with other users by publishing them to the Public database. Users access their own Personal database regardless of the client type they use - Web, Windows thin, or Windows thick. OSA also supports anonymous Web users with read-only access to libraries in the public database. Also, remote users can work in a disconnected mode on their particular slice of information; for example, sales reps can have a slice of the database on their PC that contains just their accounts.

The Application Manager is for configuring the Windows client and downloading and updating slices. The OSA administrator can modify application set- tings and create users and groups using the Database Manager. A set of data-access rules, known as a Scope, can be controlled for each group of users down to the individual cell level if necessary.

Taking advantage of the application's intelligence about sales and marketing data, the Web Analysis Library contains 19 predefined report templates that display key sales and marketing indicators such as, "Which customers are buying or not buying this year vs. last year?" and "Which 80 percent of my customers make up 20 percent of my business?" As a user, you interact with and modify these templates using an English-like hypertext dialog as shown in Figure 2. As an administrator, you can configure these report templates to work along any of the dimensions with a few mouse clicks.

You can use the Forecast Wizard to predict future values of numeric measures based on historical performance, using a variety of popular forecasting methodologies such as: Holt-Winters, single/double/ triple exponential smoothing, and linear trend. You can also opt for "Best Fit," which will cycle through all the methodologies and determine the one with the closest statistical fit. OSA saves the forecast results as data measures, which you can use in reports or graphs and also share with other users by publishing to the Public database.

Implementation

When designing OSA applications, developers use the same techniques and approach as for other OLAP and data mart applications: identifying business facts and organizing them dimensionally according to a star or snowflake design. This design approach is equally applicable whether you are deploying OSA in MOLAP or ROLAP mode, only you use different tools to build and update the application.

You build OSA MOLAP databases using a set of "Data Loader Utilities." The Data Loader Utilities are driven by a series of configuration files where you outline the database design (dimensions, hierarchies, levels, attributes, measures, and so on) and identify the layouts of the extract files that will populate the dimension values and measures. The configuration files are text files you can edit with any text editor, and the Data Loader Utilities automatically build and populate the OSA application without programming. The absence of a GUI design tool and the very specific column position requirements of the configuration files mean that configuring the build process is a little on the archaic side. However, once you have understood the layout and format requirements, you can construct OSA MOLAP applications quite quickly.

ROLAP databases are built using the Relational Access Administrator (RAA), which is a component of the Relational Access Manager (RAM). The OES review in the April 10 issue provides more information about RAM. RAA is a GUI tool you use to map the logical Express data model (dimensions, levels, hierarchies, attributes, and measures) to the corresponding fact and dimension tables in an underlying star or snowflake relational database schema. In ROLAP, OSA assumes that all the data measures and dimension values will be populated from the underlying relational database. As well as supporting a single fact table schema that stores base-level data, RAM supports other schemas such as multiple fact tables that can be vertically partitioned either across different measures or with different dimensionality, and fact tables that are horizontally partitioned by different level of summarization. If a star or snowflake schema is not in place, a useful feature of RAA is the ability to generate the data definition language (DDL) to build the relational schema from the logical Express data model. The DDL can be viewed, saved to file, or executed to create the necessary fact and dimension tables.

To the end user, an OSA application looks and functions the same regardless of whether it's MOLAP or ROLAP. However, from an implementation perspective, the Express back-end database structures use totally different naming conventions; this means that the methods of database construction are mutually exclusive. You could make customizations that let you load relational data into MOLAP and load data from external files into ROLAP. However, this difference between the Express back-end database structures leaves no migration path between MOLAP and ROLAP; therefore if your data sources change, the path of least resistance is to rebuild the application.