查詢折疊和連接到 SQL Server

在本教程中，我們將學習如何連接到 SQL 服務器。我們還將討論查詢折疊在 Power Query 中的工作原理，以及我們如何在 LuckyTemplates 中運行 SQL 命令。在 Power Query 中，我們可以從各種來源提取和操作數據。通過查詢折疊，我們可以將數據轉換卸載到源，而不是在 LuckyTemplates 中進行。

查詢折疊對於大型數據庫非常有效，可確保提高報告的性能。

目錄

• 連接到 SQL Server 數據庫以進行查詢折疊
• 了解 Power Query 中的查詢折疊
• 識別和修復損壞的查詢折疊
• 連接 SQL Server 和運行 SQL 語言的好處
• 結論

連接到 SQL Server 數據庫以進行查詢折疊

我們將在 Power Query 編輯器中完成本教程。首先，單擊New Source下的SQL Server。

如果您已經安裝了 SQL 服務器計算機並獨立工作，則服務器名稱將為localhost。但是，如果您在某個組織工作，他們會為您提供服務器名稱和對他們數據庫的訪問權限。我們還需要提供數據庫名稱。在此示例中，我將使用AdventureWorksDW2012。您可以從本教程中了解如何下載此示例數據庫。

對於Data Connectivity Mode，如果我們選擇Import ，我們將選擇的所有數據都將加載到模型中。但是如果我們選擇DirectQuery，數據模型中不會加載任何內容，但所有內容都會在數據庫中。每當我們應用過濾器時，查詢都會被發送回 SQL Server。

但這效率不高，因為更新需要更多時間。因此，我們將選擇導入作為數據連接模式。

然後，如果我們點擊Advanced options，它將為我們提供一個可以編寫 SQL 語句的部分。稍後我們將學習如何執行此操作。

在此示例中，我們一次只需要一個表，因此我們不會引入關係列或表。在那種情況下，我們必須取消選中該選項。

最後，單擊確定。

之後，我們將能夠看到可用的表格，並從那裡獲得一些信息。從這個例子中，我們需要從事實互聯網銷售中獲取數據。因此，讓我們搜索並選擇FactInternetSales，然後單擊Ok。

因此，我們現在將在 Power Query 編輯器中獲得數據。

了解 Power Query 中的查詢折疊

在Source面板中，右鍵單擊Navigation，然後選擇View Native Query。

有了它，我們將能夠看到這台機器執行的命令。Power Query 引擎創建此命令以在 SQL Server 中運行。現在讓我們點擊Ok。

例如，我在此表中隨機創建了一個過濾器，只是為了向您展示一旦我們創建了這些過濾器，我們將能夠在“應用步驟”面板中看到它們。

當我們右鍵單擊其中一個過濾器時，我們會看到“查看本機查詢”選項仍然可用。

來自我們過濾器的SQL 查詢已在SQL Server中運行。當我們沒有應用過濾器時，我們得到 500 萬行。現在我們已經應用了一個過濾器，我們只得到 400 萬行。

這意味著 LuckyTemplates 現在從 SQL Server 中提取 400 萬行而不是 500 萬行。這樣，行數減少了，我們網絡的負載數也減少了。

請記住，只要我們看到Native Query，就意味著查詢折疊正在運行。因此，所有處理都在源系統內完成。這是處理數據的最有效方式，尤其是當您有大量數據時。

我們添加了另一個步驟，我們從表中刪除了一列。如果我們右鍵單擊它，我們將看到View Native Query選項，這意味著它仍在工作。

識別和修復損壞的查詢折疊

對於某些轉換（例如更改列的數據類型），查詢折疊將中斷。例如，我們將TaxAmt列數據類型更改為Whole Number。

這將在APPLIED STEPS下添加一個Change Type步驟。如果我們右鍵單擊它，我們會看到View Native Query現在被禁用，這意味著Query Folding被破壞了。

當查詢折疊被破壞時，我們將執行的任何其他轉換都將在 LuckyTemplates Power Query 中完成，但不再在源系統中完成。

例如，如果我們獲得 300 萬行，所有這些行都將進入 Power Query。我們仍然可以通過過濾來減少這些記錄。但是，這 300 萬行現在將通過網絡傳輸，效率不是很高。

再舉一個例子，假設我們要過濾 OrderDate以僅顯示 2012 年 1 月 1 日之後的日期。

如果我們在APPLIED STEPS上查看該過濾器，則View Native Query選項將不可見。

同樣，那是因為查詢折疊由於我們之前創建的轉換而被破壞。我們可以做的是將我們將執行的所有過濾步驟移到破壞查詢折疊的轉換步驟之上。

對於此示例，我們只需右鍵單擊我們創建的最近過濾步驟，然後單擊移動到之前，或將其拖動到更改類型轉換的頂部。

如果我們再次右鍵單擊該過濾器，我們將看到View Native Query選項現在可見，這意味著Query Folding再次起作用。

連接 SQL Server 和運行 SQL 語言的好處

比方說，我們想要按國家/地區格式顯示總銷售額數據，如圖所示。

在我們的FactInternetSales表中，我們有SalesAmount列，但我們沒有關於它的國家/地區信息。

我們仍然可以獲得國家信息，因為我們有SalesTerritoryKey列。

我們需要做的是將DimSales表放在這裡，以便我們可以將它與我們的FactInternetSales合併。然後，我們需要引入國家列並按國家列對它們進行分組，這非常複雜並且可能需要很長時間。因此，與其在效率不高的Power Query中執行所有這些操作，不如在SQL上執行。

為此，只需單擊New Source > SQL Server。

讓我們再次連接到名為 localhost 的服務器，並將AdventureWorksDW2012作為我們的數據庫。

這次我們要做一個高級選項，因為我們要在SQL語句字段下寫一個命令。對於這個例子，我們已經編寫了一個我們將在 SQL 語句中輸入的命令。您可以在我們的其他教程中了解 SQL 命令。

我們不想包含關係列，因此我們將在此處取消選中此選項。然後，單擊確定按鈕運行此命令。

執行該命令後，我們將看到此預覽窗口顯示按Sales Territory Region劃分的Total Sales。

因此，我們已經能夠通過使用簡單的SQL 命令而不是在 Power Query 上操作不同的表和列來獲得SalesByCountry表的類似輸出。

另一個好處是我們可以在 SQL 中轉換我們所有的數據，並且只將需要或需要的數據帶到我們的數據模型中。有了這個，我們就可以毫無困難或問題地根據我們的計劃構建一個非常高效的數據模型。

SQL Server 下載和安裝
SQL 為 LuckyTemplates 用戶 - 新的 LuckyTemplates 課程
從多個數據源查詢數據

結論

在本教程中，我們了解了查詢折疊是什麼並發現了它的好處。我們還討論瞭如何將Power Query連接到SQL Server 的步驟。

此外，我們還討論了連接到SQL Server並在SQL Server上創建轉換而不是在Power Query上進行轉換的好處。

希望您能夠看到與在Power Query上執行相比，在SQL中執行所有轉換如何更高效、更快速。

一切順利，

哈菲茲