ssas如何创建分区_如何基于SSAS信息创建Excel报告

ssas如何创建分区

介绍 (Introduction)

In SSAS, when I offer Power BI, Reporting Services, PowerPivot or SharePoint to connect to SSAS, the business analysts look scared. On the other hand, if I talk about MS Excel, everybody seems so happy and comfortable with it.

在SSAS中，当我提供Power BI，Reporting Services，PowerPivot或SharePoint连接到SSAS时，业务分析师看起来很害怕。 另一方面，如果我谈论MS Excel，那么每个人似乎对此都很满意。

Excel is still the most popular spreadsheet in the world even when there are a lot of free spreadsheets like OpenOffice and LibreOffice to download, in the BI world, Excel is still the most popular.

即使有很多免费电子表格（如OpenOffice和LibreOffice）可供下载，Excel仍然是世界上最受欢迎的电子表格，在BI领域，Excel仍然是最受欢迎的电子表格。

Since then the usage is still growing. Office 365, a new version of Office in the cloud is growing each day and you can connect from your Android or iPhone device and store the information in OneDrive, the Microsoft storage.

从那时起，用法仍在增长。 Office 365是云中Office的新版本，每天都在增长，您可以从Android或iPhone设备进行连接，并将信息存储在Microsoft存储OneDrive中。

In this article, we are going to learn how to connect to SSAS using Excel and create some reports on it.

在本文中，我们将学习如何使用Excel连接到SSAS并在其上创建一些报告。

要求 (Requirements)

1. SSAS multidimensional database installed 已安装SSAS多维数据库
2. SQL Server 2016 Installed
   已安装SQL Server 2016
3. SSDT
   固态硬盘
4. AdventureworksDW database AdventureworksDW数据库
5. SSMS 2017 installed 已安装SSMS 2017
6. Excel Installed
   已安装Excel
7. How to build a cube from scratch using SQL Server Analysis Services (SSAS) 如何使用SQL Server Analysis Services（SSAS）从头构建多维数据集中创建的示例。

入门 (Getting started)

In your machine, open Excel:

在您的计算机中，打开Excel：

In the menu, go to data. Select the icon From other sources and select from Analysis Services. This option will create a PivotTable with the SSAS cube information:

在菜单中，转到数据。 选择来自其他来源的图标，然后从Analysis Services中选择。 此选项将创建带有SSAS多维数据集信息的数据透视表：

Write the name of the SSAS Server. Your current Windows credentials should have access to SSAS server. You can also use a specified username and password:

输入SSAS服务器的名称。 您当前的Windows凭据应该可以访问SSAS服务器。 您还可以使用指定的用户名和密码：

In SSAS, you have the database (MultidimensionalProject4) and inside the database, you have cubes (My first cube). Select your database and cube and press next:

在SSAS中，您具有数据库（MultiDimensionProject4），在数据库内部，您具有多维数据集（我的第一个多维数据集）。 选择数据库和多维数据集，然后按下一步：

The information is stored in an ODC file (Office Data Connector) this file is an xml file used to connect to different data sources. In this case to SSAS. You can specify a description, keywords and the file name here. The description and keywords may be useful to identify the connection and to search it:

信息存储在ODC文件（Office数据连接器）中，该文件是用于连接到不同数据源的xml文件。 在这种情况下改为SSAS。 您可以在此处指定说明，关键字和文件名。 描述和关键字可能有助于识别连接并进行搜索：

The ODC file contains the name of the database, keywords, connection information and type of connection (OLEDB):

ODC文件包含数据库的名称，关键字，连接信息和连接类型（OLEDB）：

You can create a report, a chart or just create a connection. Select the option, PivotTable Report. You can also select the sheet where the report will be displayed or create a new one:

您可以创建报告，图表或仅创建连接。 选择选项“ 数据透视表报表” 。 您还可以选择要在其中显示报告的工作表或创建一个新的工作表：

A PivotTable will be created in Excel:

数据透视表将在Excel中创建：

In your fact table (fact reseller sales in my example) check your measures to be displayed (Discount Amount and the extended amount in my example):

在事实表中（在我的示例中为事实经销商销售）检查要显示的度量（在我的示例中为“折扣金额”和“扩展金额”）：

You will be able to visualize the total discount and extended amount. Double click the discount amount:

您将能够看到总折扣和扩展金额。 双击折扣金额：

A new sheet will be created with detailed Discount amount information. It will display the first 1000 rows:

将创建一个新的工作表，其中包含详细的折扣金额信息。 它将显示前1000行：

Drag and drop the Dim currency to the rows section. This will display discount and extended amounts grouped by currency:

将Dim货币拖放到“行”部分。 这将显示按货币分组的折扣和扩展金额：

We will drag and drop the Dim Sales Territory to the Rows to group the Discount Amount and Extended Amount by territory and currency:

我们将把Dim Sales Territory拖放到Rows中，按地区和货币对折价金额和扩展金额进行分组：

We will now create a graph based on the current information. Select the report information and go to Insert and select the pie icon:

现在，我们将基于当前信息创建一个图形。 选择报告信息，然后转到“插入”并选择饼形图标：

Select the graph that you want to use:

选择要使用的图形：

You will now have a graph about the discount amount by currency type:

现在，您将获得有关按货币类型划​​分的折扣金额的图表：

You can modify the format of your cells. If you right click the format cells, you can modify the format:

您可以修改单元格的格式。 如果右键单击格式单元格，则可以修改格式：

For example, we can change the numbers display in number format with 2 decimals:

例如，我们可以将数字显示更改为数字格式，并带有两位小数：

You can easily change the table format using Excel:

您可以使用Excel轻松更改表格格式：

If you want to edit the numbers, you will receive a warning and you will not be able to change the data:

如果要编辑数字，将收到警告，并且您将无法更改数据：

You can modify the labels like the country names:

您可以修改标签，例如国家名称：

If you right click on the numbers, you will find the option to configure PivotTable options:

如果右键单击数字，将找到用于配置数据透视表选项的选项：

In show and filters tab, you can configure if you want to see the totals for row and columns, use a custom list and more.

在显示和过滤器选项卡中，您可以配置是否要查看行和列的总计，使用自定义列表等。

In display tab, you can configure if you want to see expand and collapse buttons, show calculated members and more:

在显示选项卡中，可以配置是否要查看展开和折叠按钮，显示计算所得的成员等：

If you have SSDT, there is an option to connect to Excel and create the ODC directly. This option is useful if you have SSDT and Excel on the same machine. If you only have Excel, the previous steps should be used.

如果您有SSDT，则可以选择连接到Excel并直接创建ODC。 如果您在同一台计算机上有SSDT和Excel，则此选项很有用。 如果您只有Excel，则应使用前面的步骤。

In SSDT, go to the Cube and go to Browser tab:

在SSDT中，转到多维数据集，然后转到浏览器选项卡：

There is an Excel icon to Analyze in Excel the data. Click it:

有一个Excel图标可在Excel中分析数据。 点击它：

You may receive a warning in Excel about a potential security problem. It is the ODC file being created automatically. You can trust it. Press Enable:

您可能会在Excel中收到有关潜在安全问题的警告。 它是自动创建的ODC文件。 您可以信任它。 按启用：

The Excel will be connected automatically and you can work as we did before:

Excel将自动连接，您可以像以前一样工作：

结论 (Conclusion)

In this article, we learned how to create reports in Excel based on the cube information in SSAS. We first need to create a Data the connection and then we drag and drop the measures and dimensions.

在本文中，我们学习了如何基于SSAS中的多维数据集信息在Excel中创建报表。 我们首先需要创建一个Data连接，然后拖放度量和尺寸。

Excel is pretty simple and intuitive to create reports. You cannot modify the data, but you can modify the label names at the customer side.

Excel创建报表非常简单直观。 您不能修改数据，但是可以在客户端修改标签名称。

The connection is made using an ODC file which can be modified manually or using Excel itself. It is necessary to have a Windows credentials to SSAS in the account that is using Excel.

使用可以手动修改的ODC文件或使用Excel本身进行连接。 在使用Excel的帐户中，必须具有Windows SSAS凭据。

There are other alternatives to have reports in Excel like using Reporting Services or Power BI to create reports and then export to Excel. However, this option. in my opinion is the best if the user has experience in Excel and does not have experience in other BI technologies. The learning curve is really short.

在Excel中还有其他替代方法可以生成报告，例如使用Reporting Services或Power BI创建报告然后导出到Excel。 但是，此选项。 我认为，如果用户具有Excel经验并且没有其他BI技术经验，那是最好的。 学习曲线确实很短。

We also learned that there is an option in SSDT to create the connection immediately. With that option, you do not need to write credential information to connect to SSAS from Excel.

我们还了解到，SSDT中有一个选项可以立即创建连接。 使用该选项，您无需编写凭据信息即可从Excel连接到SSAS。

参考资料 (References)

For more information about connecting to SSAS using Excel refer to these links:

有关使用Excel连接到SSAS的更多信息，请参考以下链接：

• Get data from Analysis Services 从Analysis Services获取数据
• Analyze in Excel 在Excel中分析
• Connect to SQL Server Analysis Services Database (Import) 连接到SQL Server Analysis Services数据库（导入）
• Querying SSAS cubes in Excel 在Excel中查询SSAS多维数据集

翻译自: https://www.sqlshack.com/create-excel-reports-based-ssas-information/

ssas如何创建分区