i've a strange situation please see attachment. All table present in attachment are Transaction table (Fact Table). In this case i've to resolve synthetic keys? I can't join any table because different granularity. I can concatenate only Article_Promotion and Article_Discount, but this do not resolve any synthetic keys... i cannot concatenate the others tables.
I know that exist link table, but with this solution i just replay the same solution that qlik suggest me with the syntethic keys..
This situation (with synthetic keys) can create data inconsistency?
Please help me thank you.
Yes, keep the two fields in that master table as well as the concatenated key, and just add the concatenated key to the other table.
Try adding the tables one at a time in your load script and resolving any synthetic keys table by table, it will make the task much easier.
Synthetic keys don't create data inconsitency although they're not great from memory consumption. Having multiple tables joined is not good for memory either.
You can stack these tables by concatenating them without issue. Just do that.
you need at least two new keys
Transaction_ID & Hash_Code_Article as %Key1
Transaction_ID & Menu_ID as %Key2
furthermore you need to rename Transaction_ID and Hash_Code_Article and Menu_ID
or qualify them
Hi Paola Panizza,
You can rename the fields to remove the synthetic keys,if at all both the keys are necessary then you have to create a concatenation of those 2 fields.
Example:: Rename HASH_CODE_Article or concatenate TRANSACTION_ID,HASH_CODE_Article
You need to concatenate your fields to create a single field to join tables.
Add an "Exit Script;" lone to your load script to just load the first 2 tables, resolve the synthetic keys (if any) the n move the exit script to add another table and so on. Resolving the synthetic keys table by table is much easier than trying to work out the whole data model at once.
e.g. Add a new field TRANSACTION_ID & HASH_CODE_Article as HASH_KEY to Article table and ArticleDiscount tables and remove the TRANSACTION_ID & HASH_CODE_Article fields from ArticleDiscount.
Follow this logic for the other keys.
ARTICLE DISCOUNT & ARTICLE PROMOTION Table:
1) CREATE A KEY FIELD TRANSACTION_ID & HASH_CODE_ARTICE AS KEY_ARTICLE_DIS_PRO
2) RENAME THE FIELD
Menu or Article table
3)rename the field Menu_id
You need to rename the fields to remove the synthetic key.
The association is done on the new key field which concatenates the multiple values that cause the synthetic key.