Dimensional Modeling - Star Schema

> (OLAP|Analytic) > Dimensional Modeling - Dimensional Schemas

1 - About

A star schema is a dimensional schema with a single fact table that has foreign key relationships with several dimension tables.

The characteristic starlike 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 OLAP.


3 - Dimension Attributes or Facts

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

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

4 - 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