Synthetic Keys

[Anonymous]

Hi All,

I have started this discussion thread for the Clarifications required on the topic of Synthetic Keys. Below is My understanding of the Synthetic Keys. Please confirm if correct or  provide any suggestions if wrong.

1) Synthetic Keys are Not harmful as is the common Perception otherwise. Synthetic keys can be present in the data model without any problem as witnessed by me too

2) Now, i came across the following statement in the manual "synthetic keys are possible combined values of the matching columns in two tables". What does this exactly means?. shouldn't it be a concatenated value of Two or more matching Columns? . What does Possible combined value means?. Consider i have two tables 1(Fields A, B, C, D)  and B(Fields A, B, C, E), now what will be the synthetic key(Is it AB, AC, ABC)?. Isn't It is supposed to have a single concatenated field(ABC), same as how we will create a manual composite key?

3) Is synthetic table a link table automatically created by Qlikview?

Thanks in Advance

[md_qlikview]

Hi Sonthu,

PFA files for your reference. Synthetic keys are created between two or more tables whenever there are two or more common fields between them. Refer my attached sample files. For the example you have specified in your question, it will create synthetic key between Table 1 and Table 2 which will contain common fields i.e. A,B and C as per qlikview association property.As far as your third question synthetic table is created automatically by qlikview, every time when there are more than one common fields(having same name) between two or more tables.

[JM]

Hi,

I also believe Synthetic Keys are not harmful, but it's not good practice to have them in the data model. Synthetic table is just like another table we create to normalize data model. I will do this normalization myself rather than depending on Qlik engine do decide on such things.

Synthetic key is created when you have more than one table field forms association and all common fields will be part of it. In you example: A, B and C will be part of synthetic key.

I think it is not a link table you may call it a simpler version of it, just like calling a square, a rectangle in't incorrect. Because a Link table handles a multiple granularity but synthetic key is 1-1 relation.

Hope this helps.

Cheers,

J.

[Anonymous]

Thanks Jawed for your reply

Now when you say A, B and C will be part of synthetic key, it means it will be concatenated right?, so i do not understand why in the manual they have mentioned "possible combined values". Can you shed some light on this.

Also do you have any kind of examples to share for  "Link table handles a multiple granularity but synthetic key is 1-1 relation"

Thanks in advance

[arvind_patil]

HI Sonthu,

synthetic table automatically created by Qlikview. But link table is a different concept.

Thanks,

Arvind Patil

[PrashantSangle]

AFAIK, Possible combined value means after concatenating all fields in your case (A,B,C) all combined value will store in synthetic table.

For Example your have data like below

test1:

LOAD * INLINE [

    A, B, C, D

    1, 1, 1, 4

    2, 2, 2, 5

    3, 3, 3, 6

];

test2:

LOAD * INLINE [

    A, B, C, E

    1, 1, 1, 7

    2, 2, 2, 8

    3, 5, 3, 9

];

then synthetic table contain 4 rows

A,B,C

1,1,1

2,2,2,

3,3,3

3,5,3

Hope it clear term Possible combined value.

Regards,

[JM]

There could be many approached to solve this:

Using above example:

1. Make a key by concatenating or generating Hash for the common fields(Columns A, B, C).

2. Keep common fields(Columns A, B, C) in one table and remove them from all the remaining.

For link table read Concatenate vs Link Table

Cheers,

J.

[ahaahaaha]

Hi,

1. QlikView/Qlik Sense always after completing the script binds fields from different tables, if they have the same names.

2. If two or more tables have several fields with the same names in their structure, then automatically creates a synthetic key that combines these fields into one value.

3. Ideally, two tables should be associated one field. If two or more tables are automatically linked QlikView across several fields, you always need to evaluate the nature of this join, so that when analyzing the data is displayed correctly (keep track of one-to-one and one-to-many relationships). If you incorrectly link tables, at charts you can get unexpected results.

4. If there is still a need for associate  two or more tables in several fields, you can either use the capabilities of platform QlikView/Qlik Sense or create a synthetic key manually, creating an additional field in all tables that contains all possible combinations of the fields which participating in the key. I recommend to read the book "QlikView 11 for Developers", Chapter 8. Data Modeling Best Practices.

Regards,

Andrey

[Anonymous]

thanks for the reply Prashan, now the term is clear for me

[Anonymous]

Gotcha Jawed, this is what i was looking for.

Now moving forward with your example, consider i have one more table 3(Fields A, F,G,H) where F,G,H are measures, likewise i have D in table 1 and E in table 2 as measure(Table 1, 2, 3 are all fact tables). Should i create a link table for these three tables or i can create a table test 3 and qlikview will automatically associate(Field A) with test 1.

Thanks in advance