如何使用Power BI直接查詢功能

當您建立 Power BI 報告時，觀眾期望報告定期刷新並持續更新數據。如何？手動更新報告可以，但 Power Bi 直接查詢提供了一個更好的功能，即定期刷新。

在本教程中，您將學習如何利用 Power Bi 直接查詢功能，連接到存儲在服務器中的數據集，並對數據運行查詢。

敬請關注，提高生產力，同時節省時間！

先決條件

本教程將進行實踐演示。要跟著做，請確保您具備以下條件：

• Power Bi 桌面版 – 本教程使用 Power Bi 桌面版 2.109.1021.0 版本。

• 一個 SQL Server。

相關:開始使用 Microsoft SQL Server 標準版

• A code editor – This tutorial uses VS code version 1.71.

相關:值得學習的 Visual Studio Code 教程

• Microsoft 在線數據網關。

相關：Power BI數據網關：連接不同數據源

• SQL Server Management Studio（SSMS） – 本教程使用SQL Server Management Studio 18.12.1。

相關：使用SQL Server Management Studio (SSMS)入門

創建用於Power BI直接查詢的SQL數據庫

Power Bi直接查詢使您能夠直接連接到數據集，並為您的項目提供將實時數據上傳到項目的選項。但在創建SQL數據庫和連接之前，您必須知道服務器名稱和數據源名稱以獲取正確的詳細信息。

要創建SQL數據庫，請按照以下步驟進行：

1. 在搜索欄中搜索ODBC，查找並點擊ODBC數據源管理員（64位）以打開它。

2. 接下來，導航到ODBC數據源管理員窗口的系統DSN選項卡，並點擊添加以開始添加新的數據源。

3. 從下面的列表中選擇SQL Server驅動程序，然後點擊完成以創建SQL數據源。

直接查詢不支持每個數據源。MySQL不受支持，而SQL受支持。

4. 現在，命名您的資料來源（MssqlDataSource），選擇在您的機器上安裝的 SQL 實例（POWERSERVER\POWERSERVER），然後點擊完成。

請記下 SQL Server 和資料來源名稱，因為您將需要它們來建立 Python 連接字串。

5. 檢閱資料來源的詳細資訊，然後點擊測試資料來源以測試您的資料來源。

如果連線正常，您將收到一則「測試成功完成」的訊息，如下所示。

連接到 SQL Server

現在，您已經為 SQL server 建立了一個資料來源，您將使用該資料來源為您的 Python 程式碼建立連接字串。

1. 啟動 Visual Studio，按下 CTRL+SHFT+` 以開啟新的終端機。

2. 接下來，執行下面的 sqlcmd 命令以登入您的 SQL server 實例。

sqlcmd -S SQL_SERVER -E

3. 登入後，執行以下查詢以建立一個名為 MSSQLDB 的新資料庫（CREATE DATABASE）。

CREATE DATABASE MSSQLDB;
GO

4. 建立一個名為 DBconnect.py 的 Python 檔案，並添加以下代碼，該代碼允許您使用 Python 的 SQLAlchemy ORM 連接到您的 SQL 資料庫。

引擎標誌著您的 SQLAlchemy 應用程式的起點。引擎描述了連接池和Python 資料庫 API 規格（DBAPI）的方言。Python DBAPI 是 Python 內部的一個規格，用於定義所有資料庫連接套件的常見使用模式。該規格與指定的資料庫通訊。

//DBconnect.py
import sqlalchemy as sa
from sqlalchemy import create_engine
import urllib
import pyodbc
# 創建連接字符串，使用urllib格式化文本。
conn = urllib.parse.quote_plus(
# 從數據源中取得連接字符串的值
'Data Source Name=MssqlDataSource;'

'Driver={SQL Server};'

'Server=POWERSERVER\POWERSERVER;'

'Database=MSSQLDB;'

'Trusted_connection=yes;'

)

try:
# 使用sqlalchemy引擎創建連接
	coxn = create_engine('mssql+pyodbc:///?odbc_connect={}'.format(conn))
	print("Passed")
except:
	print("failed!")

5. 在您的主文件夾中創建一個名為CreateTable.py的Python文件，添加下面的代碼，然後運行它。下面的代碼在您的SQL數據庫中創建了一個名為students的表。

//CreateTable.py
from DBconnect import coxn
from sqlalchemy import Table, Column, Integer, String, MetaData

# 創建包含表定義的Metadata結構
  # 以及與之關聯的對象，如索引、視圖、觸發器等。
meta = MetaData()

# 代表使用常規SQL語法創建表的CREATE TABLE。
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# 使用引擎對象創建所有已定義的表對象
  # 並將信息存儲在Metadata中。
meta.create_all(coxn)

6. 接下來，打開您的SSMS，檢查已創建的數據庫、表和列。

7. 創建一個名為WriteToTable.py的Python文件， 添加下面的代碼，然後運行它。

這段代碼包含將值寫入數據庫表的邏輯。

//WriteToTable.py
# 從CreateTable.py文件中導入students表。
from CreateTable import students
# 從DBconnect.py文件中導入coxn連接字符串。
from DBconnect import coxn
# 將多個值插入students表中
ins = students.insert().values([
   {'name':'Bob','lastname':'Marley'},
   {'name':'Bob','lastname':'Dylan'}
])
conn = coxn.connect()
conn.execute(ins)

準備本地數據網關

現在您已經創建了連接字符串並成功連接到數據庫，您需要創建一個數據網關。這個數據網關將負責將您的數據庫連接到 Power BI。

1. 打開本地數據網關並登錄。

2. 檢查您的本地數據網關的狀態，確保它是在線的，如下所示。

3. 接下來，打開 Power BI，單擊主頁面選項卡下的獲取數據，然後選擇 SQL Server 以開始將數據源連接到 Power BI。

4. 現在，使用以下方法連接到 DirectQuery：

• 在相應的字段中插入您的 服務器 名稱和 數據庫 名稱確保選擇了 DirectQuery 選項。單擊 確定 以連接到 DirectQuery。

5. 從數據庫中選擇一個表（學生），然後單擊加載以加載數據。

6. 接下來，單擊表圖標以使用表格視覺化查看數據。

相關:使用 Power BI Python 進行驚人的數據可視化

下表顯示了您從數據庫表中加載的數據

7. 點擊文件菜單以訪問在 Power BI 上執行的操作。

8. 現在，單擊發佈 → 發佈到 Power BI 以發佈您的數據報告。

9. 點擊打開<您的報告>一旦您的發布成功，將您的瀏覽器重定向到您在 Power BI 中進行的最近活動列表 (步驟 10)。

10. 點擊報告以打開它，如下所示。

在首次設置時，您可能會遇到以下網關問題，因為您尚未將專案的數據源連接到 Power BI 的數據網關。

11. 點擊操作列下的設置圖標以開始添加新的網關連接。這樣做可以幫助您修復網關配置錯誤。

12. 配置新的網關連接，然後點擊“創建”以在您的數據源和網關之間創建新的連接。

13. 最後，一旦您看到網關正在運行，從“映射到”下拉菜單中選擇數據源名稱 (MssqlDataSource)，然後點擊“應用”。

查看和管理數據報告

在網關和 Power BI 之間建立連接之後，您可以查看您的報告並設置刷新計劃。

1. 導航到您的 Power BI 報告首頁。

Power Bi 直接查詢的性能取決於底層數據集來源。底層數據集對請求的回應所需的時間將決定用戶查看報告的速度。或者他們首先是否成功查看報告。

2. 接下來，點擊報告，如下所示，以打開它。

打開報告後，您應該看到數據，如下所示。

3. 返回報告首頁，然後點擊“刷新”→“安排刷新”以設置報告的定期刷新。

除了創建實時報告之外，Power Bi Direct Query還允許您設置定期刷新，自動更新您的報告。

4. 現在，從下拉字段中選擇您喜歡的刷新間隔，然後點擊”應用”以應用更改。

5. 打開您的WriteToTable.py Python腳本，添加下面的代碼以插入更多的條目到您的數據庫中，然後運行該腳本。

運行此代碼不會提供輸出，但您稍後將在以下步驟中驗證表中插入的值。

對於像SAP Business Warehouse這樣的多維源，沒有Power Query Editor，這會限制您可以通過數據實現的功能。

//WriteToTable.py
# 從CreateTable.py文件中導入students表。
from CreateTable import students
# 從DBconnect.py文件中導入coxn連接字符串。
from DBconnect import coxn

# 將多個值插入到students表中
ins = students.insert().values([
{'name':'Damian','lastname':'Marley'},
{'name':'Rita','lastname':'Marley'},
{'name':'Ziggy','lastname':'Marley'},
{'name':'Sam','lastname':'Dylan'},
{'name':'Jakob','lastname':'Dylan'},
{'name':'Maria','lastname':'Dylan'}
])
# 創建連接游標。
conn = coxn.connect()
# 執行insert()命令。
conn.execute(ins)

6. 接下來，切換到您的Power BI瀏覽器，點擊”數據集+數據流”選項卡。

如果一切順利，您將看到數據集的最後刷新時間和下一次刷新，就像下面的截圖中所示。

請注意，Power Bi Direct Query對於更改和格式敏感。如果您的Power Query Editor包含復雜的步驟，可能會出錯。

正如預期的那樣，重新加載將在15分鐘後自動進行，下一次刷新將在另外15分鐘後設置進行。

結論

在本教程中，您已經學會了如何使用Power Bi Direct Query功能連接您在服務器中存儲的數據集。您還接觸到了直接對數據進行查詢的過程。

創建報告是一個漫長的過程，包括收集、清理、上傳到數據源、加載到Power BI以及工作於Power BI視覺化方面。這個過程是有固定步驟的。但之後，您可以手動刷新數據集，或者根據設置的時間表自動刷新數據。

相關：使用Power BI Python進行驚人的數據可視化

Power BI Direct Query在處理和操作數據集中的數據時有其缺點。但事實上，您只需創建一次報告，就再也不必再次處理後端，這是使用此功能時的一個巨大激勵因素。