Data Warehousing, BI and Data Science

28 May 2011

Many-to-Many: Which Dimension is Used?

Filed under: Analysis Services — Vincent Rainardi @ 6:19 am

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
Advertisements

4 Comments »

  1. Yes, SSAS does the m2m join based upon all common intermediate dimensions. So it’s not that there are two paths… SSAS just uses one path that combines both common dimensions.

    Comment by Greg Galloway — 28 May 2011 @ 4:49 pm | Reply

    • The question is: why? Is there any explanation for this design?

      Comment by Miky Schreiber — 13 June 2011 @ 1:35 pm | Reply

      • This is not issue an issue with the M2M relationship:

        If you removed Dim_Customer and Fact_Customer_Account from the schema, you would still have the same issue, since your 2 Account dimensions are related to the main fact table through the same key.

        This puts 2 constraints on the fact table and you can only get facts when both accounts are the same.

        Further filtering of the fact table with the information contained in the bridge table will not arrange this.

        Comment by TheDataSpecialist — 13 September 2012 @ 2:12 pm

      • Thank you for your comment Laurent

        Comment by Vincent Rainardi — 13 September 2012 @ 6:21 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: