Dimensional Modeling is the data model design technique for data mart or data warehouse design. In a dimension model, there is a single fact table and multiple dimensional tables. A fact value is described with the help of different perspectives called dimensions.

• Dimension Model has a single fact table for the entire data mart or the data warehouse.
• Dimension model maximizes the understanding of the database.
• Dimension model is a data warehouse model
• Dimension model is purely optimized for data retrieval purpose and to work with OLAP applications
• Dimension model creates lesser joins between the tables.

Dimensional Modeling design steps:
The below four major design steps are applied in the dimension modeling design according to Ralph Kimball, the father of data warehousing.

1) Choose the data mart – Data mart is the subject for which the dimension model needs to be designed. Ex: Sales Data mart
2) Declare the grain – The lowest grain of information to be stored in the dimension model. Ex: Monthly sales amount or Weekly sales value
3) Choose the dimension – The various perspectives of a fact. The descriptors of the fact. Ex: Sales Dimension, Product Dimension, Customer Dimension
4) Choose the fact – Facts are numeric values that can be aggregated. The numeric values to be stored in the dimension model. Ex: Sales amount, Sales quantity

Types of Grains used in Dimension Modeling:

1) Transaction grain fact table – A transaction grain represents a single point in the space and time. Ex: Sales transaction in a particular retail store, by a customer, on a specific time of the day.
2) Periodic snapshot fact table – Data accumulated representing a regular span of time repeating over and over.
3) Accumulating snapshot fact table – Data accumulated represents the entire life span of the entity.

Here are the few best practices in dimensional modeling:
• No Null values are allowed in the fact table.
• Fact and Dimension tables are joined based on the surrogate keys generated but not joined based on the operation codes.
• Dimensional tables are de-normalized.

Click here to know more on dimension modeling.

Related posts:

1. OLAP vs OLTP If you are into databases and middleware and ever wodners...
2. Data Warehouse Data Warehouse the name was given by Bill Inmon in...
3. De Normalisation De Normalisation (or De Normalization) of data allows table to...
4. Database Normalization Normalization is the process of creating and organizing data in...
5. Data Dictionary Data Dictionary is an organized file of details about the...