Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jithu
Contributor III
Contributor III

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

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

Labels (4)
4 Replies
Anil_Babu_Samineni

May be one of the below

1. Using ALIAS to rename the field

2. Link Table / Bridge table for your tables

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
rubenmarin

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).
jithu
Contributor III
Contributor III
Author

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

 

jithu
Contributor III
Contributor III
Author

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