Query pieghevole e connessione a SQL Server

In questo tutorial impareremo come connettersi al server SQL. Discuteremo anche di come funziona il ripiegamento delle query in Power Query e come possiamo eseguire comandi SQL in LuckyTemplates. All'interno di Power Query, possiamo estrarre e manipolare dati da varie fonti. Con il ripiegamento delle query, possiamo scaricare le trasformazioni dei dati nell'origine invece di eseguirle in LuckyTemplates.

Il ripiegamento delle query è molto efficace con database di grandi dimensioni per garantire miglioramenti delle prestazioni dei report.

Sommario

• Connessione a un database SQL Server per la piegatura delle query
• Comprensione del ripiegamento delle query in Power Query
• Identificazione e correzione di una query non funzionante pieghevole
• Vantaggi della connessione di SQL Server e dell'esecuzione del linguaggio SQL
• Conclusione

Connessione a un database SQL Server per la piegatura delle query

Faremo questo tutorial in un editor di Power Query. Innanzitutto, fai clic su SQL Server in Nuova origine .

Se hai già installato una macchina server SQL e lavori in modo indipendente, il nome del server sarà localhost . Tuttavia, se lavori in un'organizzazione, ti forniranno il nome del server e l'accesso al loro database. Dobbiamo anche fornire il nome del database. In questo esempio, userò AdventureWorksDW2012 . Puoi imparare come scaricare questo database di esempio da questo tutorial .

Per la Data Connectivity Mode , tutti i dati che selezioneremo verranno caricati nel modello se selezioniamo Import . Ma se scegliamo DirectQuery , nulla verrà caricato nel modello dati ma tutto sarà nel database. Ogni volta che applichiamo un filtro, la query verrà rispedita a SQL Server.

Ma questo non è efficiente perché ci vorrà più tempo per l'aggiornamento. Pertanto, sceglieremo Importa come Modalità connettività dati .

Quindi, se facciamo clic su Opzioni avanzate , ci fornirà una sezione in cui possiamo scrivere un'istruzione SQL. Impareremo come farlo più tardi.

In questo esempio, vogliamo solo una tabella alla volta, quindi non inseriremo tabelle o colonne di relazione. In tal caso, dobbiamo deselezionare l'opzione per quello.

Infine, fai clic su OK .

Successivamente, saremo in grado di vedere le tabelle disponibili e da lì potremo avere alcune informazioni. Da questo esempio, dobbiamo ottenere i dati dalle vendite su Internet dei fatti. Quindi, cerchiamo e selezioniamo FactInternetSales quindi facciamo clic su Ok .

Di conseguenza, ora avremo i dati nel nostro editor di Power Query.

Comprensione del ripiegamento delle query in Power Query

All'interno del pannello Origine , fai clic con il pulsante destro del mouse su Navigazione , quindi seleziona Visualizza query nativa .

Con ciò, saremo in grado di vedere il comando che è stato eseguito da questa macchina. Il motore di Power Query ha creato questo comando per l'esecuzione in SQL Server. Facciamo ora clic su Ok .

Ad esempio, ho creato casualmente un filtro in questa tabella solo per mostrarti che una volta creati, saremo in grado di vederli nel pannello PASSI APPLICATI .

Quando facciamo clic con il pulsante destro del mouse su uno dei filtri, vedremo che l' opzione Visualizza query nativa è ancora disponibile.

Questa query SQL dal nostro filtro è stata eseguita in SQL Server . Quando non abbiamo applicato il filtro, otteniamo 5 milioni di righe. Ora che abbiamo applicato un filtro, otteniamo solo 4 milioni di righe.

Ciò significa che ora LuckyTemplates estrae 4 milioni di righe anziché 5 milioni di righe da SQL Server. Con questo, il numero di righe si è ridotto e anche il numero di carichi dalla nostra rete è ridotto.

Tieni presente che finché vediamo Native Query , significa che il ripiegamento delle query funziona. Pertanto, tutta l'elaborazione viene eseguita all'interno del sistema di origine. Questo è il modo più efficiente per elaborare i dati soprattutto se si dispone di un grande volume di dati.

Abbiamo aggiunto un altro passaggio in cui abbiamo rimosso una colonna dalla nostra tabella. Se facciamo clic con il tasto destro su di esso, vedremo l' opzione Visualizza query nativa , il che significa che funziona ancora.

Identificazione e correzione di una query non funzionante pieghevole

Con alcune trasformazioni come la modifica del tipo di dati di una colonna, la piegatura delle query si interromperà. Ad esempio, modificheremo il tipo di dati della colonna TaxAmt in Whole Number .

Questo aggiungerà un passaggio Tipo di modifica sotto PASSI APPLICATI . Se facciamo clic con il tasto destro su di esso, vedremo che la visualizzazione della query nativa è ora disabilitata, il che significa che la piegatura della query è interrotta.

Quando una piegatura di query viene interrotta, qualsiasi altra trasformazione che eseguiremo verrà eseguita in LuckyTemplates Power Query ma non più nel sistema di origine.

Ad esempio, se otteniamo 3 milioni di righe, verranno tutte in Power Query. Possiamo ancora ridurre quei record attraverso il filtraggio. Tuttavia, quei 3 milioni di righe ora passeranno attraverso la rete, il che non è molto efficiente.

Per un altro esempio, supponiamo di voler filtrare OrderDate per visualizzare solo le date successive al 1° gennaio 2012.

Se visualizziamo quel filtro su APPLIED STEPS , l' opzione Visualizza query nativa non sarà visibile.

Ancora una volta, è perché il ripiegamento delle query è stato interrotto a causa della precedente trasformazione che abbiamo creato. Quello che possiamo fare è spostare tutti i passaggi di filtraggio che faremo sopra il passaggio di trasformazione che ha rotto il Query Folding .

Per questo esempio, faremo semplicemente clic con il pulsante destro del mouse sul passaggio di filtro recente che abbiamo creato e faremo clic su Sposta prima oppure trascinalo semplicemente nella parte superiore della trasformazione Tipo di modifica .

Se facciamo nuovamente clic con il pulsante destro del mouse su quel filtro, vedremo che l' opzione Visualizza query nativa è ora visibile, il che significa che la piegatura della query funziona di nuovo.

Vantaggi della connessione di SQL Server e dell'esecuzione del linguaggio SQL

Diciamo ad esempio che vogliamo visualizzare i dati con un formato di vendita totale per paese come mostrato nell'immagine.

Nella nostra tabella FactInternetSales , abbiamo la colonna SalesAmount ma non abbiamo informazioni sul paese su di essa.

Possiamo ancora ottenere informazioni sul paese perché abbiamo la colonna SalesTerritoryKey .

Quello che dobbiamo fare è portare qui la tabella DimSales in modo da poterla unire al nostro FactInternetSales . Quindi, dobbiamo portare la colonna del paese e raggrupparli per colonna del paese, il che è molto complesso e potrebbe richiedere molto tempo. Quindi, invece di fare tutto ciò in Power Query che non è efficiente, dovremmo invece farlo su SQL .

Per farlo, fai clic su New Source > SQL Server .

Connettiamoci nuovamente al nostro server denominato localhost e AdventureWorksDW2012 come nostro database.

Questa volta, vogliamo fare un'opzione avanzata perché vogliamo scrivere un comando sotto il campo dell'istruzione SQL . Per questo esempio, abbiamo già scritto un comando che inseriremo nell'istruzione SQL. Puoi conoscere i comandi SQL nei nostri altri tutorial.

Non vogliamo includere colonne di relazione, quindi deselezioniamo questa opzione qui. Quindi, fare clic sul pulsante OK per eseguire questo comando.

Dopo aver eseguito il comando, vedremo questa finestra di anteprima che mostra Total Sales by Sales Territory Region .

Quindi, siamo stati in grado di ottenere un output simile della tabella SalesByCountry utilizzando un semplice comando SQL invece di manipolare tabelle e colonne diverse sulla nostra Power Query.

Un altro vantaggio è che possiamo trasformare tutti i nostri dati in SQL e portare solo i dati necessari o richiesti al nostro modello di dati. Con questo, possiamo costruire un modello di dati molto efficiente secondo il nostro piano senza alcuna difficoltà o problema.

Download e installazione di SQL Server
SQL per utenti di LuckyTemplates: nuovo corso LuckyTemplates
Esecuzione di query sui dati da più origini dati

Conclusione

In questo tutorial, abbiamo imparato cos'è un Query Folding e ne abbiamo scoperto i vantaggi. Abbiamo anche discusso i passaggi su come connettere Power Query a SQL Server .

Inoltre, abbiamo parlato dei vantaggi della connessione a SQL Server e della creazione di trasformazioni su SQL Server invece di eseguirle su Power Query .

Si spera che tu sia stato in grado di vedere come eseguire tutte le trasformazioni in SQL sia più efficiente e veloce rispetto a farlo su Power Query .

Ti auguro il meglio,

Hafiz