Como criar uma Dimension Conformed em Modelação de Dados (Kimball)

Este tutorial explica como criar uma Dimension Conformed em Modelação de Dados (Kimball) para partilhar atributos entre várias fact tables. Ter dimensões conformadas torna os relatórios consistentes e reduz a redundância quando múltiplas fact tables utilizam as mesmas descrições de entidade.

Pré-requisitos

• Conhecimentos básicos de SQL (SELECT, JOIN, INSERT)
• Ambiente com uma base de dados relacional (ex.: SQL Server, PostgreSQL)
• Exemplos de fact tables que partilham a mesma entidade (ex.: Vendas e Devoluções)

Passo 1: Identificar a entidade a conformar

Explique porquê: escolher a entidade correcta evita duplicação. Procure dimensões com os mesmos conceitos (ex.: Cliente, Produto, Loja) usadas por múltiplas fact tables. Verifique atributos idênticos ou muito semelhantes e regras de negócio.

Passo 2: Definir a estrutura da Dimension Conformed

Crie um desenho simples: chaves surrogadas, chave natural, atributos que descrevam a entidade e flags úteis. A Dimension Conformed deve conter todos os atributos comuns exigidos por cada fact table e manter a granularidade necessária.

-- Exemplo: criar a dimensão Cliente_conformed
CREATE TABLE dim_cliente_conformed (
  cliente_sk BIGINT IDENTITY PRIMARY KEY,
  cliente_nk VARCHAR(50) NOT NULL, -- natural key (ex.: customer_id)
  nome VARCHAR(200),
  segmento VARCHAR(50),
  pais VARCHAR(50),
  data_registo DATE,
  CURRENT_FLAG CHAR(1) DEFAULT 'Y'
);

Passo 3: Mapear e migrar dados das fontes

Explique porquê: garantir que cada fonte usa a mesma chave natural (ou um mapeamento) é essencial. Normalmente realiza-se um processo ETL/ELT para consolidar as chaves naturais e limpar atributos antes de povoar a dimensão conformada.

-- Exemplo de MERGE para popular e actualizar dim_cliente_conformed (SQL Server syntax)
MERGE dim_cliente_conformed AS target
USING (
  SELECT DISTINCT customer_id AS cliente_nk, name AS nome, segmento, country AS pais, signup_date AS data_registo
  FROM stg_vendas_customers
  UNION
  SELECT DISTINCT customer_id, name, segment, country, signup
  FROM stg_devolucoes_customers
) AS src
ON target.cliente_nk = src.cliente_nk
WHEN MATCHED AND (target.nome <> src.nome OR target.pais <> src.pais) THEN
  UPDATE SET nome = src.nome, segmento = src.segmento, pais = src.pais, data_registo = src.data_registo
WHEN NOT MATCHED BY TARGET THEN
  INSERT (cliente_nk, nome, segmento, pais, data_registo)
  VALUES (src.cliente_nk, src.nome, src.segmento, src.pais, src.data_registo);

Passo 4: Gerir chave surrogate e mapeamentos nas fact tables

Explique porquê: as fact tables devem referir a dimensão conformada através da chave surrogate (cliente_sk). Crie um lookup para traduzir a chave natural das fontes para a surrogate durante o ETL/ELT.

-- Exemplo: transformar fact_vendas para usar cliente_sk
INSERT INTO fact_vendas (venda_id, data_id, cliente_sk, produto_sk, quantidade, valor)
SELECT
  fv.venda_id,
  fv.data_id,
  dc.cliente_sk,
  fv.produto_sk,
  fv.quantidade,
  fv.valor
FROM stg_vendas fv
LEFT JOIN dim_cliente_conformed dc
  ON fv.customer_id = dc.cliente_nk;

Passo 5: Tratar diferenças e versão de atributos

Explique porquê: quando as fontes discordam nos atributos, defina regras de precedência e mantenha historial quando necessário. Para manter historial considere Slowly Changing Dimension Tipo 2 ou armazenar atributos anteriores numa tabela de história.

-- Exemplo simplificado de SCD Type 2 (apenas lógica de marcação)
-- Assumindo colunas: effective_date, end_date, current_flag
UPDATE dim_cliente_conformed
SET current_flag = 'N', end_date = GETDATE()
WHERE cliente_nk = @cliente_nk AND current_flag = 'Y' AND (
  nome <> @nome OR pais <> @pais OR segmento <> @segmento
);

INSERT INTO dim_cliente_conformed (cliente_nk, nome, segmento, pais, data_registo, effective_date, current_flag)
VALUES (@cliente_nk, @nome, @segmento, @pais, @data_registo, GETDATE(), 'Y');

Verificar o resultado

Valide que as fact tables referenciam a mesma dimensão conformed e que os relatórios mostram valores consistentes. Exemplos de verificações: contar clientes únicos por fact table vs dimensão, confirmar que não há cliente_sk NULL nas fact tables, e comparar atributos entre fontes e dimensão.

-- Verificações úteis
-- 1. Clientes únicos na dimensão
SELECT COUNT(*) AS total_clientes FROM dim_cliente_conformed;

-- 2. Clientes usados nas fact tables sem correspondência
SELECT fv.customer_id, COUNT(*)
FROM stg_vendas fv
LEFT JOIN dim_cliente_conformed dc ON fv.customer_id = dc.cliente_nk
WHERE dc.cliente_sk IS NULL
GROUP BY fv.customer_id;

-- 3. Conferir consistência de atributo (ex.: segmento)
SELECT dc.cliente_nk, dc.segmento AS segmento_dim, fv.segmento AS segmento_fonte
FROM stg_vendas fv
JOIN dim_cliente_conformed dc ON fv.customer_id = dc.cliente_nk
WHERE dc.segmento <> fv.segmento;

Conclusão

Uma Dimension Conformed reduz a duplicação, garante consistência dos relatórios e simplifica a manutenção do data warehouse. Próximos passos: automatizar o processo em ETL/ELT, aplicar SCD correctamente e documentar as regras de precedência. Dica: comece por uma dimensão conformed pequena (ex.: Cliente) e expanda quando as regras estiverem estáveis — qual dimensão faria sentido conformar primeiro no seu projecto?