What is OLAP and an OLAP Cube?
What is OLAP?
OLAP stands for Online (OL) Analytical (A) Processing (P) is software that is designed to allow users to navigate, retrieve and present business or organisational data.
The data is stored in a ready reporting format. There is no need to write complex queries.
Fully interactive drag and drop sessions is usually provided too rather than having to write a complex report that might take several hours. The data is catergorised into dimensions and the software used is designed to work with numeric data where you usually need to add up, consolidate or sum the data such as sales per quarter, sales per year per product.
Dimensions could be customers, products, regions, countries, and the time dimension e.g. weekly, monthly, quarterly and annually. A solution usually has many dimensions usually three or more and could be termed multidimensional.
What is an OLAP cube?
The data has a different architecture from other forms of data storage. There are two components – firstly the data built into a cube that houses the multidimensional data and secondly access tools to build and manipulate the data.
A cube is a specialised data store designed to handle multidimensional data and the aggregated numerical data. If you have 30 odd dimensions you cannot imagine this data structure…just take it for granted
There are four main features:
What is OLAP Architecture
- Consolidation – you can rollup or sum the data to higher levels of abstraction e.g local sales office, regional office, national office and so on.
- Drill Down - you can navigate down or up through the data with a few clicks.
- Calculations – you can create derived calculations such as profit margins, yields etc.
- Pivot tables – you can readily view the data from different points or perspectives.
There are four main types of architecture
- ROLAP – calculations are done in a relational database and so with large volumes response times can be slow. All calculations are pre-generated when the cube is populated.
- MOLAP – M stands for multidimensional - calculations are performed in a server based multidimensional database. Cubes are used and experts are really needed to do the design work.
- HOLAP – H stands for Hybrid – this form tries to combine the best of both worlds. Aggregations are done in memory and drilldowns use the relational type model.
- DOLAP – D stands for Desktop - in this model the cubes can be stored on the local desktop and are usually built at runtime.
The major drawback is the cube design. Business analysts have to try to anticipate what end users want before data structures are created. This becomes quite complex and can several months. It often takes experts who specialise the one particular vendors product to do this kind of work well.
This must be an exhaustive process too because once set the design becomes set in concrete and is even harder to change
Return to Top of 'What is OLAP?'
Return to An Overview of Business Intelligence Software
Return to What is Business Intelligence?