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.
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.
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;
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?
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.
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.
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.
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.
