OLAP, Cubes and Multidimensional Analysis -
Hugh McLeod is quite correct, On-Line Analytical Processing (OLAP) is actually about business, it just sounds like a science project.
Basically OLAP is an awful name, Nigel Pendse, author of the OLAP report calls the same thing FASMI, which I think is a far better term :
- Fast - 90% of queries back in under 10 secs and no query takes longer than 30 secs.
- Analysis - Drill down, multiple aggregation techniques, sophisticated graphics, trends all form part of this
- Shareable - good security at the back end and available to a wide community of users.also multi currency, multi lingual to cope with the global economy.
- Multi-Dimensional - Excel pivot tables but more so. The ability to have any multiple dimensions of information on each axis of a cross-tab with other dimensions being used to further filter the results returned.
- Information - Real world KPI's rather than raw numbers.
In OLAP the cube is the database structure that is queried on and to get a handle on how this works below is a simple 3 dimensional cube
The coordinate system in a cube not only has a reference to a point in multidimensional space it also has an understanding of hierarchies. So the cube 'knows' that January 2007 has a parent called 2007 in the example above. This forms a key part of the OLAP concept - that the results of calculations can be stored at the parent level rather than using on the fly aggregation of all the children e.g. the sales total for 2007 is stored in the cube for bike, components etc. as is the cost of sale. The profit margin % has to be worked out on the fly for bikes for 2007 but this is quick as the cost of sales and the sales that contribute to this calculation are pre-calculated. This gives OLAP it's speed while allowing for rich calculations to be stored. As always in IT there is a catch, and in my opinion that is the complexity of the language used to query a cube and that is MDX or multi-dimensional expressions. Like SQL, this is an open standard that also runs over cubes in such products as Oracle/ Hyperion Essbase, SAS, Cognos Powerplay and Microsoft's SQL Server Analysis Services (SSAS). More on MDX in future posts but if you can't wait, the go to MVP on this in the UK is Chris Webb and the book he has co-authored