Cómo crear una Bridge Table de tipo variante para atributos multivalorados en Modelado de Datos (Kimball)

Vamos a crear una Bridge Table para modelar atributos multivalorados (ej.: productos con múltiples categorías) en un esquema en estrella Kimball. Esta solución mantiene la integridad del star schema y facilita análisis con recuentos, filtros y agregación por categoría.

Prerequisitos

• Conocimientos básicos de Modelado de Datos (Kimball) y star schema.
• SQL (por ejemplo SQL Server, PostgreSQL u otra RDBMS).
• Ejemplos de tablas source: una fact table con product_id y una dimensión Product.

Paso 1: Detectar el patrón multivalorado y elegir el tipo de bridge

Identificar el atributo multivalorado (ej.: categorías de producto). Decidir entre Bridge simple (solo relaciona keys) o Bridge con pesos/orden si es necesario ponderar o preservar prioridad. Aquí hacemos una Bridge con peso simple (1 por relación) y una columna de role para futuras extensiones.

Paso 2: Modelar las tablas involucradas

Definir las columnas mínimas: Dimension Product, Dimension Category y la Bridge ProductCategory_Bridge. En el enfoque Kimball mantenemos surrogate keys en las dimensiones y guardamos las foreign keys en la bridge y en la fact.

-- Dimensão Product (simplificada)
CREATE TABLE dim_product (
  product_sk INT PRIMARY KEY,
  product_id VARCHAR(50) UNIQUE,
  product_name VARCHAR(200)
);

-- Dimensão Category
CREATE TABLE dim_category (
  category_sk INT PRIMARY KEY,
  category_id VARCHAR(50) UNIQUE,
  category_name VARCHAR(200)
);

-- Bridge entre Product e Category
CREATE TABLE bridge_product_category (
  product_sk INT NOT NULL,
  category_sk INT NOT NULL,
  role VARCHAR(50) DEFAULT 'primary', -- permite diferentes tipos de relação
  weight DECIMAL(10,4) DEFAULT 1.0,
  PRIMARY KEY (product_sk, category_sk)
);

Paso 3: Extraer y transformar los datos multivalorados

Extraer del sistema source donde el atributo está en una lista separada por comas, JSON o en una tabla relacional. El objetivo es normalizar a pares (product_id, category_id) antes de cargar a dim y bridge.

-- Exemplo: source_products com coluna categories = 'cat1,cat2'
WITH exploded AS (
  SELECT p.product_id,
         TRIM(value) AS category_id
  FROM source_products p,
       UNNEST(string_to_array(p.categories, ',')) AS value
)
SELECT * FROM exploded;

Paso 4: Cargar / actualizar dimensiones

Cargar dim_category y dim_product usando surrogate keys. Para dimensiones conformed, asegurar que category_id y product_id son únicos y crear/upsert.

-- Inserir categories novas (pseudo-upsert, sintaxe varia por RDBMS)
INSERT INTO dim_category (category_sk, category_id, category_name)
SELECT nextval('seq_cat_sk'), c.category_id, c.category_name
FROM staging_categories c
ON CONFLICT (category_id) DO NOTHING;

-- Similar para dim_product

Paso 5: Poblar la Bridge Table

Hacer un join entre los pares normalizados y las dimensiones para obtener product_sk y category_sk, luego insertar en la bridge. Evitar duplicados y mantener weight/role según necesidad.

-- Exemplo de carga da bridge
INSERT INTO bridge_product_category (product_sk, category_sk, role, weight)
SELECT p.product_sk, c.category_sk, 'primary', 1.0
FROM staging_product_category sc
JOIN dim_product p ON p.product_id = sc.product_id
JOIN dim_category c ON c.category_id = sc.category_id
ON CONFLICT (product_sk, category_sk) DO UPDATE
  SET weight = EXCLUDED.weight;

Paso 6: Ajustar la Fact table para análisis

Mantener la fact table con product_sk (o product_id) y, en consultas analíticas, unir con la bridge para distribuir medidas por categorías. Para recuentos no duplicados usar distinct o medidas ponderadas con weight.

-- Agregar ventas por categoria usando la bridge
SELECT c.category_name, SUM(f.amount * b.weight) AS amount_by_category
FROM fact_sales f
JOIN bridge_product_category b ON f.product_sk = b.product_sk
JOIN dim_category c ON b.category_sk = c.category_sk
GROUP BY c.category_name;

Verificar el resultado

Confirmar que cada product_sk tiene las categorías correctas en la bridge y que la suma de pesos por producto tiene sentido (ej.: 1 por relación o total 1 si normalizado). Probar consultas de informe: agregación por categoría, filtro por categoría y recuentos distintos por producto.

Conclusión

Una Bridge Table para atributos multivalorados mantiene el star schema limpio y flexible: permite análisis por categoría sin inflar la fact table. Próximos pasos: implementar lógica para pesos dinámicos, gestionar histórico de cambios en la bridge, o crear una view para simplificar consultas en Power BI. Consejo: empieza con bridge simple y añade role/weight solo si es necesario — ¿cuál es el caso multivalorado más común en tu organización?