Para fazer uma análise mais detalha da performance de modelos Tabular é importante entender o método de armazenamento que essa tecnologia utiliza.
A maioria dos bancos de dados relacionais armazenam fisicamente os dados linha por linha e organiza em páginas de tamanhos específicos (SQL Server 8K), fazendo com que cada página guarde um subconjunto da tabela. Em geral, esse método requer que todas as páginas sejam lidas quando uma agregação é feita utilizando apenas uma coluna. Exemplo, para saber o total de posts todos os arquivos de dados organizados em páginas precisam ser carregadas para obter o resultado.
Já com o método column-orientend que é utilizado pelo SSAS e Power BI, as páginas armazenam as colunas separadamente trazendo maior performance nas agregações, pois para obter um resultado da agregação de uma coluna menos páginas precisam ser lidas. A desvantagem é que essa arquitetura requer maior esforço computacional em casos onde várias colunas estão envolvidas. De fato este método é muito mais eficiente para sistemas analíticos.
 |
| Column-oriented |
Agora vamos falar especificamente sobre os modelos Tabular do SSAS e Power BI. Eles utilizam a estrutura de dados xVelocity in-memory que implementa o método column-store descrito acima.
Esta arquitetura mantém os dados em colunas altamente compactadas, armazenadas separadamente em dicionários de dados e uma lista de índices que representa dos valores da coluna na sequencia que são armazenados na tabela. São estas estruturas que são carregadas para a memória RAM do servidor quando o modelo é processado.
Por exemplo: a tabela de produtos possui a coluna category que possui apenas dois valores distintos. Neste caso o dicionário irá manter apenas os dados "Accessories, Bikes" e irá criar um índice que representa a sequencia que estes elementos aparecem na tabela de acordo com a posição de cada elemento no dicionário.
Como podemos ver, o dicionário pode ser a parte que irá demandar mais recursos de armazenamento. Essa arquitetura traz algumas questões importantes que devem ser consideradas durante a construção do modelo:
- Estimar o tamanho do banco de dados column-store não é tão simples quanto em modelos row-oriented (tamanho do registro * numero de linhas) que com apenas alguns exemplos e histórico de uso é possível estimar com precisão.
- O tamanho das tabelas em modelos Tabular podem ser determinados pelos seguintes fatores em ordem de importância:
- Número de colunas
- Tamanho do dicionário (número de itens distintos)
- Tipo de dado das colunas
- Número de linhas
Analisando Performance e Utilização de Memória
Os principais fatores que influenciam a pressão por mais memória são, o próprio modelo, execução de medidas complexas e usuários concorrentes. Veremos como monitorar e fazer alguns ajustes nestes itens.
Modelo
O que podemos fazer para reduzir no tamanho dos dicionários?
A primeira coisa que temos que ter em mente é: Os modelos Tabulares devem ter apenas as colunas que serão usadas para análise ou criação de relacionamentos.
Temos que identificar as colunas com maior cardinalidade e se possível remover ou segmentar.
Como não podemos remover as colunas usadas nos relacionamentos do modelo, como por exemplos os códigos das dimensões em um modelo estrela, importe apenas as chaves que serão usadas para concretizar relacionamentos. As chaves primárias das tabelas fatos criam dicionários com a quantidade de elementos do tamanho da tabela e podem aumentar muito o tamanho do dicionário.
Em tabelas de fatos não é necessário carregar uma chave primária ou substituta, o modelo cria implicitamente uma coluna "RowNumber" para identificar o registro. Por exemplo, uma medida COUNT(<chave>) pode ser substituída por COUNTROWS(). Isso pode salvar muito espaço em memória.
Não utilize colunas com datetime com horas. Isso irá gerar um dicionário muito grande, formada pela maioria de elementos distintos. Se realmente for necessário, faça a separação do dia e hora em colunas diferentes. A cardinalidade irá diminuir para 365 dias por ano e 24 * 60 na coluna de horas e se precisar calcular a diferença entre duas datas, faça antecipadamente no momento da carga incluindo em uma view por exemplo.
Caso a tabela de fatos tenha algum código da transação que implemente alguma regra ou taxonomia que possa ser pré-determinada, faça a separação em colunas diferentes. Exemplo: os primeiros 3 caracteres de um código da transação representa a categoria de um produto. Identifique e separe esta coluna, isso irá diminuir a cardinalidade.
Identificar se existe alguma coluna que pode ser obtida através da criação de uma medida. Por exemplo: uma tabela de fatos de vendas que tenha a coluna de quantidade vendida e o valor total da venda. Neste caso, a coluna valor total da venda pode ser removida e uma medida DAX pode ser criada para substitui-la.
SUMX ('Internet Sales'; RELATED( Product['Unit Price']) * 'Internet Sales'[Order Quantity])
Analisando o Modelos
A análise dos modelos pode ser feita tanto de cima para baixo, analisando as querys executadas pelos painéis e identificando as mais lentas, quanto de baixo para cima, analisando os metadados do modelo através das DMVs da SSAS que apresentam o consumo de memória e o tamanho das dicionários.
De cima para baixo:
Ative a aba "Performance Analyzer" inicie a gravação e copie a query do visual que está com pior performance.
Abra o programa DAX Studio e se conecte na instância do modelo.
Ative o botão "Server Timings" e execute a query.
Serão apresentadas as consultas realizadas no modelo, os tempos, a quantidade de linhas, espaço em memória. Estes dados servem de referência para identificar os atributos envolvidos e as medidas que estão afetando a performance.
De baixo para cima.
Outra abordagem a consulta dos metadados do modelo para identificar o tamanho dos dicionários e espaço em memória ocupados.
Identificando o tamanho do dicionário:
Identificando o espaço ocupado em memória por cada atributo. Perceba que a maioria dos atributos com maior volume de espaço em memória são as datas e as chaves.
A coluna OBJECT_MEMORY_NONSHRINKABLE está em bytes, para converter para KB divida por 1024.
Identificando as sessões de usuário conectadas e o tempo de cpu
Depois desta analise é possível ter uma boa ideia sobre como está o modelo e onde é possível intervir para otimizar o uso dos recursos.
Com as DMVs podemos ainda construir um painel no próprio power bi para monitoramento destas medidas listando os bancos, as dimensões/atributos com a quantidade de memória e o tamanho dos dicionários.
Fonte: Microsoft Analysis Services 2012 - Understanding xVelocity and DirectQuery
