Forespørgselsfoldning og forbindelse til SQL Server

I denne vejledning lærer vi, hvordan du opretter forbindelse til SQL-server. Vi vil også diskutere, hvordan forespørgselsfoldning fungerer i Power Query, og hvordan vi kan køre SQL-kommandoer i LuckyTemplates. Inde i Power Query kan vi udtrække og manipulere data fra forskellige kilder. Med forespørgselsfoldning kan vi overføre datatransformationer til kilden i stedet for at gøre dem i LuckyTemplates.

Forespørgselsfoldning er meget effektiv med store databaser for at sikre ydeevneforbedringer for dine rapporter.

Indholdsfortegnelse

• Tilslutning til en SQL Server-database til forespørgselsfoldning
• Forståelse af foldning af forespørgsel i Power-forespørgsel
• Identifikation og reparation af en ødelagt forespørgselsfoldning
• Fordele ved at forbinde SQL Server og køre SQL-sprog
• Konklusion

Tilslutning til en SQL Server-database til forespørgselsfoldning

Vi laver denne tutorial i en Power Query-editor. Først skal du klikke på SQL Server under Ny kilde .

Hvis du allerede har installeret en SQL-servermaskine og arbejder uafhængigt, vil servernavnet være localhost . Men hvis du arbejder i en organisation, vil de give dig servernavnet og adgangen til deres database. Vi skal også angive databasenavnet. I dette eksempel vil jeg bruge AdventureWorksDW2012 . Du kan lære, hvordan du downloader denne eksempeldatabase fra denne øvelse .

For dataforbindelsestilstanden vil alle de data, vi vælger, blive indlæst i modellen, hvis vi vælger Importer . Men hvis vi vælger DirectQuery , vil intet blive indlæst i datamodellen, men alt vil være i databasen. Hver gang vi anvender et filter, vil forespørgslen blive sendt tilbage til SQL Serveren.

Men dette er ikke effektivt, fordi det vil tage længere tid at opdatere. Derfor vælger vi Importer som dataforbindelsestilstand .

Hvis vi derefter klikker på Avancerede indstillinger , vil det give os et afsnit, hvor vi kan skrive en SQL-sætning. Vi lærer, hvordan man gør dette senere.

I dette eksempel vil vi kun have én tabel ad gangen, så vi vil ikke indsætte relationskolonner eller tabeller. I så fald skal vi fravælge muligheden for det.

Klik til sidst på OK .

Derefter vil vi være i stand til at se de tabeller, der er tilgængelige, og derfra kan vi få nogle oplysninger. Fra dette eksempel skal vi hente data fra faktisk internetsalg. Lad os derfor søge og vælge FactInternetSales og derefter klikke på Ok .

Som et resultat vil vi nu have dataene på vores Power Query-editor.

Forståelse af foldning af forespørgsel i Power-forespørgsel

Højreklik på Navigation i kildepanelet og vælg derefter View Native Query .

Med det vil vi være i stand til at se den kommando, der blev udført af denne maskine. Power Query-motoren oprettede denne kommando til at køre i SQL Server. Lad os nu klikke på Ok .

Som et eksempel oprettede jeg tilfældigt et filter i denne tabel bare for at vise dig, at når vi først har oprettet dem, vil vi være i stand til at se dem i panelet ANVENDT TRIN .

Når vi højreklikker på et af filtrene, vil vi se, at indstillingen View Native Query stadig er tilgængelig.

Denne SQL-forespørgsel fra vores filter er blevet kørt i SQL-serveren . Når vi ikke har anvendt filteret, får vi 5 millioner rækker. Nu hvor vi har anvendt et filter, får vi kun 4 millioner rækker.

Det betyder, at LuckyTemplates nu udtrækker 4 millioner rækker i stedet for 5 millioner rækker fra SQL Server. Hermed er antallet af rækker reduceret, og antallet af belastninger fra vores netværk er ligeledes reduceret.

Husk, at så længe vi ser den oprindelige forespørgsel , betyder det, at forespørgselsfoldning fungerer. Derfor foregår al behandling inden for kildesystemet. Dette er den mest effektive måde at behandle data på, især hvis du har en stor mængde data.

Vi har tilføjet endnu et trin, hvor vi fjernede en kolonne fra vores tabel. Hvis vi højreklikker på den, vil vi se indstillingen View Native Query , hvilket betyder, at den stadig fungerer.

Identifikation og reparation af en ødelagt forespørgselsfoldning

Med nogle transformationer som ændring af en kolonnes datatype vil forespørgselsfoldningen bryde. For eksempel ændrer vi TaxAmt- kolonnens datatype til Whole Number .

Dette vil tilføje et Ændringstype- trin under de ANVENDT TRIN . Hvis vi højreklikker på den, vil vi se, at View Native Query nu er deaktiveret, hvilket betyder, at Query Folding er brudt.

Når en forespørgselsfoldning er brudt, vil enhver anden transformation, vi laver, blive udført i LuckyTemplates Power Query, men ikke længere i kildesystemet.

For eksempel, hvis vi får 3 millioner rækker, vil de alle komme i Power Query. Vi kan stadig reducere disse poster gennem filtrering. Men de 3 millioner rækker vil nu komme på tværs af netværket, hvilket ikke er særlig effektivt.

For et andet eksempel, lad os sige, at vi vil filtrere OrderDate til kun at vise datoerne efter 1. januar 2012.

Hvis vi ser det filter på de ANVENDT TRIN , vil indstillingen View Native Query ikke være synlig.

Igen, det er fordi forespørgselsfoldningen blev brudt på grund af den tidligere transformation, som vi har oprettet. Det, vi kan gøre, er at flytte alle de filtreringstrin, vi laver, over det transformationstrin, der brød forespørgselsfoldningen .

I dette eksempel skal vi bare højreklikke på det seneste filtreringstrin, som vi har oprettet, og klikke på Flyt før , eller bare trække det øverst i Change Type- transformationen.

Hvis vi højreklikker på det filter igen, vil vi se, at indstillingen View Native Query nu er synlig, hvilket betyder, at Query Folding fungerer igen.

Fordele ved at forbinde SQL Server og køre SQL-sprog

Lad os f.eks. sige, at vi ønsker at vise data med et samlet salg efter landeformat som vist på billedet.

På vores FactInternetSales- tabel har vi kolonnen SalesAmount , men vi har ikke landeoplysninger på den.

Vi kan stadig få landeoplysninger, fordi vi har kolonnen SalesTerritoryKey .

Det, vi skal gøre, er at bringe DimSales- tabellen hertil, så vi kan fusionere den med vores FactInternetSales . Derefter skal vi bringe landekolonnen og gruppere dem efter landekolonne, hvilket er meget komplekst og kan tage lang tid. Så i stedet for at gøre alt det i Power Query , som ikke er effektivt, bør vi gøre det på i stedet.

For at gøre det skal du blot klikke på Ny kilde > SQL Server .

Lad os oprette forbindelse igen til vores server ved navn localhost, og AdventureWorksDW2012 som vores database.

Denne gang vil vi lave en avanceret mulighed, fordi vi vil skrive en kommando under SQL- sætningsfeltet. Til dette eksempel har vi allerede skrevet en kommando, som vi vil indtaste på SQL-sætningen. Du kan lære om SQL-kommandoer i vores andre selvstudier.

Vi ønsker ikke at inkludere relationskolonner, så vi fjerner markeringen her. Klik derefter på knappen OK for at køre denne kommando.

Efter at have udført kommandoen, vil vi se dette forhåndsvisningsvindue, der viser et samlet salg efter salgsområderegion .

Så vi har været i stand til at få et lignende output af SalesByCountry- tabellen ved at bruge en simpel SQL-kommando i stedet for at manipulere forskellige tabeller og kolonner på vores Power Query.

En anden fordel er, at vi kan transformere alle vores data i SQL og kun bringe de nødvendige eller nødvendige data til vores datamodel. Hermed kan vi opbygge en meget effektiv datamodel efter vores plan uden besvær eller problemer.

Konklusion

I denne øvelse har vi lært, hvad en forespørgselsfoldning er og opdaget dens fordele. Vi har også diskuteret trinene til, hvordan du forbinder Power Query til SQL Server .

Desuden talte vi om fordelene ved at oprette forbindelse til SQL Server og skabe transformationer på SQL Server i stedet for at gøre dem på Power Query .

Forhåbentlig var du i stand til at se, hvordan det at udføre alle transformationerne i SQL er mere effektivt og hurtigere sammenlignet med at gøre det på Power Query .

Alt det bedste,

Hafiz