Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Should We Stop Worrying and Love the Synthetic Key?

John,

I see it, although I couldn't begin to offer a lucid thought on this topic! I'll see if I can get a couple of guys in R&D to take a look and share their thoughts.

MVP
MVP

Should We Stop Worrying and Love the Synthetic Key?

Thanks, Jason! Yes

Or
Valued Contributor II

Should We Stop Worrying and Love the Synthetic Key?

Just a quick note regarding my earlier post - I double checked, and the complexity in synthetic keys was related to the fact that key columns appeared in multiple tables, each time within a different key context. I'm sure if I had gone through and only eliminated the columns that resulted in data model issues / circular references, I'd have been left with a reasonable number of synthetic keys - but of course, when you're eliminating them anyway, it's often easier to just eliminate the whole bunch instead of trying to figure out which ones you can keep.

Looking forward to seeing what QV's technical folks have to say about this.. 🙂

Not applicable

Should We Stop Worrying and Love the Synthetic Key?

Interesting as always John.

My perspective on the whole thing stems from the fact that Qlikview rationalizes synthetic keys this way in the first place. The simplest question here is, if it's so terrible, why does QV do it at all?

The fact that QV rationalizes correctly modeled data in a way that's consistent with good design says to me that this is a totally legit method of creating these data models.

I think the trepidation with synthetic keys stems from two places:

1. Bad data modeling design
2. General lack of understanding of what a synthetic key is

As you say, bad data design results in 'bad' synthetic keys, results in bad performance, and this usually stems from lack of experience in data design. From there, people assume that synthetic keys themselves are bad, when they're not.

If you have a data model that uses synthetic keys, and that's what you intended, then by all means, use a synthetic key. I can't imagine why you would go through the trouble of rationalizing the key manually, and creating your own composite key, when QV is prepared to do the heavy lifting for you.

Had I encountered a situation where I expected to see a synthetic key in my data model, and I did see such a synthetic key, I'd leave it right where it was. I do, however, get slightly more concerned when I've had a total blonde moment, and created multiple compound synthetic keys, and my model goes all nutty.

As you pointed out in your QV documentation, it MAY do this, and it MAY do that. It's like that warning label on your coffee that has to tell you that it's hot… because some genius put it between their legs once, and got 3rd degree crotch burns. Yup, that coffee MAY give you 3rd degree crotch burns. It may not, however, if you use it properly.

I suspect that a little bad press have done synthetic keys in for the general user community.

nathanfurby
Valued Contributor

Should We Stop Worrying and Love the Synthetic Key?

Thanks John - as a beginner it's important to hear these kinds of discussions. During my training I was definitely taught that synthetic keys should never exist and should indeed be replaced by user created keys. That never made huge sense to me.

Should We Stop Worrying and Love the Synthetic Key?

There is one area where synthetic keys do make life more difficult - when using the table viewer, you can only see the data that makes up the synthetic key in the synthetic key table itself, other tables that include SynX data do not display the synthetic key value, just the other fields in the table.

This can make it a lot more difficult to determine what is going on in the associations between the data when a report does something unexpected.

MVP
MVP

Should We Stop Worrying and Love the Synthetic Key?


Colin_Albert wrote:There is one area where synthetic keys do make life more difficult - when using the table viewer, you can only see the data that makes up the synthetic key in the synthetic key table itself, other tables that include SynX data do not display the synthetic key value, just the other fields in the table.
This can make it a lot more difficult to determine what is going on in the associations between the data when a report does something unexpected.


The view IS different, but it's the synthetic key version that shows all the fields in each table, not the concatenated key version. Or have I misunderstood what you're saying?

With synthetic key:

With concatenated key:

Both views make equal sense to me, though I do prefer the less cluttered second view. It sounds like you prefer seeing all the fields. I think this is going to be a personal preference thing, not a case where one way is better than the other. After all, if QlikTech felt that the second view was more informative, they could easily display synthetic keys this way. They don't, so it appears that QlikTech feels it is more informative to display all of the fields in all three "tables".

MVP & Luminary
MVP & Luminary

Should We Stop Worrying and Love the Synthetic Key?

John,

with all due respect, this idea sounds like an interesting hypothesis, but since it's against anything we've known and were taught before, I would suggest being careful about propagating and recommending it, until we can hear an opinion from R&D about it. There is a lot of interesting stuff happening behind the scenes in QlikView, and all we can do is speculate and theorize.

Working with large data volumes, I've seen synthetic keys crashing servers, getting QlikView "hung", raising RAM requirements beyond anything imaginable - all nine yards of fun. With all this experience, I would never recommend to anyone to "love the synthetic keys".

On a similar note, I've seen an "analysis" performed in a lab with thousands of 1 and 0 values, allegedly proving that IF formulas are performing as well or better than calculations with flags or Set Analysis conditions. Later on, I've met several beginners confused by that analysis. The fact remains the same - IF formulas are killing performance on large data sets, and synthetic keys can't be good on large data sets.

It's good to challenge basics and "rock the boat" once in a while, but we need to be careful when promoting a "maverick" hypothesis into a status of an expert recommendation.

This is, of course, just my personal opinion. It would be extremely interesting to hear from R&D on this issue.

Not applicable

Should We Stop Worrying and Love the Synthetic Key?

I just get the feeling that this post owes the headline to the movie "Dr Strangelove or how I stopped worrying and love the bomb"!


SAP Consulting

MVP
MVP

Should We Stop Worrying and Love the Synthetic Key?


sharond31 wrote:I just get the feeling that this post owes the headline to the movie "Dr Strangelove or how I stopped worrying and love the bomb"!


Well of course. Big Smile "Dr. Strangelove or: How I Learned to Stop Worrying and Love the Bomb"

Which, if you think about it, is hardly a ringing endorsement for using synthetic keys. *chuckle*