How to Create a Hierarchical Type Dimension in Data Modeling (Kimball)

João Barros 19 de October de 2026 5 min read

We will create a hierarchical Dimension (e.g.: Products > Category > Segment) in Data Modeling (Kimball) to enable efficient drill-down and correct aggregations. This technique is useful for analytical reports, optimizing joins and for preserving historical levels when needed.

Prerequisites

Basic knowledge of Kimball Modeling (star schema, fact/dimension).
Access to a SQL environment (e.g.: SQL Server, PostgreSQL) to run examples.
Data source with hierarchy columns (e.g.: product_id, category, segment).

Step 1: Understand the purpose of the hierarchical Dimension

A hierarchical Dimension organizes levels (for example: Segment > Category > Product) to facilitate drill-down and aggregations. In Kimball we use surrogate keys in the Dimension and keep levels clear to avoid attribute duplication and to optimize OLAP/Power BI queries.

Step 2: Define the logical model of the levels

Decide which levels exist and which attributes belong to each level. Keep one row per member of the lowest level (usually product) and store the codes/names of the higher levels. Example columns:

product_sk (surrogate key)
product_id (business key)
product_name
category_id
category_name
segment_id
segment_name
effective_date
end_date
current_flag

Step 3: Create the Dimension table with surrogate keys

Create the physical table with a surrogate key, indexes and columns for each level. The surrogate key improves performance and decouples from the natural key of the source.

CREATE TABLE dim_product_hierarchy (
  product_sk INT IDENTITY(1,1) PRIMARY KEY,
  product_id VARCHAR(50) NOT NULL,
  product_name VARCHAR(200),
  category_id VARCHAR(50),
  category_name VARCHAR(200),
  segment_id VARCHAR(50),
  segment_name VARCHAR(200),
  effective_date DATE DEFAULT GETDATE(),
  end_date DATE NULL,
  current_flag BIT DEFAULT 1
);
CREATE INDEX ix_dim_product_hierarchy_business ON dim_product_hierarchy(product_id);

Step 4: Loading strategy (ETL) for the hierarchy

In the ETL/ELT process, perform upsert (insert/update) by product_id. If a product changes category/segment, insert a new row (SCD Type 2) or update (Type 1) as required. Here we show the SCD Type 2 pattern to preserve hierarchy history.

-- Exemplo simplificado em T-SQL: upsert SCD2 por product_id
MERGE dim_product_hierarchy AS target
USING (SELECT product_id, product_name, category_id, category_name, segment_id, segment_name FROM staging_products) AS src
ON target.product_id = src.product_id AND target.current_flag = 1
WHEN MATCHED AND (
     target.product_name <> src.product_name OR
     target.category_id <> src.category_id OR
     target.segment_id <> src.segment_id
  )
  THEN
    -- fecha versão actual
    UPDATE SET end_date = GETDATE(), current_flag = 0
WHEN NOT MATCHED BY TARGET
  THEN
    INSERT (product_id, product_name, category_id, category_name, segment_id, segment_name)
    VALUES (src.product_id, src.product_name, src.category_id, src.category_name, src.segment_id, src.segment_name);

-- Após fechar versões, inserir novas linhas para alterações (se necessário) com current_flag = 1
INSERT INTO dim_product_hierarchy (product_id, product_name, category_id, category_name, segment_id, segment_name)
SELECT s.product_id, s.product_name, s.category_id, s.category_name, s.segment_id, s.segment_name
FROM staging_products s
LEFT JOIN dim_product_hierarchy d ON d.product_id = s.product_id AND d.current_flag = 1
WHERE d.product_id IS NULL OR (
  d.product_name <> s.product_name OR d.category_id <> s.category_id OR d.segment_id <> s.segment_id
);

Step 5: Model the hierarchy in the star schema and keys in the Facts

In the fact tables, reference only the surrogate key of the lowest level (product_sk). For reports that require aggregation by category or segment, use the category/segment columns already present in the dimension or create indexes/columnstore to accelerate. Do not add redundant keys of higher levels in the facts unless necessary.

-- Exemplo de fact com referência à product_sk
CREATE TABLE fact_sales (
  sales_id BIGINT IDENTITY(1,1) PRIMARY KEY,
  sale_date DATE,
  product_sk INT NOT NULL,
  quantity INT,
  amount DECIMAL(18,2)
);
-- Join para relatório:
SELECT f.sale_date, d.segment_name, d.category_name, SUM(f.amount) total
FROM fact_sales f
JOIN dim_product_hierarchy d ON f.product_sk = d.product_sk
GROUP BY f.sale_date, d.segment_name, d.category_name;

Step 6: Performance and common mistakes

Index product_sk in the facts and place indexes or columnstore on the dimension for reads. Common mistakes: 1) using business keys in the facts instead of surrogate keys (impacts performance and history); 2) duplicating higher-level attributes in the facts; 3) not versioning when the hierarchy changes. Test with real change data.

Verify the result

Validate that each current product has current_flag = 1 and that there are historical versions with end_date when a change occurred. Test aggregation queries by category/segment and confirm that sums match the records in the facts. Verification examples:

-- Produtos activos
SELECT COUNT(*) FROM dim_product_hierarchy WHERE current_flag = 1;
-- Histórico para um produto
SELECT * FROM dim_product_hierarchy WHERE product_id = 'P123' ORDER BY effective_date;
-- Agregação por segmento
SELECT d.segment_name, SUM(f.amount) total
FROM fact_sales f JOIN dim_product_hierarchy d ON f.product_sk = d.product_sk
GROUP BY d.segment_name;

Conclusion

A well-modeled hierarchical Dimension facilitates drill-down, preserves history and improves performance in Data Modeling (Kimball). Next steps: implement automated tests in the ETL, optimize indexes for analytical workloads and consider alternative hierarchies (parent-child) if depth varies. Tip: start with a simple SCD and only complicate when history and reporting requirements demand it — which level of your hierarchy is really necessary?