Dimensional Modeling - Dimensional Schemas
Table of Contents
1 - About
In an OLAP application, the data is modeled as a:
Complex schemas rarely scale well.
2 - Model Elements
3 - 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 facts 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).
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
4 - Weakness
Dimensional models assume an underlying hierarchical structure of data and exclude data that is naturally non-hierarchical (e.g., network structured data).
5 - Parent-Child/Aggregate Dimensional schema
Multiple dimensional schemas at different levels of detail may be required to speed up the retrieval process of the data query but they still concern the same dimensional model.
6 - Elements of Dimensional Model
7 - 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.
8 - 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. Kimball (1996)