Dimensional Modeling - Dimensional Schemas

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).

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

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.

  1. Select the business process
  2. Declare the Fact Grain
  3. Choose the dimensions
  4. Identify the facts

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)

olap/dimensional_modeling/dimensional_modeling.txt · Last modified: 2017/09/17 18:31 by gerardnico