Cómo crear una Bridge Table para M:N en Modelado de Datos (Kimball)

Este tutorial muestra cómo crear una Bridge Table para resolver una relación muchos-a-muchos (M:N) entre dos dimensiones en un star schema Kimball, útil para análisis correctos y rendimiento en herramientas como Power BI. Explicamos el porqué, los errores comunes y un ejemplo práctico en SQL para implementar y consumir la Bridge Table.

Prerequisitos

• Conocimientos básicos de Modelado Dimensional (Kimball) y SQL.
• Entorno con una base de datos relacional (p. ej.: SQL Server, PostgreSQL) para ejecutar queries.
• Ejemplo de fact table y dos dimensiones con relación M:N (p. ej.: Sales, Product, Promotion).

Paso 1: Entender cuándo necesitar una Bridge Table

Una Bridge Table es necesaria cuando una fact o dos dimensiones tienen una relación muchos-a-muchos que causa duplicación de medidas si simplemente realizamos joins entre las tablas. Un ejemplo común: un Product puede estar en varias Promotion y una Promotion se aplica a varios Product. Sin Bridge, las sumas pueden quedar infladas.

Paso 2: Modelar la Bridge Table (claves y atributos)

Defina la Bridge Table con claves foráneas para cada dimensión involucrada y una clave surrogate. Incluya atributos que describan la relación (por ejemplo, weighting, start_date, end_date) para permitir asignación y filtros. No coloque atributos de dimensión que pertenezcan solo a una dimensión.

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

Paso 3: Poblar la Bridge Table a partir de los datos fuente

Extraiga las relaciones M:N de la fuente de transacción o de la ETL de referencia. Valide duplicados y calcule el 'weight' si una promoción cubre parte del producto (p. ej.: share). Si no hay share, use 1.0 por defecto. Ejemplo con datos de mapeo.

-- Exemplo: popular a Bridge a partir de una 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 o lógica de agregación
GROUP BY p.product_key, pm.promotion_key, pm.share, pm.start_date, pm.end_date;

Paso 4: Integrar la Bridge Table en la consulta de la Fact

Al consultar la fact (p. ej.: Sales) y querer analizar por Promotion y Product, realice joins a la Bridge Table y aplique el weight para evitar doble contabilización. Si la fact ya referencia directamente un product_key y la promoción es derivada, una la bridge para vincular promotion_key.

-- Exemplo de query agregada que evita duplicación
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 y 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;

Paso 5: Tratar casos comunes y errores

Errores comunes: 1) No aplicar weight conduce a duplicación; 2) Olvidar la ventana temporal (start/end) causa asignaciones incorrectas; 3) Hacer joins erróneos causando multiplicación de la fact. Verifique cardinalidades y use COUNT(DISTINCT ...) en las validaciones. Documente la lógica de weight y cómo se pobló la Bridge.

Verificar el resultado

Valide con estos pasos: 1) Compare sumas totales antes/después de la Bridge — sin weight, los totales pueden aumentar; 2) Pruebe escenarios con una promoción que afecta a dos productos y confirme que la suma asignada del promotion equals la suma original (cuando weight=1 y reglas aplicables); 3) Use queries de diagnóstico para contar multiplicidad en la bridge: SELECT product_key, COUNT(*) FROM bridge_product_promotion GROUP BY product_key HAVING COUNT(*) > 1.

Conclusión

Una Bridge Table correcta resuelve relaciones M:N en el star schema Kimball y evita duplicación en las métricas. Próximos pasos: automatizar la carga de la Bridge en su ETL, incluir pruebas de regresión y exponer la Bridge para Power BI con medidas que respeten el weight. Consejo: comience con datos reales de un caso de negocio y verifique siempre las ventanas temporales y pesos — ¿qué relación M:N en su dominio le está causando problemas de contabilización?