如何通过Power Pivot在Excel中使用SQL Server Analysis Services（SSAS）查询结果

Prerequisites for using query results from any Analysis Services model database in Excel through Power Pivot add-in are SQL Server 2014 or higher with installed Analysis Services Tabular instance (Multidimensional is installed by default), deployed Multidimensional or Tabular model database (for data source) and Power Pivot add-in for Excel 2010 or higher (Power Pivot is native add-in in Excel 2016).

通过Power Pivot加载项在Excel中使用来自任何Analysis Services模型数据库的查询结果的先决条件是SQL Server 2014或更高版本，并已安装Analysis Services Tabular实例（默认情况下安装了多维），已部署的多维或表格模型数据库（用于数据源）和Excel 2010或更高版本的Power Pivot加载项（Power Pivot是Excel 2016中的本机加载项）。

In this article, the custom SSAS Tabular model database will be used (“AW Tabular”) as an external data source.

在本文中，将使用自定义 SSAS表格模型数据库（“ AW表格”）作为外部数据源。

Power Pivot概述 (Power Pivot overview)

Being a link between the SQL Server and Excel, Power Pivot is the Excel analytical feature which relies is built on SQL Server Analysis Services. Even though it is still an Excel add-in, and uses pivot tables, as a main form, Power Pivot is acting like a localized instance of SSAS, incorporating ROLAP (Relational OLAP) storage mode, which includes in-memory data processing and using DAX as the query language, similar to the SSAS Tabular instance characteristics.

作为SQL Server和Excel之间的链接， Power Pivot是依赖于SQL Server Analysis Services构建的Excel分析功能。 尽管它仍然是Excel加载项，并使用数据透视表作为主要形式，但Power Pivot的行为类似于SSAS的本地化实例，并结合了ROLAP（ 关系OLAP ）存储模式，其中包括内存数据处理和使用DAX作为查询语言，类似于SSAS表格实例的特征。

Furthermore, with these similarities, SSAS query results can be easily replicated and used in Excel.

此外，由于具有这些相似性，因此可以轻松复制SSAS查询结果并在Excel中使用。

准备Power Pivot工作簿 (Preparing Power Pivot workbook)

Create an empty Excel workbook, and select Add to Data Model feature in Power Pivot tab, like in picture below:

创建一个空的Excel工作簿，然后在Power Pivot选项卡中选择“ 添加到数据模型”功能 ，如下图所示：