No Excel, pode criar modelos de dados com milhões de linhas e, em seguida, efetuar uma análise de dados eficaz com base nesses modelos. Os modelos de dados podem ser criados com ou sem o suplemento Power Pivot para suportar o número de Tabelas Dinâmicas, gráficos e visualizações do Power View no mesmo livro.
Apesar de poder criar facilmente modelos de dados enormes no Excel, existem várias razões para não o fazer. Primeiro, os modelos grandes que contêm imensas tabelas e colunas são exagerados para a maioria das análises e criam uma Lista de Campos complicada. Em segundo lugar, os modelos grandes consomem memória valiosa, afetando negativamente outros aplicativos e relatórios que compartilham os mesmos recursos do sistema. Por fim, no Microsoft 365, tanto o SharePoint Online como o Excel Online limitam o tamanho de um ficheiro do Excel a 10 MB. No caso dos modelos de dados de livros que contêm milhões de linhas, atingirá rapidamente o limite de 10 MB. Consulte Especificação e limites do Modelo de Dados.
Neste artigo, você aprenderá a construir um modelo bem construído, mais fácil de trabalhar e que usa menos memória. Dedicar algum tempo a aprender as melhores práticas de conceção de modelos eficientes será vantajoso para qualquer modelo que crie e utilize, quer esteja a visualizá-lo no Excel, no Microsoft 365 SharePoint Online, num Office Aplicações Web Server ou no SharePoint.
Pondere também executar o Otimizador do Tamanho do Livro. Analisa o seu livro do Excel e, se possível, comprime-o ainda mais. Transfira o Otimizador do Tamanho do Livro.
Neste artigo
Nada supera uma coluna inexistente para baixo uso de memória
E se precisarmos da coluna; ainda podemos reduzir o seu custo espacial?
Taxas de compressão e o motor de análise na memória
Os modelos de dados no Excel utilizam o motor de análise na memória para armazenar dados na memória. O motor implementa técnicas de compressão poderosas para reduzir os requisitos de armazenamento, reduzindo um conjunto de resultados até que seja uma fração do seu tamanho original.
Em média, é de esperar que um modelo de dados seja 7 a 10 vezes menor que os mesmos dados no seu ponto de origem. Por exemplo, se estiver a importar 7 MB de dados a partir de uma base de dados SQL Server, o modelo de dados no Excel pode facilmente ser igual ou inferior a 1 MB. O grau de compressão realmente alcançado depende principalmente do número de valores exclusivos em cada coluna. Quanto mais valores exclusivos, mais memória é necessária para armazená-los.
Porque estamos a falar de compressão e valores únicos? A criação de um modelo eficiente que minimize o uso da memória tem tudo a ver com a maximização da compactação, e a maneira mais fácil de fazer isso é livrar-se de todas as colunas que você realmente não precisa, especialmente se essas colunas incluírem um grande número de valores exclusivos.
Nota
As diferenças nos requisitos de armazenamento para colunas individuais podem ser enormes. Em alguns casos, é melhor ter múltiplas colunas com um número baixo de valores exclusivos do que uma coluna com um número elevado de valores exclusivos. A seção sobre otimizações Datetime aborda essa técnica em detalhes.
Nada supera uma coluna inexistente para baixo uso de memória
A coluna com maior consumo de memória é aquela que nunca importou. Se quiser criar um modelo eficiente, olhe para cada coluna e pergunte-se se contribui para a análise que pretende executar. Se não tiver ou se não tiver a certeza, deixe-o de fora. Pode sempre adicionar novas colunas posteriormente se precisar delas.
Dois exemplos de colunas que devem ser sempre excluídas
O primeiro exemplo refere-se a dados que se originam de um armazém de dados. Em um data warehouse, é comum encontrar artefatos de processos ETL que carregam e atualizam dados no warehouse. Colunas como "data de criação", "data de atualização" e "execução de ETL" são criadas quando os dados são carregados. Nenhuma destas colunas é necessária no modelo e deverá ser desmarcada quando importar os dados.
O segundo exemplo envolve a omissão da coluna de chave primária ao importar uma tabela de factos.
Muitas tabelas, incluindo tabelas de factos, têm chaves primárias. Para a maioria das tabelas, como as que contêm dados de clientes, funcionários ou vendas, é recomendável a chave primária da tabela para que possa utilizá-la para criar relações no modelo.
As tabelas de factos são diferentes. Numa tabela de factos, a chave primária é utilizada para identificar cada linha de forma exclusiva. Embora seja necessário para fins de normalização, é menos útil num modelo de dados em que pretende que apenas essas colunas sejam utilizadas para análise ou para estabelecer relações entre tabelas. Por este motivo, ao importar a partir de uma tabela de factos, não inclua a respetiva chave primária. As chaves primárias numa tabela de factos consomem enormes quantidades de espaço no modelo, mas não proporcionam vantagens, uma vez que não podem ser utilizadas para criar relações.
Nota
Em armazéns de dados e bases de dados multidimensionais, as grandes tabelas que consistem principalmente em dados numéricos são frequentemente referidas como "tabelas de factos". As tabelas de factos normalmente incluem dados de transações ou desempenho da empresa, como pontos de dados de vendas e custos que são agregados e alinhados com unidades organizacionais, produtos, segmentos de mercado, regiões geográficas e assim sucessivamente. Todas as colunas de uma tabela de factos que contenham dados de negócio ou que possam ser utilizadas para aplicar referências cruzadas a dados armazenados noutras tabelas devem ser incluídas no modelo para suportar a análise de dados. A coluna que pretende excluir é a coluna de chave primária da tabela de factos, que consiste em valores exclusivos que existem apenas na tabela de factos e em mais nenhum lugar. Devido ao facto de as tabelas de factos serem enormes, alguns dos maiores ganhos em eficiência dos modelos resultam da exclusão de linhas ou colunas das tabelas de factos.
Como excluir colunas desnecessárias
Os modelos eficientes contêm apenas as colunas de que realmente precisa no seu livro. Se quiser controlar que colunas são incluídas no modelo, terá de utilizar o Assistente de Importação de Tabelas no suplemento Power Pivot para importar os dados em vez da caixa de diálogo "Importar Dados" no Excel.
Ao iniciar o Assistente de Importação de Tabelas, selecione as tabelas a importar.
Para cada tabela, pode clicar no botão Pré-visualizar & Filtrar e selecionar as partes da tabela de que realmente precisa. Recomendamos que primeiro desmarque todas as colunas e, em seguida, continue a verificar as colunas que pretende, depois de ponderar se são necessárias para a análise.
Que tal filtrar apenas as linhas necessárias?
Muitas tabelas em bases de dados empresariais e armazéns de dados contêm dados históricos acumulados ao longo de longos períodos de tempo. Além disso, poderá descobrir que as tabelas em que está interessado contêm informações sobre áreas da empresa que não são necessárias para a sua análise específica.
Ao utilizar o Assistente de Importação de Tabelas, pode filtrar dados históricos ou não relacionados e poupar bastante espaço no modelo. Na imagem seguinte, um filtro de data é utilizado para obter apenas linhas que contêm dados do ano atual, excluindo dados do histórico que não serão necessários.
E se precisarmos da coluna; ainda podemos reduzir o seu custo espacial?
Existem algumas técnicas adicionais que pode aplicar para tornar uma coluna um melhor candidato para compressão. Lembre-se de que a única característica da coluna que afeta a compressão é o número de valores exclusivos. Nesta secção, irá aprender como algumas colunas podem ser modificadas para reduzir o número de valores exclusivos.
Modificar colunas Data/Hora
Em muitos casos, as colunas Data/Hora ocupam muito espaço. Felizmente, existem várias formas de reduzir os requisitos de armazenamento para este tipo de dados. As técnicas variam dependendo da forma como utiliza a coluna e do seu nível de conforto na criação de consultas SQL.
As colunas data/hora incluem uma parte de data e uma hora. Quando se perguntar se precisa de uma coluna, faça a mesma pergunta várias vezes para uma coluna Data/Hora:
- Preciso da peça de tempo?
- Preciso da parte do tempo ao nível das horas? , minutos? , segundos? , milissegundos?
- Tenho múltiplas colunas Data/Hora porque quero calcular a diferença entre elas ou apenas para agregar os dados por ano, mês, trimestre e por aí adiante.
A maneira como responde a cada uma destas perguntas determina as opções disponíveis para lidar com a coluna Data/Hora.
Todas essas soluções exigem a modificação de uma consulta SQL. Para facilitar a modificação de consultas, deve filtrar pelo menos uma coluna em cada tabela. Ao filtrar uma coluna, altera a construção da consulta de um formato abreviado (SELECT *) para uma instrução SELECT que inclui nomes de coluna completamente qualificados, que são muito mais fáceis de modificar.
Vamos ver as consultas que são criadas para si. A partir da caixa de diálogo Propriedades da Tabela, pode mudar para o Editor de consultas e ver a consulta SQL atual para cada tabela.
Em Propriedades da Tabela, selecione Editor do Power Query.
O Editor do Power Query mostra a consulta SQL utilizada para preencher a tabela. Se filtrou qualquer coluna durante a importação, a sua consulta inclui nomes de coluna completamente qualificados:
Por outro lado, se tiver importado uma tabela na sua totalidade, sem desmarcar qualquer coluna ou aplicar qualquer filtro, verá a consulta como "Selecionar * de ", o que será mais difícil de modificar:
|
|---|
Modificando a consulta SQL
Agora que sabe como localizar a consulta, pode modificá-la para reduzir ainda mais o tamanho do seu modelo.
- Para colunas que contenham dados monetários ou decimais, se não precisar dos decimais, utilize esta sintaxe para eliminar os decimais:
"SELECT ROUND([Decimal_column_name],0)... .”
Se precisar de cêntimos, mas não de frações de cêntimos, substitua o 0 por 2. Se utilizar números negativos, pode arredondar para unidades, dezenas, centenas, etc. - Se tiver uma coluna Datahora denominada dbo. Bigtable. [Date Time] e não precisar da parte Time, utilize a sintaxe para se livrar da hora:
"SELECT CAST (dbo. Bigtable. [Data hora] como data) AS [Data hora]) " - Se tiver uma coluna Datahora denominada dbo. Bigtable. [Date Time] e precisar das partes de Data e Hora, utilize várias colunas na consulta SQL em vez da única coluna Data/Hora:
"SELECT CAST (dbo. Bigtable. [Date Time] as date ) AS [Date Time],
PartData(hh, dbo. Bigtable. [data, hora]) como [Date Time Hours],
PartData(mi, dbo. Bigtable. [data, hora]) como [Data, Hora, Minutos],
PartData(ss, dbo. Bigtable. [data, hora]) como [Data e Hora Segundos],
DatePart(ms, dbo. Bigtable. [data, hora]) como [Data, Hora, milissegundos]"
Utilize todas as colunas necessárias para armazenar cada parte em colunas separadas. - Se precisar de horas e minutos e preferi-los em conjunto como uma coluna de uma só vez, pode utilizar a sintaxe :
Timefromparts(datepart(hh, dbo. Bigtable. [Date Time]), datepart(mm, dbo. Bigtable. [Data e hora])) como [Data, Hora, HoraMinuto] - Se tiver duas colunas data/hora, como [Hora de Início] e [Hora de Fim], e aquilo de que realmente precisa é da diferença de tempo entre elas em segundos, como uma coluna chamada [Duração], remova ambas as colunas da lista e adicione:
"Datediff(ss,[Data de Início],[Data de Fim]) como [Duração]"
Se utilizar a palavra-chave ms em vez de ss, obterá a duração em milissegundos
Utilizar medidas calculadas DAX em vez de colunas
Se já trabalhou com a linguagem de expressões DAX, talvez já saiba que as colunas calculadas são utilizadas para derivar novas colunas com base noutra coluna do modelo, ao passo que as medidas calculadas são definidas uma vez no modelo, mas avaliadas apenas quando utilizadas numa Tabela Dinâmica ou noutro relatório.
Uma técnica que economiza memória é substituir colunas regulares ou calculadas por medidas calculadas. O exemplo clássico é Preço Unitário, Quantidade e Total. Se tiver os três, pode poupar espaço ao manter apenas dois e calcular o terceiro através de DAX.
Quais as 2 colunas que deve manter?
No exemplo acima, mantenha Quantidade e Preço Unitário. Estes dois têm menos valores do que o Total. Para calcular o Total, adicione uma medida calculada como:
"TotalSales:=sumx('Tabela de Vendas','Tabela de Vendas'[Preço Unitário]*'Tabela de Vendas'[Quantidade])"
As colunas calculadas são como colunas normais, na medida em que ambas ocupam espaço no modelo. Em contraste, as medidas calculadas são calculadas em tempo real e não ocupam espaço.
Conclusão
Neste artigo, falamos sobre várias abordagens que podem ajudá-lo a criar um modelo mais eficiente em termos de memória. A forma de reduzir o tamanho do ficheiro e os requisitos de memória de um modelo de dados é reduzir o número total de colunas e linhas e o número de valores exclusivos que aparecem em cada coluna. Aqui estão algumas técnicas que abordamos:
- Remover colunas é, naturalmente, a melhor forma de poupar espaço. Decida quais as colunas de que precisa realmente.
- Por vezes, pode remover uma coluna e substituí-la por uma medida calculada na tabela.
- Poderá não precisar de todas as linhas numa tabela. Pode filtrar linhas no Assistente de Importação de Tabelas.
- Em geral, dividir uma única coluna em múltiplas partes distintas é uma boa forma de reduzir o número de valores exclusivos numa coluna. Cada uma das partes terá um pequeno número de valores exclusivos e o total combinado será menor do que a coluna unificada original.
- Em muitos casos, também precisa de partes distintas para utilizar como segmentações de dados nos seus relatórios. Quando apropriado, pode criar hierarquias a partir de partes como Horas, Minutos e Segundos.
- Muitas vezes, as colunas contêm mais informações do que as necessárias. Por exemplo, suponha que uma coluna armazena decimais, mas aplicou formatação para ocultar todos os decimais. O arredondamento pode ser muito eficaz na redução do tamanho de uma coluna numérica.
Agora que já fez o que pode para reduzir o tamanho do seu livro, considere também executar o Otimizador do Tamanho do Livro. Analisa o seu livro do Excel e, se possível, comprime-o ainda mais. Transfira o Otimizador do Tamanho do Livro.
Ligações relacionadas
Especificação e limites do Modelo de Dados
Otimizador do Tamanho do Livro
Power Pivot: análise e modelação de dados avançadas no Excel