Indexação de dados no Vertipaq: armazenamento de linha versus armazenamento de coluna

Neste tutorial, abordaremos a diferença de como a indexação de dados funciona em um banco de dados relacional e no Vertipaq.

Os bancos de dados relacionais armazenam os dados linha por linha. Por outro lado, Vertipaq faz isso coluna por coluna.

Vamos ver como essas duas formas de armazenar e indexar dados podem afetar o processo de desenvolvimento de relatórios, especialmente ao executar suas consultas.

Índice

• Indexação de dados por linha
• Indexação de dados por coluna
• Comparando o tempo de execução em consultas simples
• Comparando o tempo de execução em consultas mais complicadas
• Conclusão

Indexação de dados por linha

Armazenar os dados linha por linha é a maneira tradicional de armazenar dados. No entanto, esse processo leva mais tempo, o que afetará o desempenho da sua consulta.

Digamos que temos uma tabela que contém Marca, Cor, Gênero, Quantidade e Preço Líquido.

Se armazenarmos tudo nesta tabela em um banco de dados, ele armazenará os dados linha por linha. É por isso que a estrutura de armazenamento de dados tradicional também é chamada de armazenamento de linha.

Primeiro, na mesma linha, ele armazenará os cabeçalhos de coluna encontrados na primeira linha — Marca, Cor, Gênero, Quantidade e Preço Líquido. Em seguida, ele passa para a próxima linha para armazenar os primeiros itens em cada coluna — A. Datum, Azure, um espaço em branco, 1 e 103,2. Isso continua linha por linha.

Então, como vamos calcular a da Quantidade usando esse método de indexação de dados?

Primeiro, começa com a primeira linha, que contém os cabeçalhos das colunas. Em seguida, ele pula para a próxima linha e pula os outros dados até chegar à primeira quantidade que vê, que é 1. Em seguida, pula de linha em linha, percorrendo todos os dados que cada linha contém e reúne todos os quantidades são encontradas.

Depois de separar todas as quantidades de cada linha, é a única vez que o cálculo é concluído.

Você provavelmente pode imaginar como o processo é tedioso se estiver preparando um relatório do LuckyTemplates que usa uma conexão DirectQuery com uma fonte de dados SQL. Nesse caso, os serviços de análise converterão o código DAX na linguagem SQL e começarão a percorrer a estrutura de dados linha por linha.

Indexação de dados por coluna

Para evitar o longo processo envolvido, você tem a opção de armazenar os dados coluna por coluna por meio do Vertipaq ao escolher o modo de importação.

Quando você usa armazenamento de coluna em vez de armazenamento de linha, Marca, Cor, Gênero, Quantidade e Preço Líquido serão armazenados em diferentes estruturas de dados.

Digamos que queremos obter a SOMA dos valores na coluna Quantidade. Não haverá necessidade de passar pela Marca, Cor e outros dados fora da coluna Quantidade. Em uma única varredura, ele lê toda a coluna Quantidade de cima para baixo e soma todos os valores.

Por causa disso, as consultas serão executadas mais rapidamente em comparação com a execução da esquerda para a direita.

Comparando o tempo de execução em consultas simples

Para realmente ver a enorme diferença entre armazenamento de linha e armazenamento de coluna, vamos fazer algumas consultas de teste em SQL e Vertipaq. O tempo de execução deve nos dizer o quão rápido um processo é comparado ao outro.

Vamos começar com uma consulta simples em SQL. Vamos calcular a SOMA da coluna Quantidade na tabela Vendas.

Como você pode ver, o tempo total de execução é de 2,2 segundos.

Agora, vamos ao DAX Studio e usamos a função EVALUATE para executar a mesma consulta. Precisamos ativar as temporizações do servidor e aguardar a conclusão do rastreamento.

Também precisamos garantir que a opção “Limpar cache e executar” esteja selecionada quando executarmos a consulta.

Depois que a consulta for executada, você verá que leva apenas 3 milissegundos para concluir a mesma consulta que fizemos no SQL anteriormente.

O conjunto de resultados também deve corresponder ao SQL e ao DAX Studio. Se colocá-los lado a lado, você pode ver que estamos retornando o mesmo valor.

Você pode tentar executar a consulta mais algumas vezes para ver a consistência do tempo de execução.

Comparando o tempo de execução em consultas mais complicadas

Desta vez, vamos comparar o tempo de execução quando estamos executando consultas mais complicadas.

Digamos que queremos identificar a SOMA da Quantidade de Vendas de cada marca. Para isso, podemos usar sobre os da Marca de cada Produto. No contexto baixo, também criaremos uma nova tabela chamada Quantidade Total onde iremos para a SOMA da Quantidade de Vendas.

Se executarmos esse código, você verá que o tempo total de execução é de 7 milissegundos.

Em segundo plano, esse código está, na verdade, executando duas consultas. O primeiro pega a coluna Marca da tabela Produtos e, em seguida, executa um OUTER JOIN nas colunas Chave do Produto, tanto na coluna Vendas quanto na coluna Produtos.

A segunda consulta simplesmente recupera a coluna Marca da tabela Produtos.

Se eu for para a tela de resultados, você verá que a medida da Quantidade Total foi dividida com base em cada Marca.

Agora vamos para o servidor SQL e escrever a mesma consulta.

Empurraremos a tabela DaxStudio Sales para a próxima linha, referenciando a tabela Sales AS S. Em seguida, também executaremos um LEFT JOIN na tabela DaxStudio Products referenciada como AS P, com a chave S.Product igual à P. Chave do produto. Também vamos usar P.Brand com a SOMA da Quantidade e Quantidade Total na instrução SELECT . Finalmente, vamos usar para P.brand.

Uma vez executado este código, obteremos uma tabela que contém a Quantidade Total segregada por cada Marca, que é a mesma que obtivemos anteriormente no Vertipaq.

Quanto ao tempo total de execução, permanece muito mais lento em 2,5 segundos.

DAX para LuckyTemplates: otimização usando mecanismos de fórmula no DAX Studio
Técnicas e lições de otimização de consultas DAX
Desempenho de consultas e configuração do DAX Studio

Conclusão

É evidente a rapidez com que o armazenamento de coluna por meio do Vertipaq é realmente em comparação com o armazenamento de linha em um banco de dados SQL. Isso mostra a importância de realmente conhecer como funciona a indexação de dados por meio de diferentes plataformas.

Pode parecer um pequeno sacrifício no início se você ainda optar por 2,5 segundos que o armazenamento de linha executa sua consulta em comparação com 7 milissegundos. Mas todos nós executamos várias consultas quando criamos nossos relatórios e todos esses tempos de execução serão somados, impactando a produtividade e a experiência do usuário a longo prazo.