如何通过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选项卡中选择“ 添加到数据模型”功能 ，如下图所示：

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

After clicking the button, check the option My table has headers in Create Table dialog, to preserve headers of the future data model (which will be as the same as names of the objects in Tabular model database). It is not necessary which range of cells will be selected, because this step creates a blank Power Pivot data model.

单击按钮后，在“ 创建表”对话框中选中“ 我的表具有标题 ”选项，以保留将来的数据模型的标题（该名称与表格模型数据库中对象的名称相同）。不必选择哪个单元格范围，因为此步骤将创建一个空白的Power Pivot数据模型。

Press OK button, and the Power Pivot window will appear:

按确定按钮，将出现“ Power Pivot”窗口：

将数据源从SSAS表格添加到Power Pivot数据模型 (Adding data source from SSAS Tabular to Power Pivot data model)

To choose SSAS Tabular as a data source, click the From Database button, and From Analysis Services or PowerPivot option.

若要选择SSAS表格格式作为数据源，请单击“ 从数据库”按钮，然后单击“ 从Analysis Services或PowerPivot”选项。

The Table Import Wizard dialog will appear:

将出现“ 表导入向导”对话框：

Enter the proper name of the SSAS instance and pick the desired model database from the drop-down menu. Press Test Connection button to ensure validity of the connection to the chosen instance. The confirmation should appear:

输入SSAS实例的适当名称，然后从下拉菜单中选择所需的模型数据库。按测试连接按钮以确保与所选实例的连接的有效性。确认应显示：

If the name of the SSAS instance is not properly input, it will not be possible to choose a corresponding model database from the drop-down menu, and the error message will appear:

如果未正确输入SSAS实例的名称，将无法从下拉菜单中选择相应的模型数据库，并且将出现错误消息：

The following step is crucial in order to retrieve data from SSAS Tabular model which will be used for further analysis and visualization in Excel.

为了从SSAS表格模型检索数据，下一步将至关重要，该模型将用于Excel中的进一步分析和可视化。

In the next step of the wizard, specify MDX query to retrieve desired dataset.

在向导的下一步中，指定MDX查询以检索所需的数据集。

Just to be mentioned, SSAS Tabular cube data can be queried with basic MDX statements, because in this case, there is no possibility to use DAX, the native query language of Tabular models (and Power Pivot model, as well).

值得一提的是，可以使用基本MDX语句查询 SSAS表格多维数据集数据，因为在这种情况下，不可能使用DAX （表格模型（以及Power Pivot模型）的本机查询语言）。

If a query is already prepared, just input that query in the related field in a dialog. Press Validate button to ensure that the query is properly written. If the query has some syntax errors, the warning message will appear and provide details on the error itself.

如果已经准备好查询，只需在对话框的相关字段中输入该查询即可。按验证按钮以确保正确编写查询。如果查询中存在某些语法错误，则会出现警告消息，并提供有关错误本身的详细信息。

However, it is possible to preview the result set and then use that query input later in the MDX Statement field, by clicking Design button (marked on the picture above).

但是，可以通过单击“ 设计”按钮（如上图所示）预览结果集，然后稍后在“ MDX语句”字段中使用该查询输入。

Following dialog displays the structure of the actual cube and query dock (with an appearance similar to SQL Server Management Studio MDX query dock):

下面的对话框显示实际的多维数据集和查询停靠结构（外观类似于SQL Server Management Studio MDX查询停靠结构）：

Execute the query with the button (marked above in the picture), and review the results. The actual result set will display in the same form later in the Power Pivot data model and Excel workbook, as well.

使用按钮（在图片上方标记）执行查询，然后查看结果。实际结果集稍后将以相同的形式显示在Power Pivot数据模型和Excel工作簿中。

The result set displayed here is entirely for illustrative purpose, and there is no technical limitation on how much data will be retrieved through a query – it depends on needs and purpose of data retrieval.

此处显示的结果集完全出于说明目的，对于通过查询检索多少数据没有技术限制-它取决于数据检索的需求和目的。

When the desired result set is acquired, press OK, and the query will appear automatically in the statement field. Validate the query again.

获取所需的结果集后，按OK，查询将自动出现在语句字段中。再次验证查询。

After clicking Finish button, the process of importing data will start. If importing process succeeds, and retrieved data (rows and columns) transfer properly, the following message will appear:

单击完成按钮后，将开始导入数据的过程。如果导入过程成功，并且检索到的数据（行和列）正确传输，将显示以下消息：

Otherwise, when the importing process fails, the whole procedure must be repeated all over again. If this happens, make sure that every query input in the final step is validated.

否则，当导入过程失败时，必须重新重复整个过程。如果发生这种情况，请确保验证了最后一步中的每个查询输入。

将处理后的多维数据集数据集从模型复制到普通的Excel工作簿中 (Copying processed cube dataset from model into ordinary an Excel workbook)

The result set from the query will appear in the Power Pivot window:

查询的结果集将显示在Power Pivot窗口中：

At this moment, just select these two columns and paste it in ordinary Excel workbook.

此时，只需选择这两列并将其粘贴到普通的Excel工作簿中即可。

After pasting, they will look like this:

粘贴后，它们将如下所示：

At this point, there are numerous possibilities to customize these query results, to incorporate and use them fully in an Excel workbook, perform additional calculations, add visual elements like charts, add more result sets and placing them in other sheets, and much more.

此时，有多种可能性可以自定义这些查询结果，将其完全合并并在Excel工作簿中使用，执行其他计算，添加图表之类的可视元素，添加更多结果集并将其放置在其他工作表中等等。

Enjoy using SSAS query results in Excel, and building your documentation on-the-go!

喜欢在Excel中使用SSAS查询结果，并随时随地构建文档！

有用的链接： (Useful links:)

翻译自: https://www.sqlshack.com/use-sql-server-analysis-services-ssas-query-results-excel-power-pivot/