Dimension design idea for vendor and employee relationship
2 posters
Page 1 of 1
Dimension design idea for vendor and employee relationship
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?
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
Re: Dimension design idea for vendor and employee relationship
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.
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.
Similar topics
» Modeling Employee and Employee Role dimension.
» Employee Dimension and Employee "Profile" Dimension?
» Vendor Dimension
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» [b]Need Help on Employee Data Mart Design[/b]
» Employee Dimension and Employee "Profile" Dimension?
» Vendor Dimension
» Modeling an Employee Dimension to a Fact which has two columns relating to the Dimension
» [b]Need Help on Employee Data Mart Design[/b]
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum