Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Any feedback on this data model would be appreciated. Im trying to eliminate the synthetic keys, but Im in need of a direction. anyone would help
Hi Catt,
If the tables shown in that pic are fact tables try to build a link table. It is only an assumption . see links attached,
Avoid Synthetic Key and Loop in Data Modeling
MASTERTABLE (LINKTABLE or Concatenated FACT Table)
Cheers
Ganesh
The way to get rid of Synthetic Keys is to make a "surrogate key" by concatenating the different parts of the synthetic key into one single field.
For instance in your load script:
Complaints:
LOAD
....
STATUS & '|' & CREATION_DATE & '|' & AGE & '|' & CREATION_YEAR & '|' & CREATION_MONTH & '|' &
SITE & '|' & PART_NUMBER & '|' & PRODUCT_BRAND & '|' & PART_DESC AS %ComplaintsKey,
....;
SQL
SELECT
.....;
This %ComplaintsKey is quite long so to make it much more space efficient your could use AutoNumber().
LOAD
....
AutoNumber( STATUS & '|' & ...........
..................) AS %ComplaintKey,
You have to construct a corresponding surrogate key in the table that should connect to Complaints and concatenate the fields in the same order or they will not match. A synthetic key does not care about the order of fields that are part of the keys in each of the tables in fact. Make sure to put a separating character between each field while concatenating.
This is from the QlikView Reference Manual page 26 "Data Structures":
An overall analysis of the intended table structure by the application designer is recommended, including the following:
NCMR table:
Load-------
LEFT JOIN
COMPLAINTS table:
Load------
rename/qualify use fro AGILE table but ORIGINATOR field is key field it maintains both tables relation
[[[[or]]]]
linktable use between the tables;;;;;;
by using compositekey,,,where common fields present in tables
Hi Catt,
If the tables shown in that pic are fact tables try to build a link table. It is only an assumption . see links attached,
Avoid Synthetic Key and Loop in Data Modeling
MASTERTABLE (LINKTABLE or Concatenated FACT Table)
Cheers
Ganesh
Qlikview is quite efficient in creating these synthetic keys, there have been some argues but generally if the created synthetic keys are as you would by creating composite keys yourself, don't bother. You only need to make sure that there are no unwanted links made (You can qualify/rename these fields)
Hi,
regarding synthetic keys please also read this legendary thread from JohnW:
Should We Stop Worrying and Love the Synthetic Key?
hope this helps
regards
Marco
Hi,
an alternative method to create the already proposed combined key could be:
AutonumberHash128(field1, field2, field3, ...) as %combinedKey
hope this helps
regards
Marco
thank you Ganesh - the articles helped.
I only need 1 field to tie them all together - thats what I did.
again, thank you
you are welcome Catt. i am glad that it helped you.
cheers,
Ganesh