topic Re: Synthetic Keys in QlikView

Synthetic Keys

Anonymous — Tue, 25 Jul 2017 20:15:52 GMT

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

Re: Synthetic Keys

md_qlikview — Tue, 25 Jul 2017 20:34:33 GMT

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.

Re: Synthetic Keys

JM — Wed, 26 Jul 2017 00:28:17 GMT

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,

Re: Synthetic Keys

Anonymous — Wed, 26 Jul 2017 05:07:36 GMT

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

Re: Synthetic Keys

arvind_patil — Wed, 26 Jul 2017 05:11:31 GMT

HI Sonthu,

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

Thanks,

Arvind Patil

Re: Synthetic Keys

PrashantSangle — Wed, 26 Jul 2017 05:26:38 GMT

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,

Re: Synthetic Keys

JM — Wed, 26 Jul 2017 06:22:34 GMT

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,

Re: Synthetic Keys

ahaahaaha — Wed, 26 Jul 2017 06:51:36 GMT

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

Re: Synthetic Keys

Anonymous — Wed, 26 Jul 2017 11:02:54 GMT

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

Re: Synthetic Keys

Anonymous — Wed, 26 Jul 2017 11:25:16 GMT

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

Re: Synthetic Keys

PrashantSangle — Wed, 26 Jul 2017 11:56:34 GMT

Hi,

If you want to associate field A from table 3 to table 1 then Just create add new table Qlikview will automatically associate it with table 1

Regards,

Re: Synthetic Keys

Anonymous — Wed, 26 Jul 2017 12:05:20 GMT

I want my model to be a star schema, is it feasible?, can we have three fact tables connected with each other?

Re: Synthetic Keys

PrashantSangle — Wed, 26 Jul 2017 12:17:00 GMT

I want my model to be a star schema, is it feasible?,

> completely depend on number of records in each fact table.

can we have three fact tables connected with each other?

> Yes you can.

Regards,

Re: Synthetic Keys

Anonymous — Wed, 26 Jul 2017 12:34:42 GMT

Thanks for the answers Prashant

Is there no need to create a link table if we are using three fact tables in a star schema?

thanks in advance