Relating dimensions together and fact table design
2 posters
Page 1 of 1
Relating dimensions together and fact table design
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?
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
Re: Relating dimensions together and fact table design
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Similar topics
» relating 2 existing dimensions combined to a fact table
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Transfer Courses Fact/Dimensions Design
» FACT Design Question - How to design FACT Table when computation is required on the degenerate dimension?
» 'Routing' the grain of the fact table to multpile members of multiple dimensions causes the fact table to 'explode'
» Design a snapshot Fact table in data mart from compliance-enable fact table in enterprise data warehouse
» Transfer Courses Fact/Dimensions Design
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum