Suppose you have an account balance table:
And you have “Which customer owns which account” table:
In other words, Account1 are owned by customer 1 and 2. Same with Account2.
How much money does customer 1 have?
How much money does customer 2 have?
Assuming that customer1 doesn’t have money in another bank or at home or in his wallet, then the correct answer is: we don’t know. The £10 in Account1 could be split £3-£7 between customer1 and customer2. Or it could be £2.50-£7.50. We don’t know.
If we do this as M2M in SSAS, what does SSAS think?
Why £20 for customer1? Well customer1 owns both account1 and account2, and there’s £10 on each account hence £20.
Why is the total £30? Hmm, that’s where I can’t understand. SSAS sums all the accounts, 3 x £10 = £30. SSAS should be summing account1 and account2 only (2x£10=£20) or summing what on display, i.e. customer1 & customer2 (2x£20=£40). But no, it displays £30.
To proof that it sums all 3 accounts, I’ll change the balance of account3 from £10 to £7, and reprocess the cube.
The total is now 27.
What does account3 have to do with customer1 and 2? I don’t know. Hence the title of this article, Many to Many Is Not Always Right.
If anybody can explain this, I’d be glad to hear from them. In the mean time I’ll also try to find out.
One more thing, if it’s not many-to-one (many customers own one account), but one-to-many instead (one customer owns many account), then the balance for each customer is correct. Of course it is! There’s no dispute about how to proportion the balance of an account to 2 people! For example:
In this case SSAS displays:
But, as you notice, the total is still wrong! Well it doesn’t make sense that the numbers above it are 20 and 10 and the total is 27, does it?
Vincent Rainardi, 26/5/11, email@example.com
PS. The script to create and populate the tables is below.
use Test go if exists (select * from sys.schemas where name = 'M2M') drop schema M2M go create schema M2M go -- Create account balances fact table if object_id('M2M.Fact_Account_Balances') is not null drop table M2M.Fact_Account_Balances create table M2M.Fact_Account_Balances ( Account_Key int, Balance money ) go insert into M2M.Fact_Account_Balances values (1, 10) insert into M2M.Fact_Account_Balances values (2, 10) insert into M2M.Fact_Account_Balances values (3, 10) go -- Create account dimension table if object_id('M2M.Dim_Account') is not null drop table M2M.Dim_Account create table M2M.Dim_Account ( Account_Key int, Account_Number varchar(20) ) go insert into M2M.Dim_Account values (1, 'Account1') insert into M2M.Dim_Account values (2, 'Account2') insert into M2M.Dim_Account values (3, 'Account3') go -- Create customer-account factless fact table if object_id('Fact_Customer_Account') is not null drop table M2M.Fact_Customer_Account create table M2M.Fact_Customer_Account ( Customer_Key int, Account_Key int ) go insert into M2M.Fact_Customer_Account (Customer_Key, Account_Key) values (1, 1) insert into M2M.Fact_Customer_Account (Customer_Key, Account_Key) values (1, 2) insert into M2M.Fact_Customer_Account (Customer_Key, Account_Key) values (2, 1) insert into M2M.Fact_Customer_Account (Customer_Key, Account_Key) values (2, 2) go -- Create customer dimension table if object_id('M2M.Dim_Customer') is not null drop table M2M.Dim_Customer create table M2M.Dim_Customer ( Customer_Key int, Customer_Name varchar(20) ) go insert into M2M.Dim_Customer values (1, 'Customer1') insert into M2M.Dim_Customer values (2, 'Customer2') go select * from M2M.Dim_Customer select * from M2M.Dim_Account select * from M2M.Fact_Customer_Account select * from M2M.Fact_Account_Balances update M2M.Fact_Account_Balances set Balance = 7 where Account_Key = 3