Delen via

Query folding op systeemeigen query's

  • Artikel

In Power Query kunt u een systeemeigen query definiëren en uitvoeren op uw gegevensbron. In het artikel Gegevens importeren uit een database met behulp van een systeemeigen databasequery wordt uitgelegd hoe u dit proces kunt uitvoeren met meerdere gegevensbronnen. Maar met behulp van het proces dat in dat artikel wordt beschreven, profiteert uw query niet van het vouwen van query's uit volgende querystappen.

In dit artikel wordt een alternatieve methode beschreven om systeemeigen query's te maken voor uw gegevensbron met behulp van de functie Value.NativeQuery en het query folding-mechanisme actief te houden voor volgende stappen van uw query.

Notitie

We raden u aan de documentatie over het vouwen van query's en de indicatoren voor het vouwen van query's te lezen om meer inzicht te krijgen in de concepten die in dit artikel worden gebruikt.

Ondersteunde gegevensconnectors

De methode die in de volgende secties wordt beschreven, is van toepassing op de volgende gegevensconnectors:

Verbinding maken met het doel vanuit een gegevensbron

Notitie

Om dit proces te laten zien, maakt dit artikel gebruik van de SQL Server-connector en de voorbeelddatabase AdventureWorks2019. De ervaring kan variëren van connector tot connector, maar in dit artikel worden de basisprincipes beschreven van het inschakelen van mogelijkheden voor het vouwen van query's ten opzichte van systeemeigen query's voor de ondersteunde connectors.

Wanneer u verbinding maakt met de gegevensbron, is het belangrijk dat u verbinding maakt met het knooppunt of het niveau waarop u uw systeemeigen query wilt uitvoeren. Voor het voorbeeld in dit artikel is dat knooppunt het databaseniveau op de server.

Schermopname van het dialoogvenster verbindingsinstellingen voor de verbinding met de AdventureWorks2019-database op een lokaal exemplaar van SQL Server.

Nadat u de verbindingsinstellingen hebt gedefinieerd en de referenties voor uw verbinding hebt opgegeven, wordt het navigatiedialoogvenster voor uw gegevensbron geopend. Het navigatiedialoogvenster bevat alle beschikbare objecten waarmee u verbinding kunt maken.

In deze lijst moet u het object selecteren waarop de systeemeigen query wordt uitgevoerd (ook wel het doel genoemd). In dit voorbeeld is dat object het databaseniveau.

Selecteer en houd in het navigatorvenster in Power Query het databaseknooppunt in het navigatorvenster ingedrukt (of klik erop met de rechtermuisknop) en selecteer de optie Gegevens transformeren. Als u deze optie selecteert, wordt een nieuwe query gemaakt van de algehele weergave van uw database. Dit is het doel dat u nodig hebt om uw systeemeigen query uit te voeren.

Schermopname van het kiezen van gegevens waarbij de gebruiker met de rechtermuisknop op het databaseknooppunt in de navigator heeft geklikt, met nadruk op Gegevens transformeren.

Zodra uw query in de Power Query-editor terechtkomt, moet alleen de bronstap worden weergegeven in het deelvenster Toegepaste stappen. Deze stap bevat een tabel met alle beschikbare objecten in uw database, vergelijkbaar met de manier waarop ze werden weergegeven in het navigatorvenster.

Schermopname van de query met alleen de bronstap.

De functie Value.NativeQuery gebruiken

Het doel van dit proces is het uitvoeren van de volgende SQL-code en het toepassen van meer transformaties met Power Query die weer naar de bron kunnen worden gevouwen.

SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'

De eerste stap was het definiëren van het juiste doel, in dit geval de database waarin de SQL-code wordt uitgevoerd. Zodra een stap het juiste doel heeft, kunt u die stap selecteren( in dit geval bron in toegepaste stappen) en vervolgens de fx-knop in de formulebalk selecteren om een aangepaste stap toe te voegen. Vervang in dit voorbeeld de Source formule door de volgende formule:

Value.NativeQuery(Source, "SELECT DepartmentID, Name FROM HumanResources.Department WHERE GroupName = 'Research and Development'  ", null, [EnableFolding = true])

Het belangrijkste onderdeel van deze formule is het gebruik van de optionele record voor de vierde parameter van de functie waarvoor het veld EnableFolding-record is ingesteld op true.

Schermopname van de nieuwe aangepaste stapformule met het gebruik van de functie Value.NativeQuery en de expliciete SQL-query.

Notitie

Meer informatie over de functie Value.NativeQuery vindt u in het officiële documentatieartikel.

Nadat u de formule hebt ingevoerd, wordt een waarschuwing weergegeven waarvoor u systeemeigen query's moet uitvoeren voor uw specifieke stap. Selecteer Doorgaan om deze stap te evalueren.

Deze SQL-instructie levert een tabel met slechts drie rijen en twee kolommen op.

Schermopname met de resultaten van de systeemeigen query die wordt geëvalueerd op basis van de doeldatabase.

Query folding testen

Als u het vouwen van query's van uw query wilt testen, kunt u proberen een filter toe te passen op een van uw kolommen en kijken of de query folding-indicator in de sectie toegepaste stappen de stap als gevouwen weergeeft. In dit geval kunt u de kolom DepartmentID filteren op waarden die niet gelijk zijn aan twee.

Schermopname die laat zien hoe u de kolom DepartmentID filtert om alleen de waarden te hebben die niet gelijk zijn aan twee.

Nadat u dit filter hebt toegevoegd, kunt u controleren of de indicatoren voor het vouwen van query's nog steeds worden weergegeven in deze nieuwe stap.

Filterstap die wordt weergegeven als teruggevouwen naar de gegevensbron in de sectie toegepaste stappen.

Als u verder wilt valideren welke query naar de gegevensbron wordt verzonden, kunt u de stap gefilterde rijen selecteren en vasthouden (of erop klikken met de rechtermuisknop) en de optie Queryplan weergeven selecteren om het queryplan voor die stap te controleren.

In de weergave queryplan ziet u dat een knooppunt met de naam Value.NativeQuery met een hyperlink Details weergeven heeft. U kunt deze hyperlink selecteren om de exacte query weer te geven die naar de SQL Server-database wordt verzonden.

De systeemeigen query wordt verpakt rond een andere SELECT-instructie om een subquery van het origineel te maken. Power Query doet het beste om de meest optimale query te maken op basis van de gebruikte transformaties en de systeemeigen query.

Schermopname van het queryplan voor de stap Gefilterde rijen.

Tip

Voor scenario's waarin u fouten krijgt omdat het vouwen van query's niet mogelijk was, raden we u aan om uw stappen te valideren als een subquery van uw oorspronkelijke systeemeigen query om te controleren of er syntaxis- of contextconflicten kunnen zijn.