Elements of Dimensional Data Model

Fact

Facts are the measurements/metrics or facts from your business process. For a Sales business process, a measurement would be quarterly sales number

Dimension

Dimension provides the context surrounding a business process event. In simple terms, they give who, what, where of a fact. In the Sales business process, for the fact quarterly sales number, dimensions would be

  • Who – Customer Names
  • Where – Location
  • What – Product Name

In other words, a dimension is a window to view information in the facts.

Attributes

The Attributes are the various characteristics of the dimension in dimensional data modeling.

In the Location dimension, the attributes can be

  • State
  • Country
  • Zipcode etc.

Attributes are used to search, filter, or classify facts. Dimension Tables contain Attributes

Fact Table

A fact table is a primary table in dimension modelling.

A Fact Table contains

  1. Measurements/facts
  2. Foreign key to dimension table

Dimension Table

  • A dimension table contains dimensions of a fact.
  • They are joined to fact table via a foreign key.
  • Dimension tables are de-normalized tables.
  • The Dimension Attributes are the various columns in a dimension table
  • Dimensions offers descriptive characteristics of the facts with the help of their attributes
  • No set limit set for given for number of dimensions
  • The dimension can also contain one or more hierarchical relationships