Storage types in Data Warehousing

Tuesday, January 26, 2010

Overview

There are mainly three kinds of storage types based on where the data or facts are stored.
1. MOLAP (Multi-dimensional OLAP)
2. ROLAP (Relational OLAP)
3. HOLAP (Hybrid OLAP)

MOLAP
• In this storage mode the data and aggregated data are stored in multi dimensional cube.
• It processes data which is already stored in multidimensional array with all possible combinations of data reflected, each in a cell that can be accessed directly.
• The main advantage is it can retrieve data very fast.

ROLAP
• The data which is stored in the relational database. Aggregated or summary data is also stored in the relational database.
• Performance can be slow. Because each ROLAP report is essentially a SQL query (or multiple SQL queries) in the relational database, the query time can be long if the underlying data size is large.

HOLAP
• A combination of relational OLAP (ROLAP) and multidimensional OLAP.
• Data will be stored in relational format (ROLAP). Aggregations, on the other hand, are stored in multi dimensional format(MOLAP) in the cube to give better query performance.

Key elements of Data Wahrehousing

Overview
• A multi-dimensional database is created from fact and dimension tables to form objects called dimensions and cubes

Dimensions
•Dimensions are most often made up of several hierarchies
•Examples: time, geography, employee

Hierarchy
• Logical entity by which a business user might want to analyze fact data
• Each hierarchy can have one or more levels
Example: A hierarchy in the geography dimension –Country, State, County, City

Completely Balanced Hierarchy
• All leaf(end) nodes would be an equal level from the top level.
• E.g. : Geography dimension

Unbalanced Hierarchy
• Hierarchy in dimensions having an unbalanced distribution of leaf nodes relative to the top level.
E.g : Organization chart

Ragged Hierarchy
• Some hierarchies are typically balanced but are missing a unique characteristic of some members in a level.
• Example: geography hierarchy that contains the levels Country, State, and City. (USA-Washington-Seattle, Greece-Athens)

Cubes
• The cube is a multi dimensional data structure which we can query for business information.
• Each block of the cube is called a cell and is uniquely identified by a member in each dimension.
• Cubes reduce the query response time for the information worker to extract knowledge from the data – contain precalculated summary data called aggregations
• That is, cubes not only store multi dimensional data from fact tables, but also aggregations of that data.
(summing of sales figures up from stores level, to district level, to regional level)