Dimensional Modeling and Elements of Dimensional Data Model

 

What is Dimension Modelling?

Dimensional Modeling (DM) is a data structure technique optimized for data storage in a Data warehouse. The Purpose is to optimize the database for faster retrieval of data. The concept of Dimensional Modelling was developed by Ralph Kimball and consists of “fact” and “dimension” tables.

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.

There are three types of facts.

• Additive --total sales of a Company.
• Non-additive --averages and percentages and ratios.
• Semi- additive --the number of items in the warehouse for each day.

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 Names

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

Types of Dimensions are. 

• Conformed, 
• Outrigger, 
• Shrunken, 
• Role-playing, 
• Dimension to Dimension Table, 
• Junk, 
• Degenerate, 
• Swappable and 
• Step Dimensions.

Attributes

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

In the Location dimension, the attributes can be.

• State
• Country
• Zip Code 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.