Business intelligence: modelagem dimensional, ETL e dashboards

Mais cedo ou mais tarde, toda organização faz o mesmo tipo de pergunta: quantos, quanto, comparado com quando e por que está mudando. Os dados para responder já existem nos sistemas operacionais, espalhados por esquemas projetados para algo completamente diferente. Business intelligence é a disciplina de remodelar esses dados para que essas perguntas sejam respondidas com rapidez, de forma consistente e por pessoas que não escrevem SQL.

As ferramentas mudam o tempo todo; a modelagem, não. Um esquema estrela bem projetado há vinte anos funcionaria muito bem em um data warehouse moderno na nuvem, e um mal projetado vai decepcionar em qualquer plataforma. Este guia se concentra nessa parte duradoura, com exemplos de SQL que rodam em PostgreSQL ou Oracle.

Dois trabalhos diferentes: OLTP e OLAP

Sistemas transacionais e sistemas analíticos têm prioridades opostas, e é por isso que um único esquema não consegue atender bem aos dois.

Um esquema operacional armazena cada fato uma única vez e o remonta com joins, a disciplina abordada em projeto de banco de dados e normalização. É exatamente o que se quer quando o objetivo são escritas concorrentes corretas. Para análise, gera dois problemas: as consultas precisam de dez joins que só um especialista consegue escrever corretamente, e as atualizações sobrescrevem o passado, então "qual era o segmento deste cliente quando ele fez aquele pedido" passa a não ter resposta.

Por isso os modelos analíticos fazem uma troca deliberada: aceitam redundância, mantêm o histórico e otimizam para a compreensão.

Fatos e dimensões

A modelagem dimensional divide o mundo em medições e no contexto dessas medições.

Uma tabela fato guarda as medições (quantidades, valores, durações, contagens) mais as chaves estrangeiras para as dimensões que as descrevem. Ela é longa e estreita, e é onde ficam quase todas as linhas.

Uma tabela dimensão guarda os atributos descritivos pelos quais você filtra, agrupa e rotula: cliente, produto, loja, data, canal. Ela é curta e larga, muitas vezes bem desnormalizada, e é onde fica quase toda a legibilidade.

Tudo começa pela granularidade (o grain): uma frase simples dizendo exatamente o que uma linha da tabela fato representa. "Uma linha por item de pedido" é uma granularidade. "Dados de vendas" não é. Declare-a, registre-a na documentação e rejeite qualquer coluna que não se encaixe nela.

CREATE TABLE dim_date (
  date_key     integer PRIMARY KEY,          -- 20260907
  full_date    date NOT NULL,
  day_of_week  varchar(9) NOT NULL,
  month_name   varchar(9) NOT NULL,
  quarter      smallint NOT NULL,
  year         smallint NOT NULL,
  is_weekend   boolean NOT NULL,
  is_holiday   boolean NOT NULL
);

CREATE TABLE dim_product (
  product_key  bigint PRIMARY KEY,           -- chave substituta
  sku          varchar(20) NOT NULL,         -- chave natural vinda da origem
  title        varchar(200) NOT NULL,
  category     varchar(60) NOT NULL,
  brand        varchar(60) NOT NULL,
  valid_from   date NOT NULL,
  valid_to     date,
  is_current   boolean NOT NULL
);

CREATE TABLE fact_sales_line (
  date_key     integer NOT NULL REFERENCES dim_date(date_key),
  product_key  bigint  NOT NULL REFERENCES dim_product(product_key),
  customer_key bigint  NOT NULL REFERENCES dim_customer(customer_key),
  store_key    integer NOT NULL REFERENCES dim_store(store_key),
  order_number varchar(20) NOT NULL,         -- dimensão degenerada
  quantity     integer NOT NULL,
  net_amount   numeric(12,2) NOT NULL,
  tax_amount   numeric(12,2) NOT NULL,
  discount     numeric(12,2) NOT NULL
) PARTITION BY RANGE (date_key);

Três detalhes desse modelo são convenções que vale a pena adotar. As dimensões usam chaves substitutas (surrogate keys) em vez dos identificadores do sistema de origem, para que o histórico possa ser versionado e para que uma origem que mude a numeração das chaves não corrompa o data warehouse. A dimensão data é uma tabela de verdade, não uma expressão calculada, porque permite filtrar por "feriado" ou "trimestre fiscal" sem codificar um calendário em cada consulta. E um identificador sem atributos próprios, como o número do pedido, fica na tabela fato como dimensão degenerada em vez de ganhar uma tabela com a qual ninguém faria join.

Os fatos vêm em três variedades, e misturá-las gera números errados. Fatos aditivos (valor líquido, quantidade) podem ser somados em todas as dimensões. Fatos semiaditivos (o saldo de uma conta, o nível de estoque) podem ser somados em algumas dimensões, mas não ao longo do tempo; nesse caso, você tira a média ou pega o valor mais recente. Fatos não aditivos (um percentual de margem, um preço unitário) não podem ser somados de jeito nenhum; armazene o numerador e o denominador e calcule a razão na hora da consulta.

Estrela e floco de neve

Um esquema estrela coloca a tabela fato no centro, com um join para cada dimensão. Esse é o projeto inteiro, e suas virtudes são práticas: qualquer pergunta exige um join por dimensão, as ferramentas de BI conseguem navegar por ele automaticamente e o SQL é legível para quem não é especialista.

SELECT d.year, d.quarter, p.category,
       SUM(f.net_amount) AS revenue,
       SUM(f.quantity)   AS units
FROM   fact_sales_line f
JOIN   dim_date    d ON d.date_key    = f.date_key
JOIN   dim_product p ON p.product_key = f.product_key
WHERE  d.year = 2026 AND p.brand = 'Northwind'
GROUP  BY d.year, d.quarter, p.category
ORDER  BY revenue DESC;

Um esquema floco de neve normaliza as dimensões, separando categoria e marca em tabelas próprias. Ele economiza um pouco de espaço e acrescenta joins a toda consulta. Use o floco de neve de forma seletiva (para uma hierarquia realmente profunda ou uma tabela de referência compartilhada entre dimensões) e deixe o resto desnormalizado.

Quando várias tabelas fato compartilham as mesmas tabelas dimensão, essas dimensões são conformadas, e é isso que torna possível a análise entre processos: vendas e devoluções podem ser comparadas por produto e por mês porque ambas usam as mesmas dimensões de produto e de data. Dimensões conformadas são a espinha dorsal de um data warehouse; sem elas, você tem uma coleção de data marts sem relação entre si.

Como as tabelas fato são grandes e quase sempre filtradas por tempo, particione-as pela chave de data e deixe o motor descartar as partições desnecessárias. A indexação também é diferente da de OLTP: índices bitmap em chaves de dimensão de baixa cardinalidade funcionam bem em data warehouses Oracle, e o armazenamento colunar torna muitos índices desnecessários. O raciocínio por trás dessas escolhas está no guia de índices e planos de execução.

Mantendo o histórico: dimensões de mudança lenta

Um cliente muda de cidade. Um produto muda de categoria. O que o relatório do ano passado deveria mostrar? A resposta é uma decisão de modelagem, e o vocabulário padrão é numerado (fala-se em SCD, slowly changing dimensions).

• Tipo 0: manter o original. O atributo nunca muda depois da primeira carga. Adequado para uma data de nascimento ou o canal de cadastro original.
• Tipo 1: sobrescrever. Atualiza no lugar e perde o valor antigo. Correto para corrigir erros reais, como um nome escrito errado. Os relatórios históricos mudam retroativamente, o que às vezes é o certo e muitas vezes surpreende.
• Tipo 2: adicionar uma linha. Insere uma nova linha na dimensão com uma nova chave substituta, fecha a linha anterior com valid_to e inverte is_current. Os fatos continuam apontando para a linha que era a atual quando aconteceram, então o histórico é preservado com exatidão. É o padrão para qualquer atributo usado em análise.
• Tipo 3: adicionar uma coluna. Mantém current_region e previous_region lado a lado. Útil só para uma reorganização pontual em que as pessoas querem comparar as duas visões.

A atualização do tipo 2 é a que precisa sair certa:

-- Fechar a versão que está saindo
UPDATE dim_product
SET    valid_to = current_date - 1, is_current = false
WHERE  sku = 'BK-1042' AND is_current;

-- Abrir a nova com uma chave substituta nova
INSERT INTO dim_product (product_key, sku, title, category, brand, valid_from, valid_to, is_current)
VALUES (nextval('dim_product_seq'), 'BK-1042', 'Field Guide to Estuaries',
        'Natural history', 'Northwind', current_date, NULL, true);

Duas observações práticas. Em cada execução, carregue os fatos depois das dimensões, para que a busca de chave encontre a versão correta. E adicione um membro "desconhecido" (chave 0 ou -1) a cada dimensão, para que um fato que chegue com uma referência ausente ainda possa ser carregado e contado, em vez de ser descartado silenciosamente.

Trazendo os dados para dentro: ETL e ELT

ETL extrai das fontes, transforma em uma camada de processamento dedicada e carrega o resultado. ELT carrega primeiro os dados brutos na plataforma de destino e os transforma com o poder de processamento da própria plataforma. O ELT virou o padrão onde o armazenamento é barato e o motor é elástico, porque a cópia bruta é mantida, as transformações viram SQL versionado e não há uma camada de transformação separada para escalar.

Em qualquer um dos casos, as mesmas preocupações aparecem:

• Extração incremental. Reler uma fonte inteira toda noite logo deixa de escalar. Use uma coluna de watermark ou change data capture a partir do log de transações da origem.
• Idempotência. Um job precisa produzir o mesmo resultado quando é executado de novo após uma falha parcial. Carregue em uma tabela de staging e depois troque ou faça o merge de forma atômica.
• Busca de chaves substitutas. Mapeie cada chave natural da origem para a chave de dimensão atual, tratando explicitamente os membros que chegam atrasados e os desconhecidos.
• Auditoria. Registre em cada carga a contagem de linhas de entrada e de saída, as linhas rejeitadas e a duração da execução. Quando alguém questionar um número três semanas depois, é a auditoria da carga que vai responder.
• Agendamento e dependências. As cargas formam um grafo, não uma lista. Orquestre-as para que uma dimensão nunca seja carregada depois dos fatos que a referenciam. Para pipelines batch escritos em Java, o Spring Batch oferece o processamento em chunks, a capacidade de reinício e o job repository de que isso precisa.

Quando o volume ou a variedade das fontes passa do que um data warehouse aguenta com folga, o pipeline normalmente coloca os dados primeiro em um lake; os padrões para isso estão no guia de arquitetura de big data.

Data marts, cubos e camada semântica

Um data mart é um subconjunto do data warehouse orientado a um assunto (finanças, cadeia de suprimentos, marketing) construído a partir de dimensões conformadas. Construídos assim, os data marts são visões sobre um único modelo. Construídos de forma independente por cada departamento, viram o problema clássico de três equipes apresentando três números de receita diferentes na mesma reunião.

Os cubos OLAP pré-agregam as medidas ao longo das hierarquias das dimensões para que descer de ano para trimestre e para mês seja instantâneo, e são consultados com MDX em vez de SQL. Os cubos multidimensionais clássicos cederam boa parte do espaço para motores colunares em memória e agregados materializados, mas o vocabulário deles sobrevive em todo lugar: slice, dice, drill down, roll up, pivot. Em SQL, GROUPING SETS, ROLLUP e CUBE produzem linhas de subtotal em vários níveis em uma única passagem, e as funções de janela cuidam de totais acumulados, médias móveis e comparações entre períodos sem self-joins.

A camada semântica é onde moram as definições das métricas: receita é o valor líquido excluindo itens cancelados; cliente ativo é aquele com algum pedido nos últimos 90 dias. Definir isso uma única vez (no modelo da ferramenta de BI, em uma camada de métricas ou em views de banco de dados governadas) é o que produz uma versão única da verdade. Quando cada autor de dashboard reimplementa a definição, a divergência é garantida. Views materializadas são um detalhe de implementação útil aqui, pré-calculando agregados pesados e sendo atualizadas conforme um agendamento.

Dashboards e KPIs que as pessoas usam

A maioria dos dashboards fracassa por motivos de design, e não de dados.

• Comece por uma decisão. Todo gráfico deve responder a uma pergunta sobre a qual alguém age. Se ninguém consegue dizer qual é a ação, apague o gráfico.
• Dê contexto ao número. "€ 1,4 mi" sozinho não diz nada. Comparado com a meta, com o ano passado, com o mês passado, com uma tendência: isso é um KPI.
• Escolha medidas que as pessoas consigam influenciar. Uma métrica que ninguém consegue influenciar gera resignação, não melhoria.
• Defina com precisão e publique a definição. Coloque as condições de filtro, o fuso horário e o horário de atualização no próprio dashboard.
• Mostre a atualidade e a completude. Um dashboard que exibe dados desatualizados sem avisar é pior do que um que está fora do ar.
• Mantenha tudo em uma tela. O detalhe fica a um clique de distância, não em um paredão de blocos.

Ferramentas de BI self-service permitem que os analistas montem as próprias visões sobre o modelo governado, e essa é a divisão de trabalho certa: o time de dados é responsável pelas dimensões conformadas, pela granularidade dos fatos e pelas definições de métricas; os analistas, pelas perguntas e pela apresentação. Sem o modelo governado por baixo, o self-service gera uma proliferação de extrações contraditórias, e os hábitos de consulta do guia de boas práticas de SQL importam mais, e não menos, quando centenas de consultas ad hoc batem no data warehouse.

Por fim, trate a qualidade dos dados como parte do produto. Completude, validade, consistência entre fontes, pontualidade e unicidade são todas testáveis, e testes executados a cada carga pegam os problemas antes que um diretor os perceba.

Principais pontos

• Esquemas OLTP otimizam escritas concorrentes corretas; modelos analíticos aceitam redundância e mantêm o histórico para que as perguntas sejam fáceis de fazer e as respostas permaneçam estáveis.
• Declare a granularidade de uma tabela fato em uma frase antes de escolher as colunas, e nunca misture na mesma tabela linhas com significados diferentes.
• Prefira um esquema estrela com dimensões desnormalizadas, conformadas e com chaves substitutas; use floco de neve só em hierarquias realmente grandes ou compartilhadas.
• Saiba se cada fato é aditivo, semiaditivo ou não aditivo, e armazene as razões como seus componentes.
• Use a dimensão de mudança lenta do tipo 2 para atributos usados em análise, o tipo 1 apenas para corrigir erros, e sempre ofereça um membro desconhecido.
• Defina cada métrica uma única vez em uma camada semântica e projete os dashboards em torno de decisões, com contexto, atualidade e uma definição publicada.