Data Model / Synthetic Keys

[leocattqv]

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

[petter]

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:

• Forming the own non-composite keys, typically using string concatenation inside an AutoNumber script function.
• Making sure only the necessary fields connect. If for example a date is used as a key, make sure not to load e.g. year, month or day_of_month from more than one internal table.

[pratap6699]

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

[ganeshreddy · Accepted solution]

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

[stigchel]

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)

[MarcoWedel]

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

[MarcoWedel]

Hi,

an alternative method to create the already proposed combined key could be:

AutonumberHash128(field1, field2, field3, ...)  as %combinedKey

hope this helps

regards

Marco

[leocattqv]

thank you Ganesh - the articles helped.

I only need 1 field to tie them all together - thats what I did.

again, thank you

[ganeshreddy]

you are welcome Catt. i am glad that it helped you.

cheers,

Ganesh