The goal of a data warehouse project is not to warehouse data, but to enable business managers to become self-sufficient analysts. In a successful business intelligence environment, even nontechnical business managers compose their own requests for information as they conceive of them, without programmer assistance, and they retrieve results at the speed of thought. Their follow-up questions are immediately answered through instantaneous drilling down and slicing and dicing of the data. Managers must be able to maintain their trains of thought as they conduct an analysis through free-form exploration of a database.
Online analytical processing (OLAP) technology has gained popularity because its use of multidimensional structures, special database indexing schemes, and summarization have enabled intuitive access and rapid response times for complex analytical queries. But the ultimate success of OLAP depends not strictly on its capabilities but rather on how well OLAP fits into broader data warehouse architectures. In this article Iıll analyze several current trends affecting the state of OLAP technology, with a special focus on integrating OLAP with other technologies. Microsoftıs forthcoming OLAP server and its OLE DB for OLAP API make OLAP a particularly timely topic.
To date, most OLAP technology purchases have been application-specific decisions made at the departmental level. OLAP tools tend to be relatively easy to learn and deploy. Hence, business unit managers frustrated by the slow pace of enterprise data warehouse projects frequently purchase them directly and implement them over the objections of ı and with minimal assistance from ı corporate IT departments. Consequently, itıs not unusual to find companies in which three different departments have purchased three different OLAP tools, totally unaware of each otherıs activities and making no effort whatsoever at cross-departmental integration. In fact, it would be fair to say that most businesses have introduced standalone OLAP products in ways that have aggravated the "islands of information" problem. Isolated departments are able to satisfy their immediate information needs in this manner, but information compiled by one business unit remains beyond the reach of analysts in other departments. There can be no assurance that the numbers presented by one department on one set of reports will match the numbers on a seemingly equivalent set of reports produced by a second business unit.
On the other hand, in those cases where IT departments have successfully thwarted the introduction of OLAP technology into the enterprise, business managers remain virtual prisoners in the data jailhouse. Their continuing complaints that, "I have all this data, but I just canıt get at it!" remain unanswered.
OLAP is not a panacea for decision support. Like other hot decision-support technologies such as data mining, data visualization, and data warehousing, OLAP is just one key component of the overall business intelligence framework. As is the case with these other technologies, OLAP must be woven into the larger fabric of a companyıs business intelligence architecture to realize its full potential. (See the sidebar "OLAPıs Place in the Warehouse Architecture.")
To accomplish this integration, some vendors, such as Seagate Software IMG with its Holos and Crystal Info product lines, have endeavored to include OLAP, data mining, statistical analysis, and other decision-support technologies within a single product line. Other vendors, such as Arbor Software Inc., have opted for an open, best-of-breed approach, partnering with vendors of complementary products. These vendorsı success has the rest of the industry sitting up and taking notice. Letıs examine how integration is happening, both at the interface between the OLAP client and server layers and between the OLAP server and data warehouse layers.
OLAP clients communicate with OLAP servers using multidimensional APIs, not SQL. All OLAP servers, both multidimensional OLAP (MOLAP, multidimensional databases, or MDDB) and relational OLAP (ROLAP, front ends to relational databases) are proprietary insofar as each product publishes its own API for communicating with client front ends. Likewise, a majority of OLAP servers, both MOLAP and ROLAP, still communicate only with their own proprietary front ends using their own APIs. (See Table 1, for the exceptions.) An industry-standard OLAP API would enable customers to mix and match freely among best-of-breed OLAP clients and OLAP servers, just as ODBC has obviated the need for point-to-point native driver connections between client tools and database servers. To date, two nonproduct-specific OLAP APIs have emerged.
The first effort to develop a standard OLAP API was undertaken by the OLAP Council (www.olapcouncil.org), an industry consortium with a fluctuating roster that currently has 12 members. The first version of the OLAP Councilıs MD-API was delivered in late 1996. So far, the only vendors to implement this API as an option are Gentia Software, an MDDB server vendor, and InfoSpace Inc., a Web application server vendor. Thus, the only practical use of the OLAP Councilıs API at this point is to enable InfoSpace to be used as a front end for the GentiaDB MDDB server.
In late January 1998, the OLAP Council announced version 2.0 of the MD-API, and most members of the council rushed to embrace it, though, once again, no delivery dates were announced. Most significantly, Oracle announced that it would make the MD-API the native API for its Express OLAP server, which will assure at least limited support for this standard. Council member Arbor Software Corp. was noticeably absent from the list of supporters.
Into the breach steps Microsoft, which purchased a nascent OLAP database server from an Israeli software vendor, Panorama Software Systems Ltd., in 1996 and is busy molding it into a Microsoft OLAP server, currently code-named Plato, which has just gone into testing. To assure the instant success of this new product when it hits the market later in 1998, Microsoft undertook its own OLAP API initiative, originally code-named Tensor but now called OLE DB for OLAP. The OLE DB for OLAP API is a set of communications interfaces designed to extend Microsoftıs OLE DB for efficient access to multidimensional data. Designed to be generic enough so that they can be employed by any relational or multidimensional data provider, these extensions allow for representing, expressing, transporting, and efficiently navigating multidimensional data within the Microsoft operating system environment. They are designed to link multidimensional data providers and consumers of such data, regardless of the data storage environment.
As only it can do, Microsoft was able to obtain input from more than 40 OLAP vendors before issuing the first complete draft of the API in September 1997. The standard will continue to evolve ı read/write capabilities were included for the first time in a late October revision ı but more than 20 vendors, including both client and server vendors, have already announced support for the new de facto "standard." The companies listed in Table 2) have committed to develop products that will use the Microsoft OLE DB for OLAP API to provide access to data stored in OLAP data marts. Few of these vendors have yet announced delivery dates.
Conspicuous by their absence from the list are OLAP market leaders such as Oracle (which markets the Express OLAP server) and Arbor Software. Oracle is now the driving force behind the reinvigoration of the OLAP Council API. Arbor is the only vendor that can afford to take a wait-and-see attitude, because every OLAP client vendor to announce support for OLE DB already connects to Arborıs Essbase OLAP server via the native Essbase APIs. Conspicuous by their presence on the list are major ROLAP vendors, such as Information Advantage Inc. and MicroStrategy Inc., that were never members of the OLAP Council and have never avidly pursued connections between their database servers and third-party front-end tools.
Few of the vendors now pledged to support OLE DB have actually announced target dates for completing the integration. (Arbor, however, already announced that its Wired for OLAP client, acquired with its recent purchase of AppSource Corp., is ready to connect to the beta version of Plato using this API.) In the meantime, companies looking to realize the full vision of integrated client/server OLAP have relatively few options to consider. For example, if you wanted to include both MOLAP and three-tier ROLAP servers in your architecture, and use the same front ends for both, the only ROLAP database server you could use is Informix Software Inc.ıs MetaCube, which allows connections to Andyne Computing Ltd.ıs PaBLO, Business Objectsı BusinessObjects, and Seagateıs Crystal Reports. All three client front ends can also be used with Arborıs Essbase MDDB, and the first two can be used with Oracle Express MDDB. Thatıs it.
An alternative would be to purchase an internally integrated "hybrid OLAP" solution such as Holos from Seagate, which has both three-tier ROLAP and MDDB data storage options within the same product line. Within an overall Holos database environment, there may exist both star-schema relational databases holding relatively detailed data with many dimensions and MDDBs holding more focused sets of data summarized from the larger relational database; data from both would be viewable from the same custom-programmed Holos front ends. When query response time lags against the broader, less summarized ROLAP database, you can carve out a new subset of the detailed data and load it into a preaggregated MDDB database to obtain speed-of-thought response times against a more focused set of information. However, with this option you would lose the ability to select a third-party front-end tool until Seagate implements the OLE DB API.
Oracleıs Relational Access Manager provides a similar capability to draw a subset of a star-schema relational database into an Express MDDB. Arbor is currently developing a competitive capability for Essbase, code-named "Project Mayflower," for delivery in 1998. IBMıs DB2/OLAP is just the opposite. While the aforementioned options let you easily extract data from a star-schema relational database into a new MDDB hypercube, DB2/OLAP allows you to build an Essbase hypercube and physically store the data in a special star-schema relational database.
Another segment of the OLAP market is composed of desktop, or portable OLAP products, that create small OLAP cubes that fit on a client workstation. Although products such as Cognos Corp.ıs PowerPlay or Andyneıs PaBLO are widely deployed on a standalone basis to solve small-scale business analysis problems, another potential use of these products is to carve a small subset cube out of a larger OLAP database for portable analysis. For example, field salespeople might dial into the corporate sales data mart, carve out the latest data for the piece of the database that corresponds to their territory, and take it with them on their laptops to analyze at their leisure. Oracle (Personal Express), Applix (TM/1 Perspectives), and Arbor (Personal Essbase) all offer personal computer versions of their vendorsı larger-scale database servers, and MicroStrategy Inc.ıs DSS Agent can also be run in a disconnected "single-tier" mode. BusinessObjects can now "screen scrape" a subset of data from either an Essbase hypercube or a relational star schema directly into a small cube in client memory, as can Brio Technology Inc.ıs Brio Query, which will also soon be able to subset Information Advantage Inc. databases. Both Andyne (the PaBLO Slicer Dicer) and AppSource Corp. (Wired for OLAP "Cubes to Go"), which was recently acquired by Arbor, have announced facilities for carving a desktop cube out of a larger Essbase hypercube, but neither has actually delivered a commercial product to the marketplace. As I already mentioned, Arbor announced it will acquire AppSource.
Much has been written about Web-enabled OLAP lately, so I wonıt dwell on it here except to note that, in addition to the offerings of the OLAP database server vendors themselves, companies such as InfoSpace Inc. and Track Objects are now offering outstanding third-party Web servers that can be used as graphical front ends for a variety of OLAP and relational database servers. Several OLAP client vendors, including AppSource/Arbor, Brio, and Business Objects are also offering Web server versions of their OLAP front ends. People are increasingly looking at Java-based OLAP Web application servers as an alternative to portable OLAP for field access to corporate databases because of both reduced client hardware requirements and the virtual absence of remote client software maintenance hassles.
Data mining and OLAP are complementary technologies. In fact, many data mining techniques would be most efficiently run against databases that are organized multidimensionally. (See Ralph Kimballıs "Preparing for Data Mining" column, DBMS, November 1997, for further discussion of this issue.) However, there has been relatively little integration between OLAP and data mining products to date. The Information Discovery Inc. product suite is currently the only product line that seamlessly integrates the two disciplines. (Dr. Kamran Parsaye of Information Discovery has written several good white papers on the relationship between the two technologies. These papers are available at the Information Discovery Web site, www.datamining.com.) For the most part, data mining tools read flat files and, at best, provide outputs that can easily be imported into OLAP databases for review. DataMind Corp. announced that it will be able to mine data stored in Essbase hypercubes by mid-1998, at which point it will probably be the first data mining tool that can be seamlessly connected to a third-party OLAP database. Seagate claims its Holos product has some data mining capabilities, while OLAP vendor Pilot Software Inc.ıs Discovery Server data mining tool cannot read data from Pilotıs own OLAP database server. Version 1.0 of Cognosıs data mining product Scenario could not read Cognos PowerPlay data; however, version 2.0 will.
Many other complementary decision-support technologies ought to be integrated with OLAP database technology, including statistical analysis packages, geographic information systems (GIS), and data visualization tools. A majority of OLAP server vendors now offer spreadsheet add-ins as front-end options. With respect to GISs, both MapInfo Corp. and ESRIıs Map Objects software can be found embedded as display options in several OLAP client front ends and invoked when the underlying OLAP database has a geographic dimension. Statistical analysis software vendor SPSS Inc. is working to establish API links with several OLAP server vendors. The first links to be delivered will enable SPSS to connect to Oracleıs Express MDDB server and Arborıs Essbase MDDB server by the time this article appears. Good mathematical analysis capabilities are included with Kenan Systems Corp.ıs Accumate MDDB server. Seagateıs Holos product line includes its own statistical analysis, data visualization, and data mining tools. SAS Institute Inc. was slow out of the gate in integrating its OLAP database servers with its own impressive set of statistical analysis and data mining tools, but the company now claims that you can run its entire library of analytical software against its OLAP database server.
Most OLAP APIs will allow you to develop custom applications that tap directly into the OLAP database using popular object-oriented programming languages such as C++ and Visual Basic. In many cases, products such as Borland International Inc.ıs Delphi and Sybase Inc.ıs PowerBuilder are also supported.
At least as important as integration between OLAP client and OLAP database server is the integration of the operational data store, data warehouse, and data mart layers of the architecture. In any business intelligence project, the lionıs share of the work goes into extracting, scrubbing, and transforming the data from OLTP to OLAP format. This has been a tedious, multistage process with little synergy between the steps of extracting the data from OLTP systems to the warehouse and the process of populating the data marts from the warehouse.
Several OLAP database servers offer excellent graphical administration tools for mapping data from well-designed warehouse databases to OLAP data structures. Cognosı PowerPlay Transformer, Arborıs Essbase Application Manager, and Knosysı Database Administration Tool come to mind among the MDDBs; Sagent Technology Inc.ıs Design Studio, MicroStrategyıs DSS Architect, and Informixıs MetaCube Warehouse Manager are good examples on the ROLAP side.
The latest release of Informatica Corp.ıs PowerMart suite stands out as a graphical toolset for extracting data from OLTP systems and transforming it into dimensional star schema format at the atomic data warehouse or data mart layers of the architecture. This metadata includes mappings of tables and fields in a data warehouse (or data mart) to dimensions and levels in a multidimensional OLAP database.
Informaticaıs Metadata Exchange (MX) architecture employs an API to exchange metadata between the Informatica repository and other OLAP or query applications. MicroStrategy, Information Advantage, Andyne, Brio, Business Objects, Cognos, InfoSpace, and IQ Software Corp. have also announced support for Informaticaıs MX architecture. Metadata sharing can save an enormous amount of effort in the construction of individual OLAP data marts from a central data warehouse or "dynamic data store" as Informatica labels it.
For example, MicroStrategyıs DSS Suite now reads Informaticaıs metadata just as if it had been created by MicroStrategyıs own DSS Architect. Thus it is no longer necessary to use DSS Architect to recreate this information manually ı a tremendous time saver. It also guarantees that if multiple marts are created from the same warehouse (and from the same Informatica metadata), they will all use the same terminology, and all business analysts will be reading from the same (Informatica) prayer book. In addition, the MicroStrategy DSS Agent client front end can now drill down into the Informatica metadata repository to look up things such as the OLTP source of the raw data used to populate the OLAP data structure and the date that the data mart was last refreshed from the operational source.
Within a single product line, Sagent Data Mart Solution suite offers the most complete internal integration from warehouse to data mart to desktop. Sagent incorporates a graphical tool for point-and-click mapping of tables and fields from source databases to data marts, a ROLAP database server, and its own OLAP query tool. However, at the time of this writing, Sagent has not announced support for any of the industry APIs and, like other OLAP server vendors, its own front end is not as intuitive as those of the leading third-party OLAP client vendors. (Version 3.0 of the Sagent Data Mart, due to be released in the first quarter of 1998, introduces a new browser-based front end.)
In terms of functional breadth, the leader would have to be Seagateıs Holos system, which includes both ROLAP and MDDB data storage options bundled with an application development tool suite, statistical analysis and data mining software, and a tight integration with Seagateıs Crystal Info toolset for reporting. Seagate Holos is relatively expensive and the learning curve is steeper than most, but thereıs a lot of functionality under the covers.
For those who prefer the best-of-breed approach, the current leader in OLAP integration is Arbor Software. There are now more than two dozen "Essbase Ready" third-party software products that can seamlessly connect to the Essbase MDDB server via Arborıs native APIs. The list includes almost all of the leading OLAP client front ends, plus administrative applications (such as Aclue and Clockwork) and complementary decision-support tools such as ABC profitability analysis software. The APC partners are Armstrong-Laing and ABC Technologies ı both use Essbase to report their outputs.
Although there is still much progress to be made, OLAP software is poised to enter the mainstream of enterprise decision-support software and take its place in a well-integrated business intelligence architecture. According to Nigel Pendse and Richard Creeth, the authors of the OLAP Report (www.olapreport.com), the market for OLAP software continues to grow at a clip of 40 percent per year, which has postponed what is seen as an inevitable shake-out in the OLAP server marketplace. Microsoft will certainly put pressure on the weaker players with its late-1998 entry into the marketplace with an OLAP server that may be either bundled in BackOffice or priced much lower than completing OLAP servers. However, I believe that when the day of reckoning finally comes, one of the key characteristics of the survivors is that they will all be firms that opened up and made their products easy to integrate into the larger data warehouse architecture through the early adoption of standard OLAP APIs and metadata and the aggressive pursuit of partnerships with third-party vendors of complementary decision-support software.
| OLAP Servers | Arbor Essbase | Oracle Express | Applix TM/1 | Gentia GentiaDB | Informix MetaCube |
|---|---|---|---|---|---|
| Olap Clients | |||||
| Andyne Computing Ltd. PaBLO | x | x | x | ||
| AppSource Corp. 1 Wired for OLAP | x | ||||
| ArcPlan Inc. ArcPlan | x | ||||
| Brio Technology Inc. Brio Query | x | x | |||
| Business Objects Inc. BusinessObjects | x | x | |||
| Cognos Corp. PowerPlay | x | x | |||
| Comshare Inc. Decision | x | x | x | ||
| Corvu Corp. Corvu | x3 | ||||
| ESRI Map Objects | x | ||||
| Hyperion Software Corp. Hyperion OLAP | x | ||||
| InfoSpace inc. SpaceOLAP | x | x | x2 | ||
| IQ Software Corp. IQ/Vision | x | x | |||
| Lighten Inc. Advance | x | ||||
| Seagate Software IMG Crystal Reports | x | x | |||
| Show Business Software Show Busines EIS | x | x | |||
| SPSS Inc. | x4 | x4 | |||
| Track Objects Inc. track Objects | x |
| 1. Recently acquired by Arbor Software 2. Connection via the OLAP Council MD API 3. Estimated March 1998 delivery 4. February 1998 delivery |
| Vendor | Product Type | Web Site |
|---|---|---|
| Andyne Computing Ltd. | Client | www.andyne.com |
| Applix TM1 Software | MDDB Server | www.applix.com/index.htm |
| Arbor Software Corp. | Client | www.arborsoft.com |
| Arcplan Inc. | Client | www.arcplan.com |
| AVOCA Systems Ltd. | Client | www.avoca.co.uk |
| Brio Technology Inc. | Client | www.brio.com |
| Business Objects Inc. | Client | www.businessobjects.com |
| Cognos Corp. | Client | www.cognos.com |
| Comshare Inc. | Client | www.comshare.com |
| Gentia Software | MDDB Client/Server | www.gentia.com |
| Hyperion Software Corp. | MDDB Client/Server | www.hysoft.com |
| Information Advantage Inc. | ROLAP Client/Server | www.infoadvan.com |
| International Software Group Inc. | www.isg.co.uk | |
| Intersolv Inc. | Middleware | www.intersolv.com |
| IQ Software Corp. | Client | www.iqsc.com |
| Knosys | MDDB Client/Server | www.knosysllp.com |
| MicroStrategy Inc. | ROLAP Client/Server | www.strategy.com |
| NCR Corp. | MPP RDBMS | www.ncr.com |
| Panorama Software Systems Ltd. | HOLAP Server | www.panorama.com |
| Pilot Software Inc. | MDDB Client/Server | www.pilotsw.com |
| SAS Institute Inc. | MDDB Client/Server | www.sas.com |
| Seagate Software IMG | HOLAP Client/Server | www.seagatesoftware.com |
| Silvon Software Inc. | MDDB Client/Server | www.silvon.com |
| Simba Technologies Inc. | Middleware | www.simbatech.com |
| Speedware Corp. Inc. | MDDB Client/Server | www.speedware.com |
| WhiteLight Systems Inc. | ROLAP Server | www.whitelight.com |
| X-Tension | MDDB Client/Server | www.x-tension.com/homepage.html |
Table 2. Companies that have committed to develop products using the Microsoft OLE DB for OLAP API to provide access to data stored in OLAP data marts.