synthetic key and virtual memory

[#1]

Hi to all,

I´m adding tables to my qlikview constanly. I think the volume I´ve been working with It hasn´high (8 tables), but one one them has 175.000 registers. I told you that because when I try to upload my next table appears me an error message saying something like ' OUT OF VIRTUAL MEMORY'.

On the script, my synthetic key is:

$Syn 1 = SOCIEDAD+BLOQUEO+MATERIAL
$Syn 2 = SOCIEDAD+PEDIDO
$Syn 3 = SOCIEDAD+CTA_MAYOR
$Syn 4 = SOCIEDAD+CTA_MAYOR+AÑO

As its definition is automatic, I don´t know which is the problem. Have I must re-define my tables? Is there any way to improve or analyze synthetic key inside qlikview? Have I to extend my computer memory?

Thank you very much, Fiber

[#2]

HI Fiber,

your synthetic key is not the essential topic, nevertheless you should avoid them by creating surrogato keys i.e. by concatenating these fields to one keyfield (of course in all according tables).

For your memory issue, please take a look at others threads using your keywords and you will find a lot of good stuff about to less memory.

regards, Roland

[#3]

Synthetic Key should be first removed by creating keys on each table.

It is possible that Synthetic key is not the problem but I would check that first because at the end Qlikview create Synthetic keys based on Synthetic keys, then link each of them, it can really highly increase the memory usage on a application.

Regards,

Sébastien

[#4]

Thank you for replying me (both of you).

If I understand you, first thing I must do is re-check my synthetic key definition to improve qvw performance. I started to use qlikview more intensively three months ago, and I always understood that the automatic synthetic keys were the way to relation diferent tables (through table field). Now that file is growing I find other new problems.

I know SQL language but I don´t know what is better way to create this new fields you are talking about to perfomance my new synthetic keys. I could do an access view or on the qlikview script. The first option I think I lose qlikview properties (I really don´t understand); the second one, I don´t know how to do it.

I am really happy about this problem now. It would be worst if my file were bigger.

Appreciate any response. I´m a bit confuse....

Fiber

[Anonymous]

From day one of working with Qlikview, I've always been instructed to eliminate ALL synthetic keys, because they create more work for the program. To eliminate them you combine the fields in which the synthetic keys are built. Thus creating a direct link between two tables, rather then a synthetic key table.

[johnw · Accepted solution]

If all you do is create concatenated keys that match the synthetic keys, all you're doing is wasting time (or perhaps at best getting an education). Your concatenated keys will take more time to load, then take just as much memory and be just as slow to process. Basically, synthetic keys ARE concatenated keys, just generated by QlikView for you.

Beginners are commonly told to remove them because for most beginners, synthetic keys usually appear by accident instead of on purpose. They're therefore a red flag that there might be a problem with the data model, and the process of removing them will often resolve the data model problem, and can often be informative. Unfortunately, I think this good beginner advice has slowly turned into the much stronger, "synthetic keys are horrible; always remove them," which is going too far.

If your synthetic keys are the WRONG way to connect your tables together, then obviously remove them, and replace them with the right way of connecting your tables together. If they're the right way of connecting your tables together, only remove them if you want the practice and want a better understanding of what the synthetic keys are doing for you.

I don't know your data model, so I can't tell you what your data model should be. If it is already correct, you will need more memory, and concatenated keys won't help. If it is incorrect, it's hard to predict how much memory the correct model would require.

For more discussion of synthetic keys, you may want to read the following thread:

http://community.qlik.com/forums/t/31028.aspx

[#7]

If one thing I´ve been learning about programs is that there´s no bad solutions (answers) to solve some problems. I mean, sometimes it should be good to modify synthetic keys, but only if you really know why. This last answer is very logical and it´s in order what I´ve been thinking of qlikview. It isn´t my first problem with synthetic keys (and it won´t be the last) because I am learning by myself and with your help.

Abou my data model, I really don´t know which table will be the next, although I don´t think my data model should be wrong. I just have to improve it.

I really appreciate you linked me that thread, I will read with atention.

Thank you everybody, Fiber

[#8]

That was a very incredible and useful reading post. Now, I´m sure that my data modeling is wrong (and its meaning)

Really thank you, Fiber