Criar um modelo de dados de Otimização da memória com o Excel e o suplemento Power Pivot

Nota:  Queremos fornecer-lhe os conteúdos de ajuda mais recentes o mais rapidamente possível e no seu idioma. Esta página foi traduzida automaticamente e pode conter erros gramaticais ou imprecisões. O nosso objetivo é que estes conteúdos lhe sejam úteis. Pode informar-nos se as informações foram úteis no final desta página? Eis o artigo em inglês para referência.

No Excel 2013 ou posterior, pode criar modelos de dados que contenham milhões de linhas e, em seguida, efetuar análise de dados relativamente a estes modelos. Modelos de dados podem ser criados com ou sem o suplemento PowerPivot para suportar qualquer número de tabelas dinâmicas, gráficos e visualizações do Power View no mesmo livro.

Nota: Este artigo descreve os modelos de dados no Excel 2013. No entanto, os mesmos modelos de dados e funcionalidades do Power Pivot introduzidas no Excel 2013 também são aplicáveis ao Excel 2016. Existem, efetivamente, poucas diferenças entre estas versões do Excel.

Embora possa facilmente criar modelos de dados enorme no Excel, vários motivos não existem para. Em primeiro lugar, grandes modelos que contêm multitudes de tabelas e colunas estão adore para a maior parte das análises e certifique-se para uma lista de campos complexo. Utilizam os modelos de segundo, grandes memória útil, negativa afetar outras aplicações e relatórios que partilhem os mesmos recursos de sistema. Por fim, no Office 365, SharePoint Online e Excel Web App limitam o tamanho de um ficheiro do Excel para 10 MB. Para os modelos de dados do livro que contêm milhões de linhas, encontrará que o limite de 10 MB muito rapidamente. Consulte o artigo limites e especificação do modelo de dados.

Neste artigo irá aprender a criar um modelo compacto que é mais fácil de utilizar e que utiliza menos memória. Gastar algum tempo a aprender sobre modelos eficazes será vantajoso para os modelos que criar e utilizar no futuro, quer os visualize no Excel 2013, no SharePoint Online do Office 365, num Servidor do Office Web Apps ou no SharePoint 2013.

Considere também a executar o otimizador de tamanho do livro. -Analisa o livro do Excel e se possível, comprime-lo ainda mais. Transferir o otimizador de tamanho do livro.

Neste artigo

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

Não há nada melhor uma coluna inexistente para uma baixa utilização de memória

Dois exemplos de colunas que deverão ser sempre excluídas

Como excluir colunas desnecessárias

E para filtrar apenas as linhas necessárias?

O que acontece se a coluna; for necessária Podemos reduzir o espaço que ocupa?

Modificar colunas data/hora

Modificar a consulta SQL

Utilizar o DAX medidas em vez de colunas calculadas

Quais as 2 colunas que devo manter?

Conclusão

Ligações relacionadas

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

Os modelos de dados do Excel utilizam o motor de análise na memória para armazenar dados na memória. Este motor implementa técnicas de compressão avançadas para reduzir requisitos de armazenamento, compactando um conjunto de resultados até ter uma fração do tamanho original.

Em média, um modelo de dados pode ser 7 a 10 vezes mais pequeno que os dados originais. Por exemplo, se importar 7 MB de dados de uma base de dados do SQL Server, o modelo de dados no Excel poderá ter 1 MB ou menos. O grau de compressão atingido varia principalmente de acordo com o número de valores exclusivos em cada coluna. Quantos mais valores exclusivos houver, mais memória é necessária para os armazenar.

Porque é que mencionámos compressão e valores exclusivos? Porque para criar um modelo eficiente que minimize a quantidade de memória utilizada é necessário maximizar a compressão e a forma mais fácil de o fazer é eliminar colunas desnecessárias, especialmente se incluírem um grande número de valores exclusivos.

Nota: Pode haver uma grande diferença nos requisitos de armazenamento de colunas individuais. Em alguns casos, é melhor ter várias colunas com menos valores exclusivos em vez de apenas uma coluna com muitos. A secção sobre otimizações de Data/Hora explica detalhadamente esta técnica.

Não há nada melhor que uma coluna inexistente para uma baixa utilização de memória

A coluna que utiliza a memória de forma mais eficiente é aquela que nunca foi importada. Se pretende criar um modelo eficiente, pondere que colunas contribuem para a análise que quer realizar. Se não contribuem ou não tiver a certeza, exclua-as. Poderá sempre adicionar novas colunas posteriormente, se precisar.

Dois exemplos de colunas que deverão ser sempre excluídas

O primeiro exemplo inclui dados de um armazém de dados. Num armazém de dados é frequente encontrar artefactos de processos ETL que carregam e atualizam dados no armazém. São criadas colunas com os nomes "data de criação", "data de atualização" e "execução de ETL" quando os dados são carregados. Nenhuma destas colunas são necessárias no modelo e deve anular a respetiva seleção ao importar dados.

O segundo exemplo passa por omitir a coluna de chave primária ao importar uma tabela de factos.

A maioria das tabelas, incluindo tabelas de factos, possuem chaves primárias. Na maioria dos casos como, por exemplo, para as tabelas que contêm dados de clientes, funcionários ou vendas, utilizará a chave primária da tabela para criar relações no modelo.

O mesmo não acontece com as tabelas de factos. Numa tabela de factos a chave primária é utilizada para identificar exclusivamente cada linha. Apesar de ser necessário para efeitos de normalização, a chave primária não é tão útil num modelo de dados onde pretende estas colunas apenas para analisar ou estabelecer relações de tabelas. Assim, ao importar uma tabela de factos, não inclua esta chave primária. As chaves primárias numa tabela de factos consumem muito espaço no modelo sem nenhuma vantagem, visto não poderem ser utilizadas para criar relações.

Nota: Em armazéns de dados e bases de dados multidimensionais, as tabelas de grandes dimensões são compostas maioritariamente por dados numéricos e são frequentemente denominadas "tabelas de factos". As tabelas de factos geralmente incluem dados de desempenho de negócios ou de transações, tais como dados de pontos de vendas e de custos, agregados e alinhados em unidades organizacionais, produtos, segmentos de mercado, regiões geográficas, etc. Todas as colunas de uma tabela de factos que contenham dados de negócio ou que possam ser utilizadas para comparar dados armazenados noutras tabelas devem ser incluídas no modelo para suportar a análise de dados. A coluna a excluir é a coluna da chave primária da tabela de factos, que contém valores exclusivos existentes apenas na tabela de factos. Devido ao tamanho colossal das tabelas de factos, algumas das melhores otimizações de modelos dados provêm da exclusão de linhas ou colunas das tabelas de factos.

Como excluir colunas desnecessárias

Modelos de eficientes contenham apenas as colunas que realmente terá no seu livro. Se pretender para controlar as colunas que estã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.

Quando inicia o Assistente de Importação de Tabelas, selecionará as tabelas a importar.

Assistente de Importação de Tabelas no suplemento PowerPivot

Para cada tabela, pode clicar no botão Pré-visualizar e Filtrar e selecionar as partes da tabela que precisa mesmo. Recomendamos desmarcar todas as colunas e, em seguida, selecionar as colunas que pretende, caso sejam mesmo necessárias para a análise.

Painel de Pré-visualização no Assistente de Importação de Tabelas

E para filtrar apenas as linhas necessárias?

A maioria das tabelas em bases de dados de empresas e armazéns de dados contêm dados históricos acumulados ao longo de extensos períodos de tempo. Também pode chegar à conclusão que as tabelas que lhe interessam contêm informações de áreas de negócio não necessárias para a análise em questão.

Com o Assistente de Importação de Tabelas, pode filtrar e excluir dados históricos ou não relacionados e poupar espaço no modelo. Na imagem seguinte, foi utilizado um filtro de dados para obter apenas linhas com dados do ano corrente, excluindo dados históricos que não serão precisos.

Painel Filtro no Assistente de Importação de Tabelas

E se a coluna for necessária, podemos reduzir o espaço que ocupa?

Existem algumas técnicas adicionais que pode aplicar para tornar uma coluna mais fácil de comprimir. Lembre-se 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

Na maioria dos casos, as colunas Data/Hora ocupam muito espaço. No entanto, existem algumas maneiras de reduzir os requisitos de armazenamento para este tipo de dados. As técnicas variam conforme a utilização da coluna e a sua prática na compilação de consultas de SQL.

As colunas Data/Hora incluem uma parte de data e uma parte de horas. Ao ponderar se precisa de uma coluna, questione o seguinte acerca de uma coluna Data/Hora:

  • Preciso da parte das horas?

  • Preciso da parte das horas ao nível das horas, dos minutos, dos segundos ou dos milissegundos?

  • Tenho várias colunas Data/Hora porque quero calcular a diferença entre as mesmas ou apenas para agregar os dados por ano, mês, trimestre, etc.?

A sua resposta a cada pergunta determina como deverá lidar com a coluna Data/Hora.

Todas estas soluções necessitam uma consulta SQL modificada. Para facilitar a modificação da consulta, deverá filtrar e excluir pelo menos uma coluna em cada tabela. Ao filtrar uma coluna, altera a estrutura da mesma de um formato abreviado (SELECT*) para uma declaração SELECT que inclui nomes de colunas qualificados, que são mais fáceis de alterar.

Analisemos as consultas 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 de cada tabela.

Friso na janela do PowerPivot a mostrar o comando Propriedades da Tabela

A partir das Propriedades da Tabela, selecione Editor de Consultas.

Editor de Consultas aberto a partir do diálogo Propriedades da Tabela

O Editor de Consultas mostra a consulta SQL usada para preencher a tabela. Se filtrou colunas durante a importação, a sua consulta incluirá nomes de colunas qualificados:

Consulta SQL utilizada para obter os dados

Se importou uma tabela por inteiro, sem anular a seleção de colunas ou aplicar filtros, verá a consulta como "Select* from", que será mais difícil de modificar:

Consulta SQL com a sintaxe predefinida mais curta

Modificar a consulta SQL

Agora que sabe como localizar a consulta, pode modificá-la para reduzir o tamanho do modelo.

  1. Para colunas com dados decimais ou de moeda, utilize esta sintaxe para eliminar os decimais, caso não precise deles:

    “SELECT ROUND([Nome_da_coluna_decimal],0)… .”

    Se precisar dos cêntimos mas não das frações dos cêntimos, substitua o 0 por 2. Se utilizar números negativos, pode arredondar para unidades, décimas, centenas, etc.

  2. Se tem uma coluna Data/Hora chamada dbo.Tabelagrande.[Data Hora] e não precisar da parte Hora, utilize a sintaxe seguinte para eliminar a hora:

    “SELECT CAST (dbo.Tabelagrande.[Data hora] as data) AS [Data hora]) “

  3. Se tem uma coluna Data/Hora chamada dbo.Tabelagrande.[Data Hora] e precisar de ambas as partes Data e Hora, utilize várias colunas na consulta SQL em vez da coluna Data/Hora:

    “SELECT CAST (dbo.Tabelagrande.[Data Hora] as data) AS [Data Hora],

    datepart(hh, dbo.Tabelagrande.[Data Hora]) as [Data Hora Horas],

    datepart(mi, dbo.Tabelagrande.[Data Hora]) as [Data Hora Minutos],

    datepart(ss, dbo.Tabelagrande.[Data Hora]) as [Data Hora Segundos],

    datepart(ms, dbo.Bigtable.[Data Hora]) as [Data Hora Milissegundos]”

    Utilize o número de colunas que precisar para armazenar cada parte em colunas separadas.

  4. Se precisar de horas e de minutos e preferir juntá-los numa só coluna, pode usar esta sintaxe:

    Timefromparts(datepart(hh, dbo.Tabelagrande.[Data Hora]), datepart(mm, dbo.Tabelagrande.[Data Hora])) as [Data Hora HoraMinuto]

  5. Se tem duas colunas Data/Hora como, por exemplo, [Hora de Início] e [Hora de Fim] e precisar apenas da diferença de tempo entre as duas em segundos com uma coluna chamada [Duração], remova ambas as colunas da lista e adicione:

    “datediff(ss,[Data Início],[Data Fim]) como [Duração]”

    Se utilizar a palavra-chave ms em vez de ss, obterá a duração em milissegundos

Utilizar medidas calculadas do DAX em vez de colunas

Se já trabalhou com a linguagem de expressões DAX, poderá já ter conhecimento que as colunas calculadas são utilizadas para criar novas colunas baseadas noutra coluna do modelo, enquanto que as medidas calculadas são definidas no modelo, mas apenas analisadas quando utilizadas numa Tabela Dinâmica ou noutro relatório.

Uma técnica para poupar memória é substituir colunas normais ou calculadas por medidas calculadas. O exemplo mais comum é Preço Unitário, Quantidade e Total. Se tem as três, pode poupar espaço mantendo apenas duas e calcular a terceira utilizando DAX.

Quais as 2 colunas que devo manter?

No exemplo anterior, mantenha Quantidade e Preço Unitário. Estas deverão ter menos valores que o Total. Para calcular o Total, adicione uma medida calculada como, por exemplo:

“TotalDeVendas:=sumx(‘Tabela de Vendas’,’Tabela de Vendas’[Preço Unitário]*’Tabela de Vendas’[Quantidade])

As colunas calculadas são parecidas com colunas normais na medida em que ambas ocupam espaço no modelo. As medidas calculadas são calculadas na altura e não ocupam espaço.

Conclusão

Neste artigo, abordámos várias medidas que o podem ajudar a criar um modelo de dados com consumo de memória otimizado. Para reduzir o tamanho do ficheiro e requisitos de memória de um modelo de dados é necessário reduzir o número total de colunas e de linhas e o número de valores exclusivos que aparecem em cada coluna. Eis algumas das técnicas que mencionámos:

  • Remover colunas é a melhor maneira de poupar espaço. Decida que colunas precisa mesmo.

  • 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 com o Assistente de Importação de Tabelas.

  • Regra geral, dividir uma coluna em várias partes distintas é uma boa forma de reduzir o número de valores exclusivos numa coluna. Cada uma das partes tem poucos valores exclusivos e o total combinado é mais pequeno do que a coluna unificada original.

  • Na maioria das situações, também precisará das diferentes partes para utilizar como segmentações de dados nos relatórios. Quando aplicável, pode criar hierarquias a partir de partes como Horas, Minutos e Segundos.

  • Muitas vezes as colunas contêm mais informações do que as que realmente precisa. Por exemplo, uma coluna armazena decimais, mas decidiu ocultá-los através da aplicação de formatação específica. O arredondamento pode reduzir eficazmente o tamanho de uma coluna numérica.

Agora que tenha feito, pode reduzir o tamanho do livro, considere também a executar o otimizador de tamanho do livro. -Analisa o livro do Excel e se possível, comprime-lo ainda mais. Transferir o otimizador de tamanho do livro.

Ligações relacionadas

Especificação e limites do Modelo de Dados

Transferir o Otimizador de tamanho do livro

Power Pivot: análise e modelação de dados avançadas no Excel

Aumente os seus conhecimentos do Office
Explore as formações
Seja o primeiro a obter novas funcionalidades
Adira ao Office Insider

As informações foram úteis?

Obrigado pelos seus comentários!

Obrigado pelo seu feedback! Parece que poderá ser benéfico reencaminhá-lo para um dos nossos agentes de suporte do Office.

×