Como criar uma Dimensão de Tipo Hierárquico em Modelação de Dados (Kimball)

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

Vamos criar uma Dimensão hierárquica (ex.: Produtos > Categoria > Segmento) em Modelação de Dados (Kimball) para permitir drill-down eficiente e agregações correctas. Esta técnica é útil para relatórios analíticos, optimização de joins e para preservar níveis históricos quando necessário.

Pré-requisitos

Conhecimentos básicos de Modelação Kimball (star schema, fact/dimension).
Acesso a um ambiente SQL (ex.: SQL Server, PostgreSQL) para executar exemplos.
Fonte de dados com colunas de hierarquia (ex.: product_id, category, segment).

Passo 1: Compreender o propósito da Dimensão hierárquica

Uma Dimensão hierárquica organiza níveis (por exemplo: Segmento > Categoria > Produto) para facilitar drill-down e agregações. Em Kimball usamos chaves substitutas (surrogate keys) na Dimensão e mantemos níveis claros para evitar duplicação de atributos e para optimizar consultas OLAP/Power BI.

Passo 2: Definir o modelo lógico dos níveis

Decide quais níveis existem e que atributos pertencem a cada nível. Mantém uma linha por membro de nível mais baixo (geralmente produto) e armazena os códigos/nomes dos níveis acima. Exemplo de colunas:

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

Passo 3: Criar a tabela de Dimensão com chaves substitutas

Cria a tabela física com chave substituta, índices e colunas para cada nível. A chave substituta melhora o desempenho e desvincula da chave natural da fonte.

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);

Passo 4: Estratégia de carregamento (ETL) para hierarquia

No processo ETL/ELT, faz o upsert (insert/update) por product_id. Se um produto muda de categoria/segmento, regista nova linha (SCD Tipo 2) ou actualiza (Tipo 1) conforme necessidade. Aqui mostramos o padrão SCD Tipo 2 para preservar histórico de hierarquia.

-- 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
);

Passo 5: Modelar hierarquia no star schema e chaves nas Facts

Nas fact tables, referencia apenas a surrogate key do nível mais baixo (product_sk). Para relatórios que necessitam agregação por categoria ou segmento, usa colunas de categoria/segmento já presentes na dimensão ou cria índices/columnstore para acelerar. Não adiciones chaves redundantes dos níveis superiores nas facts sem necessidade.

-- 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;

Passo 6: Desempenho e erros comuns

Indexa product_sk nas facts e coloca índices ou columnstore na dimensão para leituras. Erros comuns: 1) usar business keys nas facts em vez de surrogate keys (impacta desempenho e histórico); 2) duplicar atributos de nível superior nas facts; 3) não versionar quando a hierarquia muda. Testa com dados de alterações reais.

Verificar o resultado

Valida que cada produto actual tem current_flag = 1 e que existam versões históricas com end_date quando houve mudança. Testa queries de agregação por categoria/segmento e confirma que as somas correspondem aos registos nas facts. Exemplos de verificação:

-- 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;

Conclusão

Uma Dimensão hierárquica bem modelada facilita drill-down, preserva historial e melhora desempenho em Modelação de Dados (Kimball). Próximos passos: implementar testes automáticos no ETL, optimizar índices para workloads analíticos e considerar hierarquias alternativas (parent-child) se a profundidade variar. Dica: começa com SCD simples e só complica quando os requisitos de histórico e reporting o exigirem — qual nível da tua hierarquia é realmente necessário?