Customer dimension

View previous topic View next topic Go down

Customer dimension

Post  rajeshwarr59 on Fri Jun 26, 2015 6:38 pm

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?

rajeshwarr59

Posts : 21
Join date : 2015-06-26

View user profile

Back to top Go down

Re: Customer dimension

Post  ngalemmo on Fri Jun 26, 2015 8:34 pm

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

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

View user profile http://aginity.com

Back to top Go down

Customer Dimension

Post  rajeshwarr59 on Sat Jun 27, 2015 8:11 am

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?

rajeshwarr59

Posts : 21
Join date : 2015-06-26

View user profile

Back to top Go down

Re: Customer dimension

Post  ngalemmo on Sat Jun 27, 2015 12:34 pm

Bridge tables are used to resolve M:M relationships between dimensions and facts.  You do not have that case.
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 dimension

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