Steve Elkins and Paul Dean     

Express Picks Up Speed

Express 6.3 is faster and more controllable, but tools and apps are still not uniformly integrated

Oracle Express is an integrated environment for building and deploying analytical applications. The Express technology stack includes a powerful and flexible multidimensional OLAP (MOLAP) server, custom development environment, and prebuilt analytical applications.

The major improvement from version 6.2 is in the area of performance. While both the OLAP APB-1 benchmark itself and the hardware on which it was run have since changed, it is nonetheless clear that Express 6.3 is dramatically faster than 6.2 at loading and aggregating data.

Server Architecture and Components

At the core of the Express architecture lies the Oracle Express Server, a mature, robust, and flexible OLAP database server. (See Figure 1) OLAP database servers store data in virtual multidimensional arrays (also known as cubes) that mimic the business perspectives by which managers analyze data. Data about statistical measures, such as revenues, costs, and units sold, are organized by business perspectives such as time period, product, customer, and location to form the cubes’ dimensions. This organization lets OLAP database servers make information access fast and intuitive for nontechnical business managers, so as not to interrupt their train of thought as they conduct free-form data analysis.

FIGURE 1 Oracle Express Suite product relationships.

Scalability issues are the bane of OLAP servers. Cubes can grow exponentially in size with each added dimension, a problem called “combinatorial database explosion.” OLAP database vendors use a variety of architectural devices to manage database explosion, and the Express architecture includes most of them — namely, multicube architecture, aggregation management, sparse data handling, and implicit storage of null pages.

Multicube architecture. Express has always used a “multicube” architecture, which lets you easily answer questions involving multiple measures across their overlapping dimensions; for example, “What is the average number of items purchased in each cash register transaction by Month, by Store.” In the relational world, answering this type of query would involve joining two separate fact tables, but Express’s multicube OLAP architecture handles this type of query much more quickly and elegantly. This architecture also helps manage database explosion, because it doesn’t reserve disk for dimensions irrelevant to a measure.

Aggregation management. Express allows the database designer complete flexibility to manage the degree of pre-aggregation in each cube. Preaggregating cross-dimensional data at the time the cube is loaded speeds query response times, but is the primary contributor to combinatorial explosion. Express has long supported dynamic calculation between cubes, such as Price = Sales/Units. The new Aggregation Management System in 6.3 completely frees the designer to control the degree of preaggregation within cubes in order to balance query response time vs. database explosion.

Sparse data handling. Express has automatic sparse data handling. An example: If the Miami store doesn’t sell snow shovels, Express does not carve out space to hold the nonexistent data. In many OLAP applications, far less than one percent of a cube’s potential cells contain data, and it is extremely important for the OLAP database server to minimize the amount of space used to store or index null data cells.

Implicit storage of null pages. Express does not store data pages on which all data values are null. Designers can take advantage of this feature by ordering the dimensions to group null data values together. For example, if Actual and Forecast data are stored, ordering the Time dimension last for Actuals will reduce storage. Express’s only weakness in this regard is that it does not squeeze null values out of stored pages.

Finally, it is important to understand that OLAP is a technology for building data marts holding data summarized to focus on solving individual business analysis problems. Generically, “sales and marketing” is not a well-focused business problem; however, pricing, sales, and campaign analysis are. Printing mailing labels is not an analysis purpose; identifying the characteristics of customers who are likely to respond to a mailing is. Summarizing data to analyze sales data by Month, Product, Store, and Customer Type in order to analyze sales trends is an appropriate use of OLAP database technology. Detailed data about individual customer purchases (transactional data) belongs in an underlying relational data warehouse — it just will not all fit into an OLAP database.

Relational Integration

If you are a regular reader of Intelligent Enterprise, you have probably planned well and “dimensionally” organized the detailed data in your relational data warehouse, or stored data in the “star-schema” relational database format that contributing editor Ralph Kimball popularized. If so, the Express family of products:

• Affords you tremendous flexibility to seamlessly balance data storage between OLAP and relational technology in order to manage combinatorial explosion in your OLAP data marts

• Enables you to deliver intuitive access to almost all levels of data detail in your overall business-intelligence architecture.

The Relational Access Administrator (RAA), in conjunction with the Relational Access Manager (RAM), lets you map the measures and dimensions in the Express database to the corresponding measures and dimensions in an underlying star- (or snowflake-) schema relational database. After you do so, you have complete control over the dimensionality and granularity of the data that the Express OLAP cube draws from the underlying RDBMS. RAA and RAM support all major RDBMSs.

You can implement relational OLAP (ROLAP) or hybrid OLAP (HOLAP) modes of operation.

ROLAP. No data is preloaded into Express; in effect, Express is a virtual cube. End users query the virtual cube from an OLAP front-end tool and RAM translates the query into SQL and submits the query to the underlying RDBMS for an answer.

HOLAP. Some data, for example, the 20 percent of the higher-level summary data that answers 80 percent of the business questions is preloaded into Express; the lower-level detail remains within the RDBMS.

RAM uses a sophisticated caching routine and, in both modes, Express takes query results that the RDBMS calculates on the fly and caches them to speed query response time on subsequent requests in the same session.

The flexibility of a HOLAP architecture is such that the designer can provide end-user access to all but the lowest transaction levels of detail in the entire architecture, through a single user interface. For example, a manager can begin with a query retrieving summarized data stored in the Express database (some of it pre-aggregated, some aggregated on the fly) and then drill through to more detailed data stored in the underlying RDBMS. (Express’s drill-through mechanism has been around awhile and is well-tested and quite robust.)

From the end user’s perspective, all this data appears to be part of the same logical database structure. From the designer’s perspective, the two most important aspects are:

• The complete flexibility to manage the trade-off between response time and database explosion by tuning the mix of OLAP storage (fast but big) and relational storage (slow but compact)

• The degree of preaggregation on the OLAP side.

The RAA Query Statistics tool lets the designer analyze the pattern of queries posed to the RDBMS, in order to determine how it will use summary fact tables and preload data into Express. (See Figure 2.)

FIGURE 2 Analysis criteria for the RAA Query Statistics tool.

The underlying assumption is that at least one relational fact table must exist with the same dimensionality and granularity level of the data the Express database will import. There can be only one corresponding row in the lowest-level RDBMS fact table to each lowest-level cell in the Express cube. It also assumes that the underlying RDBMS is optimized for query performance.

You achieve access to transaction-level detail through the integration between Express and Oracle Discoverer, currently available only through the Oracle Express Objects/Analyzer.

Administration Tools

You can now design and develop both MOLAP and ROLAP/HOLAP Express applications by using well-designed GUIs that include several wizards. Older versions made you conduct almost all design and maintenance via Express’s Stored Procedure Language (SPL), a 4GL. Using SPL always empowered Express database developers to create highly customized Express applications, but the absence of GUI design tools meant that only “programmer types” were able to build and deploy Express applications at all. Express now offers the best of both worlds: a good graphical design environment that facilitates rapid deployment, plus the ability to expose the underlying SPL code the GUI generates so you can still customize applications as needed.

Tuning an Express application, from a design standpoint, typically involves identifying dense and sparse data categorizations and determining the most appropriate dimension ordering. You can manage security at multiple levels, all the way down to an individual cell within a cube.

The Java-based Express Instance Manager (EIM) — new with 6.3 — is for administering and controlling the Express Server. As with most tunable OLAP servers, tuning is more of an art than a science; trial and error is still the rule.

Express Clients

Oracle offers several ways to analyze an Express database:

• Prepackaged applications — Oracle Sales Analyzer (OSA) and Oracle Financial Analyzer (OFA)

• Client/server development tools — Oracle Express Objects/Analyzer

• Web development tools — Oracle Express Agent and Oracle Web Publisher

• Excel — via the Oracle Express Spreadsheet Add-In.

If you are looking for a good out-of-the-box reporting and analysis GUI front end, you should definitely have a look at Oracle’s too narrowly named Oracle Sales Analyzer. OSA is actually the best general-purpose front end in the Express toolkit. It combines all the basic slicing, dicing, drilling, graphing, navigating, and formatting tools that you expect in an OLAP front-end tool into a well-organized package you can learn in a day. OSA has a Web interface that does everything the client/server interface does, except some minor cosmetic report-formatting options. The OSA Web interface also comes with a Web Analysis Library that comprises 19 sales- and marketing-oriented reports that you can configure with a few mouse clicks to work across all dimensions within the Express database. OSA is well-integrated with the Express database and is perfectly at home within MOLAP, ROLAP, and HOLAP implementations. OSA is a read-only application, not designed to take advantage of Express’s read-write capabilities, which would be useful for sales planning applications.

Oracle Financial Analyzer, however, is designed for distributed financial reporting, planning, and forecasting applications. It has read-write capabilities that include the ability to develop new planning scenarios “offline” before writing them back to the Express database. OFA is the analysis application of choice for companies using Oracle Financials, with which OFA is tightly integrated. OFA can write data back to the Oracle GL (for example a revised budget or annual plan) Oracle Financials can load external data through the Express Administrator, typically via flat files. OFA also supports user-input data, via its own worksheet object or from Excel, using the Express Spreadsheet Add-In. New in OFA 6.3 is the ability to input data directly from the Web.

For end-user analysts who prefer to remain in Excel, Oracle now offers a competitive Spreadsheet Add-In that can access data in any Express database.

Express Objects is an object-oriented client/server development environment for building custom applications on top of Express. You use Express Basic language (syntactically compatible with Visual Basic) to develop the user interface, and Express Analyzer to run the application. “Power users” can opt to build briefing book-style interfaces with Express Analyzer.

You can use the Express Web Agent and Web Publisher to create custom Express Web applications.

As with all OLAP databases, you access Express cubes via a multidimensional API, not SQL. The Express SNAPI is a published API for Express, and several third-party front-end vendors, including ACG (Active OLAP Suite), Business Objects, Brio Technology (BrioQuery), Cognos (PowerPlay), and Viador Inc. (SpaceOLAP), have used it to connect their products to Express databases. However, third-party vendors have found SNAPI relatively hard to code. For this reason, as well as the fact that Oracle offers its own prepackaged analytic applications and development environment, there are fewer third-party options for Express than for some competing OLAP servers.

If there is one weakness to this product suite overall, it’s the disparity among the tools and applications. Custom Express applications (via Express Administrator and Objects/Analyzer), OFA, and OSA all have their own Express database internal structures.

All things considered, Express 6.3 is the latest in a long line of proven OLAP development suites. If you’re seeking a robust and extendable OLAP development environment, you owe it to yourself to look at this product.

Steve Elkins and Paul Dean both work in the Business Intelligence Service Line at Braun Consulting. Steve can be reached at 612-596-4378 or selkins@braunconsult.com, Paul at 312-984-7160 or pdean@braunconsult.com

RESOURCES Brio: brio.com Business Objects: businessobjects.com Cognos: cognos.com Viador: viador.com