A equipe de análise e inteligência de negócios da Clicou Vendas recentemente fez uma parceria com uma grande empresa de varejo para modernizar seu ecossistema de relatórios. Depois de analisar cuidadosamente o estado atual e as necessidades futuras, decidimos substituir a solução atual de cubos do SQL Server Analysis Services e os relatórios de tabela dinâmica no Excel por um data warehouse modernizado e o Microsoft Power BI.
A equipe projetou e desenvolveu as pastas de trabalho necessárias do Power BI e, assim que o desenvolvimento, o teste de unidade e a revisão de código foram concluídos, a etapa final foi publicar as pastas de trabalho e agendar uma atualização usando os serviços do Power BI. No entanto, durante a atualização agendada, recebemos a seguinte mensagem de erro:
The amount of uncompressed data on the gateway client has exceeded the limit of 10 GB for a single table. Please consider reducing the use of highly repetitive strings values through normalized keys, removing unused columns, or upgrading to Power BI Premium.
Como o desenvolvimento dessas pastas de trabalho foi realizado localmente em nossos computadores, esse não foi um problema que surgiu até publicarmos no serviço Power BI e tentarmos agendar uma atualização.
Pela Microsoft, os limites de dados impostos aos usuários do Power BI Pro são:
- Tamanho máximo do conjunto de dados compactados de 1 GB.
- Limite de 10 GB na quantidade de dados não compactados processados durante as atualizações.
- Não há restrições de tamanho de dados no Power BI Premium.
A mensagem de erro fornecida pela Microsoft é informativa e fornece orientações para uma solução, mas e se seus dados já estiverem altamente normalizados, você removeu colunas não utilizadas e a atualização para o Power BI Premium não é uma opção? Como você identifica o problema exato, para saber onde e como otimizar seu modelo de dados?
À medida que a equipe pesquisava o problema, era necessário analisar o modelo de dados criado pelo mecanismo VertiPaq no Power BI.
Após analisar o tamanho do modelo de dados fornecido pela ferramenta analisadora VertiPaq, a equipe conseguiu identificar a causa raiz do problema. Uma das práticas recomendadas da Microsoft para criar e importar dados para o Power BI é limitar a largura das tabelas, se possível.
A equipe descobriu que as chaves compostas criadas durante o desenvolvimento usando o Power Query M consumiram uma quantidade considerável de espaço, pois muitas das colunas usadas eram valores de texto e, devido aos dados de origem e ao escopo do projeto, não conseguimos criar uma chave primária singular .
A natureza do nosso conjunto de dados exigia que todas as tabelas fossem unidas em pelo menos duas colunas. O Power BI nativamente apenas permite junções em uma única coluna, portanto, colunas ou índices concatenados devem ser usados.
O Power BI permite junções apenas em um único campo no Editor de Relacionamento, mas as tabelas podem ser mescladas em várias colunas no editor do Power Query. Portanto, nossa primeira solução foi usar a funcionalidade Index Column e Merge Queries no editor do Power Query. A funcionalidade da coluna de índice atribui um número exclusivo a cada linha nas tabelas dimensionais que podem ser mescladas de volta à tabela de fatos usando a funcionalidade de consulta de mesclagem. As colunas indexadas podem ser usadas para criar o relacionamento no editor de relacionamentos. No entanto, essas etapas causaram problemas de desempenho ao atualizar o conjunto de dados que nos forçou a considerar outras opções.
Exemplo:
Usando a Coluna do Índice para criar uma chave primária na tabela de dimensões que pode ser usada no Editor de Relacionamento:
Em seguida, a equipe criou uma coluna calculada em M delimitada por dois pontos (por exemplo, Coluna A: Coluna B) nas tabelas de dimensões e fatos que poderiam ser relacionadas no Editor de Relacionamento.
Diferentemente da opção anterior, esse desempenho aprimorado, mas como as alterações no modelo de dados usando M são criadas no lado do cliente e devem fluir pelo gateway de dados, o tamanho da carga de dados aumentou drasticamente.
A opção final que produziu os melhores resultados foi usar o DAX para criar o mesmo campo concatenado usando o comando COMBINEVALUES DAX delimitado por dois pontos. Isso não aumenta o tamanho dos dados que fluem pelo gateway de dados, porque as colunas criadas no DAX são criadas após o carregamento dos dados no serviço.
Algumas outras alterações foram feitas para reduzir o tamanho dos dados:
- Mover todos os cálculos para o DAX para limitar o tamanho do conjunto de dados.
- Nosso aplicativo de origem tinha muitos campos decimais acima de 18 casas e reduzimos para quatro casas decimais.
- Colunas redundantes removidas e dados normalizados sempre que possível.
Existem várias maneiras de modelar e transformar dados no Power BI para atender às suas necessidades de relatórios. No entanto, cada método tem suas próprias vantagens e desvantagens em relação à localização da maior parte dos cálculos, tempo de atualização e recursos de modelagem.
Saber onde e como fazer qualquer cálculo e modelagem pode fazer uma diferença drástica no desempenho e no tempo de atualização na pasta de trabalho do Power BI.
Fonte: credera