topic Synthetic Key Removal? in QlikView

Synthetic Key Removal?

Wed, 27 Apr 2011 23:00:46 GMT

I am trying to remove a synthetic key but am struggling how to do so. I have looked at a few examples on the community but am having a hard time translating this to my data model.


//First two tables get combined. master_date combines all my dates together- to my calendar table. Sales_ID is a an identifier for each sales person.
OrderHeader:
LOAD
date(OrderDate) AS master_date,
SQL SELECT *
FROM OrderHeader;
SalesTerritory: //this table just joinsinto the OrderHeader table
LEFT JOIN (SalesOrderHeader) LOAD
text(BusinessEntityID) AS Sales_ID,
SQL SELECT *
FROM SalesTerritoryHistory;
Quota:
LOAD
BusinessEntity_ID AS Sales_ID,
date(master_date) AS master_date,
RESIDENT QuotaTemp;
DROP TABLE QuotaTemp;

This should be relatively straight forward but I seem to be missing something. It creates a key on master_date and sales_id.

Thanks.

Synthetic Key Removal?

Thu, 28 Apr 2011 16:22:01 GMT

Well you've got Sales_ID and master_date in two different tables which is obviously the problem. That being said you need to concatenate a key like...


Sales_ID & '|' & master_date as 'Key'

I don't think I've ever seen date used as a key before but... I don't see any reason why you couldn't? I'm not exactly the go to guy for concatenating keys or anything but maybe that's a start. It's hard to say because you have SQL SELECT * in there twice and I have no idea what fields it's loading.

Synthetic Key Removal?

Thu, 28 Apr 2011 17:42:18 GMT

Yes. I have tried this. This creates a 'unique key' just as it is supposed to. However it does not connect to the 'real keys'. I would like the SalesRep_ID (275, 276, etc..) to connect back to the 'real' SalesRep_ID and the same with the master_date (1/1/2007, etc). Currently it creates a unique key like this: 0|1/1/2007. Can I break down the key into its two parts and connect it back up within the OrderHeader and quota table? Or using a Resident Load?



if(SalesRep_ID='|',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key //my attempt to break it apart
SalesRepID_ID & '|' & master_date as 'Key' //creates the '275|1/1/2007' key

Synthetic Key Removal?

Thu, 28 Apr 2011 17:50:18 GMT

I think this is better advice. Try this:

<pre>
Quota:
LOAD
BusinessEntity_ID AS link_Sales_ID,
date(master_date) AS master_date,
RESIDENT QuotaTemp;


DROP TABLE QuotaTemp;

Then in the SalesOrder table, add another field called [link_Sales_ID] that is exactly the same as Sales_ID. You might need to do this on a resident load and drop the initial table or you could probably just add the field twice (once for the first table the table is loaded and the other for the join).

The only problem you might have is if there are Sales_ID's in the Quota table that are not in the SalesOrder table. If there are you probably need to make a link table by concatenating the two tables or something but... hopefully you don't need to do that because that's something I am terrible at. Hopefully this advice isn't terrible as I'm lucky and rarely need to do this.

Synthetic Key Removal?

Thu, 28 Apr 2011 18:08:00 GMT

I have tried something similar. See below. I am new to the Resident Loads as well.



Concatenate (SalesOrderHeader)
LOAD
if(SalesRep_ID='|',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key
//SalesRepID_ID & '|' & master_date as 'Key'
RESIDENT SalesOrderHeader;

Quota:
LOAD
//BusinessEntity_ID AS SalesRep_ID,
BusinessEntity_ID,
//date(master_date) AS master_date,
date(master_date) AS quota_date,
//BusinessEntity_ID & '|' & master_date as 'Key',
//AUTONUMBER(''&'-'& master_date &'-'& BusinessEntity_ID &'-'&'') AS MastKey,
if(BusinessEntity_ID='Website',0,BusinessEntity_ID) &'|'& QuarterStart(master_date) AS mast_key,
//(QBusinessEntity_ID & '|' & QuarterStart(master_date)) AS master_key,
Quota
RESIDENT QuotaTemp;

DROP TABLE QuotaTemp;

QuotaID:
LOAD
BusinessEntity_ID AS SalesRep_ID
RESIDENT Quota;

QuotaDate:
LOAD
quota_date AS master_date
RESIDENT Quota;

DROP TABLE Quota;

My question now is how do calculate the quota amount? If I put Quota on one of the tables it only calculates it by date or by id. How else I can calculate this?

Synthetic Key Removal?

Thu, 28 Apr 2011 18:18:34 GMT

If you post a small .qvw that would help.

For example you have

<pre>
LEFT JOIN (SalesOrderHeader) LOAD

but I don't see a SalesOrderHeader table in the code you posted. Is the date field in EVERY table? I'm not even sure how many tables there are.

Synthetic Key Removal?

Thu, 28 Apr 2011 18:20:16 GMT

Here is my schema with the key. This generates the correct results but I do not want the key to be created. My attempts to remove it have failed.

Synthetic Key Removal?

Thu, 28 Apr 2011 18:34:40 GMT

Here is the section of my QVW. Thanks for all your help.

Synthetic Key Removal?

Thu, 28 Apr 2011 18:35:05 GMT

Make the mast_key field in the QuoteFinal table as well and then do the following:


DROP FIELDS [master_date],[SalesRep_ID] FROM QuoteFinal;

If you made the mast_key right, the fact that salesrep_ID and master_date aren't in that table won't matter b/c they are linked in the SalesOrderHeader table. Hopefully that works b/c if not... I'm lost.

Synthetic Key Removal?

Thu, 28 Apr 2011 19:00:49 GMT

Not sure if I set this up correctly. mast_key appears to be correct. It still concatenating the results 'correctly'. I did this but believe I am just running in circles.



Concatenate (SalesOrderHeader)
LOAD
if(SalesRep_ID='Website',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key
//SalesRep_ID & '|' & master_date as 'Key'
RESIDENT SalesOrderHeader;

QuotaID:
LOAD
BusinessEntity_ID AS SalesRep_ID,
Quota AS QuotaID
RESIDENT Quota;

QuotaDate:
LOAD
quota_date AS master_date,
Quota AS QuotaDate
RESIDENT Quota;

QuotaFinal:
LOAD
BusinessEntity_ID AS SalesRep_ID,
quota_date AS master_date,
mast_key,
Quota
RESIDENT Quota;

DROP TABLE Quota;
DROP TABLE QuotaID;
DROP TABLE QuotaDate;
DROP FIELDS [master_date],[SalesRep_ID] FROM QuotaFinal;

Synthetic Key Removal?

Thu, 28 Apr 2011 19:09:04 GMT

I don't think this part is right:
<blockquote><pre>

Concatenate (SalesOrderHeader)

LOAD

if(SalesRep_ID='Website',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key

//SalesRep_ID & '|' & master_date as 'Key'

RESIDENT SalesOrderHeader;

Just make mast_key on the first load of the SalesOrderHeader table. Concatenate adds rows to the bottom of the table which isn't what you want. You want it joined, but you don't even need to do a join b/c you can just do it on the first load anyway. When I say concatenate a key in this case I just mean combine two fields to make a key like something|somethingelse which I guess is a poor choice of words on my part. In this case you shouldn't need to actually use the CONCATENATE statement for anything... I don't think. If you had a third table with mast_key then you probably would. If you could just post the qvw or a sample of it that would make it a lot easier b/c your trying to use two different "master" keys and I really don't see why you would need to do that but I can't tell without seeing it.

Synthetic Key Removal?

Thu, 28 Apr 2011 19:32:47 GMT

I tried this. The issue is the 'master_date' (OrderDate from SalesHeader table) and 'SalesRep_ID' (BusinessEntityID from Employee and Territory) are in separate tables. So I need to join them together. No one table besides the quote table contain both date and SalesRep_ID fields I need to make the join.

I have uploaded my QVW again. Let me know if this does not work for you. You have been a great help.


//This with Concatenate (SalesOrderHeader). Does the same exact thing. You are correct.
BusinessEntity_ID & '|' & master_date AS 'Key',
//when I run this code. It runs out of virtual. So something is not right with this...

LEFT JOIN (SalesOrderHeader) LOAD
 //if(SalesRep_ID='Website',0,SalesRep_ID) &'|'& QuarterStart(master_date) AS mast_key
 SalesRep_ID & '|' & master_date as 'Key'
RESIDENT SalesOrderHeader;

Synthetic Key Removal?

johnw — Thu, 28 Apr 2011 23:19:03 GMT


Jacob Bingen wrote:Here is my schema with the key. This generates the correct results but I do not want the key to be created. My attempts to remove it have failed.

Is this your original schema from before you tried to remove the synthetic key? If it generates the correct results, why do you not want the key to be created?

Synthetic Key Removal?

Mon, 02 May 2011 21:05:33 GMT

This is the original schema. It creates a synthetic key. I do not want the synthetic key to be generated. I am having a hard time creating a 'real' key that works.

Re: Synthetic Key Removal?

Fri, 06 May 2011 12:47:02 GMT

Figured this out.

if(BusinessEntity_ID='Website',text('Website'),BusinessEntity_ID) &'|'& num(QuarterStart(quota_date)) AS mast_key,

Had to ensure the data types were the same for the key to properly connect.

Re: Synthetic Key Removal?

johnw — Fri, 06 May 2011 21:18:22 GMT

Again, if the synthetic key generated the correct results, why did you not want the key to be created? Why did you think it was a problem?

A synthetic key is functionally identical to a real key of the same structure, which is what you've now built. QlikView simply creates it for you instead of requiring you to go to the effort you just went to. Now, replacing synthetic keys with real keys IS good experience. It tells you something about what a synthetic key IS. Probably every developer should do it once or twice. But your application isn't now better as a result. I'm betting your script now takes longer to execute and that your charts are no faster. So now that you've taken the effort to hopefully understand how real keys and synthetic keys are related (by essentially duplicating the synthetic key), it might be a good idea to just go back to the synthetic key.

I probably should have just explained up front why you didn't need to get rid of it instead of asking why you wanted to. But I wasn't anticipating the forum being shut down right then, and I'm also genuinely curious. I would really like to know why people have concluded that synthetic keys should be replaced with composite keys because the idea is ubiquitous. Heck, there are even suggestions to do so in the reference manual. I have the feeling that some trainers and consultants are telling people that synthetic keys are always bad and should always be removed. And certainly a lot of people see them in bad data models, and maybe conclude that the symptom is the disease. Synthetic keys ARE sometimes bad, but only in the sense that sometimes your data model is bad, and some synthetic keys are a result of that bad data model. But to the best of my knowlege, simply replacing a synthetic key 1:1 with a real key is useless. If the data model was bad, doing that doesn't fix the data model - you're removing a symptom, not curing the disease. And if the data model was good, changing a synthetic key to a real key gives nothing of value, wastes developer time, and slows down the script.

More infomation here:

http://community.qlik.com/thread/10279

Oh, good lord. The forum move completely trashed the formatting in some of the posts, dropped included pictures, and so on. I'm going to need to fix that. Or maybe just start over and write a document with everything from the thread that I consider important.

Synthetic Key Removal?

Mon, 09 May 2011 17:52:34 GMT

The requirments of the document required it to be removed. Yes this is being taught in training classes as something 'bad.' I noticed no performance increase or decrease when removing the automatically created key into the key I created.

I have multiple synthetic in our production application and none of them effect the performance of the application.