powerbi怎么连接本地mysql_教程：连接到 SQL Server 中的本地数据 - Power BI | Microsoft Docs...

从本地 SQL Server 数据库刷新数据Refresh data from an on-premises SQL Server database

07/15/2019

本文内容

在本教程中，将了解如何从本地网络中存在的关系数据库中刷新 Power BI 数据集。In this tutorial, you explore how to refresh a Power BI dataset from a relational database that exists on premises in your local network. 具体而言，本教程使用示例 SQL Server 数据库，Power BI 必须通过本地数据网关访问该数据库。Specifically, this tutorial uses a sample SQL Server database, which Power BI must access through an on-premises data gateway.

在本教程中，将完成以下步骤：In this tutorial, you complete the following steps:

创建并发布从本地 SQL Server 数据库导入数据的 Power BI Desktop (.pbix) 文件。Create and publish a Power BI Desktop (.pbix) file that imports data from an on-premises SQL Server database.

通过数据网关在 Power BI for SQL Server 连接中配置数据源和数据集设置。Configure data source and dataset settings in Power BI for SQL Server connectivity through a data gateway.

配置刷新计划，确保 Power BI 数据集包含最新数据。Configure a refresh schedule to ensure your Power BI dataset has recent data.

执行数据集的按需刷新。Perform an on-demand refresh of your dataset.

查看刷新历史记录，分析过去刷新周期的结果。Review the refresh history to analyze the outcomes of past refresh cycles.

删除本教程中创建的项目来清理资源。Clean up resources by deleting the artifacts created in this tutorial.

必备组件Prerequisites

如果你还没有，请在开始之前注册 Power BI 免费试用版。If you don't already have one, sign up for a free Power BI trial before you begin.

在与 SQL Server 相同的本地计算机上安装本地数据网关(在生产环境中，通常为其他计算机)。Install an on-premises data gateway on the same local computer as SQL Server (in production, it would typically be a different computer).

备注

如果你不是网关管理员且不想自己安装网关，请与组织中的网关管理员联系。If you're not a gateway administrator and don't want to install a gateway yourself, contact a gateway administrator in your organization. 他们可创建所需的数据源定义，以将数据集连接到 SQL Server 数据库。They can create the required data source definition to connect your dataset to your SQL Server database.

创建并发布 Power BI Desktop 文件Create and publish a Power BI Desktop file

按照以下过程通过 AdventureWorksDW 示例数据库创建基本 Power BI 报表。Use the following procedure to create a basic Power BI report using the AdventureWorksDW sample database. 将报表发布到 Power BI 服务，以便在 Power BI 中获取数据集，然后可在后续步骤中配置和刷新。Publish the report to the Power BI service, so that you get a dataset in Power BI, which you can then configure and refresh in subsequent steps.

在 Power BI Desktop 的“主页”选项卡上，选择“获取数据” “SQL Server” > 。In Power BI Desktop, on the Home tab, select Get Data > SQL Server.

在“SQL Server 数据库”对话框中，输入“服务器”和“数据库(可选)”名称，确保数据连接模式为“导入”，然后选择“确定” 。In the SQL Server database dialog box, enter the Server and Database (optional) names, make sure the Data Connectivity mode is Import, and then select OK.

在本教程中，我们没有使用“高级选项” ，但是请注意，你可以指定 SQL 语句并设置其他选项，例如使用 SQL Server 故障转移。We're not using Advanced options in this tutorial, but note that you can specify a SQL statement and set other options like using SQL Server Failover.

验证凭据，然后选择“连接” 。Verify your credentials, then select Connect.

备注

如果无法进行身份验证，请确保选择正确的身份验证方法并使用具有数据库访问权限的帐户。If you're unable to authenticate, make sure you select the correct authentication method and use an account with database access. 在测试环境中，可使用具有显式用户名和密码的数据库身份验证。In test environments, you might use Database authentication with an explicit username and password. 在生产环境中，通常使用 Windows 身份验证。In production environments, you typically use Windows authentication. 请参阅刷新方案疑难解答，并联系数据库管理员联系获取其他帮助。Refer to Troubleshooting refresh scenarios and contact your database administrator for additional assistance.

如果出现“加密支持”对话框，请选择“确定” 。If an Encryption Support dialog box appears, select OK.

在“导航器”对话框中，选择“DimProduct”表，然后选择“加载” 。In the Navigator dialog box, select the DimProduct table, then select Load.

在 Power BI Desktop“报表” 视图的“可视化效果” 窗格中，选择“堆积柱形图” 。In the Power BI Desktop Report view, in the Visualizations pane, select the Stacked column chart.

在报表画布中选择柱形图后，在“字段”窗格中选择“EnglishProductName”和“ListPrice”字段 。With the column chart selected in the report canvas, in the Fields pane select the EnglishProductName and ListPrice fields.

将“EndDate”拖到报表级筛选器，在“基本筛选”下，仅选中“(空白)”复选框 。Drag the EndDate onto Report level filters, and under Basic filtering select only the checkbox for (Blank).

此时，图表应如下所示。The chart should now look like the following.

请注意，列出的 5 件“Road-250”产品标价最高 。Notice that the five Road-250 products are listed with the highest list price. 在本教程稍后部分更新数据和刷新报表时，此内容将出现变化。This will change when you update the data and refresh the report later in this tutorial.

使用名称“AdventureWorksProducts.pbix”保存报表。Save the report with the name "AdventureWorksProducts.pbix".

在“主页”选项卡上，选择“发布” “我的工作区” “选择” > > 。On the Home tab select Publish > My Workspace > Select. 如果系统要求你登录到 Power BI 服务，请登录。Sign in to the Power BI service if you're asked to do so.

在“成功”屏幕上，选择[在 Power BI 中打开“AdventureWorksProducts.pbix”] 。On the Success screen, select Open 'AdventureWorksProducts.pbix' in Power BI.

将数据集连接到 SQL Server 数据库Connect a dataset to a SQL Server database

在 Power BI Desktop 中，可直接连接到本地 SQL Server 数据库，但 Power BI 服务需要一个数据网关来充当云与本地网络之间的网桥。In Power BI Desktop, you connected directly to your on-premises SQL Server database, but the Power BI service requires a data gateway to act as a bridge between the cloud and your on-premises network. 按照以下步骤将本地 SQL Server 数据库作为数据源添加到网关，然后将数据集连接到此数据源。Follow these steps to add your on-premises SQL Server database as a data source to a gateway and then connect your dataset to this data source.

登录到 Power BI。Sign in to Power BI. 在右上角，选择设置齿轮图标，然后选择“设置” 。In the upper-right corner, select the settings gear icon and then select Settings.

在“数据集”选项卡上，选择“AdventureWorksProducts”数据集，以便可通过数据网关连接到本地 SQL Server 数据库 。On the Datasets tab, select the dataset AdventureWorksProducts, so you can connect to your on-premises SQL Server database through a data gateway.

展开网关连接，并验证是否至少列出了一个网关 。Expand Gateway connection and verify that at least one gateway is listed. 如果没有网关，请参阅本教程前面的先决条件部分，获取有关安装和配置网关的产品文档链接。If you don't have a gateway, see the Prerequisites section earlier in this tutorial for a link to the product documentation for installing and configuring a gateway.

在“操作”下，展开切换按钮以查看数据源，然后选择“添加到网关”链接 。Under Actions, expand the toggle button to view the data sources and select the Add to gateway link.

备注

如果你不是网关管理员且不想自己安装网关，请与组织中的网关管理员联系。If you're not a gateway administrator and don't want to install a gateway yourself, contact a gateway administrator in your organization. 他们可创建所需的数据源定义，以将数据集连接到 SQL Server 数据库。They can create the required data source definition to connect your dataset to your SQL Server database.

在“网关”管理页的“数据源设置”标签上，输入并验证以下信息，然后选择“添加” 。On the Gateways management page, on the Data Source Settings tab, enter and verify the following information, and select Add.

选项Option

ValueValue

数据源名称Data Source Name

AdventureWorksProductsAdventureWorksProducts

数据源类型Data Source Type

SQL ServerSQL Server

服务器Server

SQL Server 实例名称，例如 SQLServer01(必须与 Power BI Desktop 中指定的名称相同)。The name of your SQL Server instance, such as SQLServer01 (must be identical to what you specified in Power BI Desktop).

DatabaseDatabase

SQL Server 数据库名称，例如 AdventureWorksDW(必须与 Power BI Desktop 中指定的名称相同)。The name of your SQL Server database, such as AdventureWorksDW (must be identical to what you specified in Power BI Desktop).

身份验证方法Authentication Method

Windows 或 Basic(通常为 Windows)。Windows or Basic (typically Windows).

用户名Username

用于连接到 SQL Server 的用户帐户。The user account you use to connect to SQL Server.

密码Password

用于连接到 SQL Server 的帐户的密码。The password for the account you use to connect to SQL Server.

在“数据集”选项卡上，再次展开“网关连接”部分 。On the Datasets tab, expand the Gateway connection section again. 选择你配置的数据网关(它显示在安装它的计算机上运行的状态)，然后选择“应用” 。Select the data gateway you configured, which shows a Status of running on the machine where you installed it, and select Apply.

配置刷新计划Configure a refresh schedule

现在，你已通过数据网关将 Power BI 中的数据集连接到本地 SQL Server 数据库，接下来请按照以下步骤配置刷新计划。Now you've connected your dataset in Power BI to your SQL Server database on-premises through a data gateway, follow these steps to configure a refresh schedule. 按计划刷新数据集有助于确保报表和仪表板具有最新数据。Refreshing your dataset on a scheduled basis helps to ensure that your reports and dashboards have the most recent data.

在导航窗格中，打开“我的工作区” “数据集” > 。In the nav pane, open My Workspace > Datasets. 为 AdventureWorksProducts 数据集选择省略号 (...)，然后选择“计划刷新”。Select the ellipsis (. . .) for the AdventureWorksProducts dataset, then select Schedule refresh.

备注

请确保为 AdventureWorksProducts 数据集选择省略号，而不是为名称相同的报表选择省略号 。Make sure you select the ellipsis for the AdventureWorksProducts dataset and not the ellipsis for the report with the same name. AdventureWorksProducts 报表的上下文菜单没有“计划刷新”选项 。The context menu of the AdventureWorksProducts report does not include a Schedule refresh option.

在“计划刷新”部分的“不断更新数据”下，将刷新设置为“开” 。In the Scheduled refresh section, under Keep your data up to date, set refresh to On.

选择适当的刷新频率(本例中为每日)，然后在“时间”下选择“添加其他时间”，以指定所需刷新时间(本例中为上下午 6:30) 。Select an appropriate Refresh frequency, ( Daily for this example), and then under Time, select Add another time to specify the desired refresh time (6:30 AM and PM for this example).

备注

如果数据集位于共享容量上，则每天最多可配置 8 个时间段；如果位于 Power BI Premium 上，则每天最多可配置 48 个时间段。You can configure up to 8 daily time slots if your dataset is on shared capacity, or 48 time slots on Power BI Premium.

将“向我发送刷新失败通知电子邮件”复选框保留为启用状态，并选择“应用” 。Leave the checkbox Send refresh failure notification emails to me enabled and select Apply.

执行按需刷新Perform an on-demand refresh

你现已配置刷新计划，Power BI 会在下一计划时间(15 分钟内)刷新数据集。Now that you've configured a refresh schedule, Power BI refreshes your dataset at the next scheduled time, within a margin of 15 minutes. 如果要加快数据刷新速度以执行网关和数据源配置测试等操作，请使用导航窗格的数据集菜单中的“立即刷新”选项进行按需刷新 。If you want to refresh the data sooner, such as to test your gateway and data source configuration, perform an on-demand refresh by using the Refresh Now option in the dataset menu in the nav pane. 按需刷新不影响下一计划刷新时间，但会计入在上一部分中提到的每日刷新限制。On-demand refreshes don't affect the next scheduled refresh time, but they count against the daily refresh limit, mentioned in the previous section.

为便于说明，通过使用 SQL Server Management Studio (SSMS) 更新 AdventureWorksDW 数据库中的 DimProduct 表来模拟对示例数据的更改。For illustration purposes, simulate a change to the sample data by updating the DimProduct table in the AdventureWorksDW database using SQL Server Management Studio (SSMS).

UPDATE [AdventureWorksDW].[dbo].[DimProduct]

SET ListPrice = 5000

WHERE EnglishProductName ='Road-250 Red, 58'

现按照以下步骤操作，使更新后的数据可通过网关连接传输到数据集并进入 Power BI 中的报表。Now follow these steps so the updated data can flow through the gateway connection to the dataset and into the reports in Power BI.

在 Power BI 服务的导航窗格中，选择并展开“我的工作区” 。In the Power BI Service, in the nav pane, select and expand My Workspace.

在“数据集”下，针对 AdventureWorksProducts 数据集选择省略号 (...)，然后选择“立即刷新” 。Under Datasets, for the AdventureWorksProducts dataset, select the ellipsis (. . .) then select Refresh now.

请注意，右上角显示 Power BI 正在准备执行请求的刷新。Note in the upper right corner, that Power BI is preparing to perform the requested refresh.

选择“我的工作区”>“报表”>“AdventureWorksProducts”。Select My Workspace > Reports > AdventureWorksProducts. 查看更新后的数据是如何传输的，现标价最高的产品是“Road-250 Red, 58” 。See how the updated data flowed through, and the product with the highest list price is now Road-250 Red, 58.

查看刷新历史记录Review the refresh history

最好在刷新历史记录中定期查看既往刷新周期的结果。It is a good idea to check the outcomes of past refresh cycles periodically in the refresh history. 数据库凭据可能已过期，或者所选网关在计划刷新到期时可能已脱机。Database credentials might have expired, or the selected gateway might have been offline when a scheduled refresh was due. 按照以下步骤检查刷新历史记录并检查问题。Follow these steps to examine the refresh history and check for issues.

在 Power BI 用户界面的右上角，选择设置齿轮图标，然后选择“设置” 。In the upper-right corner of the Power BI user interface, select the settings gear icon and then select Settings.

切换到“数据集”并选择要检查的数据集，例如 AdventureWorksProducts 。Switch to Datasets and select the dataset, such as AdventureWorksProducts, you want to examine.

选择“刷新历史记录”链接，打开“刷新历史记录”对话框 。Select the Refresh history link to open the Refresh history dialog.

在“计划”选项卡上，请注意过去计划的刷新和按需刷新及其“开始”和“结束”时间，状态为“已完成”，这表示 Power BI 已成功刷新。On the Scheduled tab, notice the past scheduled and on-demand refreshes with their Start and End times, and a Status of Completed, which indicates that Power BI performed the refreshes successfully. 对于失败的刷新，可看到错误消息并检查错误详细信息。For failed refreshes, you can see the error message and examine error details.

备注

OneDrive 选项卡仅与连接到 OneDrive 或 SharePoint Online 上的 Power BI Desktop 文件、Excel 工作簿或 CSV 文件的数据集相关，详见 Power BI 中的数据刷新。The OneDrive tab is only relevant for datasets connected to Power BI Desktop files, Excel workbooks, or CSV files on OneDrive or SharePoint Online, as explained in more detail in Data refresh in Power BI.

清理资源Clean up resources

如果你不想再使用示例数据，请在 SQL Server Management Studio (SSMS) 中删除该数据库。If you don't want to use the sample data anymore, drop the database in SQL Server Management Studio (SSMS). 如果你不想使用 SQL Server 数据源，请从数据网关中删除该数据源。If you don't want to use the SQL Server data source, remove the data source from your data gateway. 如果安装数据网关的目的只是要完成本教程，请考虑卸载它。Also consider uninstalling the data gateway if you only installed it for the purposes of completing this tutorial. 还应删除上传 AdventureWorksProducts.pbix 文件时由 Power BI 创建的 AdventureWorksProducts 数据集和 AdventureWorksProducts 报表。You should also delete the AdventureWorksProducts dataset and AdventureWorksProducts report that Power BI created when you uploaded the AdventureWorksProducts.pbix file.

后续步骤Next steps

在本教程中，你学习了如何将本地 SQL Server 数据库中的数据导入 Power BI 数据集，还学习了如何按计划和按需刷新此数据集，让使用此数据集的报表和仪表板在 Power BI 中保持更新状态。In this tutorial, you've explored how to import data from an on-premises SQL Server database into a Power BI dataset and how to refresh this dataset on a scheduled and on-demand basis to keep the reports and dashboards that use this dataset updated in Power BI. 接下来，你可在 Power BI 中详细了解如何管理数据网关和数据源。Now you can learn more about managing data gateways and data sources in Power BI. 查看“Power BI 中的数据刷新”这篇概念性文章可能也是一个好方法。It might also be a good idea to review the conceptual article Data Refresh in Power BI.