Criar um modelo de dados de memória eficiente com o Excel e o suplemento Power pivot

No Excel 2013 ou posterior, pode criar modelos de dados que contenham milhões de linhas e, em seguida, efetuar uma análise de dados eficiente contra estes modelos. Os modelos de dados podem ser criados com ou sem o suplemento do PowerPivot para suportar quaisquer números 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.

Apesar de poder criar facilmente modelos de dados enormes no Excel, existem vários motivos para não o fazer. Em primeiro lugar, os modelos grandes que contêm inúmeros índices e colunas são um exagero para a maioria das análises e tornam-se uma lista de campos complicada. Segundo, os modelos grandes utilizam memória valiosa, afetando negativamente outras aplicações e relatórios que partilham os mesmos recursos do sistema. Por fim, no Office 365, o SharePoint Online e o Excel Web App limitam o tamanho de um ficheiro do Excel a 10 MB. Para os modelos de dados de livros que contenham milhões de linhas, irá encontrar um limite de 10 MB muito rapidamente. Consulte as especificações e limites do modelo de dados.

Neste artigo, irá aprender a criar um modelo rigidamente construído que é mais fácil de trabalhar com e utiliza menos memória. Tomar o máximo tempo para aprender as melhores práticas de estrutura de modelos eficientes irá pagar desagregar o caminho de qualquer modelo que criar e utilizar, quer esteja a vê-lo no Excel 2013, Office 365 SharePoint Online, num servidor Office Web Apps ou no SharePoint 2013.

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

Neste artigo

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

Nada supera uma coluna não existente para utilização baixa de memória

Dois exemplos de colunas que devem ser sempre eliminadas

Como eliminar colunas desnecessárias

O que devo filtrar apenas as linhas necessárias?

E se precisar de a coluna; Ainda podemos reduzir o custo do espaço?

Modificar colunas DateTime

Modificar a consulta SQL

Utilizar medidas calculadas do DAX em vez de colunas

Que duas colunas deve manter?

Conclusão

Ligações relacionadas

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

Os modelos de dados no Excel utilizam o mecanismo de análise de memória para armazenar dados na memória. O motor implementa poderosas técnicas de compressão para reduzir os requisitos de armazenamento, reduzindo um conjunto de resultados até que seja uma fração do tamanho original.

Em média, pode esperar que um modelo de dados seja 7 a 10 vezes mais pequeno do que os mesmos dados no seu ponto de origem. Por exemplo, se estiver a importar 7 MB de dados de uma base de dados do SQL Server, o modelo de dados no Excel pode ser facilmente 1 MB ou menos. O grau de compactação obtido realmente depende principalmente do número de valores exclusivos em cada coluna. Quanto mais valores exclusivos, mais memória é necessária para os armazenar.

Por que motivo estamos a falar sobre a compactação e valores exclusivos? Como criar um modelo eficiente que minimize a utilização da memória é tudo sobre a maximização da compactação e a forma mais fácil de o fazer é eliminar todas as colunas que não necessita realmente, principalmente se essas colunas incluírem um grande número de valores exclusivos.

Nota:  As diferenças em 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, em vez de uma coluna com um grande número de valores exclusivos. A secção sobre as otimizações DateTime aborda esta técnica em detalhe.

Nada supera uma coluna não existente para utilização baixa de memória

A coluna mais eficiente de memória é aquela que nunca importou no primeiro local. Se pretender criar um modelo eficiente, consulte cada coluna e pergunte-lhe se contribui para a análise que pretende efetuar. Se não tiver a certeza de que não tem a certeza, deixe-a. Pode sempre adicionar novas colunas mais tarde, se precisar delas.

Dois exemplos de colunas que devem ser sempre eliminadas

O primeiro exemplo relaciona-se com os dados originados a partir de um armazém de dados. Num armazém de dados, é comum encontrar artefatos de processos de ETL que carregam e atualizam dados no armazém. As 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 deve ser desmarcada quando importar 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, tais como as que contêm clientes, funcionários ou dados de vendas, pretende que a chave primária da tabela o possa utilizar para criar relações no modelo.

As tabelas de fatos são diferentes. Numa tabela de fatos, 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 onde só pretende as colunas utilizadas para análise ou para estabelecer relações de tabela. Por este motivo, ao importar a partir de uma tabela de fatos, não inclua a sua chave primária. As chaves primárias numa tabela de fatos ocupam enormes quantidades de espaço no modelo, mas não fornecem nenhum benefício, uma vez que não podem ser utilizadas para criar relações.

Nota:  Em data warehouses e bases de dados multidimensionais, as tabelas grandes que consistem em mais dados numéricos são muitas vezes conhecidas como "tabelas de fatos". Normalmente, as tabelas de fatos incluem dados de desempenho ou transacções para empresas, tais como os pontos de dados de vendas e custos agregados e alinhados a unidades organizacionais, produtos, segmentos de mercado, regiões geográficas, entre outros. Todas as colunas numa tabela de fatos que contenham dados de negócio ou que podem ser utilizadas para fazer referência cruzada de 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 fatos, que consiste em valores exclusivos que existem apenas na tabela de fatos e em qualquer outro lugar. Uma vez que as tabelas de fatos são tão enormes, alguns dos maiores ganhos na eficiência do modelo são derivados da exclusão de linhas ou colunas de tabelas de fatos.

Como eliminar colunas desnecessárias

Os modelos eficientes contêm apenas as colunas que serão realmente necessárias no seu livro. Se pretender controlar as colunas 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, seleciona as tabelas que pretende importar.

Assistente de Importação de Tabelas no suplemento PowerPivot

Para cada tabela, pode clicar no botão pré-visualizar & filtro e selecionar as partes da tabela de que precisa. Recomendamos que desmarque primeiro todas as colunas e, em seguida, continue a verificar as colunas que pretende, depois de considerar se são necessárias para a análise.

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

O que devo filtrar apenas as linhas necessárias?

Muitas tabelas em bases de dados empresariais e armazéns de dados contêm dados históricos acumulados durante longos períodos de tempo. Além disso, poderá descobrir que as tabelas em que está interessado contêm informações para as áreas da empresa que não são necessárias para a sua análise específica.

Com o assistente de importação de tabelas, pode filtrar dados históricos ou não relacionados e, por isso, poupar muito espaço no modelo. Na seguinte imagem, é utilizado um filtro de data para obter apenas linhas que contenham dados do ano atual, excluindo dados históricos que não serão necessários.

Painel Filtro no Assistente de Importação de Tabelas

E se precisar de a coluna; Ainda podemos reduzir o custo do espaço?

Existem algumas técnicas adicionais que pode aplicar para tornar uma coluna um candidato melhor para a 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á saber como algumas colunas podem ser modificadas para reduzir o número de valores exclusivos.

Modificar colunas DateTime

Em muitos casos, as colunas DateTime ocupam muito espaço. Felizmente, existem várias formas de reduzir os requisitos de armazenamento para este tipo de dados. As técnicas irão variar consoante a forma como utiliza a coluna e o seu nível de conforto na criação de consultas SQL.

As colunas DateTime incluem uma parte de data e uma hora. Quando se perguntar se necessita de uma coluna, faça a mesma pergunta várias vezes para uma coluna data/hora:

  • Preciso da parte de tempo?

  • Preciso da parte do tempo no nível de horas? tempo? Seg? milissegundos?

  • Tenho múltiplas colunas DateTime porque quero calcular a diferença entre as mesmas ou apenas para agregar os dados por ano, mês, trimestre e assim sucessivamente.

A forma como responde a cada uma destas perguntas determina as suas opções para lidar com a coluna DateTime.

Todas estas soluções necessitam de modificações de uma consulta SQL. Para facilitar a modificação de consultas, deve filtrar pelo menos uma coluna em todas as tabelas. Ao filtrar uma coluna, pode alterar a construção de consultas a partir de um formato abreviado (selecionar *) para uma instrução SELECT que inclua nomes de colunas completamente qualificados, o que é muito mais fácil 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 de cada tabela.

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

Em 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 utilizada para preencher a tabela. Se tiver filtrado qualquer coluna durante a importação, a sua consulta incluirá nomes de colunas totalmente qualificados:

Consulta SQL utilizada para obter os dados

Por outro lado, se tiver importado uma tabela completamente, sem ter de selecionar qualquer coluna ou aplicar um filtro, verá a consulta como "selecionar * de", que será mais difícil de modificar:

Consulta SQL com a sintaxe predefinida mais curta

Modificar a consulta SQL

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

  1. Para colunas que contenham dados de moeda ou de casas decimais, se não necessitar de casas decimais, utilize esta sintaxe para eliminar os decimais:

    "Selecione ARREd ([Decimal_column_name]; 0)... .”

    Se precisar de as centavos, mas não as frações de centavos, substitua o valor 0 por 2. Se utilizar números negativos, pode arredondar para unidades, dezenas, centenas, etc.

  2. Se tiver uma coluna DateTime com o nome dbo. Bigtable. [Data/hora] e não precisa da parte do tempo, utilize a sintaxe para eliminar o tempo:

    "Selecione CAST (dbo. Bigtable. [Data hora] como Data) como [data/hora]) "

  3. Se tiver uma coluna DateTime com o nome dbo. Bigtable. [Data hora] e precisa de ambas as partes de data e hora, utilize várias colunas na consulta SQL em vez de uma única coluna datetime:

    "Selecione CAST (dbo. Bigtable. [Data hora] como Data) como [data/hora],

    PartData (hh, dbo. Bigtable. [Data hora]) as [horas de data/hora],

    PartData (mi, dbo. Bigtable. [Data hora]) as [data/hora minutos]

    PartData (SS, dbo. Bigtable. [Data hora]) as [data hora segundos],

    PartData (MS, dbo. Bigtable. [Data hora]) as [data/hora em milissegundos] "

    Utilize quantas colunas forem necessárias para armazenar cada parte em colunas separadas.

  4. Se precisar de horas e minutos e se pretender as mesmas como uma coluna de tempo, pode utilizar a sintaxe:

    Partesdehora (DATEPART (hh; dbo. Bigtable. [Data hora]), DATEPART (mm, dbo. Bigtable. [Data hora])) as [data hora HourMinute]

  5. Se tiver duas colunas DateTime, como [hora de início] e [hora de fim], e o que realmente precisa é a diferença de tempo entre as mesmas em segundos como uma coluna denominada [duração], remova ambas as colunas da lista e adicione:

    "DifData (SS; [data de início], [data de fim]) como [duração]"

    Se utilizar a palavra-chave MS em vez de SS, irá obter a duração em milissegundos

Utilizar medidas calculadas do DAX em vez de colunas

Se já trabalhou com a linguagem de expressão DAX anteriormente, poderá já ter conhecimento de que as colunas calculadas são utilizadas para derivar novas colunas com base em alguma outra coluna no modelo, enquanto as medidas calculadas são definidas uma vez no modelo, mas são avaliadas apenas quando utilizada num Tabela dinâmica ou outro relatório.

Uma técnica de economia de memória é substituir as colunas normal ou calculada por medidas calculadas. O exemplo clássico é o preço unitário, a quantidade e o total. Se tiver as três, pode poupar espaço mantendo apenas dois e calculando o terceiro com o DAX.

Que duas colunas deve manter?

No exemplo acima, mantenha a quantidade e o preço unitário. Estas duas têm menos valores do que o total. Para calcular o total, adicione uma medida calculada como:

"Totalvendas: = SUMX (" tabela de vendas "," tabela de vendas "[preço unitário] *" tabela de vendas "[quantidade])"

As colunas calculadas são como colunas normais, por isso, ocupam espaço no modelo. Por outro lado, as medidas calculadas são calculadas imediatamente e não têm espaço.

Conclusão

Neste artigo, falamos sobre várias abordagens que podem ajudá-lo a criar um modelo mais eficiente 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. Eis algumas técnicas que discutimos:

  • Remover colunas é do curso a melhor forma de poupar espaço. Decida as colunas de que precisa.

  • Por vezes, pode remover uma coluna e substituí-la por uma medida calculada na tabela.

  • Poderá não precisar de todas as linhas de uma tabela. Pode filtrar linhas no assistente de importação de tabelas.

  • Em geral, separar uma única coluna em várias 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 as partes distintas para utilizar como segmentações de problemas 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 casas decimais, mas que aplicou formatação para ocultar todos os decimais. O arredondamento pode ser muito eficaz para reduzir o tamanho de uma coluna numérica.

Agora que já concluiu o que pode para reduzir o tamanho do seu livro, considere também executar o otimizador de tamanho do livro. Analisa o seu livro do Excel e, se possível, comprime-o ainda mais. Transferir o otimizador de tamanho do livro.

Ligações relacionadas

Especificação e limites do Modelo de Dados

Transferência do 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.

×