Customer Dimension Modeling Question
2 posters
Page 1 of 1
Customer Dimension Modeling Question
I am in process of designing a customer dimension for our sales DW and I need some advice.
Here is our customer data example.
In our customer data, let's just say we have 2 GE subsidiaries as individual customer plus GE HQ location (total 3). Some GE subsidiaries have tax exempt and non tax exempt account. By default non tax exempt account is the primary account for the location. In addition to that, each GE location has its own unique demographic information, but only the certain demographic attributes can be rolled up to the HQ/corporate level.
For example:
GE California has tax and non tax exempt account. Total employee size at the location is 200. Business classification is Medical Support Equipment.
GE Washington has only tax account. Total employee size at the location is 50. Business classification is Financial
GE HQ has only both tax and non tax exempt account. Total employee size at the location is 150. Business classification is 'Utilities'
All GE rolls up to GE Corp. GE Corp uses GE HQ location as location address, but GE corp has its own unique HQ demographic,
GE Corp has employee size of 400, and it has business classification of 'Financial'. Not 'Utilities' (this is HQ location classification).
I need to be able to report sales at the account, location, and corp level.
Question 1: Would it be okay to use single table dimension rather than snowflake? I see our customer dimension as 1:M (or M:1), and I think one Kimball's design tip suggest of using one table rather than snowflake.
Question 2: Would it be better to create separate HQ dimension? Although most of attributes are going to be similar between location and corporate, since I cannot roll up all of the location attributes, I think separate dimension makes more sense. Also, we have over 5 million customers, and this Corporate dim will server as mini dimension.
Any design tip and suggestion would be very appreciated!
Thank you.
Here is our customer data example.
In our customer data, let's just say we have 2 GE subsidiaries as individual customer plus GE HQ location (total 3). Some GE subsidiaries have tax exempt and non tax exempt account. By default non tax exempt account is the primary account for the location. In addition to that, each GE location has its own unique demographic information, but only the certain demographic attributes can be rolled up to the HQ/corporate level.
For example:
GE California has tax and non tax exempt account. Total employee size at the location is 200. Business classification is Medical Support Equipment.
GE Washington has only tax account. Total employee size at the location is 50. Business classification is Financial
GE HQ has only both tax and non tax exempt account. Total employee size at the location is 150. Business classification is 'Utilities'
All GE rolls up to GE Corp. GE Corp uses GE HQ location as location address, but GE corp has its own unique HQ demographic,
GE Corp has employee size of 400, and it has business classification of 'Financial'. Not 'Utilities' (this is HQ location classification).
I need to be able to report sales at the account, location, and corp level.
Question 1: Would it be okay to use single table dimension rather than snowflake? I see our customer dimension as 1:M (or M:1), and I think one Kimball's design tip suggest of using one table rather than snowflake.
Question 2: Would it be better to create separate HQ dimension? Although most of attributes are going to be similar between location and corporate, since I cannot roll up all of the location attributes, I think separate dimension makes more sense. Also, we have over 5 million customers, and this Corporate dim will server as mini dimension.
Any design tip and suggestion would be very appreciated!
Thank you.
shinji23- Posts : 11
Join date : 2012-01-19
Re: Customer Dimension Modeling Question
From your description there appears to be multiple dimensions at play. At minimum customer and account , and probably a customer hierarchy.
It is ok to carry a customer key on the account, but not as a snowflake. Your facts should hold keys to both customer and account. If your fact source does not provide the customer natural key, you can use the customer key on the account dimension to populate the customer key in the fact.
It is ok to carry a customer key on the account, but not as a snowflake. Your facts should hold keys to both customer and account. If your fact source does not provide the customer natural key, you can use the customer key on the account dimension to populate the customer key in the fact.
Similar topics
» Modeling the Customer Dimension
» Policy Customer/Address dimension question
» Customer Dimension Modeling - Level attribute
» Merging customer data from disparate sources to create a master customer dimension
» De-normalizing Customer Information to create a Customer Dimension
» Policy Customer/Address dimension question
» Customer Dimension Modeling - Level attribute
» Merging customer data from disparate sources to create a master customer dimension
» De-normalizing Customer Information to create a Customer Dimension
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum