本地数据网关故障排查Troubleshooting the on-premises data gateway

本文介绍了使用本地数据网关时可能会遇到的一些常见问题。This article discusses some common issues you may encounter when using the on-premises data gateway.

备注

如果遇到下面未列出的问题,可以在下列位置中寻求进一步的帮助。If you encounter an issue that is not listed below, you can ask for further assistance in the following locations.

更新到最新版本Update to the latest version

网关版本过期后,可能会遇到很多问题。A lot of issues can surface when the gateway version is out of date. 好的常规做法是确保所使用的是最新版本。It is a good general practice to make sure you are on the latest version. 如果你已经一个月或更长时间没有升级网关,可能需要考虑安装网关最新版本,并检查此问题是否会重现。If you haven't updated the gateway for a month, or longer, you may want to consider installing the latest version of the gateway and see if you can reproduce the issue.

常见问题Common issues

以下是一些常见的问题及解决方案,这些解决方案已帮助了许多处于 Internet 访问受限环境的客户。Here are a few common issues and resolutions that have helped a number of customers in environments that restrict internet access.

对代理服务器的身份验证Authentication to proxy server

代理可能需要对域用户帐户进行身份验证。Your proxy may require authentication from a domain user account. 默认情况下,网关使用 Windows 服务登录用户的服务 SID。By default, the gateway uses a Service SID for the windows service log on user. 将登录用户更改为域用户可有助于完成此操作。Changing the log on user to a domain user can help with this. 有关详细信息,请参阅将网关服务帐户更改为域用户For more information, see Changing the gateway service account to a domain user.

你的代理服务器仅支持端口 80 和 443 通信Your proxy only allows ports 80 and 443 traffic

部分代理服务器将通信限制为仅端口 80 和 443。Some proxies restrict traffic to only ports 80 and 443. 默认情况下,与 Azure 服务总线的通信将发生在除 443 之外的端口上。By default, communication to Azure Service Bus will occur on ports other than 443.

可以使用 HTTPS 替代直接 TCP,以强制网关与 Azure 服务总线通信。You can force the gateway to communicate with Azure Service Bus using HTTPS instead of direct TCP. 需要修改 Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config 文件。You will need to modify the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file. 将值从 AutoDetect 更改为 HttpsChange the value from AutoDetect to Https. 默认情况下,此文件位于 C:\Program Files\On-premises data gatewayThis file is located, by default, at C:\Program Files\On-premises data gateway.

<setting name="ServiceBusSystemConnectivityModeString" serializeAs="String">
    <value>Https</value>
</setting>

安装Installation

错误:无法将用户添加到组。Error: Failed to add user to group. (-2147463168 PBIEgwService Performance Log Users )(-2147463168 PBIEgwService Performance Log Users )

如果尝试在域控制器上安装网关,你可能会收到此错误。You may receive this error if you are trying to install the gateway on a domain controller. 不支持在域控制器上部署。Deploying on a domain controller is not supported. 你需要在不是域控制器的计算机上部署网关。You will need to deploy the gateway on a machine that is not a domain controller.

配置Configuration

如何重启网关How to restart the gateway

因为网关作为 Windows 服务运行,因此可使用多种方式进行其启动和停止。The gateway runs as a Windows service, so you can start and stop it in multiple ways. 例如,可以在网关正在运行的计算机上使用提升权限打开命令提示符,然后运行以下命令之一:For example, you can open a command prompt with elevated permissions on the machine where the gateway is running and then run either of these commands:

  • 若要停止服务,则运行该命令:To stop the service, run this command:

    ''' net stop PBIEgwService '''''' net stop PBIEgwService '''

  • 若要启动服务,则运行该命令:To start the service, run this command:

    ''' net start PBIEgwService '''''' net start PBIEgwService '''

错误:无法创建网关。Error: Failed to create gateway. 请重试。Please try again.

所有详细信息均可用,但对 Power BI 服务的调用返回了一个错误。All of the details are available, but the call to the Power BI service returned an error. 将显示错误和一个活动 ID。The error, and an activity id, will be displayed. 这可能是不同的原因导致的。This could happen for different reasons. 你可以收集并查看以下所述的日志,获取更多详细信息。You can collect, and review, the logs, as mentioned below, to get more details.

这也可能是由于代理配置问题导致的。This could also be due to proxy configuration issues. 用户界面现在允许代理配置。The user interface does now allow for proxy configuration. 你可以了解有关进行代理服务器配置更改的详细信息You can learn more about making proxy configuration changes

错误:未能更新网关详细信息。Error: Failed to update gateway details. 请重试。Please try again.

从 Power BI 服务到网关收到了信息。Information was received from the Power BI service, to the gateway. 信息已传递到本地 Windows 服务,但它无法返回值。The information was passed onto the local windows service, but it failed to return. 或者,对称密钥生成失败。Or, a symmetric key generation failed. 内部异常将显示在显示详细信息下。The inner exception will be displayed under Show details. 你可以收集并查看以下所述的日志,获取更多详细信息。You can collect, and review, the logs, as mentioned below, to get more details.

错误:Power BI 服务报告本地网关无法访问。Error: Power BI service reported local gateway as unreachable. 请重启网关,然后重试。Please restart the gateway and try again.

配置结束时,将再次调用 Power BI 服务以验证网关。At the end of configuration, the Power BI service will be called again to validate the gateway. Power BI 服务没有将网关报告为动态。The Power BI service does not report the gateway as live. 重启 Windows 服务可能会使通信成功。Restarting the windows service may allow the communication to be successful. 你可以收集并查看以下所述的日志,获取更多详细信息。You can collect, and review, the logs, as mentioned below, to get more details.

登录 Power BI 时出现的脚本错误Script error during sign into Power BI

在执行本地数据网关配置期间登录 Power BI 可能会看到脚本错误。You may receive a script error when signing into Power BI as part of the on-premises data gateway configuration. 安装以下安全更新应可解决该问题。Installing the following security update should resolve the issue. 这可以通过 Windows 更新进行安装。This can be installed via Windows Update.

MS16-051:Internet Explorer 的安全更新:2016 年 5 月 10 日 (KB 3154070)MS16-051: Security update for Internet Explorer: May 10, 2016 (KB 3154070)

网关配置失败,出现 null 引用异常Gateway configuration failed with a null reference exception

你可能会遇到以下类似错误。You may encounter an error similar to the following.

    Failed to update gateway details.  Please try again.
    Error updating gateway configuration.

这将包括堆栈跟踪,并且该堆栈跟踪可能包括以下内容。This will include a stack trace, and that stack trace will may include the following.

    Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.CouldNotUpdateGatewayConfigurationException: Error updating gateway configuration. ----> System.ArgumentNullException: Value cannot be null.
    Parameter name: serviceSection

如果你正从旧网关中升级,我们会保存配置文件。If you are upgrading from an older gateway, we preserve the config file. 可能存在缺失的部分。There may be a section that is missing. 当网关尝试读取它时,我们将获取上述 null 引用异常。When the gateway tries to read it, we will get the above null reference exception.

要纠正这一错误,请执行以下操作。To correct this, do the following.

  1. 卸载网关。Uninstall the gateway.
  2. 删除以下文件夹。Delete the following folder.

     c:\Program Files\on-premises data gateway
    
  3. 重新安装网关。Reinstall the gateway.
  4. 酌情应用恢复密钥以还原现有网关。Optionally apply the recovery key to restore an existing gateway.

对 TLS 1.1/1.2 的支持Support for TLS 1.1/1.2

自 2017 年 8 月更新起,本地数据网关默认使用传输层安全 (TLS) 1.1 或 1.2 与 Power BI 服务进行通信。With the August 2017 update and beyond, the on-premises data gateway uses Transport Layer Security (TLS) 1.1 or 1.2 to communicate with the Power BI service by default. 以前版本的本地数据网关默认使用 TLS 1.0。Previous versions of the on-premises data gateway use TLS 1.0 by default. 对 TLS 1.0 的支持将于 2017 年 11 月 1 日结束,因此,在此之前必须将本地数据网关安装升级到 2017 年 8 月版本或更新版本,以确保网关继续运行。On November 1st 2017 support for TLS 1.0 will end, so by then you must upgrade your on-premises data gateway installations to the August 2017 release or newer to ensure your gateways continue to operate.

值得注意的是,TLS 1.0 在 11 月 1 日之前仍受本地数据网关支持,并由网关用作回退机制。It's important to note that TLS 1.0 is still supported by the on-premises data gateway prior to November 1st, and is used by the gateway as a fallback mechanism. 要确保所有网关流量使用 TLS 1.1 或 1.2(并防止在网关上使用 TLS 1.0),必须在运行网关服务的计算机上添加或修改以下注册表项:To ensure all gateway traffic uses TLS 1.1 or 1.2 (and to prevent the use of TLS 1.0 on your gateway), you must add or modify the following registry keys on the machine running the gateway service:

    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework\v4.0.30319]"SchUseStrongCrypto"=dword:00000001
    [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework\v4.0.30319]"SchUseStrongCrypto"=dword:00000001

备注

添加或修改这些注册表项会将更改应用于所有 .NET 应用程序。Adding or modifying these registry keys applies the change to all .NET applications. 有关影响其他应用程序的 TLS 的注册表更改的信息,请参阅传输层安全性 (TLS) 注册表设置For information about registry changes that affect TLS for other applications, see Transport Layer Security (TLS) registry settings.

数据源Data sources

错误:无法连接。Error: Unable to Connect. 详细信息:“无效的连接凭据”Details: "Invalid connection credentials"

显示详细信息中,应显示从数据源收到的错误消息。Within Show details, it should display the error message received from the data source. 对于 SQL Server,你应看到如下所示的内容。For SQL Server, you should see something like the following.

Login failed for user 'username'.

验证你具有正确的用户名和密码。Verify that you have the correct username and password. 还会验证这些凭据是否可以成功地连接到数据源。Also verify that those credentials can successfully connect to the data source. 请确保所使用的帐户与身份验证方法匹配。Make sure the account being used matches the Authentication Method.

错误:无法连接。Error: Unable to Connect. 详细信息:“无法连接到数据库”Details: "Cannot connect to the database"

我们可以连接到服务器,但不能连接所提供的数据库。We were able to connect to the server, but not to the database supplied. 验证该数据库的名称以及该用户凭据有适当的权限来访问该数据库。Verify the name of the database, and that the user credential as the proper permission to access that database.

显示详细信息中,应显示从数据源收到的错误消息。Within Show details, it should display the error message received from the data source. 对于 SQL Server,你应看到如下所示的内容。For SQL Server, you should see something like the following.

Cannot open database "AdventureWorks" requested by the login. The login failed. Login failed for user 'username'.

错误:无法连接。Error: Unable to Connect. 详细信息:“数据网关中的未知错误”Details: "Unknown error in data gateway"

此错误可能会由于不同的原因发生。This error could occur for different reasons. 请务必验证你可以从承载网关的计算机连接到数据源。Be sure to validate that you can connect to the data source from the machine hosting the gateway. 这可能是不可访问的服务器的结果。This could be the result of the server not being accessible.

显示详细信息中,你将看到错误代码 DM_GWPipeline_UnknownErrorWithin Show details, you will see an error code of DM_GWPipeline_UnknownError.

你还可以查看“事件日志”>“应用程序和服务日志” > “本地数据网关服务”,了解更多详细信息。You can also look in the Event Logs > Applications and Services Logs > on-premises data gateway Service for more details.

错误:我们在尝试连接到时遇到错误。Error: We encountered an error while trying to connect to . 详细信息:“我们已连接到数据网关,但此网关无法访问本地数据源。”Details: "We reached the data gateway, but the gateway can't access the on-premises data source."

我们无法连接指定的数据源。We were unable to connect to the specified data source. 请务必验证为该数据源所提供的信息。Be sure to validate the information provided for that data source.

显示详细信息中,你将看到错误代码 DM_GWPipeline_Gateway_DataSourceAccessErrorWithin Show details, you will see an error code of DM_GWPipeline_Gateway_DataSourceAccessError.

如果基础错误消息类似于以下内容,这意味着你正在对数据源使用的帐户不是该 Analysis Services 实例的服务器管理员。If the underlying error message is similar to the following, this means that the account you are using for the data source is not a server admin for that Analysis Services instance. 了解详细信息Learn more

The 'CONTOSO\account' value of the 'EffectiveUserName' XML for Analysis property is not valid.

如果基础错误消息类似以下消息,则可能意味着 Analysis Services 的服务帐户可能缺少 token-groups-global-and-universal (TGGAU) 目录属性。If the underlying error message is similar to the following, it could mean that the service account for Analysis Services may be missing the token-groups-global-and-universal (TGGAU) directory attribute.

The user name or password is incorrect.

具有 Windows 2000 以前版本兼容访问权限的域将启用 TGGAU 属性。Domains with Pre-Windows 2000 compatibility access will have the TGGAU attribute enabled. 但是,最新创建的域不会默认启用此属性。However, most newly created domains will not enable this attribute by default. 你可以在此处了解更多相关信息。You can read more about this here.

通过执行以下操作对此进行确认。You can confirm this by doing the following.

  1. 连接 SQL Server Management Studio 中的 Analysis Services 计算机。Connect to the Analysis Services machine within SQL Server Management Studio. 在高级连接属性中,输入问题用户的 EffectiveUserName,并检查是否会产生错误。Within the Advanced connection properties, include EffectiveUserName for the user in question and see if this reproduces the error.
  2. 可以使用 dsacls Active Directory 工具来验证是否列出了属性。You can use the dsacls Active Directory tool to validate whether the attribute is listed. 此工具通常可在域控制器上找到。This is tool is normally found on a domain controller. 你需要知道帐户的可分辨域名是什么,并将其传递给该工具。You will need to know what the distinguished domain name is for the account and pass that to the tool.

     dsacls "CN=John Doe,CN=UserAccounts,DC=contoso,DC=com"
    

    你应该在结果中看到与以下类似的内容。You want to see something similar to the following in the results.

         Allow BUILTIN\Windows Authorization Access Group
                                       SPECIAL ACCESS for tokenGroupsGlobalAndUniversal
                                       READ PROPERTY
    

若要更正此问题,你需要启用用于 Analysis Services Windows 服务的帐户上的 TGGAU。To correct this issue, you will need to enable TGGAU on account used for the Analysis Services windows service.

用户名或密码错误的另一种可能Another possibility for user name or password incorrect

如果 Analysis Services 服务器与用户位于不同的域,并且没有建立双向信任,则也可能导致此错误。This error could also be caused if the Analysis Services server is in a different domain than the users and there is not a two-way trust established.

你需要通过域管理员来验证域间的信任关系。You will need to work with your domain administrators to verify the trust relationship between domains.

在 Power BI 服务中使用 Analysis Services 的“获取数据”功能时,无法查看数据网关数据源Unable to see the data gateway data sources in the 'Get Data' experience for Analysis Services from the Power BI service

确保你的帐户列于网关配置中数据源的用户选项卡。Make sure that your account is listed in the Users tab of the data source within the gateway configuration. 如果你没有权限访问网关,请与网关管理员核对,并请他们进行验证。If you don't have access to the gateway, check with the administrator of the gateway and ask them to verify. 用户列表中的帐户可查看列于 Analysis Services 列表中的数据源。Only accounts in the Users list will see the data source listed in the Analysis Services list.

数据集Datasets

错误:对于此行没有足够的空间。Error: There is not enough space for this row.

如果有大于 4 MB 大小的单个行,则会出现此错误。This will occur if you have a single row greater than 4 MB in size. 需要从数据源确定行是什么,并尝试将其筛选出或减少该行的大小。You will need to determine what the row is from your data source and attempt to filter it out or reduce the size for that row.

错误:提供的服务器名称与 SQL Server SSL 证书上的服务器名称不一致。Error: The server name provided doesn't match the server name on the SQL Server SSL Certificate.

如果证书 CN 针对的是服务器完全限定的域名 (FQDN),而你只提供了服务器 netbios 名称,就会生成此错误。This can occur when the certificate CN is for the servers fully qualified domain name (FQDN) but you only supplied the netbios name for the server. 这会导致证书不匹配。This will cause a mismatch for the certificate. 若要解决此问题,你需要将网关数据源和 PBIX 文件内的服务器名称设置为使用服务器的 FQDN。To resolve this issue, you will need to make the server name within the gateway data source, and the PBIX file, to use the FQDN of the server.

我在配置计划的刷新时看不到本地数据网关。I don't see the on-premises data gateway persent when configuring scheduled refresh.

这可能是由几种不同的原因所致。This could be because of a few different scenarios.

  1. 你在 Power BI Desktop 中输入的服务器和数据库名称和网关数据源中的名称不一致。The server and database name don't match between what was entered in Power BI Desktop and the data source configured for the gateway. 值必须相同。These need to be the same values. 不区分大小写。They are not case sensitive.
  2. 网关配置中数据源的用户选项卡上未列出你的帐户。Your account is not listed in the Users tab of the data source within the gateway configuration. 必须与要添加到相应列表的网关的管理员联系。You will need to get with the administrator of the gateway to be added to that list.
  3. Power BI Desktop 文件中有多个数据源,并不是所有这些数据源都配置为网关数据源。Your Power BI Desktop file has multiple data sources within it and not all of those data sources are configured with the gateway. 必须定义每个网关数据源,这样相应网关才能在计划的刷新内显示。You will need to have each data source defined with the gateway for the gateway to show up within Scheduled Refresh.

错误:网关客户端上收到的未压缩数据已超出限制。Error: The received uncompressed data on the gateway client has exceeded limit.

每个表的未压缩数据量的确切限制为 10GB。The exact limitation is 10 GB of uncompressed data per table. 如果遇到此问题,可以使用实用选项来优化和避免此类问题发生。If you are hitting this issue, there are good options to optimize and avoid the issue. 具体而言,减少使用高度重复内容、过长的字符串值,并改用规范化的键或删除列(如果不使用)将会有所帮助。In particular, reducing the use of highly repetitive, long string values and instead using a normalized key or removing the column (if not in use) will help.

报表Reports

报表无法访问数据源,因为你无权通过本地数据网关访问我们的数据源。Report could not access the data source because you do not have access to our data source via an on-premises data gateway.

这通常是由于以下原因之一导致的。This is usually caused by one of the following.

  1. 数据源信息与基础数据集中的内容不匹配。The data source information does not match what is in the underlying dataset. 为本地数据网关定义的数据源和为 Power BI Desktop 提供的内容之间的服务器和数据库名称需要匹配。The server and database name need to match between the data source defined for the on-premises data gateway and what you supply within Power BI Desktop. 如果在 Power BI Desktop 中使用 IP 地址,则用于本地数据网关的数据源也需要使用 IP 地址。If you use an IP Address in Power BI Desktop, the data source, for the on-premises data gateway, needs to use an IP Address as well.
  2. 你的组织内的任何网关上均没有可用的数据源。There is not a data source available on any gateway within your organization. 可以在新的或现有的本地数据网关上配置数据源。You can configure the data source on a new, or existing, on-premises data gateway.

错误:数据源访问错误。Error: Data source access error. 请联系网关管理员。Please contact the gateway administrator.

如果此报表正在使用实时 Analysis Services 连接,你可能遇到的问题是值被传入无效或无权访问 Analysis Services 计算机的 EffectiveUserName 中。If this report is making use of a live Analysis Services connection, you could be encountering an issue with a value being passed to EffectiveUserName that is either not valid, or doesn't have permissions on the Analysis Services machine. 通常来说,出现身份验证问题是由于传给 EffectiveUserName 的值与本地用户主体名称 (UPN) 不匹配。Typically, an authentication issue is due to the fact that the value being passed for EffectiveUserName doesn't match a local user principal name (UPN).

若要对此进行确认,可执行以下操作。To confirm this, you can do the following.

  1. 网关日志中查找有效的用户名。Find the effective username within the gateway logs.
  2. 传递值后,验证其是否正确。Once you have the value being passed, validate that it is correct. 如果它是你的用户,可以从命令提示符处使用以下命令,查看 UPN 应该是什么。If it is your user, you can use the following command from a command prompt to see what the UPN should be. UPN 类似电子邮件地址。The UPN will look like an email address.

     whoami /upn
    

或者,你可以查看 Power BI 从 Azure Active Directory 获取的内容。Optionally, you can see what Power BI gets from Azure Active Directory.

  1. 浏览到 https://graphexplorer.cloudapp.netBrowse to https://graphexplorer.cloudapp.net.
  2. 选择右上角的“登录”。Select Sign in in the upper right.
  3. 运行以下查询。Run the following query. 你将看到相当大的 JSON 响应。You will see a rather large JSON response.

     https://graph.windows.net/me?api-version=1.5
    
  4. 查找 userPrincipalNameLook for userPrincipalName.

如果你的 Azure Active Directory UPN 与本地 Active Directory UPN 不匹配,则可以使用映射用户名功能将其替换为有效的值。If your Azure Active Directory UPN doesn't match your local Active Directory UPN, you can use the Map user names feature to replace it with a valid value. 或者,可以通过租户管理员或本地 Active Directory 管理员更改 UPN。Or you can work with either your tenant admin, or local Active Directory admin, to get your UPN changed.

防火墙或代理Firewall or Proxy

有关为你的网关提供代理信息的信息,请参阅为 Power BI Gateway 配置代理设置For information on providing proxy information for your gateway, see Configuring proxy settings for the Power BI gateways.

可以在 PowerShell 提示符处运行 Test-NetConnection 以进行测试,从而确定防火墙或代理是否可能会阻止连接。You can test to see if your firewall, or proxy, may be blocking conections by running Test-NetConnection from a PowerShell prompt. 这将测试与 Azure 服务总线的连接性。This will test connectivity to the Azure Service Bus. 这仅测试网络连接,与云服务器服务或网关没有任何关系。This only tests network connectivity and doesn't have anything to do with the cloud server service or the gateway. 它有助于确定你的计算机是否可以实际连接到互联网。It helps to determine if your machine can actually get out to the internet.

Test-NetConnection -ComputerName watchdog.servicebus.windows.net -Port 9350

备注

Test-NetConnection 仅适用于 Windows Server 2012 R2 及更高版本。Test-NetConnection is only available on Windows Server 2012 R2 and later. 还适用于 Windows 8.1 及更高版本。It is also available on Windows 8.1 and later. 在旧版操作系统中,可以使用 Telnet 测试端口连接性。On earlier OS versions, you can use Telnet to test port connectivity.

结果应与以下所示类似。The results should look similar to the following. 不同之处在于 TcpTestSucceeded。The difference will be with TcpTestSucceeded. 如果 TcpTestSucceeded 不为 true,则你可能会被防火墙阻止。If TcpTestSucceeded is not true, then you may be blocked by a firewall.

ComputerName           : watchdog.servicebus.windows.net
RemoteAddress          : 70.37.104.240
RemotePort             : 5672
InterfaceAlias         : vEthernet (Broadcom NetXtreme Gigabit Ethernet - Virtual Switch)
SourceAddress          : 10.120.60.105
PingSucceeded          : False
PingReplyDetails (RTT) : 0 ms
TcpTestSucceeded       : True

如果你想做到面面俱到,请将 ComputerNamePort 值替换为对端口列出的相应项If you want to be exhaustive, substitute the ComputerName and Port values with those listed for ports

防火墙可能也会阻止 Azure 服务总线与 Azure 数据中心之间的连接。The firewall may also be blocking the connections that the Azure Service Bus makes to the Azure data centers. 如果是这种情况,那么你需要把这些数据中心中你所在区域的 IP 地址列入白名单(取消阻止)。If that is the case, you will want to whitelist (unblock) the IP addresses for your region for those data centers. 你可以在此处获得 Azure IP 地址列表。You can get a list of Azure IP addresses here.

通过执行以下操作,可以找到你所在的数据中心区域:You can find the data center region you are in by doing the following:

  1. 选择 Power BI 服务右上角方的 ?Select the ? in the upper right of the Power BI service.
  2. 选择“关于 Power BI”。Select About Power BI.
  3. 你的数据区域将被列入“你的数据存储于”。Your data region will be listed in Your data is stored in.

如果你仍未前往任何位置,则可以尝试使用 fiddler 或 netsh 一类的工具来获取网络跟踪,不过这些都是高级收集方法,并且你在分析所收集的数据时可能需要帮助。If you are still not getting anywhere, you could try getting a network trace using a tool like fiddler or netsh, although these are advanced collection methods and you may need assistance in analyzing the collected data. 可以联系支持人员以获得帮助。You can contact support for assistance.

性能Performance

性能计数器Performance Counters

有多个性能计数器可用于度量网关的活动。There are a number of performance counters that can be used to gauge the activities for the gateway. 这些计数器有助于你判断是否出现高活动负载,以及是否需要新建网关。These can be helpful to understanding if we have a large load of activity and may need to make a new gateway. 这些计数器不反映某个活动所花的时间。These counters will not reflect how long something takes.

可以通过 Windows 性能监视器工具访问这些计数器。These counters can be access through the Windows Performance Monitor tool.

以下是这些计数器的一般分组。There are general groupings of these counters.

计数器类型Counter Type 说明Description
ADO.NETADO.NET 适用于任何 DirectQuery 连接。This is used for any DirectQuery connection.
ADOMDADOMD 适用于 Analysis Services 2014 和更早版本。This is used for Analysis Services 2014 and earlier.
OLEDBOLEDB 供某些数据源使用。This is used by certain data sources. 包括 SAP HANA 和 Analysis Service 2016 及更高版本。This includes SAP HANA and Analysis Service 2016 or later.
MashupMashup 包括任何导入的数据源。This includes any imported data source. 如果要执行计划刷新或按需刷新,则该刷新将通过 mashup 引擎进行。If you are scheduling refresh or doing an on-demand refresh, it will go through the mashup engine.

以下是可用的性能计数器的列表。Here is a listing of the available performance counters.

计数器Counter 说明Description
执行的 ADO.NET 开放式连接次数/秒# of ADO.NET open connection executed / sec 每秒执行的 ADO.NET 开放式连接操作数量(成功或失败)。Number of ADO.NET open connection actions executed per second (succeeded or failed).
ADO.NET 开放式连接失败次数/秒# of ADO.NET open connection failed / sec 每秒 ADO.NET 开放式连接操作失败次数。Number of ADO.NET open connections actions failed per second.
执行的 ADO.NET 查询数/秒# of ADO.NET queries executed / sec 每秒执行的 ADO.NET 查询数量(成功或失败)。Number of ADO.NET queries executed per second (succeeded or failed).
ADO.NET 查询失败数/秒# of ADO.NET queries failed / sec 每秒执行的 ADO.NET 查询失败数。Number of ADO.NET failed queries executed per second.
执行的 ADOMD 开放式连接次数/秒# of ADOMD open connection executed / sec 每秒执行的 ADOMD 开放式连接操作数量(成功或失败)。Number of ADOMD open connection actions executed per second (succeeded or failed).
ADOMD 开放式连接失败次数/秒# of ADOMD open connection failed / sec 每秒 ADOMD 开放式连接操作失败次数。Number of ADOMD open connection actions failed per second.
执行的 ADOMD 查询数/秒# of ADOMD queries executed / sec 每秒执行的 ADOMD 查询数量(成功或失败)。Number of ADOMD queries executed per second (succeeded or failed).
ADOMD 查询失败数/秒# of ADOMD queries failed / sec 每秒执行的 ADOMD 查询失败数。Number of ADOMD failed queries executed per second.
执行的所有开放式连接次数/秒# of all open connection executed / sec 每秒执行的开放式连接操作数量(成功或失败)。Number of open connection actions executed per second (succeeded or failed).
开放式连接失败总数/秒# of all open connection failed / sec 每秒执行的开放式连接操作失败数量。Number of failed open connection actions executed per second.
执行的查询总数/秒# of all queries executed / sec 每秒执行的查询数量(成功或失败)。Number of queries executed per second (succeeded or failed).
ADO.NET 连接池中的项目数# of items in the ADO.NET connection pool ADO.NET 连接池中的项目数Number of items in the ADO.NET connection pool.
OLEDB 连接池中的项目数# of items in the OLEDB connection pool OLEDB 连接池中的项目数Number of items in the OLEDB connection pool.
服务总线池中的项目数# of items in the Service Bus pool 服务总线池中的项目数Number of items in the Service Bus pool.
执行的 Mashup 开放式连接次数/秒# of Mashup open connection executed / sec 每秒执行的 Mashup 开放式连接操作数量(成功或失败)。Number of Mashup open connection actions executed per second (succeeded or failed).
Mashup 开放式连接失败数/秒# of Mashup open connection failed / sec 每秒 Mashup 开放式连接操作失败次数。Number of Mashup open connection actions failed per second.
执行的 Mashup 查询数/秒# of Mashup queries executed / sec 每秒执行的 Mashup 查询数量(成功或失败)。Number of Mashup queries executed per second (succeeded or failed).
Mashup 查询失败数/秒# of Mashup queries failed / sec 每秒执行的 Mashup 查询失败数。Number of Mashup failed queries executed per second
多结果集 OLEDB 查询失败数/秒# of multiple result set OLEDB queries failed / sec 每秒执行的多结果集 OLEDB 查询失败数。Number of multiple resultset OLEDB failed queries executed per second.
执行的 OLEDB 多结果集查询数/秒# of OLEDB multiple resultset queries executed / sec 每秒执行的 OLEDB 多结果集查询数量(成功或失败)。Number of OLEDB multiple resultset queries executed per second (succeeded or failed).
执行的 OLEDB 开放式连接次数/秒# of OLEDB open connection executed / sec 每秒执行的 OLEDB 开放式连接操作数量(成功或失败)。Number of OLEDB open connection actions executed per second (succeeded or failed).
OLEDB 开放式连接失败数/秒# of OLEDB open connection failed / sec 每秒 OLEDB 开放式连接操作失败次数。Number of OLEDB open connection actions failed per second.
执行的 OLEDB 查询数/秒# of OLEDB queries executed / sec 每秒执行的 OLEDB 多结果集查询数量(成功或失败)。Number of OLEDB multiple resultset queries executed per second (succeeded or failed).
OLEDB 查询失败数/秒# of OLEDB queries failed / sec 每秒执行的多结果集 OLEDB 查询失败数。Number of OLEDB mutiple resultset failed queries executed per second.
执行的 OLEDB 单结果集查询数/秒# of OLEDB single resultset queries executed / sec 每秒执行的 OLEDB 单结果集查询数量(成功或失败)。Number of OLEDB single resultset queries executed per second (succeeded or failed).
查询失败数/秒# of queries failed / sec 每秒执行的查询失败数。Number of failed queries executed per second.
单结果集 OLEDB 查询失败数/秒# of single result set OLEDB queries failed / sec 每秒执行的单结果集 OLEDB 查询失败数。Number of single resultset OLEDB failed queries executed per second.

查看低性能查询Reviewing slow performing queries

可能会发现通过网关的响应较比较慢。You may find that response through the gateway is slow. 这可能发生在执行 DirectQuery 查询或在刷新导入数据集时。This could be for DirectQuery queries or when refreshing your imported dataset. 可以启用附加的日志记录用于输出查询及其执行时间,以帮助了解哪些查询执行缓慢。You can enable additional logging to output queries and their timings to help understand what is performing slow. 找到长时间运行的查询后,可能需要对数据源进行额外修改才能优化查询性能。When you find a long running query, it may require additional modification on your data source to tune query performance. 例如,调整 SQL Server 查询的索引。For example, adjusting indexes for a SQL Server query.

需要修改两个配置文件才能确定查询的持续时间。You will need to modify two configuration files to determine the duration of a query.

Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.configMicrosoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

在 Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config 文件中将 EmitQueryTraces 的值从 False 更改为 TrueWithin the Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config file, change the EmitQueryTraces value from False to True. 默认情况下,此文件位于 C:\Program Files\On-premises data gateway。This file is located, by default, at C:\Program Files\On-premises data gateway. 启用 EmitQueryTraces 开始对从网关发送到数据源的查询进行日志记录。Enabling EmitQueryTraces will begin to log queries that are sent from the gateway to a data source.

重要

启用 EmitQueryTraces 可能会根据网关使用情况显著增加日志大小。Enabling EmitQueryTraces could increase the log size significantly depending on gateway usage. 完成日志审阅后,需要将 EmitQueryTraces 设置为 False。Once you are done reviewing the logs, you will want to set EmitQueryTraces to False. 不建议长期将此设置保留为启用状态。It is not recommended to leave this setting enabled long term.

<setting name="EmitQueryTraces" serializeAs="String">
    <value>True</value>
</setting>

示例查询条目Example query entry

DM.EnterpriseGateway Information: 0 : 2016-09-15T16:09:27.2664967Z DM.EnterpriseGateway    4af2c279-1f91-4c33-ae5e-b3c863946c41    d1c77e9e-3858-4b21-3e62-1b6eaf28b176    MGEQ    c32f15e3-699c-4360-9e61-2cc03e8c8f4c    FF59BC20 [DM.GatewayCore] Executing query (timeout=224) "<pi>
SELECT
TOP (1000001) [t0].[ProductCategoryName],[t0].[FiscalYear],SUM([t0].[Amount])
 AS [a0]
FROM
(
(select [$Table].[ProductCategoryName] as [ProductCategoryName],
    [$Table].[ProductSubcategory] as [ProductSubcategory],
    [$Table].[Product] as [Product],
    [$Table].[CustomerKey] as [CustomerKey],
    [$Table].[Region] as [Region],
    [$Table].[Age] as [Age],
    [$Table].[IncomeGroup] as [IncomeGroup],
    [$Table].[CalendarYear] as [CalendarYear],
    [$Table].[FiscalYear] as [FiscalYear],
    [$Table].[Month] as [Month],
    [$Table].[OrderNumber] as [OrderNumber],
    [$Table].[LineNumber] as [LineNumber],
    [$Table].[Quantity] as [Quantity],
    [$Table].[Amount] as [Amount]
from [dbo].[V_CustomerOrders] as [$Table])
)
 AS [t0]
GROUP BY [t0].[ProductCategoryName],[t0].[FiscalYear] </pi>"

Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.configMicrosoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config

在 Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.dll.config 文件中将 TraceVerbosity 的值从 4 更改为 5Within the Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.dll.config file, change the TraceVerbosity value from 4 to 5. 默认情况下,此文件位于 C:\Program Files\On-premises data gateway。This file is located, by default, at C:\Program Files\On-premises data gateway. 更改此设置会将详细条目写入网关日志。Changing this setting will begin to log verbose entries to the gateway log. 其中包括显示持续时间的条目。This includes entries that show duration.

重要

将 TraceVerbosity 设置为 5 可能会大大增加日志大小,具体视网关使用情况而定。Enabling TraceVerbosity to 5 could increase the log size significantly depending on gateway usage. 完成日志审阅后,不妨将 TraceVerbosity 设置为 4Once you are done reviewing the logs, you will want to set TraceVerbosity to 4. 不建议长期将此设置保留为启用状态。It is not recommended to leave this setting enabled long term.

<setting name="TracingVerbosity" serializeAs="String">
    <value>5</value>
</setting>

活动类型Activity Types

活动类型Activty Type 说明Description
MGEQMGEQ 通过 ADO.NET 执行的查询。Queries executed over ADO.NET. 包括 DirectQuery 数据源。This includes DirectQuery data sources.
MGEOMGEO 通过 OLEDB 执行的查询。Queries executed over OLEDB. 这包括 SAP HANA 和 Analysis Services 2016。This includes SAP HANA and Analysis Services 2016.
MGEMMGEM 从 Mashup 引擎执行的查询。Queries executed from the Mashup engine. 适用于使用计划刷新或按需刷新的导入数据集。This is used with imported datasets that use scheduled refresh or refresh on-demand.

确定查询的持续时间Determine the duration of a query

若要确定查询数据源所花的时间,可以执行以下操作。To determine the time it took to query the data source, you can do the following.

  1. 打开网关日志。Open the gateway log.
  2. 搜索活动类型来查找查询。Search for an Activity Type to find the query. 此处的示例为 MGEQ。An example of this would be MGEQ.
  3. 记下次要 GUID,因为这是请求 id。Make note of the second GUID as this is the request id.
  4. 继续搜索 MGEQ,直到找到具有持续时间的 FireActivityCompletedSuccessfullyEvent 条目。Continue to search for MGEQ until you find the FireActivityCompletedSuccessfullyEvent entry with the duration. 确认该条目具有相同请求 id。持续时间以毫秒为单位。You can verify the entry has the same request id. Duration will be in milliseconds.

     DM.EnterpriseGateway Verbose: 0 : 2016-09-26T23:08:56.7940067Z DM.EnterpriseGateway    baf40f21-2eb4-4af1-9c59-0950ef11ec4a    5f99f566-106d-c8ac-c864-c0808c41a606    MGEQ    21f96cc4-7496-bfdd-748c-b4915cb4b70c    B8DFCF12 [DM.Pipeline.Common.TracingTelemetryService] Event: FireActivityCompletedSuccessfullyEvent (duration=5004)
    

    备注

    FireActivityCompletedSuccessfullyEvent 是一个详细条目。FireActivityCompletedSuccessfullyEvent is a verbose entry. 除非 TraceVerbosity 处于级别 5,否则不会记录此条目。This entry will not be logged unless TraceVerbosity is at level 5.

用于故障排除的工具Tools for troubleshooting

从网关配置器收集日志Collecting logs from the gateway configurator

可以收集多个网关日志,应始终从日志入手。There are several logs you can collect for the gateway, and you should always start with the logs. 在安装网关后收集日志的最简单方法是通过用户界面。The simplest way to collect logs after installing the gateway is through the user interface. 在“本地数据网关”用户界面中,依次选择“诊断”和页面底部附近的“导出日志”链接,如下图所示。In the On-premises data gateway user interface, select Diagnostics and then select the Export logs link near the bottom of the page, as shown in the following image.

On-prem-data-gateway-UI-logs

安装程序日志Installer logs

%localappdata%\Temp\On-premises_data_gateway_*.log

配置日志Configuration logs

%localappdata%\Microsoft\On-premises Data Gateway\GatewayConfigurator*.log

本地数据网关服务日志On-premises data gateway service logs

C:\Users\PBIEgwService\AppData\Local\Microsoft\On-premises Data Gateway\Gateway*.log

事件日志Event Logs

本地数据网关服务事件日志位于“应用程序和服务日志”下。The On-premises data gateway service event logs are present under Application and Services Logs.

On-prem-data-gateway-event-logs

Fiddler 跟踪Fiddler Trace

Fiddler 是 Telerik 提供的一款用于监视 HTTP 流量的免费工具。Fiddler is a free tool from Telerik that monitors HTTP traffic. 可以从客户端计算机通过 Power BI 服务来回查看。You can see the back and forth with the Power BI service from the client machine. 这可能会显示错误和其他相关的信息。This may show errors and other related information.

刷新历史记录Refresh History

在计划刷新中使用网关时,如果需要创建一个支持请求,刷新历史记录可帮助查看发生了什么错误,以及提供有用的数据。When using the gateway for scheduled refresh, Refresh History can help you see what errors have occurred, as well as provide useful data if you should need to create a support request. 可以查看计划刷新和按需刷新。You can view both scheduled, as well as on demand, refreshes. 下面是有关如何刷新历史记录的说明。Here is how you can get to the Refresh History.

  1. 在 Power BI 导航窗格中的数据集中,选择一个数据集>打开菜单>计划刷新In the Power BI navigation pane, in Datasets, select a dataset > Open Menu > Schedule Refresh.

  2. 设置...>计划刷新中,选择刷新历史记录In Settings for... > Schedule Refresh, select Refresh History.

若要详细了解如何对刷新方案进行故障排除,请参阅对刷新方案进行故障排除一文。For additional information about troubleshooting refresh scenarios, take a look at the Troubleshooting Refresh Scenarios article.

后续步骤Next steps

为 Power BI Gateway 配置代理设置Configuring proxy settings for the Power BI gateways
本地数据网关On-premises data gateway
本地数据网关 - 深入了解On-premises data gateway - in-depth
管理数据源 - Analysis ServicesManage your data source - Analysis Services
管理数据源 - SAP HANAManage your data source - SAP HANA
管理数据源 - SQL ServerManage your data source - SQL Server
管理数据源 - 导入/计划刷新Manage your data source - Import/Scheduled refresh
更多问题?More questions? 尝试参与 Power BI 社区Try the Power BI Community