10 maneiras de melhorar o desempenho do Power Pivot

Ao publicar relatórios do Power Pivot na web, você pretende dar ao seu público a melhor experiência possível. Uma grande parte dessa experiência é garantir que o desempenho seja bom. A palavra desempenho (no que se refere a aplicativos e relatórios) é normalmente sinônimo de velocidade - ou a rapidez com que um aplicativo executa certas ações, como abrir no navegador, executar consultas ou filtrar.

1Limite o número de linhas e colunas em suas tabelas de modelo de dados.

Uma grande influência no desempenho do Power Pivot é o número de colunas que você traz ou importa para o modelo de dados. Cada coluna que você importa é mais uma dimensão que o Power Pivot deve processar ao carregar uma pasta de trabalho. Não importe colunas extras “por precaução” - se você não tiver certeza de que usará certas colunas, apenas não as traga. Essas colunas são fáceis de adicionar mais tarde, se você achar que precisa delas.

Mais linhas significam mais dados para carregar, mais dados para filtrar e mais dados para calcular. Evite selecionar uma tabela inteira se não for necessário. Use uma consulta ou exibição no banco de dados de origem para filtrar apenas as linhas que você precisa importar. Afinal, por que importar 400.000 linhas de dados quando você pode usar uma cláusula WHERE simples e importar apenas 100.000?

2Use visualizações em vez de tabelas.

Falando em visualizações, para melhores práticas, use visualizações sempre que possível.

Embora as tabelas sejam mais transparentes do que as visualizações - permitindo que você veja todos os dados brutos e não filtrados - elas vêm fornecidas com todas as colunas e linhas disponíveis, quer você precise delas ou não. Para manter seu modelo de dados Power Pivot em um tamanho gerenciável, você geralmente é forçado a tomar a etapa extra de filtrar explicitamente as colunas de que não precisa.

As visualizações podem não apenas fornecer dados mais limpos e fáceis de usar, mas também ajudar a otimizar seu modelo de dados do Power Pivot, limitando a quantidade de dados importados.

3Evite relacionamentos em vários níveis.

Tanto o número de relacionamentos quanto o número de camadas de relacionamento têm um impacto no desempenho de seus relatórios do Power Pivot. Ao construir seu modelo, siga as melhores práticas e tenha uma única tabela de fatos contendo principalmente dados numéricos quantitativos (fatos) e tabelas de dimensão que se relacionam diretamente aos fatos. No mundo do banco de dados, essa configuração é um esquema em estrela, como mostrado.

Evite construir modelos onde as tabelas de dimensão se relacionam com outras tabelas de dimensão.

4Deixe os servidores de banco de dados back-end fazerem a análise.

A maioria dos analistas do Excel que são novos no Power Pivot tende a extrair dados brutos diretamente das tabelas em seus servidores de banco de dados externos. Depois que os dados brutos estão no Power Pivot, eles criam colunas e medidas calculadas para transformar e agregar os dados conforme necessário. Por exemplo, os usuários geralmente obtêm dados de receita e custo e, em seguida, criam uma coluna calculada no Power Pivot para calcular o lucro.

Então, por que fazer o Power Pivot fazer esse cálculo quando o servidor back-end poderia ter feito isso? A realidade é que os sistemas de banco de dados back-end, como o SQL Server, têm a capacidade de moldar, agregar, limpar e transformar dados com muito mais eficiência do que o Power Pivot. Por que não utilizar seus poderosos recursos para massagear e modelar dados antes de importá-los para o Power Pivot?

Em vez de extrair dados brutos da tabela, considere aproveitar consultas, visualizações e procedimentos armazenados para realizar o máximo possível de agregação de dados e trabalho de análise. Essa alavancagem reduz a quantidade de processamento que o Power Pivot terá que fazer e melhora naturalmente o desempenho.

5Cuidado com colunas com valores não distintos.

As colunas com um grande número de valores exclusivos são particularmente difíceis para o desempenho do Power Pivot. Colunas como ID da transação, ID do pedido e Número da fatura costumam ser desnecessárias em painéis e relatórios do Power Pivot de alto nível. Portanto, a menos que sejam necessários para estabelecer relacionamentos com outras tabelas, deixe-os fora de seu modelo.

6Limite o número de segmentações de dados em um relatório.

O fatiador é um dos melhores novos recursos de inteligência de negócios (BI) do Excel nos últimos anos. Usando segmentação de dados, você pode fornecer ao seu público uma interface intuitiva que permite a filtragem interativa de seus relatórios e painéis do Excel.

Um dos benefícios mais úteis do divisor é que ele responde a outros divisores, fornecendo um efeito de filtro em cascata. Por exemplo, a figura ilustra não apenas que clicar em Centro-Oeste no fatiador de região filtra a tabela dinâmica, mas que o fatiador de mercado também responde, destacando os mercados que pertencem à região Centro-Oeste. A Microsoft chama esse comportamento de filtragem cruzada.

Por mais útil que seja o divisor, ele é, infelizmente, extremamente ruim para o desempenho do Power Pivot. Sempre que uma segmentação de dados é alterada, o Power Pivot deve recalcular todos os valores e medidas na tabela dinâmica. Para fazer isso, o Power Pivot deve avaliar cada ladrilho no divisor selecionado e processar os cálculos apropriados com base na seleção.

7Crie segmentações de dados apenas em campos de dimensão.

Segmentadores de dados vinculados a colunas que contêm muitos valores exclusivos geralmente causam um impacto de desempenho maior do que colunas que contêm apenas alguns valores. Se uma segmentação de dados contiver um grande número de blocos, considere usar uma lista suspensa Filtro de tabela dinâmica.

Em uma observação semelhante, certifique-se de usar os tipos de dados da coluna do tamanho certo. Uma coluna com poucos valores distintos é mais clara do que uma coluna com um grande número de valores distintos. Se você estiver armazenando os resultados de um cálculo de um banco de dados de origem, reduza o número de dígitos (após o decimal) a serem importados. Isso reduz o tamanho do dicionário e, possivelmente, o número de valores distintos.

8Desative o comportamento de filtro cruzado para determinados segmentação de dados.

Desativar o comportamento de filtro cruzado de um divisor essencialmente impede que o divisor altere as seleções quando outros divisores são clicados. Isso evita a necessidade do Power Pivot avaliar os títulos no divisor desativado, reduzindo assim os ciclos de processamento. Para desativar o comportamento de filtro cruzado de uma segmentação de dados, selecione Configurações da segmentação para abrir a caixa de diálogo Configurações da segmentação. Em seguida, basta desmarcar a opção Indicar itens visualmente sem dados.

9 Use medidas calculadas em vez de colunas calculadas.

Use medidas calculadas em vez de colunas calculadas, se possível. As colunas calculadas são armazenadas como colunas importadas. Como as colunas calculadas interagem inerentemente com outras colunas no modelo, elas calculam toda vez que a tabela dinâmica é atualizada, estejam ou não sendo usadas. As medidas calculadas, por outro lado, são calculadas apenas no momento da consulta.

As colunas calculadas se assemelham a colunas regulares no sentido de que ambas ocupam espaço no modelo. Em contraste, as medidas calculadas são calculadas em tempo real e não ocupam espaço.

10Atualize para o Excel de 64 bits.

Se continuar a ter problemas de desempenho com seus relatórios do Power Pivot, você sempre pode comprar um PC melhor - neste caso, atualizando para um PC de 64 bits com o Excel de 64 bits instalado.

O Power Pivot carrega todo o modelo de dados na RAM sempre que você trabalha com ele. Quanto mais RAM o computador tiver, menos problemas de desempenho você verá. A versão de 64 bits do Excel pode acessar mais RAM do seu PC, garantindo que ele tenha os recursos do sistema necessários para processar modelos de dados maiores. Na verdade, a Microsoft recomenda o Excel de 64 bits para qualquer pessoa que trabalhe com modelos compostos por milhões de linhas.

Mas antes de começar a instalar rapidamente o Excel de 64 bits, você precisa responder a estas perguntas:

Você já tem o Excel de 64 bits instalado?

Seus modelos de dados são grandes o suficiente?

Você tem um sistema operacional de 64 bits instalado no seu PC?

Seus outros suplementos irão parar de funcionar?