Optimaliser Power Query når du utvider tabellkolonner

Enkelheten og brukervennligheten som gjør at Power BI-brukere raskt kan samle inn data og generere interessante og kraftige rapporter for å ta intelligente forretningsbeslutninger, gjør det også mulig for brukere å enkelt generere spørringer med dårlig ytelse. Dette skjer ofte når det finnes to tabeller som er relatert i måten en sekundærnøkkel relaterer SQL-tabeller eller SharePoint-lister på. (For posten er ikke dette problemet spesifikt for SQL eller SharePoint, og forekommer i mange scenarioer for uttrekking av serverdeldata, spesielt der skjemaet er flytende og kan tilpasses.) Det er heller ikke noe galt i å lagre data i separate tabeller som deler en felles nøkkel– dette er faktisk en grunnleggende del av databaseutforming og normalisering. Men det innebærer en bedre måte å utvide relasjonen på.

Vurder følgende eksempel på en SharePoint-kundeliste.

Og følgende plasseringsliste det refererer til.

Når du først kobler til listen, vises plasseringen som en post.

Disse dataene på øverste nivå samles inn via ett enkelt HTTP-kall til SharePoint-API-en (ignorerer metadatakallet), som du kan se i et hvilket som helst nettfeilsøker.

Når du utvider posten, ser du feltene som er koblet sammen fra den sekundære tabellen.

Når du utvider relaterte rader fra én tabell til en annen, er standard virkemåte for Power BI å generere et kall til Table.ExpandTableColumn. Du kan se dette i det genererte formelfeltet. Denne metoden genererer dessverre et individuelt kall til den andre tabellen for hver rad i den første tabellen.

Dette øker antall HTTP-kall med én for hver rad i primærlisten. Dette virker kanskje ikke som mye i eksemplet ovenfor på fem eller seks rader, men i produksjonssystemer der SharePoint-lister når hundretusener av rader, kan dette føre til en betydelig opplevelsesreduksjon.

Når spørringer når denne flaskehalsen, er den beste løsningen å unngå virkemåten for anrop per rad ved hjelp av en klassisk tabellkobling. Dette sikrer at det bare vil være ett kall for å hente den andre tabellen, og resten av utvidelsen kan oppstå i minnet ved hjelp av fellesnøkkelen mellom de to tabellene. Ytelsesforskjellen kan være massiv i noen tilfeller.

Først begynner du med den opprinnelige tabellen, og noterer kolonnen du vil utvide, og sikrer at du har ID-en for elementet, slik at du kan samsvare med den. Sekundærnøkkelen kalles vanligvis lik visningsnavnet for kolonnen med ID tilføyet. I dette eksemplet er det LocationId.

Last deretter inn den sekundære tabellen, og pass på å inkludere IDen, som er sekundærnøkkelen. Høyreklikk på Spørringer-panelet for å opprette en ny spørring.

Til slutt kan du bli med i de to tabellene ved hjelp av de respektive kolonnenavnene som samsvarer. Du kan vanligvis finne dette feltet ved først å utvide kolonnen, og deretter se etter de samsvarende kolonnene i forhåndsvisningen.

I dette eksemplet kan du se at LocationId i primærlisten samsvarer med ID-en i den sekundære listen. Brukergrensesnittet gir nytt navn til Location.Id for å gjøre kolonnenavnet unikt. La oss nå bruke denne informasjonen til å slå sammen tabellene.

Ved å høyreklikke på spørringspanelet og velge Nye spørringssammenslåingsspørringer >>som ny, ser du et egendefinert brukergrensesnitt for å hjelpe deg med å kombinere disse to spørringene.

Velg hver tabell fra rullegardinlisten for å se en forhåndsvisning av spørringen.

Når du har valgt begge tabellene, velger du kolonnen som føyer sammen tabellene logisk (i dette eksemplet er det LocationId fra primærtabellen og ID-en fra den sekundære tabellen). Dialogboksen forteller deg hvor mange av radene som samsvarer med denne sekundærnøkkelen. Du vil sannsynligvis bruke standard sammenføyningstype (venstre ytre) for denne typen data.

Velg OK , så ser du en ny spørring, som er resultatet av sammenføyningen. Hvis du utvider posten nå, innebærer det ikke flere kall til serverdel.

Oppdatering av disse dataene resulterer i bare to kall til SharePoint – én for primærlisten og én for den sekundære listen. Sammenføyningen vil bli utført i minnet, noe som reduserer antall kall til SharePoint betydelig.

Denne fremgangsmåten kan brukes for to tabeller i PowerQuery som har en samsvarende sekundærnøkkel.

Merk

SharePoint-brukerlister og taksonomi er også tilgjengelige som tabeller, og kan kobles sammen på nøyaktig den måten som er beskrevet ovenfor, forutsatt at brukeren har tilstrekkelige rettigheter til å få tilgang til disse listene.