Customer Contacts

View previous topic View next topic Go down

Customer Contacts

Post  tim_goodsell on Thu Dec 08, 2011 7:51 pm

Hi

My data model has a Customer Dimension and a customer can be contaced electonically by the Web, Fax, telephone etc. Each type has its own attributes

Is it best to put all the attributes in the customer dimension (altogether about 15) or is there a better way to do it

Tim



tim_goodsell

Posts : 49
Join date : 2010-09-21

View user profile

Back to top Go down

Re: Customer Contacts

Post  ngalemmo on Fri Dec 09, 2011 3:36 am

Not knowing further details, I would say no. Contacts like that are business events and covered by a fact table. You would have a contact type dimension to indicate how the customer was contacted.
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 Contacts

Post  Steveo250k on Tue Feb 19, 2013 9:31 am

Your suggestion does not work for me. I have a vendor dimension. A vendor can have 0 to many contacts of different types. Each contact type can have multiple actual contacts. It's an open ended list of contacts at that vendor. These contacts are not part of any fact table. In other words we are not tracking contact events. The contacts are just a variable length set of attributes of the vendor.

My customer is a manufacturer who has many vendors (i.e., carriers) who move their products to my customer's customers. The events we track are these shipments and the vendor is a dimension of the shipment. Now my customer would like a report that lists all their vendors for a given geographical area. I need to enhance my vendor dimension to include contact information. This is a one to many relationship.

Is this a case for a dimension outrigger?

I'll have a similar problem reporting vendor rates. Each vendor can have multiple predefined rates based on things like length of haul, service level, etc. Part of their vendor analysis is also looking at these rates. It's seems logical to query the vendor dimension, but I think I need a snowflake architecture.

I don't see these sub dimension tables ever being used in a fact table. The shipment fact table would only reference the vendor used in that shipment.

Steveo250k

Posts : 6
Join date : 2012-08-10

View user profile

Back to top Go down

Re: Customer Contacts

Post  Jeff Smith on Tue Feb 19, 2013 10:48 am

Unless there is a relationship between vendor rates and vendor contacts, then you have to have 2 fact tables. What question would you be answering that would require Vender-Rate-Contact to be on the same fact table?

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Customer Contacts

Post  Steveo250k on Tue Feb 19, 2013 11:02 am

I don't think they're facts at all. It's a report of vendor details. Give me a list, with contact info, of all my vendors in the Chicago area. Or give me a list, with contact info, of all my vendors in the Chicago area with per mile rate < 1.50.

Steveo250k

Posts : 6
Join date : 2012-08-10

View user profile

Back to top Go down

Re: Customer Contacts

Post  Mike Honey on Wed Feb 20, 2013 1:22 am

I'd add a Vendor Contacts dimension and a Contact Type dimension. The Fact relationship would be through a Bridge populated with the Vendors - Vendor Contacts relationships.
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Customer Contacts

Post  Steveo250k on Wed Feb 20, 2013 9:10 am

A bridge table is what I've been thinking. But the documentation I've been reading describes a bridge table as a many to many relationship between a fact table and a dimension. What I have is a one to many relationship within a dimension. A given list of contacts only go with one specific vendor. A given set of rates only go with one specific vendor, etc., i.e, one to many.

Still a bridge table looks like the best model if I look at my current vendor dimension as a factless fact table with it's surrounding dimensions. A factless fact table (current vendor) with it's surrounding dimensions (contacts, rates, ).

My discomfort is that my datamart is starting to look like a snowflake. I wonder if I should expand my current vendor dimension (giving the snowflake effect) and using it as a dimension in one cube and as a factless fact table in another. Or, if I should essentially duplicate the vendor dimension and call it a vendor fact table for this new cube. I don't see these new vendor dimensions being used in any other data cube or with any other fact tables.

Steveo250k

Posts : 6
Join date : 2012-08-10

View user profile

Back to top Go down

Re: Customer Contacts

Post  Mike Honey on Thu Feb 21, 2013 10:36 pm

I dont think you need to "duplicate" your Vendors as a fact - the granularity would be insufficient.

The granularity for Bridge Fact is more detailed than either dimension - e.g. one row per combination of Customer and Contact.
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

Re: Customer Contacts

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