Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Highlighted
jithu
New 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

4 Replies

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

May be one of the below

1. Using ALIAS to rename the field

2. Link Table / Bridge table for your tables

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)

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

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

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
New Contributor III

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

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