Dimensional Data Modeling - Star Schema

About

A star schema is a relational schema that is a dimensional:

The characteristic star-like structure is often called a star join schema. This term dates back to the earliest days of relational databases.

The main advantages of star schemas are :

  • The data is easier to understand and navigate. User agree immediately that the dimensional model is their business
  • Provide highly optimized performance for typical star queries (fewer join, very strong assumption about first constraining to attack the fact table, …)
  • extensible to accommodate change. (You don't need to change this schema for future business need)
  • Are widely supported by a large number of business intelligence tools, which may anticipate or even require that the data warehouse schema contain dimension tables.

A star join is a primary key to foreign key join :

Star schemas are used generally in the data marts to deliver business data to specific business units for drill down, analysis and other OLAP operations.

Dimension or Measure Attribute ?

It's some time difficult to make the choice between measure and dimensional descriptif attributes, especially when the value is a number. You must so answer to this question :

Where the value is more used ?
Measures are Descriptif Attributes are
additional used as report constraint
semi-additive used as row header
number that behaves like a textual descriptor
discrete and constant

Accommodate change

We can :

  • add completely new dimension
  • add new fact to the fact table assuming that the level of detail is consistent with the existent fact row.
  • supplement pre-existing dimension with new unanticipated attributes

Powered by ComboStrap