Qualquer modelo tabular de Analysis Services (AS) é suportado por uma base de dados orientada a colunas denominada Vertipaq. Este mecanismo permite armazenar em memória RAM, de forma otimizada, o modelo desenvolvido, transformando, organizando e comprimindo a informação recolhida em colunas, cada qual com a sua própria estrutura e espaço físico.

De um modo geral, bases de dados com estas características, permitem executar de forma rápida e eficiente métricas calculadas sobre uma única coluna. Contudo, cálculos que envolvam diversas colunas, requerem mais tempo de processamento (e ciclos de CPU) para reorganizar os dados e devolver o resultado final.

Neste sentido, existem diversos métodos que permitem otimizar modelos tabulares. Tais métodos, diferem entre si, essencialmente, no que diz respeito às propriedades e objetivos de cada modelo.

Este artigo apresenta dez boas práticas que podem ser aplicadas a qualquer modelo tabular, de forma a melhorar significativamente o desempenho do mesmo.

1) Otimizar o modelo de dados

Em teoria, a melhor maneira de otimizar o desempenho do seu modelo tabular, seria reunir toda a informação numa única tabela. No entanto, essa abordagem não seria aconselhável, uma vez que conduziria a um processo de extração, transformação e carregamento (ELT) dos dados bastante lento e dispendioso. Deste modo, a melhor prática passa por construir o seu modelo – especialmente modelos tabulares complexos e com muitas relações definidas entre tabelas de grandes dimensões – de acordo com a metodologia de Kimball (também conhecida por Modelação Dimensional ou Star Schema). Uma das características desta arquitetura consiste na implementação de tabelas factos e dimensões otimizadas, que não só facilita cenários de análise avançados como também simplifica a interpretação do conjunto de dados.

2) Remover as colunas não utilizadas

O Vertipaq Analyzer é uma ferramenta gratuita, bastante útil, que pode ser utilizada para explorar e analisar as estruturas de armazenamento envolvidas na construção de um determinado modelo de dados de Analysis Services. Ao analisar o tamanho das diferentes tabelas e colunas que constituem o modelo, torna-se mais fácil encontrar os elementos mais dispendiosos (ou seja, aqueles que ocupam mais memória). Estes, sempre que forem considerados desnecessários para a análise dos dados, devem ser eliminados. Colunas com uma cardinalidade elevada (isto é, com um elevado número de valores distintos), tais como timestamps ou chaves primárias de tabelas de factos, são muito dispendiosas. Assim sendo, ao remover estas colunas, irá ser libertado muito espaço em memória e, consequentemente, o desempenho do modelo de dados irá ser otimizado.

3) Reduzir o número de valores, mas não a informação

Cada coluna incluída no modelo tabular será comprimida e armazenada pelo Vertipaq através de uma de duas técnicas: codificação de valor ou codificação hash.

A codificação de valor consiste num algoritmo que apenas pode ser aplicado a colunas numéricas. Através desta técnica matemática, o Vertipaq procura reduzir o número de bits necessários para armazenar os dados da coluna, tendo por base a distribuição e o intervalo de valores dos elementos que a constituem.

A codificação hash (também conhecida por codificação de dicionário) é uma metodologia que pode ser aplicada com eficiência a qualquer tipo de dados. Através desta técnica de codificação, é criado um dicionário contendo os valores distintos da coluna, posteriormente substituídos por índices (números inteiros) que apontam para os valores originais.

Por vezes, perante colunas com um elevado número de valores distintos, é recomendável dividir o seu conteúdo por duas ou mais colunas (cada qual possuindo um menor número de valores distintos) que podem ser combinadas entre si de forma a obter a coluna original. O emprego desta técnica, irá diminuir o tamanho do dicionário e, consequentemente, otimizar o processo de compactação dos dados. Se possuir no seu modelo, por exemplo, uma coluna do tipo timestamp (contendo a data e hora de um dado conjunto de registos), é muito mais eficiente dividir esta informação em duas colunas distintas: uma para a data e outra para a hora.

4) Reduzir a precisão

Poderão existir alguns casos em que a precisão de uma dada coluna do seu modelo não é considerada relevante. Nesses casos, optar pelo arredondamento dos valores dessa coluna (seja ela do tipo timestamp ou numérica) irá reduzir a sua cardinalidade e, consequentemente, libertar muito espaço na memória.

5) Optar por medidas em vez de colunas calculadas

Colunas calculadas podem revelar-se bastante dispendiosas de um ponto de vista computacional, considerando a possibilidade de gerarem dicionários extensos. Em alguns casos, isto poderá ser evitado estabelecendo-se uma expressão simples que envolva colunas da mesma tabela em que a coluna calculada foi inicialmente criada. Por exemplo, na tabela abaixo destacada, a coluna calculada “Valor da Venda” foi obtida multiplicando-se a “Quantidade” pelo “Preço Unitário”.

Uma maneira possível de otimizar esta informação seria armazenar apenas a “Quantidade” e o “Preço unitário” e utilizar a seguinte fórmula DAX para obter o “Valor da Venda”:

Vendas [Valor da venda] = SUMX (Vendas, Vendas [Quantidade] * Vendas [Preço unitário])

6) Definir a ordem da coluna

Para além das técnicas de codificação de valor e hash, existe ainda uma terceira metodologia de compactação complementar aplicada pelo Vertipaq chamada codificação de comprimento de execução (RLE). Esta técnica visa reduzir o tamanho de uma coluna, evitando armazenar valores repetidos. No RLE, cada valor repetido é associado ao número de linhas consecutivas que o contêm. A eficiência dessa abordagem, em particular, depende fortemente do padrão de repetição na coluna. Valores iguais repetidos ao longo de muitas linhas consecutivas (ou seja, colunas ordenadas) irão resultar numa melhor compressão dos dados. Assim sendo, em tabelas grandes dimensões, torna-se muito relevante encontrar a melhor maneira de ordená-las, melhorando assim a eficiência do RLE. Colunas com poucos valores distintos deverão ser priorizadas neste processo, uma vez que poderão originar muitos valores repetidos.

7) Filtrar os valores não utilizados das dimensões

Se possuir no seu modelo, registos de uma ou mais dimensões que não estejam a ser referenciados nas tabelas de fatos, a melhor prática, de modo a economizar algum espaço e otimizar o desempenho do seu modelo, é filtrá-los. Por exemplo, se possuir na sua dimensão “Data” 3.000.000 registos, dos quais apenas 92 estão a ser utilizados na tabela de factos, deverá filtrar esta dimensão de modo a reter apenas este subconjunto de valores.

8) Definir a propriedade EncodingHint

A codificação de valor possui um melhor desempenho quando aplicada a colunas utilizadas em agregações, enquanto a codificação hash é preferencialmente aplicada a colunas agregadoras (geralmente, colunas pertencentes às dimensões).

Sempre que o Analysis Services inicia o processamento de uma dada tabela, uma amostra dos valores de cada coluna numérica é analisada, de modo a determinar o tipo codificação que deverá ser aplicado. Por vezes, após o processamento parcial da coluna, com base nas novas informações recebidas, poderá ser necessário reiniciar o processo de modo a alterar o método de codificação inicialmente definido. Para evitar que tal ocorra, partindo do pressuposto que existe um conhecimento prévio à cerca dos dados, é uma boa prática sugerir ao AS a sua preferência relativamente ao método de codificação que deverá executado. Para tal, basta selecionar na propriedade “EncodingHint” da coluna, o método de codificação que considera ser o mais adequado.

9) Utilizar tipos de dados menos dispendiosos

Para o SSAS Tabular, alguns tipos de dados são mais dispendiosos de armazenar do que outros. Por exemplo, optar por uma coluna do tipo data em vez de data/hora ou IDs inteiros em vez de strings pode melhorar o desempenho do seu modelo de dados. O tamanho que cada tipo de dados ocupa difere essencialmente de acordo com o intervalo de valores que o mesmo representa.

10) Otimizar o DAX

A Data Analysis Expression (DAX) é uma linguagem que permite criar, através de funções de consulta, novas informações a partir dos dados previamente importados para o modelo tabular. Existem inúmeros aspetos que podem impactar negativamente o desempenho destas funções. Maioritariamente, estes aspetos encontram-se relacionados com cálculos de DAX demasiado complexos e pouco eficientes. Existem, no entanto, algumas boas práticas que  podem ser aplicadas de modo a otimizar as consultas em DAX:

• Usar variáveis ​​em vez de repetir métricas dentro da função IF() (diminui o número de vezes que uma determinada expressão é calculada);

• Usar a função DIVIDE() em vez de “/”. A função DIVIDE() possui um terceiro parâmetro que pode ser especificado e retornado sempre que o denominador for 0. Por outro lado, se tiver a certeza que o seu denominador nunca será 0, então a melhor prática consiste em utilizar o operador “/”.

• Não substituir os valores BLANK por zeros ou outras strings, a menos que seja um requisito. Por default, todos os registos com valores a BLANK são filtrados automaticamente no relatório. A substituição destes valores afeta negativamente o desempenho das consultas.

• Usar SELECTEDVALUE() em vez de HASONEVALUE(). Muitas vezes, a função HASONEVALUE() é utilizada para verificar se existe apenas um único valor distinto numa determinada coluna. Depois disso, geralmente, a função VALUES() é aplicada para devolver esse mesmo valor. A função SELECTEDVALUE() por si só, executa as ambas as etapas.

• Usar a função SEARCH(), definido o último parâmetro. O último parâmetro desta função define o valor que deve ser retornado caso a string de pesquisa não seja encontrada. Esta abordagem é muito mais eficiente do que utilizar funções de erro associadas à função SEARCH().

• Usar SELECTEDVALUE() em vez de VALUES(). A função VALUES() retornará um erro sempre que mais de que um valor for retornado. De modo a evitar tal comportamento, muitas pessoas utilizam funções de erro, as uais impactam negativamente o desempenho das consultas. A função SELECTEDVALUE(), por sua vez, é uma função otimizada que retorna BLANK sempre que seja devolvido um conjunto de valores.

• Usar sempre SUMMARIZECOLUMNS() em vez de SUMMARIZE(), sempre que desejar agrupar um conjunto de colunas e devolver o resultado dessa mesma agregação. A função SUMMARIZECOLUMNS() é mais recente e eficiente.

• Usar = 0 em vez de ISBLANK() || = 0. O valor BLANK encontra-se sempre associado ao valor base de cada tipo de dados. Para colunas numéricas, o valor BLANK corresponde a 0. Assim sendo, ao utilizar a expresssão = 0, estará a executar internamente as duas validações (isto é, ISBLANK() e a comparação com o valor 0). Para comparar apenas com 0, deverá ser aplicado o operador IN {}.

• Usar a função ISBLANK() em vez do operador de comparação = ISBLANK.

• Evitar usar funções iteradoras dentro de métricas. As métricas, por default, são calculadas de forma iterativa. O uso de funções iteradoras tais como ADDCOLUMNS(), SUMX(), MAXX(), CONCATENATEX() etc. dentro de métricas irá gerar “iterações aninhadas”, as quais causam uma perda considerável no desempenho da função de consulta (normalmente, funções iteradoras com uma lógica simples não são um problema). Existem duas maneiras simples de identificar este tipo de funções: geralmente, funções de agregação terminam com X; uma grande parte das funções iteradoras possuem a tabela como primeiro argumento e uma expressão como o segundo.