Metadata
- Author: Margy Ross
- Full Title:: The 10 Essential Rules of Dimensional Modeling
- Category:: 🗞️Articles
- Document Tags:: Dimensional Modeling, Dimensional Modeling,
- URL:: https://www.kimballgroup.com/2009/05/the-10-essential-rules-of-dimensional-modeling/
- Read date:: 2025-02-02
Highlights
Rule #1: Load detailed atomic data into dimensional structures. (View Highlight)
Users typically don’t need to see a single record at a time, but you can’t predict the somewhat arbitrary ways they’ll want to screen and roll up the details. (View Highlight)
business users cannot live on summary data alone; they need the gory details to answer their ever-changing questions. (View Highlight)
Rule #2: Structure dimensional models around business processes. (View Highlight)
In addition to single process fact tables, consolidated fact tables are sometimes created that combine metrics from multiple processes into one fact table at a common level of detail. Again, consolidated fact tables are a complement to the detailed single-process fact tables, not a substitute for them. (View Highlight)
Rule #3: Ensure that every fact table has an associated date dimension table. (View Highlight)
Rule #5: Resolve many-to-many relationships in fact tables. (View Highlight)
These foreign key fields should never be null. (View Highlight)
Sometimes dimensions can take on multiple values for a single measurement event, such as the multiple diagnoses associated with a health care encounter or multiple customers with a bank account. In these cases, it’s unreasonable to resolve the many-valued dimensions directly in the fact table, as this would violate the natural grain of the measurement event. Thus, we use a many-to-many, dual-keyed bridge table in conjunction with the fact table. (View Highlight)
Rule #6: Resolve many-to-one relationships in dimension tables. (View Highlight)
Occasionally many-to-one relationships are resolved in the fact table, such as the case when the detailed dimension table has millions of rows and its roll-up attributes are frequently changing. However, using the fact table to resolve M:1 relationships should be done sparingly. (View Highlight)
Rule #7: Store report labels and filter domain values in dimension tables. (View Highlight)
Though we stated in Rule #5 that fact table foreign keys should never be null, it’s also advisable to avoid nulls in the dimension tables’ attribute fields by replacing the null value with “NA” (not applicable) or another default value, determined by the data steward, to reduce user confusion if possible. (View Highlight)
Rule #9: Create conformed dimensions to integrate data across the enterprise. (View Highlight)