Frågevikning och anslutning till SQL Server

I den här handledningen lär vi oss hur du ansluter till SQL-servern. Vi kommer också att diskutera hur frågeveckning fungerar i Power Query och hur vi kan köra SQL-kommandon i LuckyTemplates. Inuti Power Query kan vi extrahera och manipulera data från olika källor. Med frågevikning kan vi överföra datatransformationer till källan istället för att göra dem i LuckyTemplates.

Frågevikning är mycket effektivt med stora databaser för att säkerställa prestandaförbättringar för dina rapporter.

Innehållsförteckning

• Ansluta till en SQL Server-databas för frågevikning
• Förstå Query Folding In Power Query
• Identifiera och fixa en trasig frågevikning
• Fördelar med att ansluta SQL Server och köra SQL Language
• Slutsats

Ansluta till en SQL Server-databas för frågevikning

Vi kommer att göra den här handledningen i en Power Query-redigerare. Klicka först på SQL Server under Ny källa .

Om du redan har installerat en SQL-serverdator och arbetar självständigt kommer servernamnet att vara localhost . Men om du arbetar i en organisation kommer de att ge dig servernamnet och tillgång till sin databas. Vi måste också ange databasnamnet. I det här exemplet kommer jag att använda AdventureWorksDW2012 . Du kan lära dig hur du laddar ner den här exempeldatabasen från den här handledningen .

För Data Connectivity Mode kommer all data som vi väljer att laddas i modellen om vi väljer Importera . Men om vi väljer DirectQuery kommer ingenting att laddas i datamodellen utan allt kommer att finnas i databasen. När vi tillämpar ett filter kommer frågan att skickas tillbaka till SQL Server.

Men detta är inte effektivt eftersom det kommer att ta längre tid att uppdatera. Därför väljer vi Importera som dataanslutningsläge .

Sedan, om vi klickar på Avancerade alternativ , kommer det att ge oss ett avsnitt där vi kan skriva en SQL-sats. Vi kommer att lära oss hur man gör detta senare.

I det här exemplet vill vi bara ha en tabell åt gången så vi tar inte in relationskolumner eller tabeller. I så fall måste vi avmarkera alternativet för det.

Klicka slutligen på OK .

Efter det kommer vi att kunna se de tabeller som är tillgängliga och därifrån kan vi få lite information. Från det här exemplet måste vi hämta data från faktisk internetförsäljning. Låt oss därför söka och välja FactInternetSales och klicka sedan på Ok .

Som ett resultat kommer vi nu att ha data i vår Power Query-redigerare.

Förstå Query Folding In Power Query

Högerklicka på Navigation på källpanelen och välj sedan View Native Query .

Med det kommer vi att kunna se kommandot som kördes av den här maskinen. Power Query-motorn skapade det här kommandot för att köras i SQL Server. Låt oss nu klicka på Ok .

Som ett exempel skapade jag slumpmässigt ett filter i den här tabellen bara för att visa dig att när vi väl har skapat dem kommer vi att kunna se dem i panelen TILLÄMPDA STEG .

När vi högerklickar på ett av filtren ser vi att alternativet View Native Query fortfarande är tillgängligt.

Denna SQL-fråga från vårt filter har körts i SQL Server . När vi inte har tillämpat filtret får vi 5 miljoner rader. Nu när vi har tillämpat ett filter får vi bara 4 miljoner rader.

Det betyder att LuckyTemplates nu extraherar 4 miljoner rader istället för 5 miljoner rader från SQL Server. Med detta har antalet rader minskat och antalet laddningar från vårt nätverk minskat också.

Tänk på att så länge vi ser Native Query betyder det att frågevikning fungerar. Därför görs all bearbetning inom källsystemet. Detta är det mest effektiva sättet att behandla data, särskilt om du har en stor mängd data.

Vi har lagt till ytterligare ett steg där vi tog bort en kolumn från vår tabell. Om vi ​​högerklickar på den ser vi alternativet View Native Query vilket betyder att det fortfarande fungerar.

Identifiera och fixa en trasig frågevikning

Med vissa transformationer som att ändra en kolumns datatyp kommer frågevikningen att gå sönder. Till exempel kommer vi att ändra datatypen för TaxAmt -kolumnen till Helnummer .

Detta kommer att lägga till ett Change Type- steg under TILLÄMPDA STEG . Om vi ​​högerklickar på den ser vi att View Native Query nu är inaktiverad, vilket betyder att Query Folding är trasig.

När en frågevikning är bruten kommer alla andra transformationer som vi kommer att göra att göra i LuckyTemplates Power Query men inte längre i källsystemet.

Till exempel, om vi får 3 miljoner rader, kommer alla att komma i Power Query. Vi kan fortfarande minska dessa poster genom filtrering. Men dessa 3 miljoner rader kommer nu att komma över nätverket, vilket inte är särskilt effektivt.

För ett annat exempel, låt oss säga att vi vill filtrera OrderDate för att endast visa datum efter 1 januari 2012.

Om vi ​​ser det filtret i TILLÄMPAD STEG kommer alternativet Visa inbyggd fråga inte att vara synligt.

Återigen, det beror på att frågevikningen gick sönder på grund av den tidigare transformationen som vi har skapat. Vad vi kan göra är att flytta alla filtreringssteg som vi kommer att göra ovanför transformationssteget som bröt frågevikningen .

I det här exemplet högerklickar vi bara på det senaste filtreringssteget som vi har skapat och klickar på Flytta före , eller bara drar det överst i transformationen av Ändringstyp .

Om vi ​​högerklickar på det filtret igen ser vi att alternativet View Native Query nu är synligt, vilket betyder att Query Folding fungerar igen.

Fördelar med att ansluta SQL Server och köra SQL Language

Låt oss till exempel säga att vi vill visa data med en total försäljning per land format som visas i bilden.

I vår FactInternetSales- tabell har vi kolumnen SalesAmount men vi har ingen landsinformation på den.

Vi kan fortfarande få landsinformation eftersom vi har kolumnen SalesTerritoryKey .

Vad vi behöver göra är att ta hit DimSales- tabellen så att vi kan slå samman den med vår FactInternetSales . Sedan måste vi ta med landkolumnen och gruppera dem efter landkolumn, vilket är mycket komplext och kan ta lång tid. Så istället för att göra allt detta i Power Query som inte är effektivt, bör vi göra det på istället.

För att göra det klickar du bara på Ny källa > SQL Server .

Låt oss ansluta igen till vår server som heter localhost, och AdventureWorksDW2012 som vår databas.

Den här gången vill vi göra ett avancerat alternativ eftersom vi vill skriva ett kommando under SQL- satsfältet. För det här exemplet har vi redan skrivit ett kommando som vi ska ange på SQL-satsen. Du kan lära dig mer om SQL-kommandon i våra andra handledningar.

Vi vill inte inkludera relationskolumner, så vi avmarkerar det här alternativet här. Klicka sedan på OK- knappen för att köra det här kommandot.

Efter att ha utfört kommandot kommer vi att se detta förhandsgranskningsfönster som visar en total försäljning per försäljningsområdesregion .

Så vi har kunnat få en liknande utdata från SalesByCountry- tabellen genom att använda ett enkelt SQL-kommando istället för att manipulera olika tabeller och kolumner på vår Power Query.

En annan fördel är att vi kan transformera all vår data i SQL och bara ta med den data som behövs eller krävs till vår datamodell. Med detta kan vi bygga en mycket effektiv datamodell enligt vår plan utan några svårigheter eller problem.

Slutsats

I den här handledningen har vi lärt oss vad en Query Folding är och upptäckt dess fördelar. Vi har också diskuterat stegen för hur du ansluter Power Query till SQL Server .

Dessutom pratade vi om fördelarna med att ansluta till SQL Server och skapa transformationer på SQL Server istället för att göra dem på Power Query .

Förhoppningsvis kunde du se hur det är effektivare och snabbare att göra alla transformationer i SQL jämfört med att göra det på Power Query .

Med vänliga hälsningar,

Hafiz