Need help on synthetic keys!

[#1]

Hi All,

I am new to qlikview. I don't have much idea about the synthetic key in qlikview. Something like how it formed and whats the problem if it is formed. I will be thankful to them if someone share their experience about synthetic keys and how to avoid that.

In the below images: In the first data model data retrieval is normal and speed. And in the second data model it is very slow and frequently i am getting out of object memory error. I don't the reason for that. Hope some one can explain me for my understanding.

Image:1

Image: 2

Regards,

Rikab

[Miguel_Angel_Baeyens]

Hello Rikab,

In your case it seems that you have two different tables with two fields named alike. That is what is causing that synthetic key problem. For QlikView, a synth key means that some tables have identical fieldnames, as far as in QlikView there are no "primary keys" as in relational database managers, you can only reach one table in one way, meaning you can only link two tables with one field.

As the way of linking is having both fields the same name, it misleads QlikView, and the way it has to ask "Which way I should follow" or "What field is the main for you" is creating synth keys. The process takes a long time (computing every possible combination), and sometimes even crashes the application or bursts cpu usage.

Although not very technical, I think the explanation is valid for your problem. Hope this helps.

[#3]

Hi Rikab,

A synthetic key is created when more than two fields from the tables in your

datamodel share the same name. As Qlikview's logic is based on associative fields

it will try to associate the same fieldnames with each other which can cause serious

performance problems.

You should rename one of the key fields to have it linked to the desired table or

concatenate the fields 'account+itemcode' in both tables and then Qlikview will join

them accordingly without synthetic keys.

A synthetic key doesn't mean you have an error and it not directly bad but can cause serious

performance issues when working with large datasets.

Regards,

Rishi

[johnw]

rishi wrote:A synthetic key doesn't mean you have an error and it not directly bad

I would like to emphasize this. At worst, a synthetic key is a warning that you might have something wrong. And sometimes, synthetic keys are the highest performance option for the data you want to present. See my post here and the thread in general for an example:

http://community.qlik.com/forums/p/23510/89975.aspx#89975

I think that synthetic keys have a bad reputation because they typically appear by accident in a bad data model. The bad data model causes serious performance problems, and also causes the synthetic key. The way we "fix" synthetic keys often fixes the data model, and the performance problem goes away. But it isn't technically the synthetic key causing the performance problem. Both are just symptoms of the bad data model.

And synthetic keys don't necessarily indicate a data model problem, and shouldn't necessarily be removed. They can also appear in good data models. In that case, they are unlikely to cause any performance problems, and leaving them in place may well be a better solution than trying to get rid of them.

[#5]

Hi all,

I agree with John. If possible, "get rid of them". And in your model my advice is to use "Concatenate" like this:

[sales]
LOAD *
SQL SELECT *
FROM Items;
Concatenate
LOAD *
SQL SELECT *
FROM sales;

bye