Query vouwen en verbinden met SQL Server

In deze zelfstudie leren we hoe u verbinding kunt maken met de SQL-server. We bespreken ook hoe het invouwen van query's werkt in Power Query en hoe we SQL-opdrachten kunnen uitvoeren in LuckyTemplates. Binnen Power Query kunnen we gegevens uit verschillende bronnen extraheren en manipuleren. Met het invouwen van query's kunnen we gegevenstransformaties overbrengen naar de bron in plaats van ze in LuckyTemplates uit te voeren.

Query-vouwen is zeer effectief bij grote databases om prestatieverbeteringen voor uw rapporten te garanderen.

Inhoudsopgave

• Verbinding maken met een SQL Server-database voor queryvouwen
• Inzicht in het vouwen van query's in Power Query
• Identificeren en repareren van een kapotte queryvouwing
• Voordelen van het verbinden van SQL Server en het uitvoeren van SQL-taal
• Conclusie

Verbinding maken met een SQL Server-database voor queryvouwen

We doen deze tutorial in een Power Query-editor. Klik eerst op SQL Server onder Nieuwe bron .

Als u al een SQL-servermachine hebt geïnstalleerd en onafhankelijk werkt, is de servernaam localhost . Als u echter in een organisatie werkt, geven zij u de servernaam en toegang tot hun database. We moeten ook de naam van de database opgeven. In dit voorbeeld gebruik ik de AdventureWorksDW2012 . In deze zelfstudie kunt u leren hoe u deze voorbeelddatabase kunt downloaden .

Voor de Gegevensverbindingsmodus worden alle gegevens die we selecteren in het model geladen als we Importeren selecteren . Maar als we DirectQuery kiezen , wordt er niets in het datamodel geladen, maar alles in de database. Wanneer we een filter toepassen, wordt de query teruggestuurd naar de SQL Server.

Maar dit is niet efficiënt omdat het meer tijd kost om te updaten. Daarom kiezen we Importeren als Data Connectivity Mode .

Als we vervolgens op de Geavanceerde opties klikken , krijgen we een sectie waar we een SQL-instructie kunnen schrijven. We zullen later leren hoe we dit moeten doen.

In dit voorbeeld willen we slechts één tabel tegelijk, dus we zullen geen relatiekolommen of -tabellen invoeren. In dat geval moeten we de optie daarvoor uitvinken.

Klik ten slotte op OK .

Daarna kunnen we zien welke tafels beschikbaar zijn en van daaruit kunnen we wat informatie krijgen. Uit dit voorbeeld moeten we de gegevens halen uit feitelijke internetverkopen. Laten we daarom zoeken en FactInternetSales selecteren en vervolgens op Ok klikken .

Als gevolg hiervan hebben we nu de gegevens in onze Power Query-editor.

Inzicht in het vouwen van query's in Power Query

Klik in het deelvenster Bron met de rechtermuisknop op Navigatie en selecteer vervolgens Native Query weergeven .

Daarmee kunnen we de opdracht zien die door deze machine is uitgevoerd. De Power Query-engine heeft deze opdracht gemaakt om te worden uitgevoerd in SQL Server. Laten we nu op OK klikken .

Ik heb bijvoorbeeld willekeurig een filter in deze tabel gemaakt om u te laten zien dat als we die eenmaal hebben gemaakt, we ze kunnen zien in het paneel TOEGEPASTE STAPPEN .

Wanneer we met de rechtermuisknop op een van de filters klikken, zien we dat de optie Native Query weergeven nog steeds beschikbaar is.

Deze SQL-query van ons filter is uitgevoerd in de SQL Server . Als we het filter niet hebben toegepast, krijgen we 5 miljoen rijen. Nu we een filter hebben toegepast, krijgen we slechts 4 miljoen rijen.

Dit betekent dat LuckyTemplates nu 4 miljoen rijen in plaats van 5 miljoen rijen uit SQL Server haalt. Hiermee is het aantal rijen verminderd en ook het aantal ladingen uit ons netwerk.

Houd er rekening mee dat zolang we de Native Query zien , dit betekent dat het opvouwen van zoekopdrachten werkt. Daarom vindt alle verwerking plaats binnen het bronsysteem. Dit is de meest efficiënte manier om gegevens te verwerken, vooral als u een grote hoeveelheid gegevens heeft.

We hebben nog een stap toegevoegd waarbij we één kolom uit onze tabel hebben verwijderd. Als we er met de rechtermuisknop op klikken, zien we de optie Native Query weergeven , wat betekent dat het nog steeds werkt.

Identificeren en repareren van een kapotte queryvouwing

Bij sommige transformaties, zoals het wijzigen van het gegevenstype van een kolom, zal de queryvouwing breken. We wijzigen bijvoorbeeld het gegevenstype van de kolom TaxAmt in Whole Number .

Dit zal een wijzigingstype stap toevoegen onder de TOEGEPASTE STAPPEN . Als we er met de rechtermuisknop op klikken, zien we dat View Native Query nu is uitgeschakeld, wat betekent dat Query Folding is verbroken.

Wanneer een Query Folding is verbroken, wordt elke andere transformatie die we zullen uitvoeren, uitgevoerd in de LuckyTemplates Power Query, maar niet meer in het bronsysteem.

Als we bijvoorbeeld 3 miljoen rijen krijgen, komen ze allemaal in Power Query. We kunnen die records nog steeds verkleinen door te filteren. Die 3 miljoen rijen komen nu echter over het netwerk, wat niet erg efficiënt is.

Laten we voor een ander voorbeeld zeggen dat we de OrderDate willen filteren om alleen de datums na 1 januari 2012 weer te geven.

Als we dat filter op de TOEGEPASTE STAPPEN bekijken , is de optie Native Query weergeven niet zichtbaar.

Nogmaals, dat komt omdat de query-vouwing is verbroken vanwege de vorige transformatie die we hebben gemaakt. Wat we kunnen doen, is alle filterstappen die we gaan uitvoeren boven de transformatiestap verplaatsen die de Query Folding heeft verbroken .

Voor dit voorbeeld klikken we met de rechtermuisknop op de recente filterstap die we hebben gemaakt en klikken op Verplaatsen voor , of slepen deze naar de bovenkant van de wijzigingstype- transformatie.

Als we opnieuw met de rechtermuisknop op dat filter klikken, zien we dat de optie Native Query weergeven nu zichtbaar is, wat betekent dat Query Folding weer werkt.

Voordelen van het verbinden van SQL Server en het uitvoeren van SQL-taal

Laten we bijvoorbeeld zeggen dat we gegevens willen weergeven met een totale verkoop per land, zoals weergegeven in de afbeelding.

Op onze FactInternetSales- tabel hebben we de kolom SalesAmount , maar we hebben er geen landinformatie over.

We kunnen nog steeds landinformatie krijgen omdat we de kolom SalesTerritoryKey hebben .

Wat we moeten doen, is de DimSales- tabel hier brengen, zodat we deze kunnen samenvoegen met onze FactInternetSales . Vervolgens moeten we de landenkolom ophalen en ze per landkolom groeperen, wat erg complex is en veel tijd kan kosten. Dus in plaats van dat allemaal in Power Query te doen , wat niet efficiënt is, zouden we het in plaats daarvan op SQL moeten doen .

Om dat te doen, klikt u gewoon op Nieuwe bron > SQL Server .

Laten we opnieuw verbinding maken met onze server genaamd localhost en AdventureWorksDW2012 als onze database.

Deze keer willen we een geavanceerde optie doen omdat we een commando willen schrijven onder het veld SQL-instructie . Voor dit voorbeeld hebben we al een opdracht geschreven die we zullen invoeren in de SQL-instructie. U kunt meer te weten komen over SQL-opdrachten in onze andere zelfstudies.

We willen geen relatiekolommen opnemen, dus we zullen deze optie hier uitvinken. Klik vervolgens op de knop OK om deze opdracht uit te voeren.

Na het uitvoeren van de opdracht zien we dit voorbeeldvenster met een Totale verkoop per verkoopregio .

We hebben dus een vergelijkbare uitvoer van de tabel SalesByCountry kunnen krijgen door een eenvoudige SQL-opdracht te gebruiken in plaats van verschillende tabellen en kolommen in onze Power Query te manipuleren.

Een ander voordeel is dat we al onze gegevens in SQL kunnen transformeren en alleen de gegevens kunnen toevoegen die nodig of vereist zijn voor ons gegevensmodel. Hiermee kunnen we zonder enige moeite of problemen een zeer efficiënt datamodel bouwen volgens ons plan.

SQL Server downloaden en installeren
SQL voor LuckyTemplates-gebruikers - Nieuwe LuckyTemplates-cursus
Gegevens opvragen uit meerdere gegevensbronnen

Conclusie

In deze tutorial hebben we geleerd wat Query Folding is en hebben we de voordelen ervan ontdekt. We hebben ook de stappen besproken voor het verbinden van Power Query met SQL Server .

Bovendien hebben we het gehad over de voordelen van het verbinden met SQL Server en het maken van transformaties op de SQL Server in plaats van ze op Power Query uit te voeren .

Hopelijk heb je kunnen zien hoe het uitvoeren van alle transformaties in SQL efficiënter en sneller is in vergelijking met het doen op Power Query .

Al het beste,

Hafiz