No Excel, pode criar modelos de dados com milhões de linhas e, em seguida, realizar análises de dados avançadas relativamente a estes modelos. Os modelos de dados podem ser criados com ou sem o suplemento do Power Pivot para dar suporte a qualquer quantidade de Tabelas dinâmicas, gráficos e visualizações do Power View na mesma pasta de trabalho.
Apesar de ser possível criar modelos de dados enormes no Excel, há vários motivos para não fazê-lo. Primeiro, modelos de dados grandes que contêm inúmeras tabelas e colunas são um excesso para a maioria das análises e gera uma Lista de campos grande demais. Segundo, os modelos de dados grandes usam memória valiosa, afetando negativamente outros aplicativos e relatórios que compartilham os mesmos recursos do sistema. Por fim, no Microsoft 365, o SharePoint Online e o Excel Web App limitam o tamanho de um ficheiro do Excel a 10 MB. Você chegará a este limite muito rapidamente no caso de pastas de trabalho de modelos de dados com milhares de linhas. Consulte Especificação e limites do modelo de dados.
Neste artigo, você aprenderá como criar um modelo bem construído com o qual é fácil trabalhar e que usa menos memória. Tirar tempo para aprender as melhores práticas de design de modelos eficientes irá dar frutos em qualquer modelo que crie e utilize, quer esteja a vê-lo no Excel, Microsoft 365 SharePoint Online, num servidor do Office Web Apps ou no SharePoint.
Considere também executar o Otimizador de Tamanho de Pastas de Trabalho. Ele analisa sua pasta de trabalho do Excel e, se possível, compacta-a ainda mais. Transfira o Otimizador de Tamanho do Livro.
Neste artigo
Taxas de compressão e o motor de análise na memória
Os modelos de dados no Excel usam o mecanismo de análise na memória para armazenar dados na memória. Esse mecanismo implementa técnicas de compressão poderosas para reduzir os requisitos de armazenamento, diminuindo um conjunto de resultados até que tenha uma fração de seu tamanho original.
Em média, você pode esperar que um modelo de dados seja 7 a 10 vezes menor que os mesmos dados em seu ponto de origem. Por exemplo, se estiver importando 7 MB de dados de um banco de dados do SQL Server, o modelo de dados no Excel poderia ter 1 MB ou menos. O grau de compressão atingido depende primeiramente da quantidade de valores exclusivos em cada coluna. Quanto mais valores exclusivos, mais memória é necessária para armazená-los.
Por que você está falando sobre compressão e valores exclusivos? Porque para criar um modelo eficiente que reduz o uso da memória é preciso maximizar a compressão, e a forma mais fácil de fazer isso é livrar-se de quaisquer colunas que não são realmente necessárias, especialmente se essas colunas contêm muitos valores exclusivos.
: As diferenças nos requisitos de armazenamento para determinadas colunas podem ser enormes. Em alguns casos é melhor ter várias colunas com uma pequena quantidade de valores exclusivos do que uma coluna com muitos valores exclusivos. A seção sobre otimizações de Datetime fala sobre esta técnica em mais detalhes.
Nada melhor do que uma coluna não existente para gerar baixo uso da memória
A coluna com uso mais eficiente de memória é a que você não importou. Se quiser criar um modelo eficiente, analise cada coluna e pergunte-se se ela contribui para a análise que você deseja realizar. Se não o fizer, ou se você não tiver certeza, deixe-a de fora. Você pode adicionar novas colunas posteriormente se precisar.
Dois exemplos de colunas que sempre devem ser excluídas
O primeiro exemplo relaciona-se aos dados originados de um data warehouse. Em um data warehouse, é comum encontrar artefatos de processos ETL que carregam e atualizam os dados no warehouse. Colunas como “data de criação”, “data de atualização” e “execução do ETL” são criadas quando os dados são carregados. Nenhuma dessas colunas é necessária no modelo e elas devem ser desmarcadas ao importar os dados.
O segundo exemplo envolve a omissão da coluna de chave primária ao importar uma tabela de fatos.
Muitas tabelas, incluindo as tabelas de fatos, têm chaves primárias. Para a maioria das tabelas, como as que contêm dados de clientes, funcionários ou vendas, você precisará usar a chave primária para criar relacionamentos no modelo.
As tabelas de fatos são diferentes. Em uma tabela de fatos, a chave primária é usada para identificar exclusivamente cada linha. Apesar de ser necessário para fins de normalização, é menos útil em um modelo de dados em que se deseja apenas as colunas usadas para análise ou para estabelecer relacionamentos entre as tabelas. Portanto, ao importar de uma tabela de fatos, não inclua sua chave primária. As chaves primárias de uma tabela de fatos consomem enormes quantidades de espaço no modelo, sem oferecer benefícios, já que não podem ser usadas para criar relacionamentos.
: Em armazéns de dados e bases de dados multidimensionais, as tabelas grandes compostas maioritariamente por dados numéricos são frequentemente referidas como "tabelas de factos". Normalmente, as tabelas de factos incluem dados de transações ou desempenho empresarial, tais como pontos de dados de vendas e custos agregados e alinhados com unidades organizacionais, produtos, segmentos de mercado, regiões geográficas, entre outros. Todas as colunas numa tabela de factos que contêm dados de negócio ou que podem ser utilizadas para fazer referência cruzada 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 nenhum outro lugar. Como as tabelas de factos são tão grandes, alguns dos maiores ganhos na eficiência do modelo derivam da exclusão de linhas ou colunas de tabelas de factos.
Como excluir colunas desnecessárias
Modelos eficientes contêm apenas as colunas das quais você realmente precisa em sua pasta de trabalho. Se quiser controlar quais colunas devem ser inclusas no modelo, é preciso usar o Assistente de importação de tabela no suplemento do 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 tabela, selecione quais tabelas deseja importar.
Para cada tabela, clique no botão Visualizar & Filtrar e selecione as partes da tabela que realmente precisa. Recomendamos que desmarque primeiro todas as colunas e, então, marque as colunas que deseja, após considerar se elas serão necessárias para a análise.
Posso filtrar apenas as linhas necessárias?
Muitas tabelas em bancos de dados corporativos e data warehouses contêm dados históricos acumulados ao longo de grandes períodos de tempo. Além disso, você poderá notar que as tabelas em que está interessado contêm informações de áreas do negócio que não são necessárias para sua análise específica.
Usando o Assistente de importação de tabela, você pode filtrar os dados históricos ou não relacionados e, assim, economizar bastante espaço no modelo. Na imagem a seguir, usamos um filtro de data para recuperar apenas as linhas que contêm dados do ano atual, excluindo dados históricos que não são necessários.
E se eu precisar de uma coluna? Há algo que possa fazer para reduzir o espaço utilizado?
Há algumas técnicas adicionais que você pode usar para que uma coluna tenha melhor compressão. Lembre-se de que a única característica da coluna que afeta a compressão é a quantidade de valores exclusivos. Nesta seção, você aprenderá como algumas colunas podem ser alteradas para reduzir a quantidade de valores exclusivos.
Alterando colunas de datetime
Em muitos casos, as colunas de datetime consomem muito espaço. Felizmente, há várias maneiras de reduzir os requisitos de armazenamento para este tipo de dados. As técnicas variam dependendo de como você usa a coluna e sua habilidade ao criar consultas SQL.
As colunas de datetime incluem uma parte de data e uma de hora. Quando você estiver considerando se precisa de uma coluna, faça o mesmo várias vezes no caso de uma coluna de datetime:
-
Preciso da parte da hora?
-
Preciso da parte do tempo ao nível das horas? , minutos? , Segundos? , milissegundos?
-
Preciso ter várias colunas de datetime porque quero calcular a diferença entre elas, ou posso apenas agregar os dados por ano, mês, trimestre, etc.?
A sua resposta a cada uma dessas perguntas determina suas opções para lidar com a coluna de datetime.
Todas essas soluções requerem a alteração de uma consulta SQL. Para que seja mais fácil, remova com os filtros ao menos uma coluna em cada tabela. Ao remover uma coluna com os filtros, você altera a construção da consulta de um formato abreviado (SELECT *) para uma declaração SELECT que contém nomes de coluna totalmente qualificados, que são muito mais fáceis de alterar.
Vamos ver as consultas que foram criadas para você. Na caixa de diálogo Propriedades da tabela, alterne para o Editor de Consultas e veja a consulta SQL atual de cada tabela.
Em Propriedades da tabela, selecione Editor de Consultas.
O Editor de Consultas mostra a consulta SQL usada para preencher a tabela. Se você remover com filtros qualquer coluna durante a importação, sua consulta incluirá nomes de coluna totalmente qualificados:
|
Em comparação, se você importou uma tabela completa, sem desmarcar colunas ou aplicar filtros, verá a consulta como “Select * from ”, o que será mais difícil de modificar:
|
Alterando a consulta SQL
Agora que você sabe como encontrar a consulta, pode alterá-la para reduzir ainda mais o tamanho de seu modelo.
-
Para colunas que contêm moedas ou dados decimais, se você não precisar dos decimais, use esta sintaxe para excluí-los:
“SELECT ROUND([nome_coluna_decimal],0)… .”
Se precisar dos centavos, mas não de frações de centavos, substitua 0 por 2. Se estiver usando números negativos, arredonde para unidades, décimos, centésimos, etc.
-
Se tiver uma coluna de datetime nomeada dbo.Bigtable.[Data Hora] e não precisar da parte da hora, use esta sintaxe para excluí-la:
“SELECT CAST (dbo.Bigtable.[Data Hora] as date) AS [Data Hora]) “
-
Se tiver uma coluna de datetime nomeada dbo.Bigtable.[Data Hora] e precisa de ambas as partes, use várias colunas na consulta SQL, ao invés de uma única coluna de datetime:
“SELECT CAST (dbo.Bigtable.[Data Hora] as date ) AS [Data Hora],
datepart(hh, dbo.Bigtable.[Data Hora]) as [Data Hora Horas],
datepart(mi, dbo.Bigtable.[Datar Hora]) as [Data Hora Minutos],
datepart(ss, dbo.Bigtable.[Data Hora]) as [Data Hora Segundos],
partdata(ms, dbo.Bigtable.[Data Hora]) como [Data Hora Milissegundos]
Use quantas colunas precisar para armazenar cada parte em colunas separadas.
-
Se precisar de horas e minutos, e preferir que fiquem juntos em uma coluna de hora, use a sintaxe:
Timefromparts(datepart(hh, dbo.Bigtable.[Data Hora]), datepart(mm, dbo.Bigtable.[Data Hora])) as [Data Hora HoraMinuto]
-
Se tiver duas colunas de datetime, como [Hora inicial] e [Hora final], o precisa da diferença entre elas em segundos, em uma coluna chamada [Duração], remova ambas as colunas da lista e adicione:
“difdata(ss,[Data inicial],[Data final] como [Duração]”
Se usar a palavra chave "ms" em vez de "ss", obterá a duração em milissegundos
Usando medidas DAX calculadas em vez de colunas
Se você já trabalhou com a linguagem de expressões DAX, pode saber que as colunas calculadas são usadas para derivar novas colunas com base em outras colunas do modelo, enquanto medidas calculadas são definidas uma vez no modelo, mas avaliadas apenas quando usadas em uma Tabela dinâmica ou em outro relatório.
Uma técnica que economiza memória é substituir colunas comuns ou calculadas por medidas calculadas. Alguns exemplos são Preço unitário, Quantidade e Total. Se tiver os três, você pode economizar espaço usando dois deles e calculando o terceiro com o DAX.
Quais duas colunas devo manter?
No exemplo acima, mantenha Quantidade e Preço unitário. Essas duas têm menos valores que o Total. Para calcular o Total, adicione uma medida calculada, como:
“TotaldeVendas:=sumx(‘Tabela de Vendas’,’Tabela de Vendas’[Preço unitário]*’Tabelas de Vendas’[Quantidade])
As colunas calculadas são como colunas comuns, já que ambas ocupam espaço no modelo. Em comparação, as medidas calculadas são calculadas no momento e não ocupam espaço.
Conclusão
Neste artigo, falamos sobre várias abordagem que podem ajudá-lo a criar um modelo que use a memória de forma mais eficiente. A maneira de reduzir o tamanho do arquivo e os requisitos de memória de um modelo de dados é reduzir a quantidade geral de colunas e linhas, e a quantidade de valores exclusivos que aparecem em cada coluna. Eis algumas técnicas que tratamos acima:
-
Remover colunas é, claro, a melhor forma de economizar espaço. Decida de quais colunas você realmente precisa.
-
Algumas vezes você pode remover uma coluna e substituí-la por uma medida calculada na tabela.
-
Talvez você não precise de todas as linhas em uma tabela. Você pode filtrar linhas no Assistente de Importação de Tabela.
-
No geral, dividir uma coluna em várias partes distintas é uma boa forma de reduzir a quantidade de valores exclusivos de uma coluna. Cada uma das partes terá uma pequena quantidade de valores exclusivos e o total combinado será menor que a coluna unificada original.
-
Em muitos casos, você também precisará usar as partes distintas como segmentações de dados em seus relatórios. Quando for apropriado, você pode criar hierarquias de partes como Horas, Minutos e Segundos.
-
Muitas vezes as colunas contêm mais informações do que você precisa. Por exemplo, suponha que uma coluna armazene decimais, mas você aplicou a formatação para ocultar todos os decimais. O arredondamento pode ser muito eficaz para reduzir o tamanho de uma coluna numérica.
Agora que você já fez de tudo para reduzir o tamanho de sua pasta de trabalho, considere também executar o Otimizador de Tamanho de Pastas de Trabalho. Ele analisa sua pasta de trabalho do Excel e, se possível, compacta-a ainda mais. Baixe o Otimizador de Tamanho da pasta de trabalho.
Links relacionados
Especificações e limitações dos Modelos de Dados
Otimizador de tamanho da pasta de trabalho
Power Pivot: análise e modelagem de dados avançadas no Excel
