Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Not applicable

Synthetic keys & Best practice

Hi everyone,
Yet another thread about synthetic keys. I could not find my answer in another thread, so here goes.

I have a table that contains the stock information of our products. These can be either used or non-used. I want to display the aggregated used and non-used stocks per product in my Qlikview file. I decided to do the aggregation in the script, causing both the raw stock table and the aggregated stock table to be in the data model. Qlikview then creates a synthetic key between these tables. Now my question is: Which of the following is usually best to do:

- Should I not create the aggregated table at all, and do the aggregation in the chart object using set analysis? This might make the report to become slower, right?

- Should I create a unique key with the productID and the used-property and put them in a link table between the raw and the aggregated stock tables?

Or are there better suggestions? Any document or information on this subject would be very welcome.

Kind regards,

Florian

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Synthetic keys & Best practice

Which of the following is usually best to do

Well, "don't fix it if it ain't broken" is usually a good thing to do. Do you actually have performance issues if you do the aggregation calculations in chart expressions?

Another option could be to concatenate the aggregated table with the non-aggregated data. See this blog post: Fact Table with Mixed Granularity


talk is cheap, supply exceeds demand
5 Replies
MVP & Luminary
MVP & Luminary

Re: Synthetic keys & Best practice

See this discussion: Should We Stop Worrying and Love the Synthetic Key?

And this blog post: Synthetic Keys


talk is cheap, supply exceeds demand
Not applicable

Re: Synthetic keys & Best practice

Hi Gysbert.

Thanks for your answer. I have read both these articles in other threads, and I understand the concept and that sometimes Synthetic keys are not a problem. I am just hoping that a more experienced Qlikview developer than myself can advice me on whether this would be such a case in which the synthetic key is the result of 'a poorly designed data model' or if it suits its purpose here.

Kind regards,


Florian

MVP & Luminary
MVP & Luminary

Re: Synthetic keys & Best practice

Which of the following is usually best to do

Well, "don't fix it if it ain't broken" is usually a good thing to do. Do you actually have performance issues if you do the aggregation calculations in chart expressions?

Another option could be to concatenate the aggregated table with the non-aggregated data. See this blog post: Fact Table with Mixed Granularity


talk is cheap, supply exceeds demand
Not applicable

Re: Synthetic keys & Best practice

Thanks very much for this article.

Florian

prashantbaste
Contributor II

Re: Synthetic keys & Best practice

Hi Floorian

I would recommend to do all possible aggression calculations/operations at script level only. Aggressions at  front level/chart object is not good practice.

You can use link-tables or do aggressions separately for used items & unused items. And then concatenate both results in one table (make sure tables that will be concatenated should have same structure/fields/columns).

In front-level chart object you can directly display those values.

Hope this will be useful for you.

--

Regards,

Prashant P Baste