The truth is out there: you can?t ignore Olap for long

DON?T be fooled by the name. Olap, or Online Analytical Processing, may sound as dull as dishwater, but it?s appearing on every business? shopping list. A data warehouse is useful only if appropriate tools and applications are available to retrieve and analyse data. Olap tools are now considered to be the obvious means of providing the analytical functions that are needed to unlock all the information stored in the warehouse.

Olap is being used in an increasingly wide range of applications, especially for finance and sales analysis, or for any management system that requires a flexible, top-down view of an organisation.

Olap applications are characterised by a flexibility which enables users to view and report data any way they require, perform ad hoc analyses, carry out large-scale, complex calculations and perform dynamic exception reporting from large databases.

They are therefore increasingly attractive to organisations that are undergoing change, whether because of deregulation, business process re-engineering, competition or rapid growth.

Nigel Pendse, director of Olap Solutions and co-author of a report on the technology from Business Intelligence, says: ?The multi-dimensional characteristic of all Olap applications is thought to be invaluable to any user who has to compare current performance with the previous period or against targets, or compare the relative profitability of different products, customers or business units?.

EMI Group is using Seagate?s Holos tool for decision support and modelling applications at its London headquarters. The company?s planning team uses a corporate financial model, while the reporting team is working with a year-end accounts analysis system and the tax department uses a year-end tax analysis and VAT application.

?We looked at a range of products, starting with spreadsheets, but found that the volume of data we had to manipulate was too large for anything other than a heavy-duty Olap system,? says Ross Martin, EMI Group?s corporate IS manager.

At the Mirror Group, data from the company?s 150 wholesalers ? responsible for distributing the papers nationwide ? is received daily via EDI, and fed into both an operational system and a Microsoft SQL Server-based data warehouse. The company uses Microstrategy?s DSS Agent to analyse the data. ?With the introduction of EDI, the company has been able to automate the sales order procedure, creating a role for the sales force that focuses strongly on information analysis,? says George Bowes, Mirror Group?s IT manager. ?And it is the user base that has driven the implementation of Olap software.?

Different users want to look at sales and performance information at different levels. They need to be able to compare the performance of different wholesalers or groups of retailers. The Olap software gives users the ability to drill down through the figures and merge the information with other factors, such as geography and monetary values.

?While information on sales based on the number of copies that have been sold or returned is obviously extremely pertinent, there is also an opportunity to show costs and profits at a more detailed level,? says Bowes. ?If we can manage to knock 1% off our returns, that is the equivalent to adding #1m to the bottom line.?

Certainly, for anyone who wants to implement a data warehouse but hasn?t yet done so, an Olap application is likely to be an excellent guide to what should be in it.

If you don?t have a data warehouse, then some would argue that a good place to start might be to install an Olap application on a single PC server. This enables you to learn the lessons of running a smaller data warehouse, or data mart (departmental warehouse), and branch out form there. However, this is not a line that all Olap vendors would encourage.

Pendse says that more than 30 vendors claim to provide Olap products. ?The Olap market currently has no dominant players, and we do not expect any single vendor to dominate it within the next two years,? he says. ?The largest current supplier, Oracle, has less than a 20% market share, and this is not increasing significantly. The largest potential player, Microsoft, has yet to enter the market, and is not expected to release a product until later this year.?

According to Pendse, the Olap market is split into four sections. ?Application Olap is the sector that has been around the longest. It existed two decades before the term Olap was coined, and has many vendors, including Oracle, Hyperion, Comshare, Holistic Systems, Pilot, Gentia Software, SAS Institute, Acuity and Information Builders. Products are sold either as complete applications or as complete toolkits from which complex applications can be built.

?Multi-dimensional Olap ?has the advantage of providing fast and consistent response times for user queries, almost independently of the size of the database which is designed and tuned only for Olap work?, says Richard Neale, SAS Institute?s data warehousing product manager. ?The downside is additional data replication, the potential for data explosion and no flexibility in the way data is stored.?

?Desktop Olap has existed since 1990, but has only become well known in the last year. Products in this sector are client-based Olap tools that are easy to deploy and boast a low cost per seat. They usually have good database links, often both to relational and multi-dimensional servers, as well as local PC files. Products are made by Cognos, Business Objects, Brio Technology, IQ Software and Andyne.

?Rolap ? relational Olap ? represents Pendse?s fourth sector and has existed since the early 1980s, so none of the current vendors can honestly claim to have invented it. Rolap has become recognised only in the last two years, by vendors such as MicroStrategy, Information Advantage, Platinum Technologies and Informix.

Rolap is by far the smallest of the Olap sectors, but has had a great deal of publicity thanks to some astute marketing. ?Products in this sector draw all their data and metadata in a standard RDBMS, with none being stored in any external files,? says Pendse.

They are suitable for read-only reporting applications and are most often used for sales analysis in the retail, consumer goods, telecoms and financial services industries. Their main market is companies which have very large numbers of customers, products, or both, and wish to report sales in detail.

?Relational Olap is very scalable and has more flexibility in the way the data is stored,? says Neale. ?But response times, even on small databases, will never be anywhere near as good as with a multi-dimensional database, and you will probably need to either spend a lot of time changing the database design to extract more performance, or invest in powerful hardware.?

Neale advises IT managers dipping their toes in the water for the first time to implement Olap at the data mart level. ?Don?t structure your entire data warehouse to be suitable for Olap because it is not the only way of exploiting the data there ? albeit very powerful,? he says. ?You will also need other techniques, such as query and reporting and data mining.?

The features offered by most Olap tools are broadly similar, but there are subtle differences to look out for. What makes a particular product unique? Does it have other tightly integrated exploitation techniques like GIS? How well integrated is it with your other data warehouse tools? What happens if you upgrade the relational database? Will the Olap vendor be able to follow?

Tight integration with underlying relational systems will quickly become a requirement so that the summary Olap data can be kept easily in step with transaction levels. Is the software scalable? Is it truly enterprise-wide? Does it provide genuine decision support?

Ian Macdonald, Gentia Software?s vice president for European marketing, sums up the case. ?In short, does the package merely deliver a useful query tool for users, or genuine business intelligence for long-term business development?

?Most user query tools are too complex for users, while most business queries are too complex for end user query tools to satisfy.? The volume of data is also bound to grow more quickly than you might expect. This is because Olap data can be more complex than relational data, and requires just as much design effort for success.

?Suggestions that Olap is easy should be treated with suspicion, particularly when people say: ?Just put all your data in one big cube and everything will be alright?,? warns Derek Taylor, vice president of marketing and sales at Seagate Software IMG. ?This is akin to taking a database problem and putting all the data into one big table.?

And of course, just because Olap analysis allows users to navigate large volumes of data quickly, this doesn?t necessarily mean that they will understand it better.

?Some Olap systems need an application infrastructure around them so users understand the information in their own terms, without getting bogged down in the Olap technology itself,? says Taylor.

MDB Olap?s extra dimension

Nearly all application Olap products include a multidimensional database (MDB), although a few also work as hybrid Olaps. ?Sometimes the bundled multidimensional engines are not especially competitive in performance terms, and there is now a tendency for companies in this sector to license multi-dimensional engines from MDB Olap vendors,? says Nigel Pendse, director of Olap Solutions. ?Typical strengths include integration and high functionality, while the weaknesses may be complexity and a high cost per user.? MDB Olap has been identified in the last three years, although the sector has existed for longer. It consists of products that can be bought as unbundled, high performance multi-dimensional databases. Vendors include Arbor, Applix, Oracle and Gentia.