Power Query optimaliseren bij het uitbreiden van tabelkolommen

Dankzij de eenvoud en het gebruiksgemak waarmee Power BI snel gegevens kunnen verzamelen en interessante en krachtige rapporten kunnen genereren om intelligente zakelijke beslissingen te nemen, kunnen gebruikers ook eenvoudig slecht presterende query's genereren. Dit gebeurt vaak wanneer er twee tabellen zijn die gerelateerd zijn op de manier waarop een SQL tabellen of SharePoint gerelateerd. (Dit probleem is niet specifiek voor SQL of SharePoint en treedt op in veel scenario's voor gegevensextractie van back-enden, met name wanneer het schema vloeiend en aanpasbaar is.) Er is ook niets inherent mis met het opslaan van gegevens in afzonderlijke tabellen die een gemeenschappelijke sleutel delen. Dit is in feite een fundamenteel grondbeginsel van — databaseontwerp en -normalisatie. Maar het impliceert wel een betere manier om de relatie uit te breiden.

Kijk eens naar het volgende voorbeeld van SharePoint lijst met klanten.

Primaire SharePoint klantenlijst.

En de volgende locatielijst waar deze naar verwijst.

Secundaire SharePoint klantenlijst.

Wanneer u voor het eerst verbinding maakt met de lijst, wordt de locatie weergegeven als een record.

Primaire locatierecords.

Deze gegevens op het hoogste niveau worden verzameld via één HTTP-aanroep naar de SharePoint-API (waarbij de aanroep van metagegevens wordt genegeerd), die u in elk webdebugger kunt zien.

Eén HTTP-aanroep in het webdebugger.

Wanneer u de record uitvoedt, ziet u de velden die zijn samengevoegd vanuit de secundaire tabel.

Velden die zijn samengevoegd vanuit de secundaire tabel.

Bij het uitbreiden van gerelateerde rijen van de ene tabel naar de andere, is het standaardgedrag van Power BI het genereren van een aanroep naar Table.ExpandTableColumn . U kunt dit zien in het gegenereerde formuleveld. Helaas genereert deze methode een afzonderlijke aanroep naar de tweede tabel voor elke rij in de eerste tabel.

Afzonderlijke aanroepen naar de tweede tabel.

Hierdoor wordt het aantal HTTP-aanroepen met één verhoogd voor elke rij in de primaire lijst. Dit lijkt misschien niet veel in het bovenstaande voorbeeld van vijf of zes rijen, maar in productiesystemen waar SharePoint-lijsten honderdduizenden rijen bereiken, kan dit leiden tot een aanzienlijke verslechtering van de ervaring.

Wanneer query's dit knelpunt bereiken, kunt u het gedrag van aanroepen per rij het beste vermijden door gebruik te maken van een klassieke tabel-join. Dit zorgt ervoor dat er slechts één aanroep is om de tweede tabel op te halen en dat de rest van de uitbreiding in het geheugen kan plaatsvinden met behulp van de gemeenschappelijke sleutel tussen de twee tabellen. Het prestatieverschil kan in sommige gevallen enorm zijn.

Begin eerst met de oorspronkelijke tabel, noteert u de kolom die u wilt uitbreiden en zorgt u ervoor dat u de id van het item hebt, zodat u deze kunt vinden. De naam van de vreemde sleutel is doorgaans vergelijkbaar met de weergavenaam van de kolom met Id toegevoegd. In dit voorbeeld is dit LocationId.

Naam van de vreemde sleutel.

Laad ten tweede de secundaire tabel, waarbij u ervoor zorgt dat de id wordt vermeld. Dit is de vreemde sleutel. Klik met de rechtermuisknop op het deelvenster Query's om een nieuwe query te maken.

Laad de secundaire tabel met de id van de vreemde sleutel.

Voeg ten slotte de twee tabellen samen met behulp van de respectieve kolomnamen die overeenkomen. U kunt dit veld doorgaans vinden door eerst de kolom uit te breiden en vervolgens te zoeken naar de overeenkomende kolommen in de preview.

Overeenkomende kolommen in preview.

In dit voorbeeld ziet u dat LocationId in de primaire lijst overeenkomt met Id in de secundaire lijst. In de gebruikersinterface wordt de naam Location.Id om de kolomnaam uniek te maken. Nu gaan we deze informatie gebruiken om de tabellen samen te voegen.

Door met de rechtermuisknop op het queryvenster te klikken en Nieuwe query combineren Query's combineren als nieuw te selecteren, ziet u een gebruikersinterface die u helpt deze twee query's > > te combineren.

Gebruik query's samenvoegen als nieuw om de query's te combineren.

Selecteer elke tabel in de vervolgkeuzelijst om een voorbeeld van de query te bekijken.

Voorbeeld van samengevoegde query's bekijken.

Nadat u beide tabellen hebt geselecteerd, selecteert u de kolom waarin de tabellen logisch worden samengezet (in dit voorbeeld is dit LocationId uit de primaire tabel en id uit de secundaire tabel). In het dialoogvenster ziet u hoeveel rijen overeenkomen met behulp van die vreemde sleutel. Waarschijnlijk wilt u het standaardtype voor joins (left outer) gebruiken voor dit soort gegevens.

Voeg left outer join soort samen.

Selecteer OK en u ziet een nieuwe query. Dit is het resultaat van de join. Het uitbreiden van de record betekent nu geen extra aanroepen naar de back-end.

Links outer join resultaat.

Het vernieuwen van deze gegevens resulteert in slechts twee aanroepen naar SharePoint één voor de primaire — lijst en één voor de secundaire lijst. De join wordt uitgevoerd in het geheugen, waardoor het aantal aanroepen naar SharePoint.

Deze aanpak kan worden gebruikt voor twee tabellen in PowerQuery die een overeenkomende vreemde sleutel hebben.

Notitie

SharePoint en taxonomie zijn ook toegankelijk als tabellen en kunnen worden samengevoegd op exact dezelfde manier als hierboven wordt beschreven, mits de gebruiker voldoende bevoegdheden heeft om toegang te krijgen tot deze lijsten.