Customer and Account dimensions

View previous topic View next topic Go down

Customer and Account dimensions

Post  eshtee on Tue Jul 19, 2011 10:34 pm

Hi,
I am currently creating a dimensional model which has a customer dimension, Account dimension, transaction type dimension and Account Transaction fact. i want to design the Customer and Account dimensions as conformed so that these can be used in future with other data marts as well.

Customer Dimension will have around a million records, Accounts Dimension will have around 10 million records as 1 customer can have multiple accounts and the Account Transaction fact will have transactions for opening/closing account with amounts.

At the moment i am doing a very simple model and creating the relationship between customer and account dimension using the Account Transaction fact so i can get the open and closed account for a period in time.

Now there is another requirement to find all the customers who have closed all of their accounts and hence left the bank. how can i fulfill this requirement efficiently.

Thanks for your help in advance,
Syed

eshtee

Posts : 6
Join date : 2011-07-19

View user profile

Back to top Go down

Re: Customer and Account dimensions

Post  Dave Jermy on Wed Jul 20, 2011 11:39 am

If an account can only have one customer, then one way to do it would be to store the Customer Key as a FK in the Account Dimension. Then you can join the two together without using the fact table to answer questions like 'How many / which customers have / don't have open / closed / certain types of accounts?'

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Customer and Account dimensions

Post  eshtee on Thu Jul 21, 2011 10:27 pm

Thanks for your reply Dave,
Since customers can have joint accounts we can have multiple customers against a single account. to solve this issue i am creating a customer group bridge dimension. I can also now use this dimension to indicate primary customer.

Now i can think of two options here.
Option 1) Create a new bridge table between Customer Group Dim and Account Dim

Option 2) Use the new Customer Group Bridge Dim and add Account ID from Account Dim in it.

Not sure which option is better, or whether i am even on the right track!

Thanks,
Syed

eshtee

Posts : 6
Join date : 2011-07-19

View user profile

Back to top Go down

Re: Customer and Account dimensions

Post  Dave Jermy on Fri Jul 22, 2011 3:56 am

Presumably, a customer can have both a joint account (with his wife, say) and a separate account of their own, in which case both options would link the wife with the separate account. A bridge between the Customer Dim and the Account Dim is the best way to go.

Dave Jermy

Posts : 33
Join date : 2011-03-24
Location : London, UK

View user profile

Back to top Go down

Re: Customer and Account dimensions

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