Dimension design idea for vendor and employee relationship

View previous topic View next topic Go down

Dimension design idea for vendor and employee relationship

Post  kjfischer on Wed Jun 08, 2011 3:03 pm

The fact table is invoice_detail, with measures of quantity_shipped, invoice_amt.

Dimensions include part, customer, channel, geography, and vendor (of the part).

In the source system there is this concept of vendor assignment where an employee is assigned to a vendor to fulfill different roles such as the product manager, buyer, invoicing, inventory, etc.

So, an invoice has a vendor for the part ordered. That vendor has multiple employees assigned to it based on a role.

I have modeled a vendor dimension off of the invoice_detail fact table. How do a model the vendor assignment to the employee without snowflaking?

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Dimension design idea for vendor and employee relationship

Post  ngalemmo on Wed Jun 08, 2011 3:11 pm

Use another fact table.

A fact table represents a business event or state. Invoicing is a business event. The dimensions relating to the fact should represent those contexts relating to the invoice.

The relationship between vendors and employees is a business state. The nature of that relationship should be reflected in another fact table.

Now, if an employee plays a specific role as it relates to the specific invoice, you can reflect that relationship as a FK to the employee dimension from the invoice.
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