Aggregation of FACT during Many to Many relationship

View previous topic View next topic Go down

Aggregation of FACT during Many to Many relationship

Post  rkraj on Wed Sep 19, 2012 7:20 am

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.




rkraj

Posts : 12
Join date : 2012-06-29

View user profile

Back to top Go down

Re: Aggregation of FACT during Many to Many relationship

Post  yuldashev on Wed Sep 19, 2012 2:26 pm

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
avatar
yuldashev

Posts : 13
Join date : 2012-08-14
Location : Ottawa, Canada

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum