ssas对数据仓库

In this article, I’m going to explain what many-to-many relationships in SSAS are and how to implement the same in a SQL Server Analysis Services (SSAS) project. For the sake of this article, we will only consider the Multidimensional Cube and not the Tabular. Also, this article assumes that you have some fair knowledge of building SSAS cubes from scratch.

在本文中，我将解释SSAS中的多对多关系以及如何在SQL Server Analysis Services（SSAS）项目中实现该关系。 为了本文的目的，我们将仅考虑多维多维数据集，而不考虑表格。 此外，本文假设您具有从头开始构建SSAS多维数据集的一些知识。

Often, in modeling dimensions and facts, BI developers face the issue of handling many-to-many relationships in the data model. In a SQL database or data warehouse, it is easier to implement the same; however, it gets complicated when the same model needs to be implemented in a multidimensional cube as well.

通常，在建模维度和事实时，BI开发人员会遇到在数据模型中处理多对多关系的问题。 在SQL数据库或数据仓库中，更容易实现。 但是，当同样需要在多维多维数据集中实现相同的模型时，它也会变得复杂。

了解多对多关系 (Understanding Many-to-Many Relationships)

Let us first understand what many-to-many relationships are all about. Consider a simple example of Students and Class. As you know, a student can register for one or more classes and a class can have one or more than one student. This is a simple example of many-to-many relationships. Other similar cases can be considered between Products and Customers. A customer can purchase one or more products, whereas a product can be purchased by one or multiple customers.

首先让我们了解一下多对多关系。 考虑一个简单的学生和课堂的例子。 如您所知，一个学生可以注册一个或多个班级，一个班级可以拥有一个或多个学生。 这是多对多关系的简单示例。 在产品和客户之间可以考虑其他类似情况。 一位客户可以购买一个或多个产品，而一个或多个客户可以购买一种产品。

However, we cannot define such kinds of many-to-many relationships directly in SQL. In order to implement such many-to-many relationships in a relational data model, we need to introduce an intermediate bridge table that has one-to-many relations with both the entities. Let us understand by following the figure below:

但是，我们不能直接在SQL中定义这种多对多关系。 为了在关系数据模型中实现这种多对多关系，我们需要引入一个中间桥表 ，该表与两个实体都具有一对多关系。 通过下图让我们理解：

If you see in the figure above, the intermediate bridge table, in this case, is the “Enrollment” table, which has a many-to-one relation with both Students and the Class tables. Similarly, this approach can be considered for designing any other data models where many-to-many relationships need to be implemented.

如果你在上面，中间桥表图中看到的，在这种情况下，是“ 登记 ”表，其中有学生和Class表一个多到一的关系。 类似地，可以考虑使用这种方法来设计需要实现多对多关系的任何其他数据模型。

在SSAS中实现多对多关系 (Implementing Many-to-Many Relationships in SSAS)

Now that we have some idea let us implement many-to-many relationships in SSAS. For demonstration purposes, I’m going to use the AdventureWorksDW2017 database as provided by Microsoft. This database is available for download free, and you can install the same on your local machine. In this data warehouse, there are also many-to-many relations, which states the reason for the purchase or sale of an item. For example, a sale can have one or more reasons linked to it and a sales reason can also be linked to multiple sales. In the warehouse, the Fact table is the “FactInternetSales” (marked in yellow) and the dimensions are “DimSalesReason” and “DimProduct” (marked in blue). Although there are many other dimensions and facts in the warehouse, it is out of scope for this article and won’t be covered here. The bridge table is created using the “FactInternetSalesReason” (highlighted in red).

现在我们有了一些想法，让我们在SSAS中实现多对多关系。 出于演示目的，我将使用Microsoft提供的AdventureWorksDW2017数据库。 该数据库可免费下载，您可以在本地计算机上安装该数据库。 在此数据仓库中，还存在多对多关系，该关系说明了购买或出售商品的原因。 例如，销售可以具有一个或多个与之相关的原因，而销售原因也可以与多个销售相关。 在仓库中，事实表为“ FactInternetSales ”（ 以黄色标记 ），尺寸为“ DimSalesReason ”和“ DimProduct ”（ 以蓝色标记 ）。 尽管仓库中还有许多其他方面和事实，但这不在本文的讨论范围之内，因此不在此处讨论。 使用“ FactInternetSalesReason ”（ 以红色突出显示 ）创建桥表。

Let us now go ahead and build many-to-many relationships in SSAS. You can follow the steps mentioned below and create the project.

现在让我们继续前进，在SSAS中建立多对多关系。 您可以按照下面提到的步骤创建项目。

Creating the SSAS Project

创建SSAS项目

Create a new Multidimensional project in SSAS.

在SSAS中创建一个新的多维项目 。

Connect it to the AdventureWorksDW2017 database and create the Data Source View accordingly.

将其连接到AdventureWorksDW2017数据库，并相应地创建数据源视图 。

In the Data Source View Wizard that appears, select the four tables as depicted in the figure below.

在出现的“ 数据源视图向导 ”中，选择四个表，如下图所示。

Click on Next and complete the wizard.

单击下一步 ，完成向导。

Once the Data Source View is ready, the next step is to create the Dimensions.

数据源视图准备就绪后，下一步就是创建Dimensions 。

创建尺寸 (Creating the dimensions)

Right-click on the Dimension and select New Dimension.

右键单击维度，然后选择新建维度 。

Select Use an Existing Table and click on Next.

选择“ 使用现有表” ，然后单击“ 下一步” 。

Select DimProduct as table and Product Key as the Key Column and click on Next.

选择DimProduct作为表，选择Product Key作为键列 ，然后单击Next 。

Select English Product Name as the attribute and click on Next and finish the wizard.

选择English Product Name作为属性，然后单击Next并完成向导。

Complete the similar steps for DimSalesReason and complete the wizard.

完成DimSalesReason的类似步骤并完成向导。

创建立方体 (Creating the cube)

Now that the dimensions are ready, the next step is to create the cube.

现在已经准备好尺寸，下一步是创建多维数据集。

Right-click on Cube and select New Cube:

右键单击“ 多维数据集”，然后选择“ 新建多维数据集” ：

Select FactInternetSales and FactInternetSalesReason as measure groups and click Next.

选择FactInternetSales和FactInternetSalesReason作为度量值组，然后单击Next 。

Select Sales Amount and Fact Internet Sales Reason Count as measure and click Next.

选择销售金额和事实互联网销售原因计数作为度量，然后单击下一步 。

Once all the above steps are completed, click Finish to complete and close the wizard.

完成上述所有步骤后，单击“ 完成”以完成并关闭向导。

The cube is now created, and you can see the data model as follows.

现在创建了多维数据集，您可以看到如下数据模型。

Once the cube is created, deploy the cube onto the server. Right-click the cube and select Deploy.

创建多维数据集后，将多维数据集部署到服务器上。 右键单击多维数据集，然后选择Deploy 。

And finally, click Process to process the cube:

最后，单击“ 处理”以处理多维数据集：

Now that our cube has been processed, let browse the cube and see what the results are. Drag and drop the “Sales Reason Name”, “Sales Amount” and “Fact Internet Sales Reason Count” fields into the query designer. As you can see in the image below, the values for the Sales Amount are the same for all the reasons which are incorrect. The reason for this error is because the many-to-many relations between the fact tables are not yet defined.

现在我们的多维数据集已经处理完毕，让我们浏览多维数据集并查看结果。 将“ 销售原因名称 ”，“ 销售金额 ”和“ 事实Internet销售原因计数 ”字段拖放到查询设计器中。 如下图所示，由于所有不正确的原因，“销售额”的值均相同。 出现此错误的原因是，事实表之间的多对多关系尚未定义。

在SSAS中定义多对多关系 (Defining Many-to-Many Relationships in SSAS)

Let’s now define many-to-many relationships in SSAS. You can follow the steps below.

现在让我们在SSAS中定义多对多关系。 您可以按照以下步骤操作。

Add a new dimension for SalesOrderNumber. Since this is the column that we are going to use in the bridge column, we need to create a separate dimension for the same.

为SalesOrderNumber添加一个新维度。 由于这是我们将在桥列中使用的列，因此我们需要为其创建单独的维度。

Let the key column be as SalesOrderNumber. Complete the wizard by clicking Next and finally on Finish.

让键列为SalesOrderNumber 。 通过单击下一步 ，最后在完成结束向导。

Once the dimension is created, the next step is to add this new dimension to the cube.

一旦创建了维度，下一步就是将这个新维度添加到多维数据集。

Right-click on the Dimensions and select Add Cube Dimension.

右键单击维度，然后选择添加多维数据集维度 。

Navigate to the Dimension Usage tab, and you’ll see the new dimension now available on the list. Select SalesOrderNumber adjacent to the FactInternetSales and click on the small box on the right. In the window that opens, select the Relationship Type as Fact.

导航到“ 维度用法”选项卡，您将在列表中看到新的维度。 选择SalesOrderNumber毗邻FactInternetSales度 ，然后点击右侧的小盒子。 在打开的窗口中，将“ 关系类型”选择为“ 事实” 。

Select the measure group FactInternetSalesReason for SalesReason. Click on the small box on the right hand and in the dialog that opens, select the Relation Type as Many-to-Many.

选择度量组FactInternetSalesReason作为SalesReason 。 单击右侧的小框，然后在打开的对话框中，将“ 关系类型”选择为“ 多对多” 。

浏览多维数据集 (Browsing the cube)

Now that we have implemented the necessary changes required for establishing many-to-many relationships in SSAS, we can now go ahead and start browsing the cube as we did in our previous step.

现在，我们已经实现了在SSAS中建立多对多关系所需的必要更改，现在我们可以像上一步一样继续浏览多维数据集了。

If you see the figure above, you can now see that the values for the Sales Amount are now being displayed correctly. Also, if you compare it with the previous example, you can see that some of the fields which did not have proper relations are also eliminated from the cube.

如果您看到上图，现在可以看到正确显示了“销售金额”的值。 此外，如果将其与前面的示例进行比较，您会发现从多维数据集中也删除了一些没有适当关系的字段。

结论 (Conclusion)

In this article, I have explained how we can implement many-to-many relationships in SSAS. Designing data models that include such many-to-many relations is quite complex and needs to be defined properly. If the relations are not defined properly, then it might lead to incorrect data and finally misleading information.

在本文中，我解释了如何在SSAS中实现多对多关系。 设计包含这种多对多关系的数据模型非常复杂，需要正确定义。 如果关系定义不正确，则可能导致数据错误，最终导致信息误导。

翻译自: https://www.sqlshack.com/introduction-to-many-to-many-relationships-in-ssas/

ssas对数据仓库