Many-to-Many: Which Dimension is Used?

In SSAS, it is possible for a M2M to have 2 paths.  The first one is through Dim1 and second one is through Dim2. In this case, which path will be used by M2M? I’ve come across this question about 2 years ago, and have been delaying finding the answer until now. So here it is.

Ah before I start, 2 things. 1) This post is related this post I wrote 2 days ago: Many to Many Is Not Always Right. 2) Thank you to Robert [last name] for bringing this up. I came across it for the first time 2 years ago, and I’ve always wanted to find out.

As always, it is easier to explain (and learn) by example.

On the top we have Fact Account Balances which list how much money is in each account.

The second fact table (Fact Customer Account) links each customer to the Fact Account Balances in two ways: via Dim_Account and Dim_Account2. For example, these 2 account dims could be Mortgage Account dimension and Saving Account dimension.

If we create a cube with M2M like this, which dim will M2M use, Dim Account or Dim Account2?

The answer is: both, it depends on the data.

Let’s populate the tables like this:

<pre>select * from M2M.Dim_Customer
select * from M2M.Dim_Account
select * from M2M.Dim_Account2
select * from M2M.Fact_Customer_Account
select * from M2M.Fact_Account_Balances

As you can see above, 3 customers, 3 accounts with balance = £20, £30, £40. Customer1 owns account1, customer2 owns account2, customer3 owns account3. Ideal situation.

As we expect, the cube displays this:

Now’s let’s change the relationship between customer and account:

On the second path, both customer 1 and 3 owns account 1.

The cube is now showing:

What does it mean? It means that only the rows where account1 = account2 are shown. SSAS only shows the rows when both paths are the same. It does not show the rows where the paths are different.

Here’s the rest of the experiment:

-- Rule 1. Only when account1=account2 it's shown, otherwise it's not shown
(1, 1, 1)
(2, 2, 2)
(3, 3, 3)
Output: all 3 customers are shown, balance: 20,30,40

(1, 1, 2)
(2, 2, 3)
(3, 3, 1)
Output: none is shown

(1, null, 1)
(2, 2, 2)
(3, 3, null)
Output: only customer2 balance 30 is shown

(1, 1, 1)
(2, 2, 2)
(3, 3, 1)
Output: only customer1&2 are shown, balance 20 & 30

-- Rule 2. null is not shown (even though they are the same, i.e. both are null):
(1, null, null)
(2, 2, 2)
(3, 3, 3)
Output: only customer2 & 3 are shown

-- By the way: the total is always 90 in all cases above

So my conclusion is: if M2M to have 2 paths, SSAS only shows the rows when both paths are the same. As always, I welcome comments and questions on vrainardi@gmail.com.

Vincent Rainardi, 27/05/11.

PS. SQL Script is below

-- Find out which dim is used by M2M

-- Create account balances fact table
create table M2M.Fact_Account_Balances
( Account_Key int, Balance money )

insert into M2M.Fact_Account_Balances values (1, 20)
insert into M2M.Fact_Account_Balances values (2, 30)
insert into M2M.Fact_Account_Balances values (3, 40)

-- Create 2 Dim Accounts
create table M2M.Dim_Account
( Account_Key int, Account_Number varchar(20) )

insert into M2M.Dim_Account values (1, 'Account1')
insert into M2M.Dim_Account values (2, 'Account2')
insert into M2M.Dim_Account values (3, 'Account3')

create table M2M.Dim_Account2
( Account2_Key int, Account2_Number varchar(20) )

insert into M2M.Dim_Account2 values (1, 'Account1')
insert into M2M.Dim_Account2 values (2, 'Account2')
insert into M2M.Dim_Account2 values (3, 'Account3')

-- Create customer-account factless fact table
create table M2M.Fact_Customer_Account
( Customer_Key int, Account_Key int, Account2_Key int )

truncate table M2M.Fact_Customer_Account
insert into M2M.Fact_Customer_Account values (1, 1, 1)
insert into M2M.Fact_Customer_Account values (2, 2, 2)
insert into M2M.Fact_Customer_Account values (3, 3, 1)

-- Create customer dimension table
create table M2M.Dim_Customer
( Customer_Key int, Customer_Name varchar(20) )

insert into M2M.Dim_Customer values (1, 'Customer1')
insert into M2M.Dim_Customer values (2, 'Customer2')
insert into M2M.Dim_Customer values (3, 'Customer3')

select * from M2M.Dim_Customer
select * from M2M.Dim_Account
select * from M2M.Dim_Account2
select * from M2M.Fact_Customer_Account
select * from M2M.Fact_Account_Balances

-- Rule 1. Only when account1=account2 it's shown, otherwise it's not shown
(1, 1, 1)
(2, 2, 2)
(3, 3, 3)
Output: all 3 customers are shown, balance: 20,30,40

(1, 1, 2)
(2, 2, 3)
(3, 3, 1)
Output: none is shown

(1, null, 1)
(2, 2, 2)
(3, 3, null)
Output: only customer2 balance 30 is shown

(1, 1, 1)
(2, 2, 2)
(3, 3, 1)
Output: only customer1&2 are shown, balance 20 & 30

-- Rule 2. null is not shown (even though they are the same, i.e. both are null):
(1, null, null)
(2, 2, 2)
(3, 3, 3)
Output: only customer2 & 3 are shown

-- By the way: the total is always 90 in all cases above