Função DAX COLUMNSTATISTICS no LuckyTemplates

Gostaria de continuar explorando os usos dinâmicos da função DAX COLUMNSTATISTICS() – uma nova função DAX quase totalmente não documentada adicionada ao LuckyTemplates em agosto de 2021 que tem alguns aspectos muito exclusivos com os quais acho que será muito interessante se familiarizar . Você pode assistir ao vídeo completo deste tutorial na parte inferior deste blog.

Fiz muitas experiências e aprendi algumas coisas realmente interessantes sobre como essa função funciona e o que você pode fazer com ela. Em meu primeiro blog sobre esta função, falei sobre alguns usos estáticos. Hoje, falarei sobre alguns usos dinâmicos realmente incríveis da função de maneiras que nem mesmo o IntelliSense está atualizado.

Índice

• Como funciona a função DAX COLUMNSTATISTICS
• Conclusão

Como funciona a função DAX COLUMNSTATISTICS

Para cada tabela e campo em seu modelo de dados, COLUMNSTATISTICS produz uma tabela de seis campos adicionais que possui um nome de tabela, nome de coluna, valores mínimo e máximo para a coluna, cardinalidade e comprimento máximo. Isso não parece ser um monte de dados, mas há muito que você pode fazer com isso.

Os dados que estou usando atualmente são apenas os dados de prática definidos para as ferramentas externas LuckyTemplates. É um modelo de dados relativamente simples com tabelas de cinco dimensões, uma tabela de fatos e uma tabela de medidas . Um modelo como esse não é tão difícil de acompanhar, mas um modelo complexo precisa de um monitoramento de conjunto de dados muito mais complexo.

Os metadados de um conjunto de dados complexo tornam-se muito mais importantes em termos de observação da cardinalidade, que é o número de valores exclusivos em um campo. Tem uma grande influência no tamanho do seu modelo, bem como na velocidade de processamento.

Além disso, ao construir um modelo, é útil saber não apenas o número de tabelas, mas também o intervalo de datas dessas tabelas. À medida que você cria sua tabela Dates, certifique-se de cobrir toda a gama de dados em sua tabela de fatos.

E assim, para este exemplo, vamos tentar adicionar uma tabela a este modelo de dados no qual estou trabalhando. No Power Query, iremos New Source , então Blank Query . E então, vamos para o Editor Avançado e vou apenas colar uma função chamada List.Dates . Vamos usar isso para criar uma tabela de cardinalidade de 10.000, então são 10.000 datas únicas.

Ele retornará uma lista de 10.000 itens e podemos apenas convertê-la em uma tabela e renomeá-la.

Podemos alterar o campo até aqui até a data. E se clicarmos em Fechar e Aplicar, veremos que nossa narrativa inteligente vai atualizar dinamicamente, sem ter que atualizar todo o modelo (como fazíamos no passado).

A razão pela qual isso é totalmente dinâmico é que estamos fazendo tudo com medidas. O interessante é que apresenta muitos erros, e a função é tão nova que o IntelliSense não está pegando direito, mas na verdade funciona.

Vamos para o Tabular Editor 3, que eu acho que é a melhor maneira de descobrir o que as medidas DAX complexas estão realmente fazendo.

Lembre-se de que a consulta DAX retorna tabelas em vez de escaladores. Portanto, se tomarmos essa medida e copiarmos para uma consulta DAX , ela mostrará um erro porque o Result aqui ainda é um redimensionador. Podemos usar a abordagem de depuração para substituir esse RETURN Result por componentes individuais da medida.

Nesse caso, vamos substituí-lo por ColStats , o que fizemos no início foi apenas colocar a função COLUMNSTATISTICS DAX em uma variável. Com isso, obtemos exatamente o que esperamos, que é a tabela de estatísticas de coluna padrão.

E agora, queremos apenas olhar para a coluna Table Name, e queremos tirar os valores distintos dela e contá-los. Esse será o número de tabelas em nosso modelo de dados.

Primeiro, selecionaremos colunas em nossa tabela ColStats e apenas retornaremos o campo de nome da tabela. Normalmente, em uma medida DAX ou consulta DAX, você deseja retornar um nome de campo com o nome da tabela na frente dele. Mas, neste caso, não sabemos o que o nome da tabela apropriado referencia porque existe apenas virtualmente. Parece não aceitar o nome da variável como o nome da tabela.

Então, neste caso, temos que ficar com o que parece ser uma medida, mas na verdade é uma referência de coluna sem a referência de tabela na frente dela. E isso ainda funciona, embora seja confuso na nomenclatura. Agora, se substituirmos essa instrução RETURN por nosso TabCol , obteremos exatamente o que esperamos, que é o campo das tabelas de modelo.

E então, para Result, estamos apenas fazendo uma contagem das colunas distintas da tabela. Portanto, se substituirmos por DISTINCT(TabsCol) , obteremos nossas sete tabelas.

Agora vamos dar uma olhada na cardinalidade mais alta na tabela de cardinalidade mais alta e ver como conseguimos isso. Na verdade, esse é um padrão importante que você pode usar para muitas coisas diferentes onde estiver procurando, não apenas o número máximo, mas o atributo máximo associado a esse número.

Então, vamos dar uma olhada no MaxCardinality . Mesmo sendo um scaler, podemos transformá-la em uma tabela de uma célula apenas colocando colchetes em volta dela. E se atingirmos cinco, veremos que esse é o valor de cardinalidade de 10.000.

Então agora a questão é, como pegamos isso e retornamos a tabela associada a essa cardinalidade.

Para fazer isso, usamos esse padrão muito comum usando . Em seguida, copiamos para nossa instrução RETURN e ela nos dará a linha que esperávamos obter, que é a coluna de 10.000 datas. Em vez de obter uma linha (porque é TOPN), obtemos duas porque há um empate.

O que o ColStats também faz é criar, para cada tabela, um índice em uma linha de índice oculto chamada RowNumber , sendo um identificador exclusivo para cada linha da tabela. Para que MaxCardinality sempre seja espelhado pelo RowNumber se for um identificador exclusivo.

E assim, realmente não importa que tenhamos duas linhas, porque o que estamos vendo é o máximo do nome da tabela. E esse máximo existe apenas para retornar algum valor que, caso contrário, seria apenas uma coluna nua. Mas precisamos colocar alguma agregação em torno disso e, neste caso, usamos .

Então, se colocarmos MaxCardinality em nossa instrução RETURN e colocá-lo entre colchetes para retornar uma tabela em vez de um scaler, ele retornará o valor de Test.

Esse padrão TOPN DAX é um padrão muito bom para lembrar quando você deseja basicamente descobrir um valor máximo ou mínimo e, em seguida, retornar um atributo associado a esse mínimo ou máximo.

Otimizar fórmulas do LuckyTemplates usando an��lise avançada
de medidas DAX DAX: detalhando medidas DAX longas
Medidas DAX no LuckyTemplates usando ramificação de medidas

Conclusão

Forneci a você a abordagem geral e o contexto que podemos usar para que você faça o melhor uso da função DAX COLUMNSTATISTICS de maneira dinâmica. Ser capaz de rastrear isso dinamicamente em termos de um modelo complexo, acho que há um valor tremendo nessa função.

Quanto mais experimentarmos com a função COLUMNSTATISTICS e com sua capacidade de lidar dinamicamente com metadados no DAX, acho que mais usos interessantes encontraremos. Portanto, se você já encontrou alguns usos interessantes para esta função DAX, informe-nos nos comentários abaixo.

Tudo de bom!