Dimensional Data Modeling - Dimensional Schemas
About
This section is about dimensional data modeling, That is the building of a cube (hypercube)
Type
The dimensional schema can modeled:
- in a relational database as:
- in a OLAP database as a
Model Elements
The terms dimension and fact come from a joint research conducted by General Mills and Darmouth Univeristy in the 1960s.
Introduction
Like most design tasks, dimensional modelling tends to be an iterative process.
A dimensional schema is a denormalized schema that follows the business model. Dimensional schemas contain:
- dimension tables, which contain attributes of the business,
- and fact tables, which contain individual records with a few measure and foreign keys to each of the dimension tables.
Dimensional schemas are very good for business analysis and have two major advantages over E-R schemas for decision support:
- Easier to understand. The database structures is easy for end users to understand and write queries against. It reduce drastically the complexity of the database structure.
- Better query performance (as opposed to update performance)
A star schema has a fixed structure that has no alternative join paths, which greatly simplifies the evaluation and optimization of queries (Raisinghani, 2000).
Dimensional schemas are not nearly as efficient as E-R schemas for updating discrete records, but they are excellent for queries that analyze the business across multiple dimension.
Dimensional Modeling is based on a single, highly regular data structure called a star schema (or snowflake schema for the normalized form).
The terminology of “dimensional model” derives from the fact that a star schema may be visualized as a data “cube” where each dimension table represents a different spatial dimension (or more generally a hypercube, as a star schema may have any number of dimensions).
Each dimensional schema is centred on a single business event.
A dimensional model is just a restricted form of an ER model
Weakness
Dimensional models assume an underlying hierarchical structure of data and exclude data that is naturally non-hierarchical (e.g., network structured data).
Parent-Child/Aggregate Dimensional schema
Multiple dimensional schemas at different levels of detail (grain) may be required to speed up the retrieval process of the data query but they still concern the same dimensional model. (ie aggregate the fact table with the levels of detail (grain) wanted and add for each level a dimension).
Elements of Dimensional Model
Step to design dimensional Model
To model the data, they are no substitutes for user input that interview a businessperson. Don't model uniquely by looking at source data file.
- Select the business process
- Declare the Fact Grain
- Choose the dimensions
- Identify the measures
Not True
Entity relation models are a disaster for querying because they cannot be understood by users and cannot be navigated usefully by DBMS software. Entity relation models cannot be used as the basis for enterprise data warehouses.