Criar um modelo de dados com uso eficiente de memória usando o Excel e o suplemento PowerPivot

Importante :  Este artigo foi traduzido por um sistema de tradução automática, leia o aviso de isenção de responsabilidade. Para sua referência, veja a versão em inglês deste artigo aqui.

No Excel 2013 ou posterior, você pode criar modelos de dados contendo milhões de linhas e, em seguida, executar análises de dados sofisticadas contra esses modelos. Modelos de dados podem ser criados com ou sem o suplemento Power Pivot para dar suporte a qualquer número de tabelas dinâmicas, gráficos e visualizações do Power View na mesma pasta de trabalho.

Observação : Este artigo descreve os modelos de dados no Excel 2013. No entanto, as mesmos modelagem de dados e os recursos de Power Pivot introduzidos no Excel 2013 também se aplicam ao Excel 2016. Não há efetivamente pouca diferença entre nessas versões do 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. Finalmente, no Office 365, o SharePoint Online e o aplicativo Web do Excel limitam o tamanho de um arquivo 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. Aprender as melhores práticas para projetar modelos eficientes será recompensador no futuro ao criar e usar modelos, quer você os visualize no Excel 2013, no SharePoint Online do Office 365, no Office Web Apps Server ou no SharePoint 2013.

Considere também executando o otimizador de tamanho. Ele analisa sua pasta de trabalho do Excel e se possível, compacta-lo ainda mais. Baixe o Otimizador de tamanho.

Neste artigo

Taxas de compressão e mecanismo de análise na memória

Nada melhor do que uma coluna não existente para reduzir o uso da memória

Dois exemplos de colunas que sempre devem ser excluídas

Como excluir colunas desnecessárias

Posso filtrar apenas as linhas necessárias?

E se eu precisar de uma coluna? Há algo que possa fazer para reduzir o espaço utilizado?

Alterando colunas de Datetime

Alterando a consulta SQL

Usando medidas DAX calculadas em vez de colunas

Quais duas colunas devo manter?

Conclusão

Links relacionados

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 principalmente de dados numéricos geralmente são chamadas de “tabelas de fatos”. Essas tabelas normalmente incluem dados de desempenho do negócio ou de transações, como dados de vendas e pontos de dados de custos que são agregados e alinhados a unidades organizacionais, produtos, segmentos de mercado, regiões geográficas, etc. Todas as colunas de uma tabela de fatos que contêm dados do negócio ou que podem ser usadas para realizar a referência cruzada com dados armazenados em outras tabelas devem ser incluídas no modelo para oferecer suporte à análise de dados. A coluna que você deve excluir é a de chave primária da tabela de fatos, que consiste de valores exclusivos que existem apenas em uma tabela de fatos, e em nenhum outro lugar. Como as tabelas de fatos são muito grandes, alguns dos maiores benefícios na eficácia do modelo deriva-se da exclusão de linhas ou colunas das 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.

Assistente de importação de tabela no suplemento do PowerPivot

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.

Painel Visualizar no Assistente de importação de tabela

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.

Painel Filtrar no Assistente de importação de tabela

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 da hora com o elemento de horas, de minutos, de segundos ou de 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.

faixa de opções na janela do powerpivot mostrando o comando propriedades da tabela

Em Propriedades da tabela, selecione Editor de Consultas.

Abra o Editor de Consultas na caixa de diálogo Propriedades da tabela

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:

consulta sql usada para recuperar os dados

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:

consulta sql usando a sintaxe padrão e mais curta

Alterando a consulta SQL

Agora que você sabe como encontrar a consulta, pode alterá-la para reduzir ainda mais o tamanho de seu modelo.

  1. 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.

  2. 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]) “

  3. 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.

  4. 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]

  5. 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.

  • Você pode não precisar de todas as linhas da tabela, então pode remover com filtros as linhas desnecessárias 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 uma formatação que oculta todos os decimais. O arredondamento pode ser muito eficaz para reduzir o tamanho de uma coluna numérica.

Agora que você já fez o que você pode para reduzir o tamanho da pasta de trabalho, considere também executando o otimizador de tamanho. Ele analisa sua pasta de trabalho do Excel e se possível, compacta-lo ainda mais. Baixe o Otimizador de tamanho.

Links relacionados

Especificações e limitações dos Modelos de Dados

Download do Otimizador de tamanho

PowerPivot: Análise de dados avançada e modelagem de dados no Excel

Observação : Aviso de Isenção de Tradução Automática: Este artigo foi traduzido por computador, sem intervenção humana. A Microsoft oferece essas traduções automáticas para ajudar as pessoas que não falam inglês a aproveitar os textos escritos sobre produtos, serviços e tecnologias da Microsoft. Como este artigo foi traduzido automaticamente, é possível que contenha erros de vocabulário, sintaxe ou gramática.

Expanda suas habilidades
Explore o treinamento
Obtenha novos recursos primeiro
Ingressar no Office Insider

Essas informações foram úteis?

Obrigado por seus comentários!

Agradecemos pelos seus comentários! Parece que pode ser útil conectar você a um de nossos agentes de suporte do Office.

×