DAX-funksjonen COLUMNSTATISTICS i LuckyTemplates

Jeg vil gjerne fortsette å utforske den dynamiske bruken av DAX-funksjonen COLUMNSTATISTICS() – en ny, nesten helt udokumentert DAX-funksjon lagt til LuckyTemplates i august 2021 som har noen veldig unike aspekter som jeg tror vil være veldig interessant å bli kjent med. Du kan se hele videoen av denne opplæringen nederst på denne bloggen.

Jeg har eksperimentert mye og lært noen virkelig interessante nye ting om hvordan denne funksjonen fungerer og hva du kan gjøre med den. I min første blogg om denne funksjonen snakket jeg om noen statiske bruksområder. I dag skal jeg snakke om noen ganske utrolige dynamiske bruk av funksjonen på måter som selv IntelliSense ikke er oppdatert på ennå.

Innholdsfortegnelse

• Hvordan DAX-funksjonen COLUMNSTATISTICS fungerer
• Konklusjon

Hvordan DAX-funksjonen COLUMNSTATISTICS fungerer

For hver tabell og felt i datamodellen din produserer COLUMNSTATISTICS en tabell med seks ekstra felt som har et tabellnavn, kolonnenavn, min- og maksverdier for kolonnen, kardinalitet og en maksimal lengde. Det virker ikke som mye data, men det er mye du kan gjøre med det.

Dataene jeg bruker for øyeblikket er bare øvelsesdatasettet for LuckyTemplates eksterne verktøy. Det er en relativt enkel datamodell med fem dimensjonstabeller, en faktatabell og en. En modell som denne er ikke så vanskelig å holde styr på, men en kompleks modell trenger en mye mer kompleks datasettovervåking.

Metadataene for et komplekst datasett blir mye viktigere når det gjelder å se på kardinalitet, som er antall unike verdier i et felt. Det har stor innflytelse på størrelsen på modellen din, så vel som potensielt prosesseringshastigheten.

I tillegg, når du bygger en modell, hjelper det å vite ikke bare antall tabeller, men datointervallet for disse tabellene. Når du bygger ut Datoer-tabellen, sørger du for å dekke hele spekteret av data i faktatabellen.

Og så, for dette eksempelet, la oss prøve å legge til en tabell til denne datamodellen som jeg jobber med. I Power Query går vi til Ny kilde og deretter Blank Query . Og så går vi inn i Advanced Editor og jeg skal bare lime inn en funksjon som heter List.Dates . Vi skal bruke det til å lage en tabell over kardinaliteten på 10 000, så det er 10 000 unike datoer.

Den kommer til å returnere en liste med 10 000 elementer, og vi kan bare konvertere den til en tabell, og deretter gi nytt navn til tabellen.

Vi kan endre feltet her til dags dato. Og hvis vi klikker på Lukk og bruk, kommer vi til å se at vår smarte fortelling kommer til å oppdateres dynamisk, uten å måtte oppdatere hele modellen (som vi gjør tidligere).

Grunnen til at dette er fullt dynamisk er at vi gjør det hele med tiltak. Det interessante er at den viser mange feil, og funksjonen er så ny at IntelliSense ikke fanger den opp riktig, men den fungerer faktisk.

La oss gå inn i Tabular Editor 3, som jeg tror er den beste måten å finne ut hva de komplekse DAX-tiltakene faktisk gjør.

Husk at DAX-spørringen returnerer tabeller i stedet for skalere. Så hvis vi tar dette tiltaket og kopierer dette over til en, viser det en feil fordi resultatet her fortsatt er en skalering. Vi kan bruke feilsøkingsmetoden for å erstatte det RETURN-resultatet med individuelle komponenter av tiltaket.

I dette tilfellet vil vi erstatte det med ColStats , som det vi har gjort øverst er bare satt at COLUMNSTATISTICSinn i en variabel. Med det får vi akkurat det vi forventer, som er standard kolonnestatistikktabell.

Og nå vil vi bare se på Tabellnavn-kolonnen, og vi vil ta de distinkte verdiene ut av det og telle dem. Det kommer til å være antallet tabeller i datamodellen vår.

Først velger vi kolonner på ColStats-tabellen vår, og returnerer bare det tabellnavnet. Vanligvis vil du i et DAX-mål eller en DAX-spørring returnere et feltnavn med tabellnavnet foran. Men i dette tilfellet vet vi ikke hva det aktuelle tabellnavnet refererer til fordi det bare eksisterer virtuelt. Det ser ikke ut til å godta variabelnavnet som tabellnavn.

Så i dette tilfellet må vi holde oss til det som ser ut som et mål, men det er faktisk en kolonnereferanse uten tabellreferansen foran. Og det fungerer fortsatt selv om det er forvirrende i nomenklaturen. Nå, hvis vi erstattet denne RETURN-setningen med vår TabCol , får vi nøyaktig det vi forventer, som er det modelltabellfeltet.

Og så for resultatet, gjør vi bare en telling av de forskjellige tabellkolonnene. Så hvis vi erstatter dette med DISTINCT(TabsCol) , får vi våre syv tabeller.

La oss nå ta en titt på den høyeste kardinaliteten i tabellen med høyeste kardinalitet og se hvordan vi fikk dem. Dette er faktisk et viktig mønster som du kan bruke til mange forskjellige ting der du leter etter, ikke bare maksnummeret, men max-attributtet knyttet til det tallet.

Så la oss ta en titt på MaxCardinality . Selv om dette er en scaler, kan vi gjøre den om til en en-celle tabell ved å bare sette parentes rundt den. Og hvis vi treffer fem, vil vi se at det er kardinalitetsverdien på 10.000.

Så nå er spørsmålet, hvordan tar vi dette og returnerer tabellen knyttet til den kardinaliteten.

For å gjøre det bruker vi dette veldig vanlige mønsteret. Vi kopierer den deretter over til RETURN-erklæringen vår, og den vil gi oss raden vi forventet å få, som er kolonnen 10 000 Datoer. I stedet for å få én rad (fordi det er TOPN), får vi to fordi det er uavgjort.

Det ColStats også gjør er at den oppretter, for hver tabell, en indeks i en skjult indeksrad kalt RowNumber , der det er en unik identifikator for hver rad i tabellen. Slik at MaxCardinality alltid vil bli speilet av RowNumber hvis det er en unik identifikator.

Og så spiller det egentlig ingen rolle at vi har to rader fordi det vi ser på er Max of tabellnavnet. Og det maks er der bare for å returnere en verdi som ellers ville dette bare vært en naken kolonne. Men vi må legge litt aggregering rundt det, og i dette tilfellet bruker vi.

Deretter, hvis vi plasserer MaxCardinality i RETURN-setningen vår, og setter den i parentes for å returnere en tabell i stedet for en scaler, returnerer den verdien av Test.

Dette TOPN DAX-mønsteret er et veldig godt mønster å huske når du i utgangspunktet vil finne ut en maks- eller min-verdi, og deretter returnere et attributt knyttet til det min eller maks.

Konklusjon

Jeg har gitt deg den generelle tilnærmingen og konteksten som vi kan bruke når det gjelder å utnytte DAX-funksjonen COLUMNSTATISTICS på en dynamisk måte. Å kunne spore dette dynamisk i form av en kompleks modell, tror jeg det er en enorm verdi i denne funksjonen.

Jo mer vi eksperimenterer med COLUMNSTATISTICS-funksjonen, og med dens evne til dynamisk å håndtere metadata i DAX, tror jeg, jo mer interessante bruksområder kommer vi til å finne. Så hvis du allerede har funnet noen interessante bruksområder for denne DAX-funksjonen, vennligst gi meg beskjed i kommentarene nedenfor.

Beste ønsker!