topic Re: Data Model / Synthetic Keys in QlikView

Data Model / Synthetic Keys

leocattqv — Thu, 11 Jun 2015 18:57:55 GMT

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

Re: Data Model / Synthetic Keys

petter — Thu, 11 Jun 2015 19:31:32 GMT

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

....

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.

Re: Data Model / Synthetic Keys

pratap6699 — Thu, 11 Jun 2015 19:35:21 GMT

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

Re: Data Model / Synthetic Keys

ganeshreddy — Thu, 11 Jun 2015 19:52:16 GMT

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

Re: Data Model / Synthetic Keys

stigchel — Thu, 11 Jun 2015 20:03:02 GMT

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)

Re: Data Model / Synthetic Keys

MarcoWedel — Thu, 11 Jun 2015 23:00:42 GMT

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

Re: Data Model / Synthetic Keys

MarcoWedel — Thu, 11 Jun 2015 23:03:36 GMT

Hi,

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

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

hope this helps

regards

Marco

Re: Data Model / Synthetic Keys

leocattqv — Fri, 12 Jun 2015 00:39:25 GMT

thank you Ganesh - the articles helped.

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

again, thank you

Re: Data Model / Synthetic Keys

ganeshreddy — Fri, 12 Jun 2015 07:58:52 GMT

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

cheers,

Ganesh