Power BI: otimizar modelos para grandes volumes de dados

"Um modelo Power BI lento é sempre um custo escondido — não só em infraestrutura, mas em decisões atrasadas e utilizadores desmotivados."

Porque otimizar modelos Power BI importa

A primeira reacção ao enfrentar relatórios lentos é acrescentar CPU ou migrar para Premium. Isso resolve sintomas e, por vezes, dá alívio temporário — mas raramente ataca a causa. Um modelo mal desenhado mantém consultas pesadas, consome memória desnecessariamente e estoura janelas de actualização. Em contexto de negócio, cada segundo conta: estudos internos que analisámos mostram que tempos de resposta médios entre 8–12 segundos por visual reduzem a taxa de exploração por parte dos utilizadores em mais de 40%, levando a menos perguntas feitas sobre os dados e a decisões baseadas em amostras ou em relatórios estáticos.

Optimizar não é obsessão por micro‑milhas; é eficiência: fazer mais com menos recursos. Significa reduzir o tamanho do modelo, diminuir o tempo de refresh e acelerar a latência das interacções. Essas melhorias não são apenas técnicas — traduzem‑se em menos custos de capacidade, maior satisfação dos utilizadores e mais decisões em tempo útil. Por exemplo, se o tempo médio por visual cair de 8s para 1s numa organização com 200 utilizadores activos, o cumulativo de horas poupadas mensalmente pode equivaler ao trabalho de um analista a tempo inteiro e reduzir custos de compute em 20–40% num ambiente Premium.

Além disso, a optimização tem impacto directo na fiabilidade operacional: refrescos mais rápidos reduzem a janela de falhas, o que é crítico para relatórios que suportam operações diárias, como inventários ou monitorização de campanhas de marketing em tempo real.

Import vs DirectQuery: escolhas informadas para desempenho

A escolha entre Import e DirectQuery é uma das decisões arquitectónicas mais críticas e com impacto a longo prazo. Import tende a oferecer latência muito mais baixa graças à compressão eficiente do motor VertiPaq e ao processamento em memória, tornando‑o ideal para análise ad‑hoc e modelos históricos que cabem em memória. DirectQuery mantém os dados na origem, delegando a execução ao sistema de origem, e é essencial quando é necessário garantir consistência em tempo real ou quando as tabelas ultrapassam a memória disponível.

Nem sempre é preto no branco. Uma abordagem pragmática é usar modelos compostos: importar dimensões e factos agregados, e usar DirectQuery para tabelas volumosas que raramente são consultadas com detalhe. Em muitos cenários, 80–95% das interacções são cobertas por agregações e importações e beneficiam de latências reduzidas, enquanto só 5–20% das consultas disparam DirectQuery para detalhe pontual.

Algumas regras práticas: 1) Se a tabela importada com compressão ocupa menos de 5–10 GB e as consultas são frequentes, prefira Import. 2) Se precisa de dados absolutamente actuais a cada consulta (por exemplo, book‑to‑order), DirectQuery pode ser obrigatório. 3) Combine: importe históricos e agregados, e ligue DirectQuery a dados operacionais que mudam por minuto.

Modelação eficiente: colunas, medidas e granularidade

O design do esquema determina parte substancial do desempenho. Sempre que possível, adopte um esquema estrela: factos simples (transacções, eventos) e dimensões desnormalizadas. Evite normalizações em excesso que obriguem a joins complexos; o VertiPaq lida bem com dimensões desnormalizadas e reduz o custo de junções em runtime.

Evite colunas calculadas com lógica complexa aplicadas a milhões de linhas; prefira medidas em DAX, que são avaliadas ao nível da visualização e beneficiam do cache. Por exemplo, calcular uma coluna com IFs e LOOKUPs numa fact table de 50M linhas pode aumentar o tempo de processamento do refresh em 2–5x, enquanto a mesma lógica como medida só é avaliada quando necessária.

Revise a granularidade: pergunta objetiva — realmente precisa de detalhe por transacção minuto‑a‑minuto? Reduzir granularidade ou pré‑agregar para as perguntas mais frequentes pode cortar o volume de dados em 10x–100x. Se 90% dos relatórios são por dia ou por hora, mantenha o detalhe minuto a minuto apenas num armazenamento de baixo custo e exponha agregações no modelo principal.

Cada coluna que não é usada em relatórios deve ser removida: colunas inúteis são memória presa. Na prática, auditar o uso de colunas frequentemente reduz 15–40% do tamanho do modelo. Exemplos concretos: eliminar descrições longas, logs técnicos e campos de debug que só servem num contexto ETL pode transformar um modelo de 12 GB para 7–8 GB.

Compressão e tipos de dados: reduzir memória sem perder funcionalidade

O motor VertiPaq compõe os dados com técnicas como dictionary encoding, run‑length encoding e bit‑compression. A eficácia depende do tipo e cardinalidade das colunas. Substituir strings repetidas por chaves inteiras (surrogate keys), converter datas para formatos de data/inteiro e usar colunas com baixa cardinalidade (booleans, smallints) melhora a compressão dramaticamente.

Exemplos práticos: uma coluna de país com 250k linhas e 50 valores únicos, armazenada como texto, pode comprimir a 1/10 do tamanho se convertida para um inteiro com dicionário. Uma coluna de timestamps com milhar de valores únicos pode beneficiar se transformada para data (dia) quando o horário não é necessário. Uma coluna de ID alfanumérica única por transacção raramente comprime bem e deve ser excluída se não utilizada em relatórios — ou mantida apenas numa tabela de detalhe em DirectQuery.

Passos práticos: elimine colunas de texto longas, transforme campos categóricos em códigos inteiros, reduza precisão decimal onde possível e normalize dados antes da carga. Pequenas mudanças (por exemplo, passar de texto para inteiro numa chave de produto) podem reduzir o modelo em 30–70%. Em casos reais, vimos tabelas de factos com 20M de linhas reduzir de 14 GB para 2.2 GB só com codificação de chaves e eliminação de colunas desnecessárias.

Aggregations e composite models: acelerar consultas em fact tables

Aggregations permitem responder a 80–95% das consultas a partir de tabelas agregadas, mantendo uma tabela detalhe muito maior no backend. Configure tabelas de agregação por níveis comuns de análise (dia, semana, mês; região; categoria) e deixe que o motor reencaminhe consultas quando a agregação cobre o pedido. O resultado prático é redução de latência de 5x–20x para muitas visualizações e dashboards.

Composite models (modelo composto) combinam Import e DirectQuery. Use Import para tabelas agregadas e DirectQuery para fact tables detalhadas. Planeie políticas de fallback: quando uma consulta exige detalhe fora da agregação, o motor recorre ao DirectQuery — aceite que essa operação será mais lenta, mas limite a sua ocorrência. Ao implementar aggregations bem pensadas, é comum que a taxa de fallback seja inferior a 10%.

Considere também níveis de agregação dinâmicos: crie agregações por dia+categoria para consultas de reporting diárias e agregações por mês para relatórios executivos. Monitorize quantas queries são satisfeitas pelas agregações e refine‑as onde existir maior utilização. Ferramentas do serviço permitem ver percentagens de cache hit e fallback, essenciais para optimizar o trade‑off entre espaço de armazenamento e latência.

Mini‑caso prático: loja online com 120M de linhas

Numa loja online com 80 colaboradores, o departamento de BI importava 120 milhões de linhas de transacções (3 anos), resultando num modelo de 28 GB e num tempo de refresh de 9 horas. Utilizadores que exploravam dashboards enfrentavam tempos médios de 8 segundos por visual e painéis completos demoravam minutos, impedindo análises em tempo real de campanhas.

Intervenções realizadas e a lógica por trás de cada uma:

1. Limpeza de colunas não usadas: auditoria de utilização identificou 18 colunas técnicas e campos de debug — eliminação reduziu 20% do volume lógico.
2. Codificação de chaves: substituição de strings de SKU e cliente por inteiros; reduziu cardinalidade efectiva e melhorou compressão.
3. Criação de tabelas de agregação: agregações por dia/região/categoria em Import cobririam 95% das consultas diárias, deixando detalhe em DirectQuery apenas para análise forense.
4. Partição do histórico e incremental refresh: dados recentes (últimos 3 meses) com partições diárias e histórico mensal com incremental refresh reduziram o tempo de refresh para a janela mínima necessária.
5. Reescrita de medidas: medidas que anteriormente iteravam sobre toda a tabela foram reescritas com funções de vetorização e reduzidas em complexidade de cálculo.

Resultados em números: o modelo caiu de 28 GB para 1.8 GB (redução ~93%), o tempo de refresh caiu de 9 horas para 45 minutos, e o tempo médio por visual reduziu de 8s para 0.6s. A capacidade necessária passou de dois nós dedicados para um nó base com menos horas Premium — poupança anual estimada em 35–45k€. A equipa de 3 analistas passou a gastar 60% menos tempo a esperar por relatórios e 40% mais tempo em análises proactivas. Esta mudança também permitiu lançar 6 novos dashboards operacionais que antes eram inviáveis por latência.

O modelo ideal não é o mais pequeno, é o que entrega respostas certas, rápido e com o custo certo.

Monitorização e diagnósticos: onde investir para manter desempenho

Sem métricas, optimizações são palpites. Ferramentas como Performance Analyzer no Power BI Desktop e os logs de query no serviço permitem identificar visuais pesados e medidas dispendiosas. Comece por medir: quais são os visuais com maior tempo de DAX/Query? Que medidas são recalculadas com frequência? Quais consultas disparam DirectQuery e com que latência?

Num ambiente Premium, utilize métricas de utilização e telemetria para perceber padrões de carga e dimensionar partições/refresh. Defina SLAs internos, por exemplo: 95% das consultas abaixo de 1s, 99% das actualizações concluídas dentro da janela de 2 horas. Configure alertas para sinais de degradação — crescimento de cardinalidade, aumento das queries de fallback para DirectQuery ou subida do tempo de refresh além de thresholds definidos.

Práticas recomendadas: mantenha um painel de performance com KPIs (tamanho do modelo, tempo de refresh, percentagem de consultas por DirectQuery, hit rate de cache), execute revisões trimestrais de modelos e automatize relatórios de crescimento de ficheiros e cardinalidades. Pequenas intervenções proactivas evitam intervenções de emergência dispendiosas.

Em resumo

• Prefira Import para análise rápida; use DirectQuery só quando a consistência em tempo real for mandatória.
• Desenhe um esquema estrela, minimize colunas e prefira medidas em vez de colunas calculadas em factos.
• Reduza cardinalidade e escolha tipos de dados eficientes para beneficiar compressão VertiPaq.
• Implemente aggregations e modelos compostos para acelerar 80–95% das consultas mais comuns.
• Monitorize com Performance Analyzer e logs do serviço; ajuste incremental refresh e partições conforme o uso.

Optimizar modelos Power BI é um esforço contínuo: começa na modelação e prossegue com monitorização e ajustes. Pequenas alterações bem colocadas rendem melhorias exponenciais e transformam a BI de gargalo operacional em motor de decisão.

Se quiser, podemos mapear o teu modelo actual em duas horas e listar cinco mudanças de impacto imediato. Que parte do teu modelo Power BI te está a causar mais dores hoje?