ssas_通过SSAS透视图提高可读性

ssas

介绍 (Introduction)

In this article, we will be looking at a feature of SQL Server Analysis Service (SSAS) OLAP Cube that is SSAS Perspectives. We discussed creating SSAS OLAP Cubes in a previous article: OLAP Cubes in SQL Server. In an SSAS OLAP cube, there can be a large number of measures, dimensions and dimension attributes. The following screenshot is the star schema for the selected example that was created from the AdventureWorksDW sample database that can be visible at Data Source View.

在本文中，我们将研究SQL Server Analysis Service（SSAS）OLAP多维数据集的一项功能，即SSAS透视图。 在上一篇文章： SQL Server中的OLAP多维数据集中，我们讨论了如何创建SSAS OLAP多维数据集。 在SSAS OLAP多维数据集中，可以有大量的度量，维和维属性。 以下屏幕截图是从AdventureWorksDW示例数据库创建的所选示例的星型模式，该模式在数据源视图中可见。

When the above schema is converted to an OLAP Cube, you will see a lot of measures and dimensions as shown in the following screen.

将上述架构转换为OLAP多维数据集后，您将看到很多度量和维度，如以下屏幕所示。

This is what you will see when an OLAP cube is accessed from the Microsoft Excel pivot tables.

这是从Microsoft Excel数据透视表访问OLAP多维数据集时将看到的内容。

度量组 (Measure Groups)

When an end-user needs to analyze data from an OLAP cube, he might not need all measures to complete his tasks. Further, he may need only selected dimensions and its attributes.

当最终用户需要分析来自OLAP多维数据集的数据时，他可能不需要所有措施来完成其任务。 此外，他可能只需要选定的尺寸及其属性。

One of the easiest ways to filter objects in an OLAP cube is, by using the measure groups. Typically, for every fact table, one measure group is created. In the above OLAP cube, there will be three measure groups such as FactInternetSales, FactFinance, and FactProductInventory. These three measure groups will be visible at the client tools such as Excel and SQL Server Management Studio in order to perform filtering.

筛选OLAP多维数据集中最简单的方法之一是使用度量值组。 通常，对于每个事实表，都会创建一个度量值组。 在上面的OLAP多维数据集中，将有三个度量值组，例如FactInternetSales，FactFinance和FactProductInventory。 这三个度量值组将在客户端工具（例如Excel和SQL Server Management Studio）上可见，以便执行过滤。

The following screenshot shows the filtering with measure groups in SQL Server Management Studio.

以下屏幕快照显示了SQL Server Management Studio中使用度量值组进行的筛选。

Similarly, Measure Group filtering can be done using Microsoft Excel as shown in the below screenshot.

同样，可以使用Microsoft Excel完成度量值组过滤，如下面的屏幕快照所示。

Once the correct measure group is selected, relevant fact tables along with the related dimensions will be filtered so that end users can work on limited objects.

选择正确的度量值组后，将过滤相关事实表以及相关维，以便最终用户可以处理有限的对象。

However, this cannot filter dimensions and their attributes and it can only filter fact tables.

但是，这不能筛选维及其属性，而只能筛选事实表。

SSAS观点 (SSAS Perspectives)

There is a more extended feature in SQL Server Analysis Service named Perspective to filter any objects in SSAS OLAP cube. In simple terms, SSAS Perspective is a view of OLAP cubes where you can filter for any objects in the OLAP Cube.

SQL Server Analysis Service中还有一个扩展功能，名为Perspective，可以过滤SSAS OLAP多维数据集中的所有对象。 简而言之，SSAS透视图是OLAP多维数据集的视图，您可以在其中过滤OLAP多维数据集中的任何对象。

Let us create an perspective in a SSAS OLAP Cube and look at the usage of them in detail.

让我们在SSAS OLAP多维数据集中创建一个透视图，并详细研究它们的用法。

SSAS Perspectives can be created by opening in the cube in visual studio as shown in the below screenshot.

可以通过在Visual Studio中打开多维数据集来创建SSAS透视图，如下面的屏幕快照所示。

When you clicked the New Perspective option, a new column is added as shown below.

单击“新建透视图”选项时，将添加一个新列，如下所示。

In the above screen, users can deselect unwanted measure groups and dimensions. An important feature in SSAS Perspective is the ability to create different levels. In a selected measure group, you can choose different measures as shown in the below screenshot.

在上面的屏幕中，用户可以取消选择不需要的度量值组和尺寸。 SSAS透视图的一个重要功能是可以创建不同级别的功能。 在选定的度量值组中，可以选择不同的度量值，如下面的屏幕截图所示。

In the above perspective, Revision Number, Unit Price, Freight are removed from the newly created Perspective.

在以上透视图中，从新创建的透视图中删除了修订号，单价，运费。

Further, to select and deselect dimension, you have the option of selecting dimension attributes. For example, there are attributes to support different languages. Multiple languages may not be required for all the users. Therefore, you can deselect those attributes so the unnecessary attributes are not visible to all users.

此外，要选择和取消选择尺寸，可以选择尺寸属性。 例如，存在支持不同语言的属性。 并非所有用户都需要多种语言。 因此，您可以取消选择那些属性，以便不必要的属性对所有用户都不可见。

In the above Product Dimension, except for the English language, other language descriptions and product names are removed.

在上述产品维度中，除英语之外，其他语言描述和产品名称也被删除。

You can create any number of SSAS Perspectives with unique names as shown in the below screenshot.

您可以使用唯一名称创建任意数量的SSAS透视图，如下面的屏幕快照所示。

When creating Perspectives, you have to decide what objects to be chosen. Apart from Measure groups, Measures, and Dimensions, there are few other objects in the SSAS. Those objects can be managed from the Perspectives too. Those OLAP Cube objects are

创建透视图时，必须决定要选择哪些对象。 除了“度量”组，“度量”和“维”之外，SSAS中几乎没有其他对象。 这些对象也可以从Perspectives中进行管理。 这些OLAP Cube对象是

• Hierarchies
  层次结构
• Named sets
  命名集
• KPIs
  关键绩效指标
• Actions
  动作
• Calculated members
  计算成员

Another important point in the SSAS Perspective is that it is only available with the Enterprise Edition of the SQL Server. This means that you need to spend a lot of money to use perspectives. If you are using the standard version of the SQL Server, you have no choice but to use Measure groups option to filter data.

SSAS透视图中的另一个重要点是，它仅在SQL Server企业版中可用。 这意味着您需要花很多钱才能使用透视图。 如果使用的是SQL Server的标准版本，则只能使用“度量值组”选项来筛选数据。

Like other options, you can modify and delete the created SSAS Perspective when needed.

与其他选项一样，您可以在需要时修改和删除创建的SSAS透视图。

性能 (Performance)

As we discussed at the start of the article, perspectives are views of the OLAP cube. This means Perspectives are only metadata. This means there is no performance impact on the increasing number of perspectives. Though cube processing is a resource incentive task, adding perspective will not add CPU or IO cost. In fact, there is no need to re-process the cube, if you just add perspectives to the cube. Further, when there are changes to the perspective, you do not need to process the cubes.

正如我们在本文开头所讨论的，透视图是OLAP多维数据集的视图。 这意味着视角只是元数据。 这意味着对越来越多的透视图没有性能影响。 尽管多维数据集处理是一项资源激励任务，但是添加透视图不会增加CPU或IO成本。 实际上，如果仅向多维数据集添加透视图，则无需重新处理多维数据集。 此外，如果更改了透视图，则无需处理多维数据集。

安全 (Security)

There is a myth among the SSAS designers as well as among the users that SSAS perspectives can be used as a security tool. It is a completely false assumption. The security of an OLAP cube is handled at the cube level with measures and dimensions. Those security configurations are inherited from the perspectives. Apart from that, there is no security configuration in Perspectives.

SSAS设计人员和用户之间都有一个神话，那就是SSAS观点可以用作安全工具。 这是完全错误的假设。 OLAP多维数据集的安全性在多维数据集级别通过度量和维度进行处理。 这些安全配置是从角度继承的。 除此之外，Perspectives中没有安全配置。

连接性 (Connectivity)

After creating Perspectives, the next important aspect is how to connect to them. Let us look at how to connect to a Perspective using SQL Server Management Studio.

创建透视图后，下一个重要方面是如何连接到它们。 让我们看看如何使用SQL Server Management Studio连接到Perspective。

As shown in the below screenshot, by clicking the marked button, it will draw the list of available perspectives so that users can choose one of them.

如下面的屏幕快照所示，通过单击标记的按钮，它将绘制可用透视图的列表，以便用户可以选择其中之一。

The first entry (Adventure Works DW2017) is the entire cube and the rest of them are created perspectives. It is important to note that you can work on a single perspective only at one time. This means that you cannot link objects between perspectives. To avoid unnecessary confusion, it is better to create perspective considering the usages. However, even if there is a lack of objects, you can add necessary objects to the perspective later when needed as it does not have any impact on the performance of the OLAP cubes as it is a metadata change only

第一个条目（Adventure Works DW2017）是整个多维数据集，其余的都是透视图。 重要的是要注意，您一次只能在一个透视图上工作。 这意味着您不能在透视图之间链接对象。 为了避免不必要的混乱，最好考虑使用情况创建透视图。 但是，即使缺少对象，您也可以稍后在需要时将必要的对象添加到透视图中，因为它对OLAP多维数据集的性能没有任何影响，因为它仅是元数据更改

Most of the end-users are connecting to the Cubes via Microsoft Excel. Therefore, let us see how perspectives can be accessible via Microsoft Excel.

大多数最终用户都通过Microsoft Excel连接到多维数据集。 因此，让我们看看如何可以通过Microsoft Excel访问透视图。

There are two ways of making a connection to an OLAP cube from excel. One of them is, making the connection from Microsoft Excel itself. When making a connection to the OLAP cube, it will request the perspective as shown in the below screenshot.

从excel到OLAP多维数据集的连接有两种方法。 其中之一是从Microsoft Excel本身进行连接。 与OLAP多维数据集建立连接时，它将要求提供透视图，如下面的屏幕快照所示。

As shown in the above screenshot, the user will know whether he is connecting to the Cube or the Perspective.

如上面的屏幕截图所示，用户将知道他是连接到多维数据集还是透视图。

When a user wants to connect to Excel from SQL Server Management Studio, it will ask for the perspective that you want to connect as shown in the below screenshot.

当用户要从SQL Server Management Studio连接到Excel时，它将要求您提供要连接的透视图，如下面的屏幕快照所示。

After connecting to the SSAS perspective, the user can work on the selected objects from the selected perspective. You can see the connection properties from the Microsot Excel as shown in the below screenshot.

连接到SSAS透视图后，用户可以从选定的透视图中处理选定的对象。 您可以从Microsot Excel中看到连接属性，如下面的屏幕截图所示。

In the connection string as marked in the above screenshot, you provide the cube name (cube name is PerspectiveCube in this example) and the English is the perspective Name as shown in the Command Text above.

在上面的屏幕快照中标记的连接字符串中，您提供了多维数据集名称（在此示例中，多维数据集名称为PerspectiveCube），而英语为透视图名称，如上面的命令文本中所示。

结论 (Conclusion)

SSAS Perspective is a view for OLAP cube where you have the option of filtering different types of objects in the cube. With this option, uses can easily manage OLAP cubes. Since perspectives are metadata, there is no impact to the cube performance. In addition, you can add OLAP cube objects to the perspective when needed as it does not impact cube performance. Further, it is important to stree that perspective is not a security mechanism in the OLAP cubes. When connecting to Excel or SQL Server Management Studio, users can provide the perspective name and connect to the relevant OLAP cube objects.

SSAS透视图是OLAP多维数据集的视图，您可以选择在多维数据集中过滤不同类型的对象。 使用此选项，用户可以轻松管理OLAP多维数据集。 由于透视图是元数据，因此不会影响多维数据集的性能。 另外，您可以在需要时将OLAP多维数据集对象添加到透视图中，因为它不会影响多维数据集的性能。 此外，重要的是要确保透视图不是OLAP多维数据集中的安全性机制。 连接到Excel或SQL Server Management Studio时，用户可以提供透视图名称并连接到相关的OLAP多维数据集对象。

翻译自: https://www.sqlshack.com/improve-the-readability-with-ssas-perspectives/

ssas