Aveek Das
Cube Data Model

Introduction to Many-to-Many Relationships in SSAS

March 26, 2020 by

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.

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.

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:

Many to Many Relationship Example

Figure 1 – Many-to-Many Relation Example

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.

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).

AdventureWorksDW Model for many to many relationships in ssas

Figure 2 -AdventureWorksDW Model

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

Creating the SSAS Project

Create a new Multidimensional project in SSAS.

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

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

Data Source View Wizard

Figure 3 – Data Source View Wizard

Click on Next and complete the wizard.

Completing the Data Source View Wizard

Figure 4 – Completing the Data Source View Wizard

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

Creating the dimensions

Right-click on the Dimension and select New Dimension.

Adding New Dimension for many to many relationships in ssas

Figure 5 – Adding New Dimension

Select Use an Existing Table and click on Next.

Using an Existing Table

Figure 6 – Using an Existing Table

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

Specify Source Dimension

Figure 7 – Specify Source Dimension

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

Selecting Dimension Attributes for DimProduct

Figure 8 – Selecting Dimension Attributes for DimProduct

Complete the similar steps for DimSalesReason and complete the wizard.

Selecting Dimension Attributes for DimSalesReason

Figure 9 – Selecting Dimension Attributes for 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:

Add a New Cube

Figure 10 – Add New Cube

Select FactInternetSales and FactInternetSalesReason as measure groups and click Next.

Select Measure Groups

Figure 11 – Select Measure Groups

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

Select Measures

Figure 12 – Select Measures

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

Complete the cube wizard

Figure 13 – Complete the cube wizard

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

Cube Data Model

Figure 14 – Cube Data Model

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

Deploy the cube

Figure 15 – Deploy the cube

And finally, click Process to process the cube:

Process the cube

Figure 16 – 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.

Browsing the cube

Figure 17 – Browsing the cube

Defining Many-to-Many Relationships in SSAS

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

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.

Adding the new dimension

Figure 18 – Adding the new dimension

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

Completing the wizard

Figure 19 – Completing the wizard

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.

Add a Cube Dimension

Figure 20 – 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.

Defining Fact Relation Type

Figure 21 – Defining Fact Relation Type

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.

Defining Many to Many relationships in ssas

Figure 22 – Defining Many-to-many relationships

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.

Browsing the cube

Figure 23 – Browsing the cube

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.

Aveek Das
Latest posts by Aveek Das (see all)
51 Views