No Excel 2013 ou posterior, 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 Power Pivot para suportar qualquer número de visualizações de Tabelas Dinâmicas, gráficos e 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. Existe efetivamente pouca diferença entre estas versões do Excel.
Embora possa facilmente criar modelos de dados enormes no Excel, existem várias razões para não o fazer. Em primeiro lugar, os modelos grandes que contêm inúmeras tabelas e colunas são exagerados para a maioria das análises e tornam uma Lista de Campos complicada. Em segundo lugar, 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 Microsoft 365, o SharePoint Online e o Excel Web App limitam o tamanho de um ficheiro do Excel a 10 MB. Para modelos de dados de livros que contêm milhões de linhas, irá encontrar rapidamente o limite de 10 MB. Veja Especificação e limites do Modelo de Dados.
Neste artigo, irá aprender a criar um modelo fortemente construído com o qual é mais fácil trabalhar e utiliza menos memória. Tirar tempo para aprender as melhores práticas em design de modelos eficientes irá dar frutos em qualquer modelo que crie e utilize, quer esteja a vê-lo no Excel 2013, Microsoft 365 SharePoint Online, num Office Web Apps Server 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. Transfira o Otimizador de Tamanho do Livro.
Neste artigo
Proporções de compressão e o motor de análise dentro da memória
Os modelos de dados no Excel utilizam o motor de análise na memória para armazenar dados na memória. O motor implementa técnicas de compressão avançadas para reduzir os requisitos de armazenamento, reduzindo um conjunto de resultados até ser uma fração do tamanho original.
Em média, pode esperar que um modelo de dados seja 7 a 10 vezes menor do que os mesmos dados no ponto de origem. Por exemplo, se estiver a importar 7 MB de dados de uma base de dados SQL Server, o modelo de dados no Excel pode facilmente ter 1 MB ou menos. O grau de compressão realmente alcançado depende principalmente do número de valores exclusivos em cada coluna. Quanto mais valores exclusivos forem, mais memória é necessária para armazená-los.
Por que estamos a falar de compressão e valores exclusivos? Uma vez que a criação de um modelo eficiente que minimiza a utilização da memória tem tudo a ver com a maximização da compressão e a forma mais fácil de o fazer é eliminar as colunas de que realmente não precisa, especialmente se essas colunas incluírem um grande número de valores exclusivos.
Nota: As diferenças nos 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 número elevado de valores exclusivos. A secção sobre otimizações datetime abrange esta técnica em detalhe.
Nada supera uma coluna inexistente para utilização de memória baixa
A coluna mais eficiente em termos de memória é aquela que nunca importou. Se quiser criar um modelo eficiente, observe cada coluna e pergunte-se se contribui para a análise que pretende efetuar. Se não tiver ou não tiver a certeza, deixe de fora. Pode sempre adicionar novas colunas mais tarde, se precisar delas.
Dois exemplos de colunas que devem ser sempre excluídas
O primeiro exemplo está relacionado com dados provenientes de um armazém de dados. Num armazém de dados, é comum encontrar artefactos de processos ETL que carregam e atualizam dados no armazém. As colunas como "criar data", "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 desselecionada quando importar dados.
O segundo exemplo envolve omitir a coluna de chave primária ao importar uma tabela de factos.
Muitas tabelas, incluindo tabelas de factos, têm chaves primárias. Para a maioria das tabelas, como as que contêm dados de clientes, colaboradores ou vendas, irá querer a chave primária da tabela para que possa utilizá-la para criar relações no modelo.
As tabelas de factos são diferentes. Numa tabela de factos, a chave primária é utilizada para identificar exclusivamente cada linha. Embora seja necessário para fins de normalização, é menos útil num modelo de dados em que pretende utilizar apenas essas colunas para análise ou estabelecer relações de tabela. Por este motivo, ao importar a partir de uma tabela de factos, não inclua a chave primária. As chaves primárias numa tabela de factos consomem enormes quantidades de espaço no modelo, mas não proporcionam qualquer benefício, uma vez que não podem ser utilizadas para criar relações.
Nota: 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
Os modelos eficientes contêm apenas as colunas de que irá realmente precisar no seu livro. Se quiser controlar que colunas 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 iniciar o Assistente de importação de tabelas, selecione as tabelas a importar.
Para cada tabela, pode clicar no botão Pré-visualizar & Filtro e selecionar as partes da tabela de que realmente precisa. Recomendamos que desmarque primeiro todas as colunas e, em seguida, verifique as colunas que pretende, depois de considerar se são necessárias para a análise.
E quanto à filtragem apenas das 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 áreas da empresa que não são necessárias para a sua análise específica.
Ao utilizar o assistente de Importação de Tabelas, pode filtrar dados históricos ou não relacionados e, assim, poupar muito espaço no modelo. Na imagem seguinte, é utilizado um filtro de data para obter apenas linhas que contenham dados para o ano atual, excluindo dados históricos que não serão necessários.
E se precisarmos da coluna; ainda podemos reduzir o custo do espaço?
Existem algumas técnicas adicionais que pode aplicar para tornar uma coluna mais adequada à 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á aprender 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 variam consoante a forma como utiliza a coluna e o 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 precisa de uma coluna, faça a mesma pergunta várias vezes para uma coluna Datetime:
-
Preciso da parte do tempo?
-
Preciso da parte do tempo ao nível das horas? , minutos? , Segundos? , milissegundos?
-
Tenho múltiplas colunas Datetime porque quero calcular a diferença entre elas ou apenas agregar os dados por ano, mês, trimestre, etc.
A forma como responde a cada uma destas perguntas determina as suas opções para lidar com a coluna Datetime.
Todas estas soluções requerem a modificação de uma consulta SQL. Para facilitar a modificação da consulta, deve filtrar pelo menos uma coluna em cada tabela. Ao filtrar uma coluna, altera a construção de consultas de um formato abreviado (SELECT *) para uma instrução SELECT que inclui nomes de coluna completamente qualificados, que são muito mais fáceis de modificar.
Vamos ver as consultas que são criadas para si. Na caixa de diálogo Propriedades da Tabela, pode mudar para o Editor de consultas e ver a consulta SQL atual para cada tabela.
Em Propriedades da Tabela, selecione Editor do Power Query.
O Editor do Power Query mostra a consulta SQL utilizada para preencher a tabela. Se tiver filtrado qualquer coluna durante a importação, a consulta inclui nomes de coluna completamente qualificados:
|
Por outro lado, se tiver importado uma tabela na sua totalidade, sem desmarcar qualquer coluna ou aplicar qualquer filtro, verá a consulta como "Selecionar * de ", o que será mais difícil de modificar:
|
Modificar a consulta SQL
Agora que sabe como localizar a consulta, pode modificá-la para reduzir ainda mais o tamanho do seu modelo.
-
Para colunas que contenham dados decimal ou moeda, se não precisar dos decimais, utilize esta sintaxe para eliminar os decimais:
"SELECT ROUND([Decimal_column_name],0)... .”
Se precisar dos cêntimos, mas não de frações de cêntimos, substitua o 0 por 2. Se utilizar números negativos, pode arredondar para unidades, dezenas, centenas, etc.
-
Se tiver uma coluna Datetime denominada dbo. Grande, grande. [Data/Hora] e não precisa da parte Hora, utilize a sintaxe para eliminar a hora:
"SELECT CAST (dbo. Grande, grande. [Date time] as date) AS [Date time]) "
-
Se tiver uma coluna Datetime denominada dbo. Grande, grande. [Data/Hora] e precisa das partes Data e Hora, utilize múltiplas colunas na consulta SQL em vez da única coluna Datetime:
"SELECT CAST (dbo. Grande, grande. [Date Time] as date ) AS [Date Time],
datepart(hh, dbo. Grande, grande. [Data/Hora]) como [Date Time Hours],
datepart(mi, dbo. Grande, grande. [Data/Hora]) como [Date Time Minutes],
datepart(ss, dbo. Grande, grande. [Data/Hora]) como [Date Time Seconds],
datepart(ms, dbo. Grande, grande. [Data/Hora]) as [Date Time Milliseconds]"
Utilize o número de colunas que precisar para armazenar cada parte em colunas separadas.
-
Se precisar de horas e minutos e preferir que fiquem juntos como uma coluna de tempo, pode utilizar a sintaxe :
Timefromparts(datepart(hh, dbo. Grande, grande. [Date Time]), datepart(mm, dbo. Grande, grande. [Data/Hora])) como [Date Time HourMinute]
-
Se tiver duas colunas datetime, como [Hora de Início] e [Hora de Fim], e o que realmente precisa é da diferença de tempo entre elas em segundos como uma coluna chamada [Duração], remova ambas as colunas da lista e adicione:
"datediff(ss,[Data de Início],[Data de Fim]) como [Duração]"
Se utilizar a palavra-chave ms em vez de ss, obterá a duração em milissegundos
Utilizar medidas calculadas DAX em vez de colunas
Se já trabalhou com a linguagem de expressão DAX anteriormente, poderá já saber que as colunas calculadas são utilizadas para derivar novas colunas com base noutra coluna no modelo, enquanto as medidas calculadas são definidas uma vez no modelo, mas avaliadas apenas quando utilizadas numa Tabela Dinâmica ou noutro relatório.
Uma técnica de poupança de memória é substituir colunas regulares ou calculadas por medidas calculadas. O exemplo clássico é Preço Unitário, Quantidade e Total. Se tiver os três, pode poupar espaço mantendo apenas dois e calculando o terceiro com DAX.
Que 2 colunas deve manter?
No exemplo acima, mantenha Quantidade e Preço Unitário. Estes dois têm menos valores do que o Total. Para calcular o Total, adicione uma medida calculada como:
"TotalSales:=sumx('Sales Table','Sales Table'[Unit Price]*'Sales Table'[Quantity])"
As colunas calculadas são como colunas normais na medida em que ambos ocupam espaço no modelo. Por outro lado, as medidas calculadas são calculadas de imediato e não ocupam espaço.
Conclusão
Neste artigo, falámos sobre várias abordagens que podem ajudá-lo a criar um modelo mais eficiente em termos 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 geral de colunas e linhas e o número de valores exclusivos apresentados em cada coluna. Seguem-se algumas técnicas que abordámos:
-
Remover colunas é, naturalmente, a melhor forma de poupar espaço. Decida quais as colunas de que realmente precisa.
-
Por vezes, pode remover uma coluna e substituí-la por uma medida calculada na tabela.
-
Pode não precisar de todas as linhas numa tabela. Pode filtrar linhas no Assistente de Importação de Tabelas.
-
Em geral, dividir uma única coluna em múltiplas 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 das partes distintas para utilizar como segmentações de dados nos seus relatórios. Quando apropriado, pode criar hierarquias a partir de partes como Horas, Minutos e Segundos.
-
Muitas vezes, as colunas também contêm mais informações do que as necessárias. Por exemplo, suponha que uma coluna armazena decimais, mas aplicou formatação para ocultar todos os decimais. O arredondamento pode ser muito eficaz na redução do tamanho de uma coluna numérica.
Agora que fez 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. Transfira o Otimizador de Tamanho do Livro.
Ligações relacionadas
Especificação e limites do Modelo de Dados
Otimizador de Tamanho do Livro
Power Pivot: análise e modelação de dados avançadas no Excel
