Relating dimensions together and fact table design

View previous topic View next topic Go down

Relating dimensions together and fact table design

Post  kjfischer on Tue Sep 25, 2012 12:47 pm

We have a fact table with associated dimension tables:
sales_fact
customer_dim_id
product_dim_id
vendor_dim_id
ship_to_location_id
shipped_qty
sale_price_amt

There is a new request to analyze sales by a representative who is assigned to a vendor by location. So for a given, vendor_dim_id and location_dim_id, there is a "representative".

My question is do I add a new dimension for representatives and add this new dim key to the fact table? Or, do I create a factless table which joins vendor_dim_id, location_dim_id, along with representative information? Then when I need to, I would join my sales_fact table to this new table on vendor and location to get the representative info?

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

Re: Relating dimensions together and fact table design

Post  BoxesAndLines on Tue Sep 25, 2012 7:39 pm

I would start with adding the Representative dimension directly to the fact table. The problem you can have is analysis based around vendors, locations, and representatives. If you want reporting around those three independent of the Sales fact, then I would still add the Representative to the Sales fact and create a new factless fact to capture the associations of vendors, locations, and representatives.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

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