ssas 分层维度

介绍 (Introduction)

This article will discuss how SSAS Dimension Hierarchies can be used to analyze data much efficiently. If you are a data analyst, you want to start the analysis with a higher hierarchy. Then navigate the narrow attributes when required. For example, it will be better to start with analyzing revenue by year. If you need to analyze further into the data, you can choose the needed year and expand the Quarter -> Month, respectively. Let us see how we can create SSAS Dimension Hierarchies in OLAP Cubes to suit different requirements.

本文将讨论如何使用SSAS维度层次结构更有效地分析数据。 如果您是数据分析师，则希望从更高层次开始分析。 然后在需要时浏览狭窄的属性。 例如，最好开始逐年分析收入。 如果需要进一步分析数据，则可以选择所需的年份，然后分别展开“季度->月”。 让我们看看如何在OLAP多维数据集中创建SSAS维度层次结构以适应不同的需求。

Before we start, it is important to note that we are creating hierarchies in a dimension, and a hierarchy can contain attributes of a single dimension only while an attribute can be a member of multiple hierarchies.

在开始之前，请务必注意，我们正在一个维度中创建层次结构，并且层次结构只能包含单个维度的属性，而一个属性可以是多个层次结构的成员。

To demonstrate different types of SSAS dimension Hierarchies, let us create an SSAS OLAP cube using Fact Internet Sales in the AdventureworksDW database. Following is the Data Source View for the proposed OLAP cube. Please note that we have used user-friendly names for the tables.

为了演示不同类型的SSAS维度层次结构，让我们使用AdventureworksDW数据库中的Fact Internet Sales创建一个SSAS OLAP多维数据集 。 以下是建议的OLAP多维数据集的数据源视图。 请注意，我们在表中使用了用户友好的名称。

Let us create an OLAP cube and process it; The processed cube can be seen as shown in the below screenshot.

让我们创建一个OLAP多维数据集并对其进行处理。 可以看到已处理的多维数据集，如下面的屏幕截图所示。

Let us analyze data using Excel just after creating the OLAP Cubes in Excel Pivot Table. Let us select Product Cost and Sales Amount and attributes from the Sales Territory, as shown below.

在Excel数据透视表中创建OLAP多维数据集之后，让我们使用Excel分析数据。 让我们从“销售地区”中选择“产品成本和销售金额”以及属性，如下所示。

Though there are defined hierarchies, users still can select the attributes and see the hierarchical effect. However, the user has to select the hierarchy in the correct business order. For example, for the above example, the user has to select Group, Country, and Region in the same order. If not, data will not be displayed in the correct order.

尽管已经定义了层次结构，但用户仍然可以选择属性并查看层次结构效果。 但是，用户必须按正确的业务订单选择层次结构。 例如，对于上面的示例，用户必须以相同的顺序选择组，国家和地区。 否则，将不会以正确的顺序显示数据。

自然业务层次结构 (Natural Business Hierarchies)

In every business, there are natural business Hierarchies. For example, Region -> Country in the Territory Dimension, Product Category -> Product Sub Category -> Product Name in the Product dimension are the natural hierarchies that can be created.

在每项业务中，都有自然的业务层次结构。 例如，“区域”维度中的“区域”->“国家/地区”，“产品”维度中的“产品类别”->“产品子类别”->“产品名称”是可以创建的自然层次结构。

The following screenshot shows how hierarchies are created for the Sales Territories.

以下屏幕截图显示了如何为销售地区创建层次结构。

If you set the AttributeHierarchyVisible to False for the attribute property, this attribute can only be accessed through the hierarchies, not by the individual attributes.

如果将属性属性的AttributeHierarchyVisible设置为False，则只能通过层次结构访问此属性，而不能通过单个属性访问此属性。

After creating the hierarchy and setting up the property, as shown in the above screenshot, you can process only the dimension.

创建层次结构并设置属性后，如上面的屏幕截图所示，您只能处理维度。

Let us view this in Microsoft Excel Pivot tables, as shown below.

让我们在Microsoft Excel Pivot表中查看此情况，如下所示。

You can see that the hierarchy is available in the pivot table while the other attributes are not visible.

您可以看到层次结构在数据透视表中可用，而其他属性不可见。

In the following Promotion Dimension, two SSAS Dimension hierarchies are created, as shown in the following screenshot.

在以下升级维度中，将创建两个SSAS维度层次结构，如以下屏幕截图所示。

非自然层次 (Non-Natural Hierarchies)

In the previous section, we created natural SSAS Dimension hierarchies. However, there are non-natural hierarchies such as Gender -> Marital Status or Occupation -> Education in the Customer dimension. The following screenshot shows how to create multiple non-natural hierarchies in the Customer Dimension.

在上一节中，我们创建了自然的SSAS维度层次结构。 但是，在客户维度中存在非自然的层次结构，例如性别->婚姻状况或职业->教育。 以下屏幕快照显示了如何在客户维度中创建多个非自然层次结构。

There is no difference when creating non-natural hierarchies to the natural hierarchies. However, there are a few best practices that should be adopted when creating Non-Natural hierarchies.

创建非自然层次结构与自然层次结构没有区别。 但是，在创建非自然层次时应采用一些最佳实践。

• Hierarchies should not have many attributes. Ideally, you should not have more than two attributes to a non-natural hierarchy
  层次结构不应具有很多属性。 理想情况下，非自然层次结构不应具有两个以上的属性
• Since these are non-natural hierarchies, it is important to create hierarchies for all possibilities. For example, when you create a hierarchy for Gender -> Marital Status, you should create another hierarchy for Marital Status and Gender
  由于这些是非自然层次结构，因此为所有可能性创建层次结构很重要。 例如，当您为“性别->婚姻状况”创建层次结构时，应为“婚姻状况”和“性别”创建另一个层次结构
• Since these are non-natural hierarchies, end-users do not have an idea of what you have in the hierarchies. Therefore, the hierarchy name should reflect what you have in the hierarchy. For example, as shown in the above screenshot, hierarchies are name, Education-Occupation, etc
  由于这些是非自然层次结构，因此最终用户不了解您在层次结构中拥有什么。 因此，层次结构名称应反映层次结构中的内容。 例如，如上面的屏幕快照所示，层次结构是名称，教育职业等。

日期层次 (Date Hierarchy)

Date hierarchy is one of the most common SSAS Dimension hierarchies that is used by many users as it is obvious that date is the most common dimension that is used for data analytical purposes.

日期层次结构是许多用户使用的最常见的SSAS维度层次结构之一，因为显然日期是用于数据分析目的的最常见的维度。

Let us create a hierarchy for the date dimension. In the above OLAP cube, the date is a role-playing dimension. A Role-playing dimension means depending on the surrogate keys that are joining with the fact table; you will have a different role for the dimension. In the above OLAP cube, the Date Dimension is linked to the fact table via OrderDateKey, ShipDateKey, and DueDateKey. For this configuration, there will be three dimensions named Order Date, Ship Date, and Due Date, as shown in the below screenshot.

让我们为日期维度创建层次结构。 在上面的OLAP多维数据集中，日期是角色扮演的维度。 角色扮演维度是指取决于与事实表关联的代理键； 您将在维度上扮演不同的角色。 在上面的OLAP多维数据集，日期维度通过OrderDateKey，ShipDateKey和DueDateKey链接到事实数据表。 对于此配置，将有三个维度，分别是订单日期，发货日期和到期日期，如下面的屏幕快照所示。

When you create a hierarchy for Date Dimension, that hierarchy will be available for all the other role-playing dimensions, as shown in the below excel pivot table.

当为日期维度创建层次结构时，该层次结构将可用于所有其他角色扮演维度，如下面的excel透视表所示。

However, you will see that the month name is ordered by the alphabetical order of the name, not the order of the month number.

但是，您会看到月份名称是按名称的字母顺序排列的，而不是月份编号的顺序。

To achieve this, we need to do three configurations for the Month Name attribute, as shown in the below screenshot.

为此，我们需要对“月名”属性进行三种配置，如下面的屏幕快照所示。

关键列 (Key Column)

For every attribute, there will be a Key and Name properties. By default, the attribute name will take the key and name properties. To facilitate the different orders, we need to change the key of the attribute, as shown in the below screenshot.

对于每个属性，都有一个Key和Name属性。 默认情况下，属性名称将采用key和name属性。 为了方便执行不同的命令，我们需要更改属性的键，如下面的屏幕快照所示。

In this configuration, the key is changed to Calendar Year and Month Number of Year.

在此配置中，密钥更改为“日历年”和“年的月份数”。

名称栏 (NameColumn)

You need to change the NameColumn property to the EnglishMonthName.

您需要将NameColumn属性更改为EnglishMonthName。

按订单 (OrderBy)

By default, any attribute will be ordered by the Name property. Now you need to configure this to Key so that it will be ordered by Year and Month Number, which is the key of the attribute.

默认情况下，任何属性都将由Name属性排序。 现在，您需要将此配置为Key，以便按Year和Month Number排序，这是属性的键。

After processing the date dimension, you will find that now the Month Name is in the order by the month number, not by the Month Name.

在处理日期维度之后，您会发现现在“月份名称”按月号而不是“月份名称”的顺序排列。

铲斗层次结构 (Bucket Hierarchies)

Let us assume that we want to analyze data with the annual income of the customers. As you know, since yearly income is a continuous variable, we need to create buckets or ranges for the yearly income. To achieve this, you can modify the DiscretizationMethod to EqualAreas, and you can define the number of buckets you need to create, as shown in the following screenshot.

让我们假设我们想用客户的年收入来分析数据。 如您所知，由于年收入是一个连续变量，因此我们需要为年收入创建存储区或范围。 为此，您可以将DiscretizationMethod修改为EqualAreas，并可以定义需要创建的存储桶数，如以下屏幕截图所示。

Then you can combine this attribute with a different attribute such as Occupation to create SSAS Dimension Hierarchies, as shown in the below screenshot.

然后，您可以将此属性与其他属性（例如，职业）结合起来以创建SSAS维度层次结构，如下面的屏幕快照所示。

亲子阶层 (Parent-Child Hierarchy)

Until now, the SSAS dimension hierarchies that we discussed have an equal number of levels. For example, in the Group -> Country-> Region hierarchy has three levels. If you look at the natural traditional organization hierarchy, there will be an unequal number of levels.

到目前为止，我们讨论的SSAS维度层次结构具有相同数量的级别。 例如，在“组->国家/地区”中，层次结构具有三个级别。 如果您查看自然的传统组织层次结构，将会有不同数量的级别。

Let us create another Cube from FactResellerSales fact table with other related dimensions. In this cube, we are interested in the DimEmployee table.

让我们从FactResellerSales事实表中创建另一个具有其他相关维度的多维数据集。 在此多维数据集中，我们对DimEmployee表感兴趣。

Note: if you are adding the employee dimension to the existing project, use it after adding include the dimension to the Dimension usage configuration of the OLAP Cube.

注意：如果要将雇员维度添加到现有项目中，请在将维度添加到OLAP多维数据集的维度使用情况配置中之后使用它。

As you see in the above data source view, in the DimEmployee table, there is a self-referencing between EmployeeKey and ParentEmployeeKey columns.

从上面的数据源视图中可以看到，在DimEmployee表中，EmployeeKey和ParentEmployeeKey列之间存在自引用。

We will add a new column in the data source view to accommodate the Full Name of the employee with the following expression.

我们将在数据源视图中添加一个新列，以使用以下表达式容纳员工的全名。

CASE
WHEN MiddleName IS NULL THEN
FirstName + ‘ ‘ + LastName + ‘ ( ‘ + Title + + ‘ )’
ELSE
Title + ‘ ‘ + FirstName + ‘ ‘ + MiddleName + ‘ ‘ + LastName + ‘ ( ‘ + Title + + ‘ )’
END

案件
当MiddleName为NULL时
名字+''+姓氏+'（'+标题+ +'）'
其他
标题+''+名字+''+中间名+''+姓氏+'（'+标题+ +'）'
结束

This expression includes the First Name, Middle Name, Last Name, and the designation attributes of the Employee.

此表达式包括“名字”，“中间名”，“姓氏”和“雇员”的指定属性。

In the Employee dimension, select the Employee Key and change the name column to the Full Name that was created above.

在“雇员”维度中，选择“雇员密钥”，然后将“名称”列更改为上面创建的“全名”。

This can be seen from the following screenshot.

从下面的屏幕截图可以看出。

For the Parent Employee Key, modify the Level Naming Template property, as shown in the below screenshot.

对于上级雇员密钥，修改“ 级别命名模板”属性，如以下屏幕截图所示。

You are done with the Parent Employee Hierarchy and now process the dimension.

父雇员层次结构已完成，现在处理维度。

You can view any measure with the employee hierarchy, as shown in the below screenshot.

您可以使用员工层次结构查看任何度量，如以下屏幕截图所示。

结论 (Conclusion)

SSAS Dimension Hierarchies can be used to improve the data analytical capabilities for the data analyst. We have looked at natural hierarchies as well as non-natural hierarchies. Though there is no significant difference in how you create non-natural hierarchies, we discussed that there are important and best practices to follow.

SSAS维度层次结构可用于提高数据分析人员的数据分析能力。 我们已经研究了自然层次结构以及非自然层次结构。 尽管创建非自然层次结构的方式没有显着差异，但我们讨论了要遵循的重要且最佳实践。

In the date hierarchies, we discussed changing the attribute key so that we can sort the month name according to the month number rather than the month name. Further, we discussed how to create clusters for the continuous attribute so that it can be converted to SSAS Dimension Hierarchies. Finally, we looked at how to create hierarchies with a non-equal level of hierarchies, such as an Organization hierarchy.

在日期层次结构中，我们讨论了更改属性键的方法，以便我们可以根据月份编号而不是月份名称对月份名称进行排序。 此外，我们讨论了如何为连续属性创建群集，以便可以将其转换为SSAS维度层次结构。 最后，我们研究了如何创建具有不相等级别的层次结构的层次结构，例如组织层次结构。

翻译自: https://www.sqlshack.com/enhancing-data-analytics-with-ssas-dimension-hierarchies/

ssas 分层维度