Customer and Account dimensions
2 posters
Page 1 of 1
Customer and Account dimensions
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
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
Re: Customer and Account dimensions
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
Re: Customer and Account dimensions
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
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
Re: Customer and Account dimensions
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
Similar topics
» customer & customer account
» Customer - Account - Service modelling
» Modelling a Customer / Account Manager relationship
» Account attributes in separate dimensions
» In Sales system how to design dimensions where city describes Order and Customer
» Customer - Account - Service modelling
» Modelling a Customer / Account Manager relationship
» Account attributes in separate dimensions
» In Sales system how to design dimensions where city describes Order and Customer
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|