customer & customer account

View previous topic View next topic Go down

customer & customer account

Post  colon832 on Fri Aug 30, 2013 11:51 am

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?

colon832

Posts : 7
Join date : 2013-07-08

View user profile

Back to top Go down

Re: customer & customer account

Post  ngalemmo on Fri Aug 30, 2013 12:36 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: customer & customer account

Post  colon832 on Fri Aug 30, 2013 1:41 pm

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

colon832

Posts : 7
Join date : 2013-07-08

View user profile

Back to top Go down

Re: customer & customer account

Post  ngalemmo on Fri Aug 30, 2013 2:10 pm

One dimension each (2 dimensions).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: customer & customer account

Post  colon832 on Tue Sep 03, 2013 9:23 am



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.

colon832

Posts : 7
Join date : 2013-07-08

View user profile

Back to top Go down

Re: customer & customer account

Post  ngalemmo on Tue Sep 03, 2013 11:24 am

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: customer & customer account

Post  Kumarpunna on Wed Sep 04, 2013 10:55 am

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


Kumarpunna

Posts : 6
Join date : 2013-09-04

View user profile

Back to top Go down

Re: customer & customer account

Post  ngalemmo on Wed Sep 04, 2013 1:26 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

customer+account

Post  Kumarpunna on Wed Sep 04, 2013 1:37 pm

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

Kumarpunna

Posts : 6
Join date : 2013-09-04

View user profile

Back to top Go down

Re: customer & customer account

Post  colon832 on Wed Sep 04, 2013 2:06 pm

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.

colon832

Posts : 7
Join date : 2013-07-08

View user profile

Back to top Go down

Re: customer & customer account

Post  Kumarpunna on Wed Sep 04, 2013 2:23 pm

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

Kumarpunna

Posts : 6
Join date : 2013-09-04

View user profile

Back to top Go down

Re: customer & customer account

Post  ngalemmo on Wed Sep 04, 2013 3:58 pm

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...
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: customer & customer account

Post  Sponsored content


Sponsored content


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