Usando ALLEXCEPT versus ALL e VALUES

Este artigo descreve a diferença semântica entre ALLEXCEPT e o uso conjunto de ALL e VALUES, mostrando exemplos práticos dos diferentes resultados no Power BI e SSAS 2016.

Para aqueles que já utilizam o Power BI já é sabido que , no DAX, os contextos de avaliação são tudo. Uma vez que entendamos os contextos de avaliação, o DAX não esconderá nenhum segredo de nós.

No entanto, mesmo com uma sólida compreensão dos contextos de avaliação, ainda é muito fácil cair em algumas armadilhas e, neste artigo, você aprenderá a diferença sutil entre usar ALLEXCEPT ou ALL e VALUES como parâmetros de filtro de CALCULATE.

Para exemplificar sobre este tópico, usamos um cenário que ocorre com bastante freqüência, quando você precisa juntar duas tabelas com uma dimensão, mas em uma granularidade diferente.  A presença de filtragem bidirecional permite que você use um padrão de análise muito satisfatório.  O modelo de dados usado para esta demo é o seguinte:

allexcept-vs-values-01

Como seu comportamento não é trivial, vale a pena descrever melhor o modelo, porque este é um padrão que você provavelmente encontrará várias vezes, sempre que precisar usar as dimensões com diferentes granularidades.

A tabela orçamento contém dados na granularidade da marca.  A dimensão do Produto contém a coluna Marca, mas possui uma maior granularidade, que é a granularidade do produto individual.  Isso é necessário porque as vendas se relacionam com um produto, não só com uma marca.  Assim, você precisa relacionar Orçamento com Produtos, mas com uma granularidade diferente.

Se você usa o SSAS 2012/2014 (ou qualquer versão do Power Pivot no Excel), uma boa opção é mudar o modelo construindo uma dimensão intermediária (Marcas, neste caso) vinculada ao produto e ao orçamento. Um filtro nas marcas atingirá o Produto (e, por sua vez, as Vendas) e o Orçamento ao mesmo tempo. É uma boa prática esconder a coluna da Marca do Produto, de modo que o usuário use apenas as Marcas para filtrar as marcas. Se ele filtrar a coluna da Marca no Produto, tal filtro nunca poderá alcançar o Orçamento.

No Power BI e SSAS 2016 você tem uma opção melhor. Ao estabelecer o relacionamento entre o Produto e as Marcas como um bidirecional (como fizemos no modelo mostrado), você obtém o efeito de que um filtro no Produto irá filtrar marcas, por causa da filtragem bidirecional e, a partir daí, atingirá o Orçamento .  Isso é muito conveniente porque, com o Power BI e o SSAS 2016, você pode deixar o usuário filtrar armazenar e manter a tabela de marcas escondida. No final, a Marcas é apenas uma tabela técnica útil para propagar um filtro, mas não é uma entidade de negócios real que você deseja mostrar aos seus usuários.

Além disso, há uma otimização interessante que o motor DAX possui:  Se você não filtrar a tabela do Produto, seus cálculos não usarão a tabela de marcas intermediárias como um filtro, obtendo desta forma melhores desempenhos se a tabela não precisar ser percorrida.  Sem filtros bidirecionais, o DAX não aproveita esta otimização e pode resultar em um desempenho pior.

Após esta pequena dica, que provavelmente vale um artigo por si só, para falar sobre o DAX.  O modelo mostrado funciona bem, desde que navegue dados na granularidade da marca. Você pode ver isso no seguinte relatório.

allexcept-vs-values-02

 

As coisas são um pouco mais complexas se você procurar dados em uma granularidade superior porque, se você filtrar por categoria também, o orçamento é repetido com os mesmos valores para todas as categorias.  Na verdade, o filtro na categoria não é ativo contra o orçamento, porque o orçamento existe apenas no nível da marca, e não na marca / categoria.

allexcept-vs-values-03

A solução é verificar a granularidade fazendo um COUNT no número de produtos nas duas granularidades diferentes para detectar qual o nível de detalhe que o usuário está navegando.  Isso pode ser conseguido criando duas medidas:

NumOfProducts := COUNTROWS ( 'Product' )
 
NumProducts at Brand Granularity := 
CALCULATE (
    [NumOfProducts], 
    ALLEXCEPT ( 'Product', 'Product'[Brand] )
)

Você pode ver que as duas medidas, projetadas em um relatório, mostram valores diferentes somente quando você está navegando dados em uma granularidade superior:

allexcept-vs-values-04

 

Para verificar as medidas em uma granularidade superior um simples IF pode garantir a visualização do orçamento na granularidade correta.

Budget Value := 
IF (
    [NumOfProducts] = [NumProducts at Brand Granularity], 
    SUM ( Budget[Budget] )
)

No entanto, se você procurar os valores em uma granularidade inferior, as coisas começam a ser mais complexas.  Para demonstrar isso, adicionamos uma coluna à tabela de produtos que contém “Internal” para apenas as duas marcas A.Datum e Contoso.  Esta nova coluna tem uma granularidade inferior à da Brand, então você esperaria poder filtrar o orçamento nesse nível.  Infelizmente, o resultado é o seguinte:

allexcept-vs-values-05

Por que você pode mostrar o orçamento no nível da Brand, mas não em uma granularidade inferior? Estão faltando os subtotais.  O problema está no código que calcula a quantidade de produtos na granularidade da marca, pois usa a função ALLEXCEPT.

NumProducts at Brand Granularity := 
CALCULATE (
    [NumOfProducts], 
    ALLEXCEPT ( 'Product', 'Product'[Brand] )
)

ALLEXCEPT remove qualquer filtro de uma tabela além das colunas que você está mostrando, usando-os como parâmetros.  No código de exemplo, ALLEXCEPT remove qualquer filtro da tabela Product, além da coluna da Brand.  Quando a Brand faz parte do contexto do filtro, como acontece com todas as linhas da caixa verde, a fórmula calcula o valor correto. Mas nas caixas vermelhas, o contexto do filtro contém apenas a coluna Manufacturer e a Brand não é parte disso.  Sim, sempre que o proprietário é Internal, você vê apenas duas Marcas (Brand), mas isso não é um efeito de um filtro direto na coluna da Brand, é um filtro cruzado proveniente da coluna do Manufacturer.  Vamos repetir este importante conceito: não há filtro na marca, há apenas um filtro na coluna Manufacturer.  O fato de que – no relatório – você vê apenas duas marcas(Brand) é um efeito colateral do filtro no Manufacturer.

Assim, quando você remove o filtro de todas as colunas, além da Marca, você está removendo – de fato – todos os filtros da tabela, e essa é a razão pela qual você vê – como o número de produtos – o total de todos os produtos.

Existem duas maneiras de evitar esse problema: você lista, na função ALLEXCEPT, todas as colunas em qualquer granularidade que seja menor do que o orçamento (neste caso, a única coluna seria Manufacturer) ou você substitui ALLEXCEPT pela dupla ALL e VALUES.   As duas medidas seguintes ficarão assim:

NumProducts at Brand Granularity := 
CALCULATE (
    [NumOfProducts], 
    ALLEXCEPT( 'Product', 'Product'[Brand], 'Product'[Owner] )
)
 
NumProducts at Brand Granularity := 
CALCULATE (
    [NumOfProducts], 
    ALL ( 'Product' ), 
    VALUES ( 'Product'[Brand] )
)

 

Como você pode ver, usando esta medida, o resultado é o esperado:

allexcept-vs-values-06

 

Existem diferenças entre as duas versões?  Sim, mas eles são um pouco difíceis de notar.  A versão com ALLEXCEPT dá ao motor uma melhor indicação do que deseja recuperar.  Ao analisar o plano de consulta, você verá que o motor DAX executa menos varreduras e resulta em desempenho um pouco melhor.  A versão com ALL e VALUES não pode ser otimizada no seu melhor, porque o código é mais difícil de ler, mesmo para o otimizador, resultando em um pouco mais de trabalho no lado da Motor de Fórmula.  A versão com ALLEXCEPT é mais rápida, mas, como a tabela Product é pequena, a diferença em termos de velocidade é difícil de notar. Com tabelas maiores e / ou código mais complexo, você pode verificar resultados diferentes.

Embora um pouco mais rápido, a versão com a ALLEXCEPT exige que você seja muito mais específico na criação – em uma medida – da estrutura da granularidade de suas tabelas e requer mais manutenção ao longo do tempo (basicamente, sempre que você adiciona uma coluna ao modelo que possui uma granularidade inferior a essa da marca(Brand)).  É muito provável que você esqueça de atualizar a medida, mais cedo ou mais tarde.
Nossa escolha pessoal é ir para a versão ALL e VALUES, e confiar apenas no ALLEXCEPT se estivermos buscando o melhor desempenho.  No entanto, o desempenho é apenas um aspecto de toda a história.  Às vezes, como neste exemplo, diferentes versões do mesmo código parecem retornar o mesmo valor, mas apenas porque a diferença é difícil de detectar. Existe uma grande diferença semântica entre o uso de ALLEXCEPT versus ALL e VALUES juntos.  O primeiro remove filtros, o último remove e restaura os filtros levando em consideração a filtragem cruzada anterior, que é ignorada pela ALLEXCEPT.

Esses pequenos detalhes fazem uma diferença real entre uma fórmula que sempre funciona e uma que às vezes funciona, mas outras vezes retornam um resultado errado. É por isso que aprender todos os segredos dos contextos de avaliação é um tempo bem gasto.

É isso pessoal, qualquer dúvida, dica ou algum ajuste a ser feito – a final estamos aqui para aprendermos uns com os outros – é só postar.

Abraços e até a próxima!

 

Arquivos para download

ALLEXCEPT VS ALL (6 downloads)

 

 

Fonte: sqlbi

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *