We have a sales_fact with several type 1 dimensions. The vendor, the part, and the product_mgr are all recorded at the time of the sale. Note, that the vendor_id (natural key) is also on the part_dim. We had a situation where one vendor merged with another vendor, this resulted in the vendor_id being updated on the part_dim to the aquiring vendor_id for all the parts of the aquired vendor.
It turns out that the business want to see sales by the current vendor, not the one at the time of the sale ( which is on the source table and our fact). This is not a real problem because we can go through the part_dim to get the current vendor_id.
But where it gets awkward and complex is in getting the "current" product_mgr_user which is assigned by vendor. I am wondering how best to model this relationship? Should this table have both the surogate keys and natural keys of the vendor and user in it? Is this a factless fact table? Something doesn't seem right about going to part_dim to get the vendor_id and then going to the vendor_assignment table to get the user_dim_id and then going to user_dim to get the user info.
Am I thinking about this correctly? Any help is appreciated. Thanks, Kim
- Posts : 28
Join date : 2011-05-04
Permissions in this forum:You cannot reply to topics in this forum