Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Dimension design idea for vendor and employee relationship

2 posters

Go down

Dimension design idea for vendor and employee relationship Empty Dimension design idea for vendor and employee relationship

Post  kjfischer 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

Back to top Go down

Dimension design idea for vendor and employee relationship Empty Re: Dimension design idea for vendor and employee relationship

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

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

http://aginity.com

Back to top Go down

Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum