Pirate Wiki

Toggle Theme

Dimensional Modeling ​

Dimensional modeling is a database design technique developed by Ralph Kimball, specifically optimized for data warehousing and business intelligence. It serves as the foundational structure for the Data Presentation Area in a DW/BI architecture (covered later).

Unlike the highly normalized models (like 3rd Normal Form) used in operational systems - which prioritize fast data entry and minimize redundancy - dimensional models are intentionally denormalized. Their primary goals are fast query performance and making the data intuitive for business users to understand.

At its core, dimensional modeling categorizes all business data into two distinct types: Facts and Dimensions. These are covered in detail on the next pages.

Do "fact" and "dimension" refer to tables or columns?

The word fact can refer to both a table and the individual measures stored in it. A Fact Table records a business process or event at a defined grain, while its numeric measures - such as order_amount or quantity - are often referred to as facts. However, not every numeric value is a fact. For example, a product's list_price belongs in a dimension table because it is an attribute describing the product, rather than a measurement of a specific business event.

The word dimension properly refers to the entire table or concept, like a Customer dimension. The individual columns inside it are called dimension attributes, like a customer's state or email. Even though modern reporting tools often call individual columns "dimensions," in dimensional modeling, the table is the dimension and the column is the attribute.

Why use Dimensional Modeling? ​

  • Understandability: The database schema closely mirrors how business users naturally think about and discuss their processes.
  • Query Performance: A denormalized structure requires fewer complex table joins, which drastically reduces query execution time for large datasets.
  • Extensibility: As business requirements evolve, new data sources, dimensions and facts can typically be integrated without breaking existing reports or queries.