Re: how to Remove synthetic key with multiple facts has common calendar i.e. common date dimension
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).