No Excel 2013 ou posterior, você pode criar modelos de dados contendo milhões de linhas e, em seguida, executar uma análise de dados eficiente em relação a esses 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.
Observação: Este artigo descreve modelos de dados no Excel 2013. No entanto, os mesmos recursos de modelagem de dados e do Power Pivot introduzidos no Excel 2013 também se aplicam ao Excel 2016. Há pouca diferença entre essas versões de Excel.
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, Microsoft 365, tanto o SharePoint Online quanto o Excel Web App limitam o tamanho de um arquivo Excel para 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. Aproveitar o tempo para aprender as práticas recomendadas em design de modelo eficiente pagará por qualquer modelo que você criar e usar, se você estiver exibindo-o no Excel 2013, no Microsoft 365 SharePoint Online, em um servidor Office Web Apps ou no SharePoint 2013.
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 Workbook.
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.
Observação: 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.
Observação: Em data warehouses e bancos de dados multidimensionais, tabelas grandes que consistem na maioria dos dados numéricos são geralmente conhecidas como "tabelas de fatos". As tabelas de fatos geralmente incluem dados de desempenho comercial ou de transação, como pontos de dados de vendas e custo agregados e alinhados a unidades organizacionais, produtos, segmentos de mercado, regiões geográficas e assim por diante. Todas as colunas em uma tabela de fatos que contenham dados comerciais ou que podem ser usadas para fazer referência cruzada de dados armazenados em outras tabelas devem ser incluídas no modelo para dar suporte à análise de dados. A coluna que você deseja excluir é a coluna de chave primária da tabela de fatos, que consiste em valores exclusivos que existem apenas na tabela de fatos e em nenhum outro lugar. Como as tabelas de fatos são tão grandes, alguns dos maiores ganhos na eficiência do modelo são derivados da exclusão de linhas ou colunas de tabelas de fatos.
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 de tempo no nível de 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 de 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 Workbook.
Links relacionados
Especificações e limitações dos Modelos de Dados
Otimizador de Tamanho da Workbook
Power Pivot: análise e modelagem de dados avançadas no Excel
