Query folding op systeemeigen query's

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 door het proces te gebruiken 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:

• Amazon Redshift
• Dataverse (bij gebruik van verbeterde rekenkracht)
• Google BigQuery
• PostgreSQL
• SAP HANA
• Snowflake
• SQL Server

Verbinding maken naar doel uit de 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.

Nadat u de verbindingsinstellingen hebt gedefinieerd en de referenties voor uw verbinding hebt opgegeven, gaat u naar het navigatiedialoogvenster voor uw gegevensbron. In dat dialoogvenster ziet u 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.

Klik in het navigatorvenster in Power Query met de rechtermuisknop op het databaseknooppunt in het navigatorvenster 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.

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.

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 bronin 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 opgegeven parameter van de functie waarvoor het veld EnableFolding-record is ingesteld op waar.

Notitie

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

Nadat u de formule hebt ingevoerd, wordt er een waarschuwing weergegeven waarvoor u systeemeigen query's moet inschakelen om voor uw specifieke stap uit te voeren. U kunt op Doorgaan klikken om deze stap te evalueren.

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

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.

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.

Als u verder wilt valideren welke query naar de gegevensbron wordt verzonden, klikt u met de rechtermuisknop op de stap Gefilterde rijen en selecteert u de optie die het queryplan weergeven leest om het queryplan voor die stap te controleren.

In de weergave queryplan ziet u dat een knooppunt met de naam Value.NativeQuery aan de linkerkant van het scherm met een hyperlinktekst met weergavedetails wordt gelezen. U kunt op deze hyperlinktekst klikken 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 opgegeven systeemeigen query.

Tip

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