Power Query optimaliseren bij het uitvouwen van tabelkolommen

De eenvoud en het gebruiksgemak waarmee Power BI-gebruikers 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 zijn gerelateerd aan de manier waarop een refererende sleutel SQL-tabellen of SharePoint-lijsten met elkaar verhoudt. (Voor de record is dit probleem niet specifiek voor SQL of SharePoint en vindt plaats in veel scenario's voor back-endgegevensextractie, met name waar 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. In feite is dit een fundamenteel tenet van databaseontwerp en normalisatie. Maar het impliceert wel een betere manier om de relatie uit te breiden.

Bekijk het volgende voorbeeld van een SharePoint-klantenlijst.

En de volgende locatielijst verwijst naar.

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

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

Wanneer u de record uitvouwt, ziet u de velden die vanuit de secundaire tabel zijn gekoppeld.

Wanneer gerelateerde rijen van de ene tabel naar de andere worden uitgebreid, 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.

Dit verhoogt het aantal HTTP-aanroepen met één voor elke rij in de primaire lijst. Dit lijkt misschien niet veel in het bovenstaande voorbeeld van vijf of zes rijen, maar in productiesystemen waarin SharePoint-lijsten honderdduizenden rijen bereiken, kan dit een aanzienlijke afname van de ervaring veroorzaken.

Wanneer query's dit knelpunt bereiken, is de beste oplossing om het gedrag van aanroepen per rij te voorkomen met behulp van een klassieke table join. Dit zorgt ervoor dat er slechts één aanroep wordt uitgevoerd om de tweede tabel op te halen en dat de rest van de uitbreiding in het geheugen kan plaatsvinden met behulp van de algemene sleutel tussen de twee tabellen. Het prestatieverschil kan in sommige gevallen enorm zijn.

Begin eerst met de oorspronkelijke tabel, waarbij u de kolom noteert die u wilt uitvouwen en ervoor zorgt dat u de id van het item hebt, zodat u deze kunt vinden. Normaal gesproken heet de refererende sleutel vergelijkbaar met de weergavenaam van de kolom waaraan id is toegevoegd. In dit voorbeeld is het LocationId.

Ten tweede moet u de secundaire tabel laden, waarbij u de id, de refererende sleutel, opneemt. Klik met de rechtermuisknop op het deelvenster Query's om een nieuwe query te maken.

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

In dit voorbeeld ziet u dat LocationId in de primaire lijst overeenkomt met de id in de secundaire lijst. De naam van de gebruikersinterface wordt gewijzigd in 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's>>samenvoegen als nieuw te selecteren, ziet u een beschrijvende gebruikersinterface om deze twee query's te combineren.

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

Nadat u beide tabellen hebt geselecteerd, selecteert u de kolom die de tabellen logisch koppelt (in dit voorbeeld is dit LocationId uit de primaire tabel en id uit de secundaire tabel). In het dialoogvenster wordt aangegeven hoeveel rijen overeenkomen met die refererende sleutel. Waarschijnlijk wilt u het standaardtype join (left outer) gebruiken voor dit soort gegevens.

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

Als u deze gegevens vernieuwt, worden er slechts twee aanroepen naar SharePoint uitgevoerd: één voor de primaire lijst en één voor de secundaire lijst. De join wordt uitgevoerd in het geheugen, waardoor het aantal aanroepen naar SharePoint aanzienlijk wordt verminderd.

Deze benadering kan worden gebruikt voor twee tabellen in PowerQuery die een overeenkomende refererende sleutel hebben.

Notitie

SharePoint-gebruikerslijsten en -taxonomie zijn ook toegankelijk als tabellen en kunnen op exact de hierboven beschreven manier worden samengevoegd, mits de gebruiker voldoende bevoegdheden heeft voor toegang tot deze lijsten.