Dimensional Modeling

Last updated Aug 9, 2024

Dimensional Modeling (DM), integral to the Kimball DW and BI Lifecycle Methodology, also known as Business Dimensional Lifecycle methodology, was developed by Ralph Kimball. It encompasses a suite of methods, techniques, and concepts essential for Data Warehouse design.

This approach emphasizes identifying and modeling key business processes initially and then progressively adding more processes. This bottom-up strategy contrasts with Inmon’s top-down approach, which involves designing an enterprise-wide data model using Entity-Relationship Modeling (ER) and other tools.

• Explore the Snowflake Schema.
• Insightful read: RW How the Modern Data Stack Is Reshaping Data Engineering - Blog Preset.
• Further details in Data Modeling- The Unsung Hero of Data Engineering- Data Architecture Pattern, Tools and The Future (Part 3).

• The ADAPT framework.
• BEAM framework from Agile Data Warehouse Design (Lawrence Corr, Jim Stagnitto).

• Facts: Large Fact Dimensions.
• Dimensions: JunkDimension, Slowly Changing Dimension (Type 2).

An insightful example from Slack (dbt Coalesce 2022 Conference):
^3ddf22

Recommended Resource: What is Dimensional Modeling in Data Warehouse? Learn Types (also see readwise notes).

Additional tasks in dimensional modeling include Snowflaking, Bridge tables, Roles, degenerate dimensions, heterogeneous dimensions, and more.

1. Facts:
   • Identify facts.
   • Classify fact types (additive, etc.).
   • Pinpoint accumulative facts.
   • Design composite keys for fact tables.
   • Estimate fact table size and projected growth.
     Source: Data Warehouse Dimensional Modeling – Holowczak.com Tutorials

Introduced by Ralph Kimball in 1996 in his book, The Data Warehouse Toolkit, dimensional modeling aims to transform raw data into Fact and Dimension tables that effectively represent business operations.

From GitHub - Data-Engineer-Camp/dbt-dimensional-modelling: Step-by-step tutorial on building a Kimball dimensional model with dbt

A nice example of How to Create a Data Modeling Pipeline (3 Layer Approach) - YouTube with a 3 layer approach:

See also Classical Architecture of Data Warehouse what we used at Trivadis.

• Star Schema Design in Oracle: Fundamentals | Data Warehousing with Oracle

The resurgence of Data Modeling, including dimensional modeling, is evident. It’s being discussed on social media, addressed in talks, and explored in recent publications like Joe Reis’s book on “modern” data modeling practices ( LinkedIn Post) and Serge Gershkovich’s insights on revitalizing Data Modeling ( LinkedIn Post).

Origin: Series- Building Airbyte’s Data Stack
References: Building Facts in a Dimensional Model
Created 2022-08-02