Mecanismo de armazenamento – sua função na otimização de consultas DAX no LuckyTemplates

Neste tutorial, vamos dar uma olhada no segundo mecanismo dentro dos serviços de análise — o mecanismo de armazenamento.

Discutimos o mecanismo de nível superior, o mecanismo de fórmula , em um tutorial anterior. Quando os usuários entendem como esses dois mecanismos funcionam, fica mais fácil otimizar e melhorar o desempenho de suas consultas DAX.

O objetivo principal do mecanismo de armazenamento é trabalhar diretamente com o banco de dados.

O mecanismo de fórmula não tem acesso direto ao banco de dados, portanto, normalmente passa pelo mecanismo de armazenamento para essa finalidade.

O mecanismo de armazenamento vem em dois tipos — modo de importação e DirectQuery . Você pode misturar e combinar os dois tipos no mesmo modelo de dados para criar um modelo composto.

Índice

• Trabalhando com o modo de importação no mecanismo de armazenamento
• Trabalhando com DirectQuery no mecanismo de armazenamento
• Trabalhando com modelos compostos
• Outros pontos críticos sobre o mecanismo de armazenamento
• Trabalhando com consultas complicadas
• Conclusão

Trabalhando com o modo de importação no mecanismo de armazenamento

Primeiro, vamos falar sobre o modo de importação. Isso também é mais comumente conhecido como Vertipaq, mas também é chamado de xVelocity ou In Memory Columnar Database.

Há quatro coisas críticas para entender sobre como o modo de importação funciona.

Primeiro, o Vertipaq cria uma cópia dos dados diretamente da fonte de dados e os armazena na RAM em formato compactado .

Em segundo lugar, os dados processados ​​no modo de importação são baseados na última operação de atualização . Isso significa que, se você atualizou seus dados pela última vez na semana passada, os dados com os quais você está lidando ainda são os mesmos da semana passada. Isso é especialmente importante se você estiver usando uma configuração composta em que uma tabela está no modo de importação e a outra tabela está no modo DirectQuery.

Digamos que você tenha a tabela Produtos, que foi atualizada na semana passada, no modo de importação. Quanto à tabela Sales, você decidiu percorrê-la pelo DirectQuery devido ao seu tamanho. Suponhamos também que você esteja criando seu relatório com base em ambas as tabelas, nas quais está trazendo a coluna Marca e criando uma medida Total de vendas na mesma tabela Vendas. Você também deseja visualizar o valor das vendas com base na marca.

Você teria que atualizar os dados vindos do modo de importação primeiro porque você pode acabar com dados novos e atualizados do DirectQuery e dados desatualizados do Vertipaq. Isso deixará algumas linhas em branco na sua matriz e na sua visualização.

A próxima coisa que você precisa saber sobre o Vertipaq é que apenas operações básicas como , , , ou estão disponíveis nativamente . Isso significa que, se houver outras operações mais complicadas incluídas no plano de consulta, o mecanismo de armazenamento teria que chamar o mecanismo de fórmula para resolver essa parte do código.

Finalmente, o mecanismo de armazenamento é um banco de dados altamente otimizado por causa da estrutura colunar do Vertipaq . Isso significa que todos os dados são armazenados coluna por coluna e não linha por linha. Devido a essa estrutura, o Vertipaq sempre será mais rápido que uma conexão DirectQuery, mesmo se você criar índices em seu modelo de dados relacionais.

Trabalhando com DirectQuery no mecanismo de armazenamento

A próxima opção que temos dentro dos serviços de análise do LuckyTemplates é o DirectQuery. Se você usar a conexão DirectQuery, os serviços de análise atuarão apenas como passagem para as consultas enviadas pelo mecanismo de fórmula.

Então, digamos que você escreva uma consulta. O mecanismo de fórmula gerará um plano de consulta. Em seguida, encaminhará a consulta ao mecanismo de armazenamento, já traduzida para o idioma nativo do banco de dados. Na maioria das vezes, essas consultas vêm em SQL.

Se a consulta usar DirectQuery, espere que ela esteja sempre atualizada. Não há necessidade de se preocupar com quando você atualizou os dados pela última vez.

O processo de otimização do código DAX e do modelo de dados também dependerá de como o banco de dados relacional foi criado. Se você tiver índices em suas colunas, suas consultas sempre serão otimizadas. Mas se seu banco de dados não for otimizado em termos de criação de relatórios usando serviços de análise ou LuckyTemplates, você poderá enfrentar alguns desafios de desempenho. Portanto, seja intencional ao criar seus bancos de dados para o desenvolvimento de relatórios.

Trabalhando com modelos compostos

A terceira opção é criar um modelo composto para que você possa ter uma tabela no modo de importação e outra tabela no DirectQuery.

Ao usar duas tabelas de fontes diferentes, o mecanismo de fórmula enviará uma solicitação para Vertipaq e outra solicitação para a fonte de dados DirectQuery. Em ambos os casos, os serviços de análise também recuperarão o cache de dados do Vertipaq e do DirectQuery. O mecanismo de fórmula usará JOIN, ou qualquer iteração em ambos os caches de dados antes de fornecer os resultados ao usuário final.

Então, digamos que você esteja tentando visualizar o valor das vendas por marca do produto em seu relatório. O mecanismo de fórmula enviará uma solicitação para a Vertipaq, onde recuperará o produto, a marca e a chave do produto. Em seguida, no DirectQuery, ele tentará recuperar as vendas, o preço líquido, a quantidade de vendas e a chave do produto de vendas.

Assim que tiver os dois caches de dados com base na chave do produto, ele unirá os dois caches de dados e calculará o valor total das vendas. Em seguida, apresentará os resultados ao usuário final.

Outros pontos críticos sobre o mecanismo de armazenamento

Agora que abordamos seus diferentes tipos, há alguns outros fatores críticos que você precisa saber sobre o mecanismo de armazenamento para ajudá-lo a otimizar suas consultas DAX.

Como mencionei anteriormente, o mecanismo de armazenamento fornece o cache de dados de volta ao mecanismo de fórmula na forma de um cache de dados não compactado. Quando você está no modo de importação, a solicitação que é enviada de volta à Vertipaq no processo é executada em uma linguagem xmSQL.

A linguagem xmSQL é um pouco semelhante ao SQL, mas não é completamente a mesma. Vamos falar sobre xmSWL em detalhes quando falarmos sobre planos de consulta em um tutorial diferente.

Também é importante lembrar que o mecanismo de armazenamento usa todos os núcleos disponíveis em sua CPU. A capacidade de usar vários núcleos é benéfica caso você tenha vários segmentos em seu modelo de dados.

Digamos que você tenha uma tabela no LuckyTemplates com 12 milhões de linhas. Essa tabela será dividida em 12 segmentos porque, tanto no Power Pivot quanto no LuckyTemplates, cada segmento cabe em 1 milhão de linhas. Isso é diferente dos serviços de análise em geral, onde um segmento acomoda 8 milhões de linhas.

Portanto, se eu tiver seis núcleos em minha CPU, todos os seis núcleos verificarão os seis primeiros dos 12 segmentos ao mesmo tempo. Assim que terminarem, eles passarão para os próximos seis segmentos.

Mas se eu estiver trabalhando com serviços de análise em que o segmento padrão contém 8 milhões de linhas, apenas dois dos meus seis núcleos serão usados ​​— um segmento processará 8 milhões de linhas enquanto o outro processará 4 milhões.

Trabalhando com consultas complicadas

Anteriormente, mencionei que o modo de importação suporta apenas operações básicas como MIN, MAX, SUM, COUNT e GROUPBY. Então, o que acontece se você trabalhar com consultas mais complicadas?

Digamos que você decida usar uma instrução IF no contexto de linha ou uma iteração aninhada como SUMX nas tabelas Produtos e Vendas.

Nesse caso, o mecanismo de armazenamento não poderá resolver a consulta por conta própria. Em seguida, ele chamará o mecanismo de fórmula, que começará a resolver o cálculo complexo linha por linha dentro do mecanismo de armazenamento. Alguns podem pensar que esse é um cenário favorável, com os dois motores trabalhando juntos — mas isso está longe de ser verdade.

Veja, quando isso acontece, o cache de dados produzido pelo mecanismo de armazenamento não pode ser armazenado em cache caso haja um callbackdataID nessa consulta específica. Portanto, se você atualizar o visual, o mesmo cálculo terá que ser feito tanto pelo mecanismo de fórmula quanto pelo mecanismo de armazenamento, mesmo que você tenha executado a mesma consulta há alguns segundos. Isso levará a atrasos no desempenho e má experiência do usuário.

Observe também que o mecanismo de armazenamento não sabe se as consultas foram executadas usando DAX ou MDX. Como mencionamos anteriormente, é tarefa do mecanismo de fórmula converter as consultas para o idioma correto antes de transmitir o plano de consulta.

Por fim, o mecanismo de fórmula envia consultas para o mecanismo de armazenamento, uma a uma. Isso significa que ter vários segmentos realmente é melhor para que o tempo geral de digitalização no Vertipaq possa ser reduzido, com vários segmentos sendo digitalizados ao mesmo tempo.

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

Entender os meandros do mecanismo de armazenamento realmente ajuda a otimizar suas consultas DAX, especialmente se você estiver usando o DAX Studio. Se você também passou pelo tutorial explicando o mecanismo de fórmula, poderá tomar melhores decisões sobre como criar consultas com melhor desempenho.

Embora o mecanismo de fórmula sirva como o mecanismo de nível superior, não há dúvida de que ele não pode funcionar tão bem se não maximizarmos os dois mecanismos.

Tudo de bom,