In this lesson, you will learn how to define the appropriate scope of descriptive attributes to ensure your dimension tables provide reliable, consistent reporting.
Defining the "One Row Per Entity" Rule
The granularity of a dimension table is defined by the unique grain of its primary key. If your Dim_Product table has a grain of Product_ID, every row must represent exactly one unique product. The biggest mistake beginners make in "Advanced Data Aggregation" is "attribute creep," where they add descriptive columns that don't actually belong to the primary key.
The Scope of Descriptive Attributes
When designing a dimension table, ask yourself: "Does this attribute describe every instance of this entity equally?" If an attribute changes at a different frequency or level of detail than your primary key, it does not belong in that table.
For example, imagine a Dim_Store table. If you add a Regional_Manager_Name column, it works fine—until a region is split and a manager changes. If you then add Daily_Store_Revenue, you have broken the granularity rule entirely because a store has many revenues, but only one store record.
Including non-granular data leads to "fan-out" issues during joins. If you try to aggregate your Fact_Sales table by a dimension that contains mismatched grains, your sums will be inflated because the SQL engine will multiply the sales amount by the number of extra rows created by those misaligned attributes.
-- Correct: Dim_Product holds attributes unique to the Product_ID
CREATE TABLE Dim_Product (
product_key INT PRIMARY KEY,
product_name VARCHAR(100),
brand_name VARCHAR(50),
category_id INT -- Valid: It's a static hierarchy attribute
);
-- Incorrect: Adding transient, high-frequency data (like stock levels)
-- would require a different dimension or a Fact table.
By keeping attributes strictly scoped to the primary key's granularity, you ensure that your JOIN operations remain predictable. If you find yourself needing to store attributes that describe a process or a changing state, consider if that data belongs in a Fact table or a separate, more granular Dimension table rather than forcing it into an existing one.
Key Takeaway: A dimension table’s attributes must be functionally dependent on its primary key; if the data describes an event or a changing metric rather than the entity itself, it belongs in a Fact table.