Design de data warehouse: esquema estrela para análise de ERP e comércio eletrônico

Seu banco de dados ERP é otimizado para transações --- inserção de pedidos, atualização de estoque, processamento de pagamentos. Sua plataforma de comércio eletrônico é otimizada para veicular páginas de produtos e processar checkouts. Nenhum dos dois está otimizado para responder às questões que orientam as decisões de negócios: quais categorias de produtos são mais lucrativas após as devoluções? Quais segmentos de clientes têm valor de vida útil crescente? Onde estão os gargalos em nossa cadeia de abastecimento?

Essa lacuna é o que um data warehouse preenche. E o esquema em estrela é o padrão de design que torna as consultas analíticas rápidas, intuitivas e fáceis de manter.

Principais conclusões

• O esquema estrela separa as métricas de negócios (fatos) do contexto descritivo (dimensões), tornando as consultas intuitivas e rápidas
• A análise de ERP e comércio eletrônico normalmente precisa de quatro a seis tabelas de fatos e de oito a doze tabelas de dimensões para cobrir as principais questões de negócios
• Os pipelines ETL devem usar carregamento incremental com dimensões que mudam lentamente para lidar com análises históricas sem reprocessar todos os dados
• Um esquema em estrela bem projetado reduz a complexidade da consulta em 60 a 80 por cento em comparação com a consulta direta de bancos de dados operacionais normalizados

---

Por que não consultar o ERP diretamente?

Antes de investir em um data warehouse separado, muitas empresas tentam executar consultas analíticas em seu banco de dados operacional. Isto falha por três razões.

Desempenho. As consultas analíticas examinam milhões de linhas, computam agregações e unem muitas tabelas. Executá-los no banco de dados de produção torna o ERP mais lento para cada usuário. Um relatório que verifica dados de pedidos de seis meses pode bloquear tabelas e prejudicar o desempenho do checkout em sua loja Shopify.

Complexidade. Os bancos de dados operacionais são normalizados --- projetados para minimizar a redundância de dados. Uma pergunta simples como “receita total por categoria de produto por mês” pode exigir a junção de oito tabelas no banco de dados PostgreSQL do Odoo. Em um esquema em estrela, a mesma consulta une duas tabelas.

Histórico. Os sistemas operacionais substituem os dados. Quando um cliente muda de endereço, o endereço antigo desaparece. Quando um produto é recategorizado, os relatórios históricos mudam retroativamente. Um data warehouse preserva o histórico por meio de dimensões que mudam lentamente.

Várias fontes. As empresas de médio porte normalmente executam de três a sete sistemas que contêm dados de negócios. O data warehouse consolida todos eles. Nosso guia para pipelines ETL para dados ERP cobre a extração e o carregamento em detalhes.

---

Fundamentos do esquema em estrela

Um esquema em estrela organiza os dados em dois tipos de tabelas: tabelas de fatos e tabelas de dimensões. As tabelas de fatos ficam no centro (o corpo da estrela) e as tabelas de dimensões as cercam (as pontas da estrela).

Tabelas de fatos

As tabelas de fatos armazenam eventos de negócios mensuráveis ​​– coisas que aconteceram. Cada linha representa um evento com a menor granularidade significativa.

Características:

• Contém medidas numéricas (quantidade, quantidade, duração, contagem)
• Contém chaves estrangeiras para tabelas de dimensão
• Normalmente são as maiores mesas do armazém
• Crescer continuamente à medida que novos eventos ocorrem
• Deve ser da melhor qualidade que apoie questões de negócios

Tabelas de dimensões

As tabelas de dimensões armazenam contexto descritivo --- quem, o quê, onde, quando e como dos eventos de negócios.

Características:

• Contém atributos textuais e hierarquias
• São relativamente pequenos (milhares a milhões de linhas, não bilhões)
• Mude lentamente ao longo do tempo
• São desnormalizados para simplicidade de consulta
• Forneça rótulos, filtros e agrupamentos para relatórios

A forma de estrela

                    Dim: Customer
                         |
    Dim: Product ---  Fact: Sales  --- Dim: Time
                         |
                    Dim: Location

Uma consulta como "receita total por categoria de produto, por trimestre e por região" une a tabela de fatos de vendas a tabelas de três dimensões. Sem subconsultas, sem junções aninhadas complexas --- apenas junções em estrela simples.

---

Projetando tabelas de fatos para ERP e comércio eletrônico

Uma típica empresa de médio porte que executa o Odoo ERP e o Shopify eCommerce precisa de quatro a seis tabelas de fatos para cobrir os principais casos de uso analítico.

Fato: Vendas

A tabela de fatos de vendas é a base. Cada linha representa um item de linha em um pedido de venda.

Grãos: uma linha por item de linha do pedido por dia.

Fato: Inventário

Rastreia os níveis de estoque como instantâneos periódicos em vez de eventos.

Grãos: Uma linha por produto, por armazém, por dia.

Fato: Produção

Para empresas de manufatura, o fato de produção rastreia as ordens de serviço.

Grãos: Uma linha por ordem de serviço, por produto, por dia.

Tabelas de fatos adicionais

• Fato: Compras --- gastos com compras por fornecedor, produto e tempo.
• Fato: Tickets de suporte --- volume de tickets, tempo de resposta, tempo de resolução por agente, cliente e categoria.
• Fato: Tráfego da Web --- visualizações de páginas, sessões, conversões por página, origem e campanha. Útil para análise de atribuição de marketing.

---

Projetando tabelas de dimensões

As tabelas de dimensões fornecem o contexto que torna os números das tabelas de fatos significativos. O princípio fundamental é a desnormalização – armazenamento de dados redundantes para simplificar as consultas.

Dim: Tempo

A dimensão do tempo está presente em todos os esquemas em estrela. Pré-calcule atributos de calendário para evitar funções de data complexas em consultas.

Dim: Cliente

Desnormalize os atributos do cliente dos sistemas CRM, contabilidade e comércio eletrônico em uma única dimensão.

As colunas rfm_segment e lifetime_value_tier são campos calculados derivados de análise RFM, atualizados periodicamente pelo pipeline ETL.

Dim: Produto

---

Mudando lentamente as dimensões

Quando um cliente se muda de Nova York para Londres, o que o data warehouse deve fazer? A resposta depende da questão comercial.

Tipo 1: Sobrescrever

Substitua o valor antigo pelo novo valor. A cidade do cliente passa a ser Londres e todos os pedidos históricos agora mostram Londres. Use isto quando a precisão histórica do atributo não importa.

Tipo 2: Adicionar nova linha

Crie uma nova linha para o cliente com a nova cidade, uma data de vigência e uma data de expiração. As ordens históricas ainda apontam para a linha antiga (Nova York) e as novas ordens apontam para a nova linha (Londres). Esta é a abordagem mais comum para atributos que afetam a análise – segmento de cliente, departamento de funcionários, categoria de produto.

Tipo 3: Adicionar nova coluna

Armazene valores antigos e novos em colunas separadas. Útil quando você precisa comparar o antes e o depois, mas não precisa do histórico completo. Menos comum na prática.

Para empresas de médio porte, use o Tipo 2 para segmento de clientes, departamento de funcionários, categoria de produto e atributos geográficos. Use o Tipo 1 para todo o resto para manter o armazém simples.

---

Padrões de projeto ETL

O processo ETL (Extract, Transform, Load) move dados dos sistemas de origem para o warehouse. Os padrões de design que funcionam bem para dados de ERP e comércio eletrônico incluem o seguinte.

Carregamento Incremental

Em vez de recarregar todos os dados em cada execução, rastreie o último carimbo de data/hora carregado com sucesso e processe apenas os registros modificados desde então. O campo write_date do Odoo e o parâmetro updated_at do Shopify tornam isso simples.

1. Query source: SELECT * FROM sale_order_line WHERE write_date > last_load_timestamp
2. Transform: Map source fields to warehouse columns, look up dimension keys
3. Load: INSERT new rows, UPDATE changed rows (upsert)
4. Update: Set last_load_timestamp to current run start time

Gerenciamento de chave substituta

As tabelas de dimensões usam chaves substitutas (números inteiros com incremento automático) em vez de chaves naturais (IDs Odoo, IDs Shopify). Isso desacopla o warehouse dos formatos de chave do sistema de origem e lida com a consolidação de múltiplas fontes onde sistemas diferentes possuem esquemas de ID conflitantes.

Dimensões de chegada tardia

Às vezes, um registro de fato chega antes do registro de dimensão correspondente – um pedido faz referência a um novo cliente que ainda não foi sincronizado. Trate isso com uma linha de dimensão de espaço reservado que é atualizada quando o registro de dimensão completo chega.

Atualizar agendamento

Para requisitos em tempo real, consulte nosso guia para análise de streaming.

---

Otimização de desempenho de consulta

Um esquema em estrela bem projetado já tem um bom desempenho devido aos seus padrões de junção simples. Otimizações adicionais incluem o seguinte.

Índices. Crie índices em todas as chaves estrangeiras de dimensão em tabelas de fatos e em atributos de dimensão comumente filtrados (intervalos de datas, segmentos de clientes, categorias de produtos).

Visualizações materializadas. Consultas comuns pré-agregadas: receita diária por categoria de produto, níveis de estoque semanais por armazém, aquisição mensal de clientes por canal. Atualize as visualizações materializadas após cada carregamento de ETL.

Particionamento. Particione grandes tabelas de fatos por data (mensal ou trimestralmente). As consultas que filtram por intervalo de datas verificam apenas as partições relevantes.

Estatísticas de coluna. Mantenha as estatísticas do PostgreSQL atualizadas com ANALYZE após carregamentos em massa para que o planejador de consultas tome decisões ideais.

Essas otimizações oferecem suporte à experiência de BI de autoatendimento, em que os usuários corporativos executam consultas ad hoc sem preocupações de desempenho.

---

Perguntas frequentes

Qual o tamanho que a empresa precisa ter para justificar um data warehouse?

Não há tamanho mínimo, mas o investimento vale a pena quando você tem diversas fontes de dados que precisam ser combinadas para análise, quando as consultas operacionais aos bancos de dados estão desacelerando os sistemas de produção ou quando você gasta mais de 10 horas por semana na coleta manual de dados e na criação de relatórios. A maioria das empresas com 30 ou mais funcionários e pelo menos dois sistemas (ERP mais eCommerce) beneficiam de um armazém.

Devemos usar um data warehouse em nuvem como Snowflake ou BigQuery?

Para empresas de médio porte, o PostgreSQL lida bem com a maioria das cargas de trabalho analíticas e custa significativamente menos. Armazéns em nuvem como o Snowflake tornam-se atraentes quando seus dados excedem 1 TB, quando você precisa separar a computação do armazenamento para otimização de custos ou quando você tem requisitos complexos de compartilhamento de dados entre organizações. Comece com o PostgreSQL e migre quando superá-lo.

Quanto tempo leva para construir um data warehouse?

Um armazém mínimo viável com uma tabela de fatos (vendas), tabelas de quatro dimensões e um pipeline ETL conectando Odoo e Shopify leva de quatro a oito semanas para uma equipe experiente. Adicionar tabelas de fatos, alterar dimensões lentamente e monitorar a qualidade dos dados leva de quatro a oito semanas por tabela de fatos. Planeje de três a seis meses um armazém abrangente que cubra todas as principais áreas de negócios.

---

O que vem a seguir

Um esquema em estrela bem projetado é a base para todos os recursos de análise, desde painéis de autoatendimento a modelos preditivos e análises incorporadas. Faz parte de uma estratégia de BI mais ampla que transforma a forma como sua empresa toma decisões.

ECOSIRE constrói data warehouses e pipelines analíticos para empresas que executam Odoo, Shopify e GoHighLevel. Nossa equipe de consultoria Odoo projeta esquemas de warehouse adaptados ao seu modelo de negócios, e nossos serviços de IA OpenClaw colocam análises preditivas em camadas.

Entre em contato conosco para discutir sua arquitetura de data warehouse.

---

Publicado por ECOSIRE --- ajudando empresas a escalar com soluções baseadas em IA em Odoo ERP, Shopify eCommerce e OpenClaw AI.