topic how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension in App Development

how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension

jithu — Sat, 16 Nov 2024 21:40:54 GMT

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

Re: how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension

Anil_Babu_Samineni — Fri, 28 Dec 2018 09:27:38 GMT

May be one of the below

1. Using ALIAS to rename the field

2. Link Table / Bridge table for your tables

Re: how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension

rubenmarin — Fri, 28 Dec 2018 10:08:34 GMT

Hi, synth keys are created because more than one field is used to reate the relationship between tables, you can reate a composite key that merges key fields in only one field, ie:
fact_cart:
LOAD date &'_'& product_id as IdFactCart,
no_of_carts
From/Resident...

Another option is removing fields from one of the tables, in axample, if one order_id only belongs to one date, you can remove date field from fact_order and keep only the one in fact_order_item.

I think that fact_order_item should be your fact table, the others are dimensional tables that should be linked with the fact table using only one field as link (as said before it can be a composite key to use more than one field).

Re: how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension

jithu — Mon, 31 Dec 2018 05:52:54 GMT

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

Re: how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension

jithu — Mon, 31 Dec 2018 05:55:14 GMT

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