在 Excel 中分析Analyze in Excel

有时,你可能希望使用 Excel 查看 Power BI 中的数据集并与之交互。There are times when you may want to use Excel to view and interact with a dataset that you have Power BI. 借助在 Excel 中分析,不仅可以做到这一点,还可以基于 Power BI 中存在的数据集访问 Excel 中的数据透视表、图表和切片器功能。With Analyze in Excel, you can do just that, and access PivotTable, chart, and slicer features in Excel based on the dataset that exists in Power BI.

要求Requirements

使用在 Excel 中分析时有以下几点要求:There are a few requirements for using Analyze in Excel:

  • Microsoft Excel 2010 SP1 和更高版本支持在 Excel 中分析Analyze in Excel is supported for Microsoft Excel 2010 SP1 and later.
  • Excel 数据透视表不支持对数值字段进行拖放聚合。Excel PivotTables do not support drag-and-drop aggregation of numeric fields. 你在 Power BI 中的数据集 必须具有预定义的度量值Your dataset in Power BI must have pre-defined measures.
  • 某些组织可能有组策略规则,导致无法对 Excel 安装所需的在 Excel 中分析更新。Some organizations may have Group Policy rules that prevent installing the required Analyze in Excel updates to Excel. 如果无法安装更新,请与管理员联系。If you’re unable to install the updates, check with your administrator.

工作原理How does it work?

从与 Power BI 中的数据集或报表关联的省略号菜单 (…) 中选择在 Excel 中分析之后,Power BI 会创建一个 .ODC 文件并将其从浏览器下载到你的计算机。When you select Analyze in Excel from the ellipses menu (the ...) associated with a dataset or report in Power BI, Power BI creates an .ODC file and downloads it from the browser to your computer.

在 Excel 中打开该文件后,将显示空的数据透视表字段列表以及来自 Power BI 数据集的表、字段和度量值。When you open the file in Excel, an empty PivotTable and Fields list appears with the tables, fields, and measures from the Power BI dataset. 你可以创建数据透视表和图表,并分析该数据集,就像在 Excel 中处理本地数据集一样。You can create PivotTables, charts, and analyze that dataset just as you would work with a local dataset in Excel.

.ODC 文件有一个 MSOLAP 连接字符串,该字符串连接到 Power BI 中的数据集。The .ODC file has an MSOLAP connection string that connects to your dataset in Power BI. 当你分析或处理数据时,Excel 会在 Power BI 中查询该数据集,并将结果返回到 Excel。When you analyze or work with the data, Excel queries that dataset in Power BI and returns the results to Excel. 如果该数据集使用 DirectQuery 连接到实时数据源,Power BI 会查询该数据源,并将结果返回到 Excel。If that dataset connects to a live data source using DirectQuery, Power BI queries the data source and returns the result to Excel.

“在 Excel 中分析” 对以下数据集和报表非常有用:连接 Analysis Services 表格多维 数据库的数据集和报表,或者来自包含数据模型(模型度量值是使用数据分析表达式 (DAX) 创建而成)的 Power BI Desktop 文件或 Excel 工作簿的数据集和报表。Analyze in Excel is very useful for datasets and reports that connect to Analysis Services Tabular or Multidimensional databases, or from Power BI Desktop files or Excel workbooks with data models that have model measures created using Data Analysis Expressions (DAX).

开始使用“在 Excel 中分析”Get started with Analyze in Excel

在 Power BI 中,选择报表或数据集旁边的省略号菜单(报表或数据集名称旁边的 ...),然后从出现的菜单中选择在 Excel 中分析In Power BI, select the ellipses menu beside a report or dataset (the ... beside the report or dataset name), and from the menu that appears, select Analyze in Excel.

安装 Excel 更新Install Excel updates

首次使用在 Excel 中分析时,需要对 Excel 库安装更新。When you first use Analyze in Excel, you need to install updates to the Excel libraries. 系统会提示你下载并运行 Excel 更新(这将启动 SQL_AS_OLEDDB.msi Windows Installer 程序包的安装)。You’ll be prompted to download and run Excel updates (this initiates installation of the SQL_AS_OLEDDB.msi Windows installer package). 此程序包将安装 Microsoft AS OLE DB Provider for SQL Server 2016 RC0(预览版)This package installs Microsoft AS OLE DB Provider for SQL Server 2016 RC0 (Preview).

备注

务必选中“安装 Excel 更新”对话框中的“不再显示此信息”。Be sure to check Don’t show this again in the Install Excel updates dialog. 此更新只需安装一次。You only need to install the update once.

如果需要为在 Excel 中分析再次安装 Excel 更新,可以通过 Power BI 中的下载图标下载更新,如下图所示。If you do need to install the Excel updates for Analyze in Excel again, you can download the update from the Download icon in Power BI, as shown in the following image.

登录到 Power BISign in to Power BI

即便你已经在浏览器中登录 Power BI,在 Excel 中首次打开新的 .ODC 文件时,也会看到使用 Power BI 帐户登录 Power BI 的提示。Although you’re signed in to Power BI in your browser, the first time you open a new .ODC file in Excel you may be asked to sign in to Power BI with your Power BI account. 这会对 Excel 到 Power BI 的连接进行身份验证。This authenticates the connection from Excel to Power BI.

拥有多个 Power BI 帐户的用户Users with multiple Power BI accounts

某些用户拥有多个 Power BI 帐户,这些用户可能会遇到这种情况:他们使用某个帐户登录到 Power BI,但有权访问“在 Excel 中分析”中所用数据集的帐户是不同的帐户。Some users have multiple Power BI accounts, and those users may encounter a situation where they're logged into Power BI with one account, but the account that has access to the dataset being used in Analyze in Excel is a different account. 在这些情况下,如果尝试访问“在 Excel 工作簿中分析”中所用的数据集,你可能会遇到禁止错误或登录失败。In those situations, you may get a Forbidden error or a sign-in failure when attempting to access a dataset that's being used in an Analyze in Excel workbook.

你有机会再次登录,届时你可以使用有权访问“在 Excel 中分析”所访问的数据集的 Power BI 帐户登录。You'll be provided an opportunity to sign in again, at which time you can sign in with the Power BI account that has access to the dataset being accessed by Analyze in Excel. 也可以从 Excel 的 Power BI 功能区选项卡中选择配置文件,它标识了你当前所用的登录帐户,并提供一个链接,允许你注销(随后使用不同的帐户登录)。You can also select Profile from the Power BI ribbon tab in Excel, which identifies which account you're currently logged in with, and provides a link that lets you sign out (and subsequently, sign in with a different account).

启用数据连接Enable data connections

若要在 Excel 中分析 Power BI 数据,系统会提示你验证 .odc 文件的文件名和路径,验证后请选择启用In order to analyze your Power BI data in Excel, you are prompted to verify the file name and path for the .odc file, and then select Enable.

备注

本地数据集托管在 Analysis Services (AS) 数据库中的情况下,Power BI 租户的管理员可以使用“Power BI 管理门户”禁用“在 Excel 中分析”。Administrators for Power BI tenants can use the Power BI Admin Portal to disable the use of Analyze in Excel with on-premises datasets housed in Analysis Services (AS) databases. 禁用该选项时,会对 AS 数据库禁用在 Excel 中分析,但它仍可用于其他数据集。When that option is disabled, Analyze in Excel is disabled for AS databases, but continues to be available for use with other datasets.

开始分析Analyze away

Excel 已打开并且你有一个空数据透视表,现在可以对 Power BI 数据集执行各种分析。Now that Excel has opened and you have an empty PivotTable, you're ready to do all sorts of analysis with your Power BI dataset. 借助“在 Excel 中分析”,你可以创建数据透视表、图表、添加来自其他源的数据等等,就像使用其他本地工作簿一样。Just as with other local workbooks, with Analyze with Excel you can create PivotTables, charts, add data from other sources, and so on. 当然,你也可以创建包含各种数据视图的不同工作表。And of course, you can create different worksheets with all sorts of views into your data.

备注

请务必了解,使用“在 Excel 中分析”会向具有数据集访问权限的任何用户公开所有详细信息级别的数据。It's important to know that using Analyze in Excel exposes all detail-level data to any users with permission to the dataset.

保存Save

你可以保存此 Power BI 数据集连接工作簿,就像保存任何其他工作簿一样。You can save this Power BI dataset connected workbook just like any other workbook. 但是,你不能将该工作簿发布或导回到 Power BI,因为你只能将表中有数据或具有数据模型的工作簿发布或导入到 Power BI。However, you cannot publish or import the workbook back into Power BI because you can only publish or import workbooks into Power BI that have data in tables, or that have a data model. 因为新工作簿与 Power BI 中的数据集建立了连接,所以将其发布或导入到 Power BI 无疑是在兜圈子!Since the new workbook simply has a connection to the dataset in Power BI, publishing or importing it into Power BI would be going in circles!

共享Share

保存工作簿后,可以将它与组织中的其他 Power BI 用户共享。Once your workbook is saved, you can share it with other Power BI users in your organization.

当你与某位用户共享了你的工作簿,而该用户打开该工作簿时,他将看到你在上一次保存该工作簿时显示的数据透视表和数据,但这些可能不是最新版本的数据。When a user with whom you’ve shared your workbook opens the workbook, they’ll see your PivotTables and data as they appeared when the workbook was last saved, which may not be the latest version of the data. 若要获取最新数据,用户必须使用数据功能区上的刷新按钮。To get the latest data, users must use the Refresh button on the Data ribbon. 由于工作簿连接到 Power BI 中的数据集,因此,尝试刷新工作簿的用户在首次尝试使用此方法更新时,必须登录 Power BI 并安装 Excel 更新。And since the workbook is connecting to a dataset in Power BI, users attempting to refresh the workbook must sign into Power BI and install the Excel updates the first time they attempt to update using this method.

由于用户需要刷新数据集,但 Excel Online 不支持刷新外部连接,因此,建议用户在其计算机上的桌面版 Excel 中打开工作簿。Since users will need to refresh the dataset, and refresh for external connections is not supported in Excel Online, it’s recommended that users open the workbook in the desktop version of Excel on their computer.