如何使用 Power BI 的直接查询功能

当您创建 Power BI 报告时，观看者期望报告定期刷新并且数据不断更新。如何做到？手动更新报告可行，但 Power Bi 直接查询提供了一个更好的功能，即定期刷新。

在本教程中，您将学习如何利用 Power Bi 直接查询功能，连接存储在服务器中的数据集，并针对您的数据运行查询。

敬请关注，并在此过程中提高生产率，节省时间！

先决条件

本教程将进行实践演示。要跟随操作，请确保具备以下条件：

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

• 一个 SQL Server。

相关：Microsoft SQL Server 标准版入门

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

相关：值得学习的 Visual Studio Code 教程

• Microsoft On-premises Data Gateway。

相关：Power BI 数据网关：连接不同的数据源

• SQL Server 管理工作室（SSMS）- 本教程使用 SQL Server 管理工作室 18.12.1。

相关：开始使用 SQL Server 管理工作室 (SSMS)

创建一个 SQL 数据库以通过 Power BI Direct Query 管理

Power BI Direct Query 允许您直接连接到一个数据集，并为您的项目提供一个上传实时数据的选项。但在创建一个 SQL 数据库和连接之前，您必须知道服务器名称和数据源名称以获取正确的详细信息。

要创建一个 SQL 数据库，请按照以下步骤操作：

1. 在搜索栏中搜索 ODBC，查找并点击 ODBC 数据源管理员（64位）以打开它。

2. 接下来，导航到 ODBC 数据源管理员窗口中的系统 DSN 标签页，并点击添加以开始添加一个新的数据源。

3. 从下面的列表中选择 SQL Server 驱动程序，并点击完成以创建一个 SQL 数据源。

DirectQuery 不支持每一个数据源。MySQL 不被支持，而 SQL 被支持。

4. 现在，为您的数据源（MssqlDataSource）命名，选择安装在您机器上的 SQL 实例（POWERSERVER\POWERSERVER），然后点击完成。

请记下 SQL Server 和数据源名称，您将需要它们用于您的 Python 连接字符串。

5. 查看数据源的详细信息，然后点击测试数据源以测试您的数据源。

如果连接正常，您将收到一个测试成功的消息，如下所示。

连接到 SQL Server

现在，您已经为您的 SQL 服务器创建了一个数据源，您将使用该数据源为您的 Python 代码创建一个连接字符串。

1. 启动您的 Visual Studio，并按下 CTRL+SHFT+` 以打开一个新的终端。

2. 接下来，运行下面的 sqlcmd 命令以登录到您的 SQL 服务器实例。

sqlcmd -S SQL_SERVER -E

3. 登录后，运行以下查询以创建一个名为 MSSQLDB 的新数据库（CREATE DATABASE）。

CREATE DATABASE MSSQLDB;
GO

4. 创建一个 Python 文件 DBconnect.py 并添加下面的代码，它允许您使用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

# 创建包含表定义的元数据结构 
  # 以及与之关联的对象，如索引、视图、触发器等。
meta = MetaData()

# 以常规SQL语法表示CREATE TABLE以创建表。
students = Table(
'students', meta,
Column('id', Integer, primary_key = True),
Column('name', String),
Column('lastname', String),
)

# 使用引擎对象创建所有定义的表对象 
  # 并将信息存储在元数据中。
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. 现在，使用以下方式连接到 DirecQuery：

• 在相应的字段中插入您的服务器名称和数据库名称确保选择了 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文件导入学生表。
from CreateTable import students
# 从DBconnect.py文件导入连接字符串。
from DBconnect import coxn

# 将多个值插入学生表。
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的直接查询功能来连接存储在服务器中的数据集。您还涉及直接针对数据运行查询。

创建报告是一个漫长的过程，涉及收集、清理、上传到数据源、将数据加载到Power BI，并处理Power BI可视化。这个提到的过程已经给出。但之后，您可以选择手动刷新数据集或根据设定的时间表自动刷新数据。

相关:使用Power BI Python进行令人惊叹的数据可视化

Power BI直接查询在处理和操作数据集中的数据时存在一些缺点。但一旦创建了报告，您就无需再处理后端，这是使用此功能时的重大激励。