Gotta put on my running shoes and sprint for the sake of Assignments!
After many years of being a Mircrosoft patron, I finally bought my Mac and thought I would never need that windows XYZ(umpteen possible OS versions) again but much to my dislike, have to install n numbers of Microsoft products(Windows OS, MS office, MS SQL Server, Visual Studio .NET etc.) for my assignment. Still struggling with the installations and wondering if I will ever have a working development environment on my laptop for my Assignment.... :(
Sorry, I got offtrack ....we are not here to discuss about my trifle with technology but to talk about Business Intelligence concepts.
Finally I got to know the much spoken jargons of the BI world... OLAP, MOLAP, ROLAP, HOLAP, MDM, Cubes, Dimensions, Facts and so on.
I used to think this all Data warehousing, mining and Business Intelligence and all thing is pretty recent but POD mentioned that the terms Dimensions and Facts were coined in 1960's by General Mills together with Dartmouth college. WOW!!
Below is a short and sweet description of the above mentioned jargons not in the same order though.
Facts - Measurable data which is of interest to the user.
Dimension - A particular angle or perspective to view the facts. For example - if facts are the sales per day for 10 various products and as a user if I want to see the sale of a particular product or sale in a week. Time and products will be dimensions here and fact will the sale figures.
The dimensions can have hierarchy for example country can have child as states and states can have cities and so on.
The position in the hierarchy is identified with level.
I learnt that there is always a Time Dimension!
There are Dimension tables to store information(attributes) about that dimension example a customer dimension table can have customer name, address and other information along with a unique key to identify a customer which we can call as customer key. So, a dimension table has Key, Attributes and Values.
A Fact table has measurable Facts(measures) and keys to the Dimension tables. Measures are numeric and describe the quantitative aspect of a fact. They can be either calculated or based on column of a table. Example sale units, revenue, cost etc.
Multi-Dimensional Model (MDM) is a representation of data in terms of dimensions, measures, dimension attributes, hierarchy, level.
Figure - Diagram of the Logical Multidimensional Model
(Reference- Oracle® OLAP Application Developer's Guide10g Release 1 (10.1))Now the C word( Cube!) hated by POD and logically so. The cube is a kind of metaphor used in BI world and using it front of a lay man should be punishable crime.
Cube is an amalgam of dimensions and measures. The edges of the cube mark the dimensions while the body contains measures.
OLAP stands for On-Line Analytical Processing and it is an approach to answer multi-dimensional queries.
MOLAP stands for Multi-dimensional OLAP and it involves data being stored in form of multi dimensional cubes. Fast as cubes are pre-calculated but can not handle much data.
ROLAP stands for Relational OLAP and the underlying data being is stored in form of in form of relational database. Can handle large volumes of data but it is slow to retrieve the data.
HOLAP stands for Hybrid OLAP and it combines best of the MOLAP and ROLAP models. For summary type information, MOLAP's cube are leveraged and for drilling down details, ROLAP is used.I found one more flavour of OLAP called as DOLAP (Desktop OLAP) in which the cubes are stored in desktops and the analysis can be done in offline mode.
Options for Supporting OLAP by Colin White
(Source-OLAP in the Database,Intelligent Business Strategies,Information Management Magazine, June 2003)
Also learnt the CODD's 12(Controversial!) rules but will cover that in next blog along with the introduction of now dead OLAP council.
No comments:
Post a Comment