Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have three fact tables and two dimension table. This will form a synthetic key between
example :
fact order : date, orderid, total_amount,order_item_id,
fact order_item : date,order_item_id,product_id,amount
fact cart: date, no_of_carts,product_id
product_dimension: product_id,productname
calendar_dimension : date,weekno, month,year
this forming 3 synth keys... how to avoid it... actually date and product_id , date and orderid are common in two tables .
need to create pivot table show the below dim,measures in single table format
date ( year to date drill down) , productname,amount,orderid,no_of_carts
and create some individual reports for analysis...
wat is the best way to model the data.... how to avoid date from synth key
May be one of the below
1. Using ALIAS to rename the field
2. Link Table / Bridge table for your tables
creating alias for necessary join will create a circular reference ...
can you give me an example of how can I create a link/bridge table ??
assume there are two fact tables :
order_item_fact: orderid, order_item_id,productID,date,order_amount
cart_item_fact:cartid,cart_item_id,productID,date,cart_amount
dimension:
product_dim: productid,productname,product_hierarchy
date: date, week,month,qtr, year
Thanks for the quick reply:
PLease assume the below scenario : even this will create one synth key. how to avoid that
productID, date will create a synth key here
assume there are two fact tables :
order_item_fact: orderid, order_item_id,productID,date,order_amount
cart_item_fact:cartid,cart_item_id,productID,date,cart_amount
dimension:
product_dim: productid,productname,product_hierarchy
date: date, week,month,qtr, year