Is is possible to duplicate the same many-to-many relationship behavior in VertiPaq that we have in SSAS multidimensional?
Since Tabular model projects were introduced in SQL Server 2012, one of the major blocking points for adaption has been the lack of out-of-the-box support for many-to-many relationships. I’ve been using, demonstrating and showcasing SSAS Tabular and Power Pivot for at least three years and in that time it became apparent that, even though this might only be a perceived limitation in many cases, for quite a few enterprise IT shops; many-to-many support has become a litmus test for whether this technology is ready to be taken seriously. Honestly most business data models don’t need many-to-many relationships to be useful but it is still a reality. This week Chris Webb posted about bidirectional relationship support in the new Power BI designer and demonstrated an example using sales of fruit that can exist in different categories.
The Power BI Designer is the first publically-available product from Microsoft that supports M2M relationships in a VertiPaq model and I wanted to conduct a test to see if I could duplicate the same M2M functionality as we have in a familiar multidimensional model, using the Adventure Works sample data. So, here goes…
Many-to-Many Example in SSAS Multidimensional
In the Enterprise examples for the Adventure Works Cycles sample data, the Adventure Works cube contains two many-to-many dimensional relationships. Here is one of those relationships; in the dimensional usage grid of the cube editor, you can see that the relationship between the Internet Sales measure group and the Sales Reasons dimension is defined as many-to-many:
The M2M relationship involves three tables in the data source view with the Internet Sales Reason Facts table bridging any number of Internet Sales Facts records to any number of Sales Reasons records. Not in view is the DimCustomer table which is related to Internet Sales Facts.
A measure group named Internet Customers contains a distinct count measure based on the CustomerKey column in the Fact Internet Sales table. This measure will exhibit behavior unique to M2M aggregation as you’ll see in these queries:
In the first query, the Customer Count measure is grouped by Sales Reason on rows. If we were to add the result for each group, the total would be 24,135. This is incorrect because it’s a non-additive measure – the same customer is counted for placing sales orders for different reasons so some customers are counted multiple times:
This is evident by replacing the Sales Reason attribute with the All member on rows, showing that there were actually only 18,484 distinct customers placing orders:
Narrowing the results to only one customer helps to explain. This is the key value for customer “Aaron Zhang”. You can see that Aaron placed two orders with a different reason for each:
Again, replacing the Sales Reason attribute with the All member shows that there was really only one customer:
Many-to-Many Example in a VertiPaq Model
To get started, I opened the Power BI Designer and imported the same tables from AdventureWorksDW2012. Because we don’t have a Power Pivot interface in the designer (yet?), we’re essentially using Power Query to populate the VertiPaq semantic model.
The FactInternetSales and FactInternetSalesReason tables are related using two columns: SalesOrderNumber and SalesOrderLineNumber. These columns needed to be converted to strings and then concatenated into a single column to facilitate the relationship. This is a common practice in Power Pivot/Tabular.
Using the Manage Relationships dialog, the tables are related:
I switched to the Report page which finalized the data load and populated the model. When I started, I was concerned about not being able to explicitly define DAX measures but it was a simple matter to change the field aggregation for the CustomerKey to “Count (Distinct)”.
Using the Power View, it was also a simple matter to verify the results and duplicate all of the query results as before. Here, you can see that Aaron Zhang placed two different orders for different reasons and the total reflects the non-additive behavior of this many-to-many relationship.
In conclusion, many-to-many relationships are now supported in this implementation of VertiPaq and it behaves the same as in multidimensional cubes! This is very good news and may give us a good idea of what to expect in the near future.
