Olap reorganises data into a form that is meaningful to people and businesses, writes Nick Langley
What is it?
Online analytical processing (Olap) is a method of extracting business information by organising data into "dimensions" such as product, sales territory, customers and sales period.
Users can devise queries to find out, for example, whether particular products are selling poorly in certain areas. Olap enables them to drill deeper into the information (for example, the performance of individual stores or salespeople) or take an historical view (ie, have new products always been slow to take off in this territory?)
Once restricted to professional analysts using complex and expensive products, Olap technology has been incorporated into business intelligence tools such as those from Cognos and Brio. It entered the mainstream with Microsoft's SQL Server 7, which incorporated an Olap engine.
Where did it originate?
The first recognisable Olap product was Express, released in 1970, which is still going strong as part of Oracle's Olap offering. The best known Olap product, Essbase, was launched in 1992.
The term "Olap" was coined by Ted Codd, father of the relational database, in 1993. But Codd's paper was sponsored by Arbor, the company behind Essbase, and other suppliers denounced Codd's definition as too product-specific.
IBM released its DB2 Olap Server, a version of Essbase, in 1998. SQL Server 7 came out in 1999, bundling what was then called Olap Services, now Analysis Services.
What is it for?
There are two types of Olap. Relational Olap (Rolap) works directly against relational databases. Multidimensional Olap, or Molap, works on summary databases.
Molap is better on small bodies of information; Rolap is more scalable. Molap has richer analytical functions, but Rolap can go down to a more granular level. Rolap uses the underlying strengths of the relational database, but Molap is easier for non-technical users. There is also a class of hybrid Olap products (Holap). Fortunately, these Olap terminologies are gradually dying out.
What makes it special?
Databases store data in the tabular form computers prefer. Olap enables information to be reorganised in a way that is meaningful to people and businesses.
How difficult is it?
Early Olap products were toolkits for building queries. Now most come with vast libraries of predefined queries, which suppliers claim meet 80% of needs. The other 20% must be defined by professional analysts.
There is a lot of grunt work involved in making data suitable for Olap: merging multiple sources, cleaning data up, and making it complete and consistent.
Where is it used?
In sales analysis, database marketing, clickstream analysis and management reporting.
Not to be confused with...
A Scandinavian christian name.
What does it run on?
The Olap Report (www.olapreport.com) says more than 30 suppliers claim to provide Olap products. The market leaders are Microsoft and Essbase (Hyperion).
Few people know that...
Up to half of all Olap seat licences bought are never deployed. "A tribute to the skills of Olap sales people, or a sad indictment of project success rates," says the Olap Report.
What's coming up?
XML for Analysis, an XML-based Olap application programming interface developed by Microsoft, Hyperion and the SAS Institute.
Independent training organisations offer courses for users of IBM, Essbase, and especially Microsoft Olap products. For others you will need to contact the supplier.
Rates of pay
Olap analysts can expect £25,000 to £30,000, while datawarehouse architects can command £50,000+. Microsoft jobs are easier to come by but pay less.
This was first published in February 2003