0

In our relational database we have both customer and contact tables. The customer is the organization or company and the contacts are all the individual contacts in the organization or company.

I am trying to conform to a star schema and I was wondering if it makes sense to combine the customers and contacts in to a single dimension. That would mean we would have the same customer number repeated for each group of contacts for that company in the dimension, and I'm not sure if that would cause problems when building a cube off of it.

Is it a good idea to combine these given their characteristics? And are there any problems I need to worry about in my cube with this design?

Thanks for your input.

user7593937
  • 545
  • 1
  • 5
  • 16

1 Answers1

1

You should probably keep Customer and Contact as separate dimensions.

Without knowing anything more about your business, my feeling is that you're likely to have facts that involve JUST customers. For example, if you try to implement this as a hierarchy of Contact->Customer it will cause problems in facts involving budgets and forecasts, and probably orders as well.

Ron Dunn
  • 2,971
  • 20
  • 27
  • When somebody places an order the order is based on the customer number and the contact placing the order. Most of time we summarize data based on the customer number only, but in some cases we will look at revenue by the contacts to see which contacts are outperforming others. Would a customer dim table with a hierarchy that includes contact make sense given the details above? Thank you. – user7593937 Aug 17 '17 at 14:49
  • I wish I could give you a better answer, but "It depends." Is it a heirarchy or is it two dimensions is a fairly common topic, one good discussion is at https://blog.oraylis.de/2012/08/one-or-two-dimensions/. Personally, I'd still model this as two dimensions, because I'm thinking more broadly than your order fact, and I believe there will be future cases where you want a CUSTOMER, distinct from a CONTACT. Also, might there be a case where contacts move between customers? Tracking the lifetime value of a contact might emerge as a future requirement. – Ron Dunn Aug 18 '17 at 00:13
  • Thank you for your insight, I appreciate it. – user7593937 Aug 18 '17 at 16:06