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:
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).
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.
Figure 3 – Data Source View Wizard
Click on Next and complete the 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.
Figure 5 – Adding New Dimension
Select Use an Existing Table and click on Next.
Figure 6 – Using an Existing Table
Select DimProduct as table and Product Key as the Key Column and click on Next.
Figure 7 – Specify Source Dimension
Select English Product Name as the attribute and click on Next and finish the wizard.
Figure 8 – Selecting Dimension Attributes for DimProduct
Complete the similar steps for DimSalesReason and complete the wizard.
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:
Figure 10 – Add New Cube
Select FactInternetSales and FactInternetSalesReason as measure groups and click Next.
Figure 11 – Select Measure Groups
Select Sales Amount and Fact Internet Sales Reason Count as measure and click Next.
Figure 12 – Select Measures
Once all the above steps are completed, click Finish to complete and close the wizard.
Figure 13 – Complete the cube wizard
The cube is now created, and you can see the data model as follows.
Figure 14 – Cube Data Model
Once the cube is created, deploy the cube onto the server. Right-click the cube and select Deploy.
Figure 15 – Deploy the cube
And finally, click Process to 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.
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.
Figure 18 – Adding the new dimension
Let the key column be as SalesOrderNumber. Complete the wizard by clicking Next and finally on Finish.
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.
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.
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.
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.
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.
He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.
In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn
View all posts by Aveek Das
- Getting started with PostgreSQL on Docker - August 12, 2022
- Getting started with Spatial Data in PostgreSQL - January 13, 2022
- An overview of Power BI Incremental Refresh - December 6, 2021