Como criar uma Bridge Table para M:N em Modelação de Dados (Kimball)

Este tutorial mostra como criar uma Bridge Table para resolver uma relação muitos-para-muitos (M:N) entre duas dimensões num star schema Kimball, útil para análises corretas e desempenho em ferramentas como Power BI. Explicamos o porquê, os erros comuns e um exemplo prático em SQL para implementar e consumir a Bridge Table.

Pré-requisitos

• Conhecimentos básicos de Modelação Dimensional (Kimball) e SQL.
• Ambiente com uma base de dados relacional (ex.: SQL Server, PostgreSQL) para executar queries.
• Exemplo de fact table e duas dimensões com relação M:N (ex.: Sales, Product, Promotion).

Passo 1: Entender quando precisar de uma Bridge Table

Uma Bridge Table é necessária quando uma fact ou duas dimensões têm uma relação muitos-para-muitos que causa duplicação de medidas se simplesmente efectuarmos junções entre as tabelas. Um exemplo comum: um Product pode estar em várias Promotion e uma Promotion aplica-se a vários Product. Sem Bridge, somas podem ficar inflacionadas.

Passo 2: Modelar a Bridge Table (chaves e atributos)

Defina a Bridge Table com chaves estrangeiras para cada dimensão envolvida e uma chave surrogate. Inclua atributos que descrevem a relação (por exemplo, weighting, start_date, end_date) para permitir alocação e filtros. Não coloque atributos de dimensão que pertençam apenas a uma dimensão.

-- Exemplo de DDL simplificado (SQL Server / PostgreSQL compatible)
CREATE TABLE bridge_product_promotion (
  bridge_id BIGSERIAL PRIMARY KEY,
  product_key BIGINT NOT NULL,
  promotion_key BIGINT NOT NULL,
  weight NUMERIC(18,6) DEFAULT 1.0,  -- para alocação se necessário
  start_date DATE,
  end_date DATE
);

-- Índices para performance
CREATE INDEX idx_bridge_product ON bridge_product_promotion(product_key);
CREATE INDEX idx_bridge_promotion ON bridge_product_promotion(promotion_key);

Passo 3: Popular a Bridge Table a partir dos dados fonte

Extraia as relações M:N da fonte de transacção ou da ETL de referência. Valide duplicados e calcule o 'weight' se uma promoção cobrir parte do produto (ex.: share). Se não houver share, use 1.0 por padrão. Exemplo com dados de mapeamento.

-- Exemplo: popular a Bridge a partir de uma tabela fonte product_promo_map
INSERT INTO bridge_product_promotion (product_key, promotion_key, weight, start_date, end_date)
SELECT p.product_key, pm.promotion_key,
       COALESCE(pm.share, 1.0) AS weight,
       pm.start_date, pm.end_date
FROM product_dim p
JOIN product_promo_map pm ON p.product_code = pm.product_code
-- evitar duplicados: usar DISTINCT ou lógica de agregação
GROUP BY p.product_key, pm.promotion_key, pm.share, pm.start_date, pm.end_date;

Passo 4: Integrar a Bridge Table na consulta da Fact

Ao consultar a fact (ex.: Sales) e pretender analisar por Promotion e Product, efectue joins à Bridge Table e aplique o weight para evitar dupla contagem. Se a fact já referencia directamente um product_key e a promoção é derivada, junte a bridge para ligar promotion_key.

-- Exemplo de query agregada que evita duplicação
SELECT pr.product_name,
       pm.promotion_name,
       SUM(sales.amount * b.weight) AS amount_allocated,
       COUNT(DISTINCT s.sale_id) AS distinct_sales_count
FROM sales_fact s
JOIN product_dim pr ON s.product_key = pr.product_key
JOIN bridge_product_promotion b ON pr.product_key = b.product_key
JOIN promotion_dim pm ON b.promotion_key = pm.promotion_key
-- considerar filtros por data usando b.start_date/end_date e s.sale_date
WHERE s.sale_date BETWEEN COALESCE(b.start_date, '1900-01-01') AND COALESCE(b.end_date, '9999-12-31')
GROUP BY pr.product_name, pm.promotion_name;

Passo 5: Tratar casos comuns e erros

Erros comuns: 1) Não aplicar weight leva à duplicação; 2) Esquecer a janela temporal (start/end) causa alocações incorrectas; 3) Fazer junções erradas causando multiplicação da fact. Verifique cardinalidades e use COUNT(DISTINCT ...) nas validações. Documente a lógica de weight e como a Bridge foi populada.

Verificar o resultado

Valide com estes passos: 1) Compare somas totais antes/depois da Bridge — sem weight, os totais podem aumentar; 2) Teste cenários com uma promoção que afeta dois produtos e confirme que a soma alocada do promotion equals soma original (quando weight=1 e regras aplicáveis); 3) Use queries de diagnóstico para contar multiplicidade na bridge: SELECT product_key, COUNT(*) FROM bridge_product_promotion GROUP BY product_key HAVING COUNT(*) > 1.

Conclusão

Uma Bridge Table correcta resolve relações M:N no star schema Kimball e evita duplicação nas métricas. Próximos passos: automatizar a carga da Bridge na sua ETL, incluir testes de regressão e expor a Bridge para Power BI com medidas que respeitem o weight. Dica: comece com dados reais de um caso de negócio e verifique sempre as janelas temporais e pesos — que relação M:N no seu domínio lhe está a causar problemas de contagem?