A New Kid on the OLAP Block

A New Kid on the OLAP Block

In what is undoubtedly a carefully orchestrated PR campaign, Microsoft has been teasing online analytical processing (OLAP) enthusiasts with details about its new OLAP server, code-named Plato, which will accompany SQL Server 7.0. Although Microsoft has yet to reveal how it will bundle Plato, what the price will be, or even the server's official name, OLAP enthusiasts, vendors, and industry analysts are already reacting to the upcoming release, which marks the company's entry into the OLAP market.

Because Microsoft’s entry into any market always changes the competitive landscape, existing OLAP vendors have been trying to grab market share while the grabbing is good. Consider these examples:

On January 26, Informix released a new version of its MetaCube ROLAP (relational online analytical processing) Option for the Informix Dynamic Server. MetaCube has important new features, including Web support (via Web Explorer) and Microsoft Transaction Server (MTS) support. "MetaCube has unique features that should make it particularly strong with really large data warehouses," said Nigel Pendse, lead author of The OLAP Report (http://www.olapreport.com). "The innovative aggregate optimizer, statistical sampling technology, and attractive Web options give it a real lead over ROLAPs in terms of both ease of deployment and performance." MetaCube is one of five options for the Informix Dynamic Server, which supports online transaction processing (OLTP) and data warehousing applications. (For information on MetaCube and other products and concepts mention in this article, see Table 1, which contains OLAP-Related Web sites and books.)

On February 6, IBM announced the availability of its first OLAP product, DB2 OLAP Server. The core DB2 OLAP Server is functionally equivalent to Arbor Software's Arbor Essbase, which supports dynamic calculation and OLAP partitioning and replication. DB2 OLAP Server ships with the Arbor Essbase Spreadsheet Client and Arbor Essbase Application Manager.

IBM sells personal, workgroup, and enterprise editions of the DB2 OLAP Server. IBM also offers a DB2 OLAP Server Tools Bundle (consisting of SQL Interface Tool, SQL Drill-Through, Currency Conversion, API, Extended Spreadsheet Tool), DB2 OLAP Web Gateway, DB2 OLAP Adjustment Module, and a DB2 OLAP Partitioning Option. IBM continues to offer its IBM Visual Warehouse (which now has an OLAP option) as a central management package that can automate the loading, updating, and recalculating of the star schema tables in the DB2 OLAP Server from a variety of data sources.

Despite IBM's and Informix's new products, Oracle's Oracle Express Server will be Microsoft's toughest competitor in the OLAP market. Oracle Express Server is a powerful but complex product that is only now beginning to be well integrated with other parts of Oracle’s product line, such as Oracle Applications and the Oracle7 and Oracle8 data servers. Oracle Express Server includes Oracle Express Web Agent, which you can implement as an Oracle WebServer cartridge or as a Common Gateway Interface (CGI) so that it can work with other Web servers. Oracle Express Server customers can opt for special client packages, such as Oracle Financial Analyzer, Oracle Sales Analyzer, or Oracle Express Analyzer. (Although Oracle publishes an API for the Oracle Express Server, no Open Database Connectivity—ODBC drivers exist for it.) Oracle bundles Oracle Express Relational Access Manager (RAM) to provide mapping between Oracle Express Server and the traditional Oracle relational database management system (RDBMS) engine. RAM supports the star and snowflake schemas that are popular in data warehousing applications. Developers use Oracle Express Objects to create OLAP applications and can use ActiveX controls in their programs.

Laying the foundation
Before discussing the design of Microsoft’s OLAP server and the server's anticipated effect on the industry, let’s review what Microsoft has been doing to prepare for its entry into this new market. Here's a synopsis of the notable events.

In September 1996, eight vendors joined Microsoft as founding members of the Microsoft Alliance for Data Warehousing. The alliance began work on establishing repository and interoperability standards.

In July 1997, Microsoft announced a joint development and marketing agreement with PLATINUM technology, an alliance member. The companies plan to co-develop and co-market the next version of Microsoft Repository, a tool for storing and reusing objects, components, and source code. Platinum was granted exclusive rights to port the repository engine to MVS, AS/400, and most UNIX platforms. (Microsoft Repository 1.0 shipped with Visual Basic 5.0 in an unheralded release.)

In September 1997, the Microsoft Alliance for Data Warehousing released OLE DB for OLAP, a set of APIs to access and manipulate OLAP data. A few months later, Microsoft announced an open-design review process for gathering industry feedback on OLE DB for OLAP, which were to become the new data warehousing extensions for Microsoft Repository. This set of repository extensions enables vendors' data warehousing products to share information.

In January 1998, Microsoft demonstrated its innovative Data Transformation Services engine at the SQL Server 7.0 reviewer’s conference. The company also demonstrated several wizards that democratize the process of doing OLAP-type work.

Introducing Microsoft's OLAP Server
There are OLAP servers (OLAP software that runs primarily on the server) and OLAP clients (OLAP software that runs primarily on the client). There are ROLAP, multidimensional OLAP (MOLAP), and hybrid OLAP servers. As Table 2 shows, Microsoft’s new OLAP server sits in the middle.

Figure 1 shows the architecture of this OLAP server architecture. (Figure 1 is reproduced with permission from The OLAP Report, published by Business Intelligence, at www.olapreport.com.) Here is a description of its parts:

A

: Applications can be written in any language that can consume object linking and embedding (OLE) automation, including Visual Basic, VB Script, Java, Java Script, Delphi, PowerBuilder, C, and C++. However, OLAP-aware objects are not likely to be available in the short term.

B: The next version of Microsoft Office for Windows will provide basic OLAP client facilities. Microsoft will use extended pivot tables that can access the data cube service (DCS) via OLE DB for OLAP standard to achieve these client facilities.

C: Numerous third party clients and applications will support the OLE DB for OLAP standard.

D: Non-OLAP-aware SQL query and reporting tools will be able to view cubes as flattened, fully denormalized SQL tables.

E: All access to the OLAP server is via the DCS. A proprietary protocol that distributes both data and metadata to local caches links the DCS to the OLAP server. The DCS can run on the server or the client. The DCS performs all multidimensional calculations other than aggregations.

F: The DCS can access any OLE DB sources directly for small-scale desktop OLAP applications.

G: An OLAP application can have multiple, independent physical cubes, each with its own dimensions. Dimensions can be shared between cubes or private.

H: Each cube can (optionally) have multiple data partitions, possibly sourced from different databases. Partitions can use ROLAP, MOLAP, or hybrid OLAP architectures and individually optimized or cloned sparse aggregation strategies. The OLAP server aggregates data from partitions on-the-fly.

I: Virtual cubes are like relational views: They store no data and are formed by joining two or more physical cubes on the common dimensions (if any). Virtual cubes can include additional calculated members of their own.

J: The server can source data and dimensions directly from any database accessible via OLE DB (and thus ODBC). In practice, this means just about every relational database running on any platform, plus some OLAP servers, including those from Microsoft, Informix, Seagate Software, and Oracle.

K: In ROLAP partitions, SQL automatically creates the sparse aggregates, which are stored as tables in the source relational database. In hybrid OLAP and MOLAP partitions, the OLAP server automatically creates the sparse aggregates, which are stored multidimensionally. Regardless of the storage and aggregation strategy, the functionality is unchanged.

Anticipating the OLAP Server's Effects
According to The OLAP Report’s in-depth January 15, 1998, analysis, "OLAP Server: Microsoft Corporation," Microsoft's OLAP server will change the OLAP marketplace. The report states: "It is no surprise that the Microsoft OLAP server can be expected to transform the OLAP industry. It will be distributed on a mammoth scale, at prices that are a small fraction of those charged for current OLAP servers. It will have all the ease-of-use features that are expected in any modern Microsoft product. It will have unrivaled client tool support and will probably be enthusiastically adopted by legions of Microsoft solution providers and specialist application builders. What is probably much more surprising to many is that this is a product that is good enough to have been a winner even without the power assistance from Microsoft's formidable marketing machine. In practical terms, it is the first OLAP solution that can take full advantage of the capabilities of all three tiers in a modern client/server environment….There are question marks, of course. There will be some applications which would work much better with a more server-centric calculation approach, particularly if large dimensions are to be analyzed….Despite the question marks, all the signs are that this product will meet the needs of the majority of OLAP users. It will be easier to implement than most of today's large-scale servers, and will integrate well with both Microsoft and other relational databases.

Just how inexpensive will Microsoft's OLAP solution be compared to competitors' solutions? The OLAP Report states that, "Overnight, Microsoft clearly intends to turn OLAP servers into a commodity. The legendary Microsoft business model of generating huge revenues through selling enormous volumes of software products at low prices will certainly be used for the new OLAP Server. Although final prices for SQL Server 7.0 are not known, they are likely to be a small fraction of the prices charged for today's high-end OLAP serversÖproducts like such as Essbase, Holos, DSS Server or Gentia are priced close to $100,000 region for a ten concurrent user server license (including essential, but unbundled, utilities). Lower priced servers, like Express, TM1, MetaCube or Media/MR are priced in the $25,000-$50,000 bracket for a ten concurrent user system. [The new IBM DB2 for OLAP bundles start at $25,000 or $32,000 for the workgroup and enterprise versions, for example, but developers can purchase the personal developer edition for $1,300—KW]. Even if someone bought SQL Server 7.0 purely for its OLAP Server, the prices are likely to be significantly less than a tenth of those currently charged for OLAP servers."

I wholeheartedly agree with The OLAP Report’s assessment. Based on the Beta 2 version I have used, I am extremely impressed with the care Microsoft has put into ease of use. Microsoft claims to have embraced a one-size-doesn't-fit-all approach that lets users select among MOLAP, ROLAP, and hybrid OLAP storage options. The OLAP server has several wizards, including one that helps construct the new OLAP data store, using the DTS engine that makes it easy to select data from any OLE DB or ODBC data source. Another wizard will analyze your proposed model for sparsity to help you make decisions about the number of dimensions and pre-calculations. Microsoft OLAP server is a true multicube OLAP, so you can join a number of cubes to form databases and calculations can involve multiple cubes.

Mark my words: This product will change your life. What’s more, it promises to be incredibly good for a 1.0 release.