Aggregation of FACT during Many to Many relationship

[rkraj]

First i would like to tell the Background

I am having a number of bank accounts coming under a particular Client. I have a number of clients coming under a logical entity called "Family". Through each account a person can buy any no of products.

So
FAMILY1->CLIENT1=>ACCT1=>Product1
=>Product2
=>Product3
=>ACCT2
=>ACCT3
=>ACCT4

->CLIENT2=>ACCT5 =>Product3
=>Product4
=>ACCT6 =>Product10
=>ACCT7


->CLIENT3=>ACCT3
=>ACCT9

DIMS
-----
FAMILY
ACCT
CLIENT
PRODUCT


If you see the above scenario we have Many=>Many relation ship between Client and Account, which means we can have one client holding many accounts and at the same time we have the JOINT Account concept inwhich we have two clients sharing the same account like Client3 has Account 3 and Account3 is also owned by Client1. But only one will be the primary client.

I am trying to create an aggregate FACT table saying "ACCT_BAL_AGG_FACT"

Case 1] Assume we have one to one relationship between client and account.
If i have this grain like the below ....
Family->Client->Acct->Product

I am eager to find out the account balances for each account.
If i aggreagate at the client level, i will be able to get for each client, what is the balance he holds.


Case 2] Here i have m->m between client and account.

Since we have many to many i cannot go by case 1. ... How should i solve this.. to get the balances for each client.

[yuldashev]

Hi rkraj,

My recommendation would be having 2 dimensions Client and Account with a bridge table to resolve the many-to-many relationship between the dimensions.
The fact table granularity will be by Client by Account by Date by Time and by any other dimensions if you have any.

Thanks,
Alisher
www.streebo.com