Synthetic keys & Best practice

[#1]

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

[Gysbert_Wassenaar]

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

And this blog post: Synthetic Keys

[#3]

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

[Gysbert_Wassenaar · Accepted solution]

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

[#5]

Thanks very much for this article.

Florian

[prashantbaste]

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