Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Datawarehouse » Data Warehouse Articles Archive » A Look Into The Future Of Microsoft And Oracle OLAP
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3548
 

A Look Into The Future Of Microsoft And Oracle OLAP

by Mark Rittman

Regular readers of this column will probably know that I work as a developer on the Oracle platform, putting together business intelligence and data warehousing applications using Oracle’s database and application server platforms. In the past, I’ve covered Oracle products such as Warehouse Builder and Oracle 10g. This month, during which I look forward to what Oracle has planned for the OLAP market,  I’m going to broaden the remit and look at what Microsoft has in store as well. It’s an interesting story, as these two giants of the database industry have quite distinct and different views on how the OLAP industry will unfold, and each view has its own appeal.

Oracle has recently released version 10g of its database, which now comes with an embedded multidimensional OLAP server known as the OLAP Option. Microsoft, which so famously took the OLAP market by storm with Analysis Services that came bundled with SQL Server 2000, is due to release the long-awaited upgrade, originally code named “Yukon,” but now renamed SQL Server 2005. Oracle and Microsoft are, in their own ways with these new releases, looking to reshape the OLAP industry, and this will have big implications for DBAs who currently look after data warehouses running on traditional relational databases. So what have Oracle and Microsoft got in store, and what impact will this have on your future business intelligence applications?

A Bit Of History

The OLAP industry has a long and illustrious history, with the concept of multidimensional analysis going back to Ken Iverson’s book, A Programming Language, released in 1962 with an implementation following on in the late 1960s, and Oracle’s own Express technology implemented by Oracle’s original designers back in 1970. The OLAP industry has, until recently, been dominated by niche vendors such as Hyperion, Comshare, Microstrategy, with OLAP servers being typically priced in the tens to hundreds of thousands of dollars, and data stored in standalone, dedicated multidimensional databases. However, in the past few years, the big database vendors such as Oracle and Microsoft have started to move into the OLAP market, bundling OLAP servers with their traditional relational databases, and pricing them competitively to undercut the “best of breed” vendors. Initial versions of these OLAP servers concentrated on playing “catch-up” with the dedicated OLAP vendors, but new releases from each of the vendors in their own way threaten to redefine the OLAP market and move the game away from the dedicated OLAP vendors. So what are Oracle and Microsoft up to?

Microsoft Analysis Services 2005

Microsoft made a huge impact on the OLAP market in 1998 when they released OLAP Services, a “free” OLAP server bundled with SQL Server 7.0. OLAP Services was built on technology Microsoft acquired when it purchased the OLAP technology of Panorama Software Systems, an Israeli startup, which gave Microsoft an experienced team of OLAP developers to drop in to their existing SQL Server development team based in Redmond, Washington. Although priced competitively and with an impressive set of features, take up of Microsoft’s OLAP server was limited until the release of SQL Server 2000, which came with an upgraded OLAP server now known as Analysis Services. Analysis Services, renamed because it now incorporated a limited set of data mining features, was a runaway success and is now the market leading OLAP server, with 23 percent of the market in 2003, ahead of Hyperion at 22 percent and Cognos at 14 percent.

Although bundled along with SQL Server 2000, Analysis Services is a separate server product and is often used with non-Microsoft databases such as IBM DB2 and Oracle RDBMS . However, given that SQL Server was released five years ago, the Analysis Services technology is getting long in the tooth now, and all attention is now focused on the version of Analysis Services being delivered as part of the next release of SQL Server, code named, “Yukon,” but now officially named SQL Server 2005.

SQL Server Analysis Services 2005 promises to be virtually a rewrite of the technology behind Analysis Services 2000, with many changes within the OLAP server itself, and a radically different approach to populating and accessing OLAP cubes. One of the key new features coming with Analysis Services 2005 is the concept of a Unified Dimensional Model (UDM).

The concept of a unified dimensional model is not a new one, and allows DBAs and developers to define dimensions, OLAP cubes, and hierarchies in an “abstract form,” which can subsequently be stored either relationally or multidimensionally. Oracle has had this for some time, with the Oracle9i database allowing OLAP data to be stored in either relational tables, or in dedicated Analytic Workspaces, with a unified dimensional model being held in the OLAP Catalog. Microsoft is now implementing its own unified dimensional model for Analysis Services 2005, and is using it to help blur the line between relational and multidimensional reporting.

Analysis Services 2000 had, like many OLAP servers, a limit on the number of dimensions that could be associated with a cube, and this limit is being removed for Analysis Services 2005. In addition, Analysis Services 2005 will come with a feature called “Intellicube,” which, when used in conjunction with the unified dimensional model and the removal of all dimension limits, will allow the DBA to throw any relational schema at Analysis Services 2005. This then automatically creates a (tunable) cube schema, identifying fact, and dimension tables.

Along with the Unified Dimensional Model, another key technology promised for Analysis Services 2005 is “pro-active caching,” a method of populating OLAP cubes from source relational data, and progressively aggregating them into multidimensional OLAP cubes.

Figure 1: A slide from a recent Microsoft presentation on UDM and Pro-Active Caching.

Pro-Active Caching of OLAP cubes works by initially defining a cube against source relational data, at which point it becomes available for analysis. Then, over time and as data can be aggregated, the cube becomes, first, a hybrid multidimensional-relational cube, and then, once the cube is aggregated in the background, the cube is fully multidimensional with optimised aggregates added as necessary, removing the need for DBAs to explicitly load and process the cube. This approach by Microsoft means that, in theory, with little design effort, DBAs can implement OLAP databases that are fully real-time, and yet have all the benefits of optimised multidimensional OLAP, the latter occurring automatically if the data isn’t changing. Oracle is planning a similar feature for Oracle OLAP, with the division between multidimensional and relational storage becoming blurred over time (but isn’t likely to roll out a working version until at least Oracle 11).

Apart from price and an impressive set of functionality, the other main plank of Microsoft’s assault on the OLAP marketplace is their OLAP query language, MDX. Superficially like SQL, but a true multidimensional query language, MDX has become the de-facto OLAP query language and is supported by the majority of OLAP query tool vendors.

For example, a basic MDX query (taken from MSDN’s Introduction to MDX) would look like:

SELECT
   { [Measures].[Unit Sales], [Measures].[Store Sales] } ON COLUMNS,
   { [Time].[1997], [Time].[1998] } ON ROWS
FROM Sales
WHERE ( [Store].[USA].[CA] )

MDX, at first glance, looks a lot like SQL, but the similar looking syntax hides quite a few conceptual differences between the two languages. According to MSDN’s “Comparison of MDX and SQL,”

“The principal difference between SQL and MDX is the ability of MDX to reference multiple dimensions. Although it is possible to use SQL exclusively to query cubes in Microsoft® SQL Server™ 2000 Analysis Services, MDX provides commands that are designed specifically to retrieve data as multidimensional data structures with almost any number of dimensions.”

SQL refers to only two dimensions, columns and rows, when processing queries. Because SQL was designed to handle only two-dimensional tabular data, the terms “column” and “row” have meaning in SQL syntax.

MDX, in comparison, can process one, two, three, or more dimensions in queries. Because multiple dimensions can be used in MDX, each dimension is referred to as an axis. The terms “column” and “row” in MDX are simply used as aliases for the first two axis dimensions in an MDX query; there are other dimensions that are also aliased, but the alias itself holds no real meaning to MDX. MDX supports such aliases for display purposes; many OLAP tools are incapable of displaying a result set with more than two dimensions.”

Analysis Services 2005 extends the functionality of MDX and introduces support for XML for Analysis (XML/A), a standard built on XML, backed, not only by Microsoft, but also by SAS and Hyperion. One area that’s particularly interesting about XML/A is that it’s designed from the outset to be a SOAP Web service, accessible over HTTP. This theoretically opens up Analysis Services 2005 to any server on the your network; this area is still somewhat in its infancy, but it’s good that the standards are there to fit in with future service-orientated architectures.

For now, however, the next version of Analysis Services significantly extends the functionality delivered with the first two releases of Microsoft’s OLAP server, and within this context, Oracle has been making improvements of their own.

The Oracle OLAP Option

Although Microsoft has had much success with Analysis Services, Oracle was the first relational database vendor to recognise the significance of OLAP, with its purchase of Express from IRI back in 1995. Oracle’s move into the OLAP world both legitimised the OLAP industry and served as a wake-up call to the traditional OLAP vendors that they were now likely to face increased competition from the vastly larger relational database vendors.

From the moment that Oracle acquired the Express technology, its aim was always to integrate it into the Oracle relational database. This aim was finally delivered with Oracle 9i, which was billed by Oracle as the “first relational-multidimensional database engine,” with the OLAP technology a separate licensable option known as the OLAP Option.

By integrating the Express technology into the database, Oracle took a distinctly different path from Microsoft, which kept its OLAP technology quite separate from its mainstream relational technology. Oracle 9i and the OLAP Option run as a single process, with a single database instance to manage, and with the OLAP technology able to take advantage of Oracle features such as Real Application Clusters, partitioning, DataGuard and now, grid technology. Oracle, unlike Microsoft SQL Server, also runs on platforms other than Windows, with the OLAP Option ported to all of the server platforms that the regular Oracle database runs on, including Linux and Apple OSX.

If you read my previous article on the Oracle 10g OLAP Option, you’ll probably have a good understanding how Oracle’s OLAP offering stacks up. Oracle is now getting ready to release the next version of Oracle 10g OLAP, known as version 10.1.0.3. Oracle will be releasing 10g OLAP together with the next generation of query tools designed to work alongside 10.1.0.3, versions which are likely to go head-to-head with Analysis Services 2005.

Figure 2: Oracle 10g OLAP architecture.

Oracle’s strength, both in terms of relational databases and OLAP servers, has traditionally been in environments that feature large analysis sets, lots of concurrent users, and demanding requirements for complex multidimensional analysis. Oracle’s highly-featured OLAP Data Manipulation Language (OLAP DML), based on Express’ 4GL, has features that are only now being copied by Microsoft in MDX, and the next version of Oracle 10g OLAP comes with a number of new features to support large analysis sets.

Partitioning has been improved in Oracle 10g OLAP, with individual measures, dimensions, and, indeed, any analytic workspace object now able to be assigned to individual partitions. 10g also introduces a novel form of cube compression, which promises to both enhance query performance (by retrieving fewer blocks of data for a given logical amount of data) and drastically reduce batch loading and aggregation times, saving disk space on the way. 

Compression in Oracle 10g OLAP is more about improving performance and scalability than saving disk space (although that’s a nice side effect). The net result of this is that, in a given batch window, Oracle 10g OLAP can now load and aggregate more data than before, and for a given amount of disk, can store more information than before. This, plus big advances in scalability internally around areas such as very big composites, makes Oracle 10g OLAP potentially a very effective platform when building particularly large cubes.

Another key differentiator between Oracle and Microsoft’s approach to OLAP is their respective choices of query languages. As we’ve seen before, Microsoft favours MDX, their own multidimensional query language and XML/A, as the API.

Oracle, however, is firmly putting its emphasis on regular SQL, with analytic extensions, as its OLAP query language of choice. Oracle provides, as part of the OLAP Option, the OLAP_TABLE function, which allows regular SQL statements to access native multidimensional datatypes held within analytic workspaces. This opens up Oracle OLAP to mainstream relational reporting tools such as Microstrategy, Business Objects, and Cognos.

As an example, the following SQL query (taken from an Oracle white paper on SQL access to OLAP data) shows how the OLAP_TABLE function can retrieve multidimensional data into a regular row-and-column rowset:

SELECT time_id, channel_id, product_id, customer_id, sales, 
forecast_sales       
      FROM table(OLAP_TABLE('ddepot DURATION query',             
            'SALES_TYPE_TABLE',             
            'FORECAST_SALES',             
            'DIMENSION time_id FROM time             
            DIMENSION channel_id FROM channel             
            DIMENSION product_id FROM product             
            DIMENSION customer_id FROM customer             
            MEASURE sales FROM sales             
            MEASURE forecast_sales from fcast_sales')) 
WHERE time_id IN ('Oct02','Nov02', 'Dec02') 
AND channel_id = 'Direct' 
AND product_id = 'Tide' 
AND customer_id in ('All Customers');

Lastly, the other big news coming out of Oracle for 10g is the imminent release of Oracle 10g Business Intelligence, the first set of Oracle query tools to work natively against Oracle OLAP analytic workspaces. Take up of the Oracle OLAP Option has been limited up to now because of the lack of available “out of the box” query tools; Oracle plans to remedy this situation over the next few months with Discoverer Plus OLAP, a new version of Oracle Discoverer that works against both relational, and OLAP, data sources; a new Microsoft Excel Add-in that allows OLAP data to be worked against using the familiar Excel interface, and Enterprise Planning and Budgeting, a full budgeting, planning, and analysis suite being delivered as part of Oracle Applications 11.5.9.

So Where Is This All Leading?

Although many of the new features in Oracle 10g OLAP and Microsoft Analysis Services 2005 are there to match new features introduced by the competition, the main difference in approach that is emerging from the two database vendors is focused around the use of MDX, on the part of Microsoft, and SQL, on the part of Oracle, as the query language of choice.

Features that Microsoft is due to introduce, such as the unified dimension model, pro-active caching, and Intellicube, are all focused on making it easier to move data into Analysis Services cubes, effectively removing the needs for any data warehouse relational queries, with an optimised Analysis Services 2005 cube schema that, in theory, provides all the flexibility of relational with the performance of MOLAP, without ever having to design aggregate tables. Of course, this makes MDX Microsoft’s query language of the future, and XML/A the API.

Oracle, however, is working towards embedding their OLAP functionality deeper into the relational database, with OLAP data being held within the Oracle database, for now either relationally or multidimensionally, but in the future, in some hybrid format that is dealt with at a lower level about which DBAs will not need to be concerned. Oracle, with its strength in the relational database market, is putting the relational database at the centre of its OLAP strategy, with the database natively supporting multidimensional datatypes and object storage. For Oracle, SQL is its query language of choice, with the Java OLAP API (or in future, JOLAP) as the API, reporting against OLAP data firmly embedded in the Oracle database.

The success of Microsoft’s approach will inevitably be down to whether it can replace SQL with MDX as the query language of choice. Oracle’s success will be down to how well it can embed OLAP into the relational database engine, and whether SQL, with analytic extensions and the OLAP_TABLE function, will be viable as a multidimensional query language.

Choices to be Made

DBAs who work within largely Oracle or Microsoft environments will naturally gravitate towards the OLAP offerings by their respected vendors. Whilst Microsoft SQL Server 2005 isn’t due until 2005, many of the new Analysis Services features will have DBAs looking at how best to take advantage of what’s going to be on offer. Oracle’s latest version of the OLAP Option is available now, and the likely availability of new versions of Oracle’s query tools is sure to increase take-up of its OLAP technology.

But what of organisations that are looking to implement OLAP analysis, but don’t have a particular allegiance to either Oracle or Microsoft? What it boils down to is this: If your organisation has experience with Microsoft SQL Server, you’re happy to move all of your reporting data into Analysis Services OLAP cubes, and do all your reporting using MDX and XML/A. Ease of administration and setup is a requirement, and if you’re standardising on the Windows platform, take a closer look at Microsoft Analysis Services 2005 when it comes out next year. If you’re interested in embedding OLAP analysis into your regular database applications, if scalability and supporting large numbers of concurrent users is a priority, if you’re willing to deal with a steeper learning curve, if you’re considering platforms in addition to Windows, and your organisation wishes to use SQL to carry out all their reporting, then it’s worth looking in more detail at Oracle OLAP.

--

Mark Rittman is a Certified Oracle Professional DBA and works as a Consulting Manager at SolStonePlus, specializing in developing BI and DW applications using the Oracle Database, Application Server, Discoverer, Warehouse Builder, and Oracle OLAP. Outside of Plus Consultancy, Mark chairs the UKOUG BI and Reporting Tools SIG, and runs a Weblog dedicated to Oracle BI and Data Warehousing technology. Mark recently was awarded an OTN Community Award for contributions to the Oracle developer community, and is a regular speaker at Oracle User events in both the UK and Europe.


Contributors : Mark Rittman
Last modified 2006-02-08 02:53 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone