Customer dimension
2 posters
Page 1 of 1
Customer dimension
I am newbie to dimensional/data modeling and trying to learn dimensional modeling concepts. I am reading about bridge tables and star schemas and want to know in what situations do we use a bridge table and what are the factors that help determine this? For example I have a customer dimension and a customer can have multiple address lines(billing address line 1, billing address line 2, mailing address line 1, mailing address line 2). I started off with including all of the address attributes in customer dimension. Is this a good approach? Or do I need to break this up into two separate dimensions-customer and address dimension? How can I decide this? I have seen examples where people were talking about using a bridge table , how does this really help and in what situations do we go for bridge approach vs having it as a normal address dimension? What are the pros and cons of each of these approaches(using bridge vs normal dimension for customer address information)
And, once I have a separate dimension(regular instead of bridge) for customer address, do I just create a customer_addr_key on customer dimension which refers back to address dimension? Or should I also include the cust_addr_key on Fact table? Since we have cust_key_id on fact table, I feel like having cust_addr_key on fact table again is redundant since we can always use cust_key_id and get to address information. But again, how does one get to determine if they should include in on both dimension and fact tables and what are the pros and cons of each of these approaches?
And, once I have a separate dimension(regular instead of bridge) for customer address, do I just create a customer_addr_key on customer dimension which refers back to address dimension? Or should I also include the cust_addr_key on Fact table? Since we have cust_key_id on fact table, I feel like having cust_addr_key on fact table again is redundant since we can always use cust_key_id and get to address information. But again, how does one get to determine if they should include in on both dimension and fact tables and what are the pros and cons of each of these approaches?
rajeshwarr59- Posts : 21
Join date : 2015-06-26
Re: Customer dimension
If the customer has a couple of clearly identified addresses (i.e. billing and shipping) then just store it in the customer dimension. If they have multiple shipping addresses and the address is a function of the shipment, then have an address dimension and reference as the shipping address in the fact.
If the customer dimension is very large (i.e. 100,000's) then you may want to consider a more complex structure for performance reasons.
If the customer dimension is very large (i.e. 100,000's) then you may want to consider a more complex structure for performance reasons.
Customer Dimension
Thank you for the reply, this is helpful for me.
And when you say more complex structure, are you talking about bridge table approach? Does it make sense to use bridge table approach for smaller dimension tables?
And when you say more complex structure, are you talking about bridge table approach? Does it make sense to use bridge table approach for smaller dimension tables?
rajeshwarr59- Posts : 21
Join date : 2015-06-26
Re: Customer dimension
Bridge tables are used to resolve M:M relationships between dimensions and facts. You do not have that case.
Similar topics
» Merging customer data from disparate sources to create a master customer dimension
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» bridge table and junk dimension on customer dimension (bank/credit union)
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
» De-normalizing Customer Information to create a Customer Dimension
» Customer Ship to Vs Customer Dimension
» bridge table and junk dimension on customer dimension (bank/credit union)
» How to handle a Type I or II dimension with a snowflaked customer sub dimension (kimball book page 337, 338)
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|