Como criar uma Bridge Table de tipo variante para atributos multivalorados em Modelação de Dados (Kimball)

Vamos criar uma Bridge Table para modelar atributos multivalorados (ex.: produtos com múltiplas categorias) num esquema em estrela Kimball. Esta solução mantém a integridade do star schema e facilita análises com contagens, filtros e agregação por categoria.

Pré-requisitos

• Conhecimentos básicos de Modelação de Dados (Kimball) e star schema.
• SQL (por exemplo SQL Server, PostgreSQL ou outra RDBMS).
• Exemplos de tabelas source: uma fact table com product_id e uma dimensão Product.

Passo 1: Detectar o padrão multivalorado e escolher o tipo de bridge

Identificar o atributo multivalorado (ex.: categorias de produto). Decidir entre Bridge simples (apenas relaciona keys) ou Bridge com pesos/ordem se for necessário ponderar ou preservar prioridade. Aqui fazemos uma Bridge com peso simples (1 por relação) e uma coluna de role para futuras extensões.

Passo 2: Modelar as tabelas envolvidas

Definir as colunas mínimas: Dimension Product, Dimension Category e a Bridge ProductCategory_Bridge. Na abordagem Kimball mantemos surrogate keys nas dimensões e guardamos as foreign keys na bridge e na 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)
);

Passo 3: Extrair e transformar os dados multivalorados

Extrair do sistema source onde o atributo está numa lista separada por vírgulas, JSON ou numa tabela relacional. O objectivo é normalizar para pares (product_id, category_id) antes de carregar para dim e 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;

Passo 4: Carregar / actualizar dimensões

Carregar dim_category e dim_product usando surrogate keys. Para dimensões conformed, garantir que category_id e product_id são únicos e criar/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

Passo 5: Popular a Bridge Table

Fazer um join entre os pares normalizados e as dimensões para obter product_sk e category_sk, depois inserir na bridge. Evitar duplicados e manter weight/role conforme necessidade.

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

Passo 6: Ajustar a Fact table para análise

Manter a fact table com product_sk (ou product_id) e, em queries analíticas, juntar à bridge para distribuir medidas por categorias. Para contagens não-duplicadas usar distinct ou medidas ponderadas com weight.

-- Agregar vendas por categoria usando a 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 o resultado

Confirmar que cada product_sk tem as categorias correctas na bridge e que a soma de pesos por produto faz sentido (ex.: 1 por relação ou total 1 se normalizado). Testar queries de relatório: agregação por categoria, filtro por categoria e contagens distintas por produto.

Conclusão

Uma Bridge Table para atributos multivalorados mantém o star schema limpo e flexível: permite análises por categoria sem inflar a fact table. Próximos passos: implementar lógica para pesos dinâmicos, gerir histórico de mudanças na bridge, ou criar uma view para simplificar queries de Power BI. Dica: comece com bridge simples e acrescente role/weight só se necessário — qual é o caso multivalorado mais comum na tua organização?