No Excel 2013 ou posterior, pode criar modelos de dados que contenham milhões de linhas e, em seguida, efetuar poderosas análises de dados com estes modelos. Os modelos de dados podem ser criados com ou sem o Power Pivot do 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. Existem, efetivamente, pouca diferença entre estas versões do Excel.
Embora possa criar facilmente grandes modelos de dados no Excel, existem várias razões para não o fazer. Em primeiro lugar, os modelos grandes que contêm várias tabelas e colunas são sobressalados para a maioria das análises e fazem-no para uma Lista de Campos mais vasta. O segundo, grandes modelos utilizam memória valiosa, afetando negativamente outras aplicações e relatórios que partilham os mesmos recursos de sistema. Por fim, no Microsoft 365, o SharePoint Online e Excel Web App limitam o tamanho de um ficheiro Excel a 10 MB. Nos modelos de dados de livros que contêm milhões de linhas, encontrará rapidamente o limite de 10 MB. Consulte Especificação e limites do Modelo de Dados.
Neste artigo, irá aprender a criar um modelo bem construída que seja mais fácil de trabalhar e que utilize menos memória. Reservar algum tempo para aprender as práticas recomendadas na estruturação de modelos eficientes serve como base para qualquer modelo que crie e utilize, quer esteja a vê-lo no Excel 2013, no 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 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 na memória
Os modelos de Excel utilizam o motor de análise na memória para armazenar os 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é se encontrar numa fração do seu tamanho original.
Em média, pode esperar que um modelo de dados seja 7 a 10 vezes menor 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 compressão atingido depende principalmente do número de valores exclusivos em cada coluna. Quanto mais valores exclusivos for necessário, mais memória será necessária para os armazenar.
Por que motivo estamos a falar de compressão e valores exclusivos? Uma vez que criar um modelo eficiente que minimize a utilização da memória é necessário maximizar a compressão e a forma mais fácil de o fazer é livrar-se de quaisquer colunas de que não precise realmente, 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 grandes. 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 as otimização da Datetime abrange esta técnica detalhadamente.
Nada melhora uma coluna inexistente para pouca utilização da memória
A coluna mais eficiente em memória é aquela que nunca importou. Se quiser criar um modelo eficiente, olhe para cada coluna e questione-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, caso precise delas.
Dois exemplos de colunas que devem ser sempre excluídas
O primeiro exemplo relaciona-se 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 "Executar ETL" são criadas quando os dados são carregados. Nenhuma destas colunas é necessária no modelo e deverá ser desmarcada 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, funcionários ou vendas, irá querer a chave primária da tabela para poder usá-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 cada linha de forma exclusiva. Embora seja necessário para fins de normalização, é menos útil num modelo de dados no qual pretende que apenas as colunas utilizadas para análise ou estabeleça relações de tabelas. Por este motivo, ao importar a partir de uma tabela de factos, não inclua a sua chave primária. As chaves primárias numa tabela de factos consomem grandes quantidades de espaço no modelo, mas não oferecem benefícios, uma vez que não podem ser utilizadas para criar relações.
Nota: Em armazéns de dados e bases de dados multidimensionais, as grandes tabelas que consistem na maioria dos dados numéricos são frequentemente denominadas "tabelas de factos". Normalmente, as tabelas de factos incluem dados de desempenho ou transação empresariais, como pontos de dados de vendas e custos que são 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 edições cruzadas 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 factos, que consiste em valores exclusivos que existem apenas na tabela de factos e em mais nenhum lugar. Uma vez que as tabelas de factos são tão grandes, alguns dos maiores ganhos na eficiência dos modelos 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 realmente precisa 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 add-in do 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 a importar.
Para cada tabela, pode clicar no botão Pré-& Filtro e selecionar as partes da tabela de que realmente necessita. Recomendamos que primeiro des marque todas as colunas e, em seguida, continue para verificar as colunas que pretende, depois de considerar se são necessárias para a análise.
E se filtrar apenas as linhas necessárias?
Muitas tabelas em bases de dados empresariais e armazéns de dados contêm dados históricos acumulados ao longo de longos períodos de tempo. Além disso, poderá descobrir que as tabelas em que está interessado contêm informações para áreas do negócio 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 relativos e poupar muito espaço no modelo. Na imagem seguinte, é utilizado um filtro de data para obter apenas as linhas que contêm dados do ano atual, excluindo dados de histórico 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 para 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 ademais o suficiente. 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 construção de SQL consultas.
As colunas datetime incluem uma parte da data e uma hora. Quando se pergunta se precisa de uma coluna, faça a mesma pergunta múltiplas vezes numa coluna Datetime:
-
Preciso 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 para agregar os dados por ano, mês, trimestre, entre outros.
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 da modificação de uma SQL consulta. Para facilitar a modificação de consultas, deve filtrar pelo menos uma coluna em cada tabela. Ao filtrar uma coluna, irá alterar a construção de consulta de um formato abreviado (SELECT *) para uma inspeção SELECT que inclua 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 consulta de cada tabela.
Em Propriedades da Tabela, selecione Editor de Consultas.
O Editor de Consultas mostra SQL consulta utilizada para preencher a tabela. Se tiver filtrado qualquer coluna durante a importação, a sua consulta incluirá nomes de coluna completamente qualificados:
|
Por outro lado, se tiver importado uma tabela na sua totalidade, sem des selecionar colunas ou aplicar filtros, verá a consulta como "Selecionar * de", o que será mais difícil de modificar:
|
Modificar a consulta SQL consulta
Agora que sabe como encontrar a consulta, pode modificá-la para reduzir ainda mais o tamanho do seu modelo.
-
Para colunas que contenham dados decimais ou de moeda, se não precisar dos decimais, utilize esta sintaxe para eliminar os decimais:
"SELECT ROUND([Decimal_column_name],0)... .”
Se precisar dos cents, mas não das frações de cents, substitua os 0 por 2. Se utilizar números negativos, pode arredondá-los para unidades, dezenas, centenas, etc.
-
Se tiver uma coluna Datetime denominada dbo. Bigtable. [Hora da Data] e não precisar da parte Hora, utilize a sintaxe para eliminar as horas:
"SELECT CAST (dbo. Bigtable. [Hora da data] como data) AS [Hora da data]) "
-
Se tiver uma coluna Datetime denominada dbo. Bigtable. [Hora da Data] e precisar das partes Data e Hora, utilize várias colunas na consulta SQL em vez da única coluna Datetime:
"SELECT CAST (dbo. Bigtable. [Hora da Data] como data ) AS [Hora da Data],
partdata(hh, dbo. Bigtable. [Hora da Data]) como [Horas de Hora de Data],
partdata(mi, dbo. Bigtable. [Hora da Data]) como [Minutos de Hora de Data],
partdata(ss; dbo. Bigtable. [Hora da Data]) como [Segundos da Hora de Data],
partdata(ms, dbo. Bigtable. [Hora da Data]) como [Milissegundos de Hora de Data]"
Utilize o número de colunas que precisar para armazenar cada parte em colunas separadas.
-
Se precisar de horas e minutos e preferir os dois em conjunto como uma coluna de tempo, pode utilizar a sintaxe:
Timefromparts(datepart(hh, dbo. Bigtable. [Hora da Data]), partdata(mm, dbo. Bigtable. [Hora da Data])) como [Hora de DataMinuo]
-
Se tiver duas colunas datetime, como [Hora de Início] e [Hora de Fim] e aquilo de que realmente necessita é a diferença de tempo entre as mesmas 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 do DAX em vez de colunas
Se tiver trabalhado anteriormente com a linguagem de expressão DAX, 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 são avaliadas apenas quando utilizadas numa Tabela Dinâmica ou noutro relatório.
Uma técnica de poupança de memória é substituir colunas normais 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 ao manter apenas dois e calcular o terceiro utilizando DAX.
Quais são as 2 colunas que deve manter?
No exemplo acima, mantenha Quantidade e Preço Unitário. Estes dois valores têm menos valores que o Total. Para calcular o Total, adicione uma medida calculada como:
"TotalSales:=somax('Tabela de Vendas','Tabela de Vendas'[Preço Unitário]*'Tabela de Vendas'[Quantidade])"
As colunas calculadas são como colunas normais na qual ambas as colunas omem espaço no modelo. Por outro lado, as medidas calculadas são calculadas de forma on-planeada e não o tiram 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 os requisitos de memória e tamanho do ficheiro 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. Eis algumas técnicas que abrangemos:
-
Remover colunas é, claro, a melhor forma de poupar espaço. Decida quais as colunas de que realmente necessita.
-
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 no Assistente de Importação de Tabelas.
-
Em geral, separar uma 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 a slicers nos seus relatórios. Quando adequado, 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 que precisa. Por exemplo, suponha que uma coluna armazena decimais, mas aplicou formatação para ocultar todos os decimais. O arredondado pode ser muito eficaz na redução do tamanho de uma coluna numérica.
Agora que já fez o que pode para reduzir o tamanho do seu livro, considere também executar o Otimizador de Tamanho do Livro. Analisa o seu 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
