Customer Contacts

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

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.

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.

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?

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.

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.

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.

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.