OLAP/MDX - Detail
OLAP, standing for On-Line Analytical Processing, is a very old term, referring originally to any analysis of data in a data warehouse or “on-line”, rather than by extracting figures and manually analysing them. In it's modern form (over the past 8-10 years), it refers to the building of specialist data constructions (“OLAP Cubes”) from the data in a data warehouse, which allow very fast and intuitive analysis of data.
Data in an OLAP cube can be thought of as having analysis Dimensions, and a Measures dimension. The measures are what you want to analyse (Sales value, Qty, Profit) the measures are how you want to analyse (by Month, by Colour, by Customer, by Process). Data is conceptually held in cells, which are defined by the combination of dimensions and measures used in a query.
The answer to a given question “How many green widgets did we sell to men in Birmingham in June”, is found at the intersection of the various dimensions. (The language used to query an OLAP database is called MDX – but normal users never see this as the analysis tools use that behind the scenes)
Unlike a relational database and OLAP cube has built-in the concept of hierarchies, and of time. This means that queries about “Last year” or “The same month last year” or “The same set of products, but with Japanese specification” can be written, without the user needing to know the precise dates or part numbers. Most importantly, because the cube understans the nature of the hierarchies, it pre-aggregates data when it is processed.
So when the user asks for the total value of everything sold by everyone over the past four years, that figure is directly available and the answer is immediate. This is in contrast to a relational database where the sum would be done at query time, with potentially a long wait for the answer.
Analysis tools such as ProClarity, Polyvista and Excel Pivot Tables allow users to intuitively navigate the hierarchies, and simply combine the dimensions and measures to get the answers they want. By defining business logic and measures in the cube itself, the company can also ensure that every user gets the same answers to the same questions without having to write queries.
Altimus are Microsoft certified partners and as such would recommend Microsoft OLAP cubes, other vendors have similar technologies and though the terms and definitions may vary the concepts are similar.
To discover how Altimus can assist you with your BI initiative and for further information on OLAP/MDX call us on 0800 804 6442 or contact us through Information Request
|