topic Removing Synthetic keys in QlikView

Removing Synthetic keys

Anonymous — Wed, 09 Mar 2011 22:55:32 GMT

Could anyone suggest me a better way to remove synthetic key from the datamodel with following tables:

Available:
LOAD TESTER,
TESTER_KEY,
TESTER_CATEGORY,
TESTER_VENDOR,
LOCATION,
TESTER_VENDOR_AND_NAME,
TESTER_TYPE,
DAY_DATE,
DATE_KEY,
DAY_TYPE,
YEAR_QTR,
WEEK,
SUN_WEEK_START_DATE,
YEAR_MONTH,
YEAR,
HOUR_SEG_PRIME,
AVAILABLE_HOURS
FROM
[\\QLIKVIEW\PDS Data\QCT Analytics\Available.QVD]
(qvd);

Used:
LOAD USED_HOURS,
DATE_KEY,
DAY_DATE,
OPERATION_KEY,
OPERATION_NAME,
TESTER_CATEGORY,
TESTER_NAME,
LOCATION,
HOUR_SEG_PRIME,
SUN_WEEK_START_DATE,
TIME_KEY,
TESTER_KEY,
PROD_TYPE_NAME,
PROJECT_NAME,
SITE,
USER_ID,
JK_TESTER_KEY,
DEPARTMENT,
TESTER_VENDOR,
TESTER_VENDOR_AND_NAME,
DAY_TYPE
FROM
[\\QLIKVIEW\PDS Data\QCT Analytics\USED_HOURS.QVD]
(qvd);

Removing Synthetic keys

Oleg_Troyansky — Wed, 09 Mar 2011 23:14:39 GMT

This largely depends on the kind of analysis you need to perform with those two tables.

Basically, there are three ways:

1. Link Table - you build a table connecting those two, with all the common keys concatenated. This table will look a lot like your currebt Synthetic key table, only you generate the concatenated key explicitly.

2. Concatenate the two tables into a single "Fact table"

Those two methods are described in the standard "Developer II" training class, and also mentioned several times within this forum.

3. There is a relatively recent thinking that these kinds of synthetic keys are harmless, and are not so bad to live with. Personally, I'm of the opinion that we are still better off building our data models explicitly and not leaving the job of generating keys to QlikView, but the option of not worrying about it exists.

cheers,

Removing Synthetic keys

mazacini — Wed, 09 Mar 2011 23:22:18 GMT

RE: Oleg's point #3 - you might find this post interesting.

http://community.qlik.com/forums/p/25440/97137.aspx

Joe

Removing Synthetic keys

Anonymous — Wed, 09 Mar 2011 23:41:48 GMT

Thank you both for the responses , i guess i can live with the synthetic keys as i dont see any performance issues and data issues as of now with my data model..