customer & customer account

Hi,

I ahve a customer dim and a customer account. customer has all the info regarding the person, address, ssn, phone etc.
and account has a list of accounts debit,credit,saving,checking, loans etc. One customer could have multiple accounts and one account could have multiple customers associated to it. the question is should I have two different dimension to represent this relationship or can it be flattened out.
what's the best aprouch?

You would maintain a factless fact table to associate customers with their accounts.

An individual transaction should carry the account and customer involved in that transaction.

so customer and account in one dimension and a factless fact table to link them?

One dimension each (2 dimensions).

The individual transactions that we have at this point are linked to the customer through the Credit/Debit Account Number because that is the only identifiable information that we receive on the transaction record. Also, multiple customers could be associated with an account, so we would not know who to point the transaction to.

If you don't know who the customer is, so be it. Don't link directly to customer. Use a bridge.

If you have room to start of then create 2 dim's
1) customer
2) account

Split the fact transaction to put it in lowest grain, if you $50 has a transaction and there 10 customers with 10 accounts , split the $50 dollars into 10 transactions and asign a customer dim key & a account dim key and have aduit process for this
if you are to far deep and hard to change then go head with bridge table, but snow flake will have issue with perfomance compared to star

Thanks
Kumar

If all you know is a transaction was performed against an account and the customer is unknown, then that is all you can store is a link to the account. The account-customer relationship would be handled with a bridge table at query time. But realistically, the bank is most concerned about the account.

You have one fact for $50 against the account.

I would assume the bussiness key for the bridge table is customer and account, if there is no customer, how will I get the surrogate key and more over in this case you will not know account also (its bridge table, account and customer are coupled )

let me know
Kumar

Customers and Accounts are related by what we have referred to as Bridge tables, which are acting as Factless Fact tables.

The individual transactions that we have at this point are linked to the customer through the Credit/Debit Account Number because that is the only identifiable information that we receive on the transaction record. Also, multiple customers could be associated with an account, so we would not know who to point the transaction to.

yes the account_key and Customer key are in the bridge table.

What you are saying is right but what I don't understand, when a transaction does not have any traces of account (or) customer how the bridge table is also help full?
One need to have customer and account Information to go bridge table, in that case when one is missing you will have multiple rows, how will you know which one to pick up
Even through account is linked through customer because you have pulled account into front (into bridge table) , you need to have both connecting Information to make it Unique
Thank you
Kumar

You do not create the bridge table based on transactions. The bridge table is created from master data (i.e. the system's account master and customer master, which should have something that associates accounts with customers). It is independent of the transactions.

All you do when you post the transaction is post it with information the transaction can give you... the account, amount, time, etc...