topic Re: Data Model in QlikView

Data Model

Fri, 06 Dec 2013 13:58:23 GMT

How to get rid of Synthetic keys in the data model ? ....

Re: Data Model

Anonymous — Fri, 06 Dec 2013 14:00:33 GMT

use a link table.. or change the field names that are not required to be linked.. or create a composite key..

Edit.. Check this.. http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Re: Data Model

swuehl — Fri, 06 Dec 2013 14:00:52 GMT

Link your tables by only 1 common field name.

Create a combined key if you need two or more fields to be involved or just leave the Syn key as is.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Re: Data Model

Fri, 06 Dec 2013 14:18:54 GMT

You get synthetic keys when several fields of 1 table are linked to these fields in another table. So,

1) try to reduce the common fields : if you have date, month, year in the fact table, you could remove month and year from it : just the date is enough to do the link with a table composed of date, month, year ....

2) create your own keys

a) you can concatenate them

Field1 & '|' & Field2 & '|' ... as UniqueKey in your fact table

Field1 & '|' & Field2 & '|' ... as UniqueKey, Field1, Field2 ... in your link table

b) use the AutonumberHashXXX that will create a unique integer for each couple given in argument

AutonumberHashXXX(Field1, Field2, Field3 ...) as uniqueKey in Fact Table

AutonumberHashXXX(Field1, Field2, Field3 ...) as uniqueKey, Field1, Field2, Field3 ..; in the link table

But I encourage you to read these two posts by Johk Witherspoon and Henric Cronström:

http://community.qlik.com/message/10279#10279

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

Fabrice

Re: Data Model

ngulliver — Fri, 06 Dec 2013 14:26:04 GMT

Hi, Anitha.

Have a look at the blog:

http://www.quickintelligence.co.uk/perfect-your-qlikview-data-model/

this discusses the use of synthetic keys and best ways to optimize your data model.

Cheers,

Neil