Customer Dimension Modeling Question

View previous topic View next topic Go down

Customer Dimension Modeling Question

Post  shinji23 on Tue Jan 19, 2016 8:42 pm

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.





shinji23

Posts : 11
Join date : 2012-01-19

View user profile

Back to top Go down

Re: Customer Dimension Modeling Question

Post  ngalemmo on Sat Jan 23, 2016 7:26 pm

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

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

View user profile http://aginity.com

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