In QlikView, the common fields of two input files are associated automatically by creating a separate third table called $Syn1. This synthetic table stores the identified common fields and thus forms a synthetic key. Let’s see what is a synthetic key, Do they affect the data model? And why should we remove synthetic keys?
If you would like to become an Qlik Sense Certified professional, then visit Mindmajix - A Global online training platform: " Qlik Sense Certification Training Course ". This course will help you to achieve excellence in this domain.
The Synthetic key is created when two or more tables share more than one common field that signifies a composite key connection between the tables. The QlikView handles composite keys by generating Synthetic keys automatically. These keys represent all occurred combinations of composite keys.
When data is loading, if you receive a warning message regarding Synthetic keys, it is recommended to review the data Structure in the table viewer, and also it is required to verify whether the data model is correct or not. Multiple Synthetic keys represent a symptom of an incorrect data model, but not necessarily. But often, Synthetic keys occur due to an error in the script.
Synthetic keys don’t do any harm to the data model; it often provides easy to handle composites keys and helps to verify the data when working on a good data model. But, when there are more synthetic keys, QlikView may take more time and money depending on the amount of data, structure, and other factors. It is not possible to predict the actual limitations that leave with the practical method to determine them using trial and error only.
With more number of Synthetic keys and poor data model, results in poor performance. To improve the performance, verify the data model and remove the Synthetic keys that help to provide the best performance. Synthetic keys may lead to poor data modeling. If an unexpected synthetic key is created, it is required to check the data model and make necessary changes that result in a good data model. It is required to create composite keys instead of synthetic keys that help to get good clarity about the data model.
(Related Article: QlikView Tutorial)
To create a Synthetic key in QlikView, do the following steps:
First, consider input data files that are used as an input for further illustration.
Load the two tables into script or memory. Let’s assume that the data regarding the product details and sales details of two files. We used the two common fields for both the files, i.e., Product ID and Subcategory.
As soon as the files are loaded and saved in memory, use Ctrl+T to view the data model with a Synthetic key.
The data model shows the creation of the third table that provides the value of the Synthetic key as the two tables have common fields in the data, i.e., Product ID and Subcategory as shown in the figure.
To remove synthetic keys, we should verify the data model and make necessary changes if required. Depending on the requirement, we have different ways to remove Synthetic keys. Let’s discuss them further:
Just by renaming, one of the common fields can avoid the Synthetic key. For example, as in the figure, the two files have the same common name called City so we rename one of the fields as City As Dim_City that helps to avoid the creation of Synthetic keys.
The fields that are common in the tables can create a Synthetic key in QlikView. We can remove such fields by making a comment before the field name like by adding ‘//’ to the field name from the script. For e.g. as in the figure, the two files have the same common field called City, one of the fields we remove the Synthetic by adding a comment // to the City field name like //City.
You can also rename a field using the keyword Qualify. This way, we can rename the two common fields to avoid synthetic keys. With the help of Qualify statement, field names are converted into TableName. FieldName format. For e.g., as in the figure, we renamed the common field called City with the help of Qualify statement. And the field name is converted into Dim. City as shown in the figure.
By using Autonumberhash256 or Autonumberhash128 functions, we can resolve the synthetic keys. These functions create composite keys and generate a 256bit or 128bit value of each key field combination.
(Related Article: QlikView Circular Reference)
Synthetic Keys are created because of inadequate data modeling. Once an UNEXPECTED synthetic key is created, we will make some required changes to the existing data model, and as a consequence, we finish with a good data model. But if we have our own Composite Keys in place of Synthetic Keys, then we can build the Best Data Model.
For removing synthetic keys and optimizing data models, we have another two essential techniques; they are as follows:
Now we will see these two techniques through the following example.
Let us take an XYZ Bank that has year by year transaction datasets, with one or two distinct fields, but the remaining fields are identical. Bank will display the Year by Year transaction datasets.
In this case, we will load Year by Year Transaction datasets. QlikView will create synthetic keys to combine these tables.
Data Loading through Link Tables
2018: LOAD Customer_ID, Customer_Name1, Branch, Balance FROM Linked_Table_Date1.xlsx (ooxml, embedded labels table is Transactions); 2019: LOAD Customer_ID, Customer_Name2, Branch, Balance FROM Linked_Table_Date2.xlsx (ooxml, embedded labels table is Transactions1);
In the above script, we have loaded the data without using concatenate. As we have common fields in the tables, when we load the data, QlikView will create Synthetic Keys to combine tables.
Example for Concatenate
LOAD Customer_ID, Customer_Name3, Branch, Balance FROM Linked_Table_Date3.xlsx (ooxml, embedded labels table is Transactions2); Concatenate LOAD Customer_ID, Customer_Name3, Branch, Balance FROM Linked_Table_Date4.xlsx (OOXML, embedded labels table is Transaction3);
In the above script, we have loaded data using concatenate.so we will combine the two tables as they have common fields like Customer-ID, Branch, Balance. As tables are combined, synthetic keys are not created by the QlikView.
If we load this data into the QlikView, we will get a data model that includes Synthetic keys. For removing the synthetic keys, we will use the Link Table. Link Table will have all the common fields of the tables, and it will have all combinations of values. In the below screenshot you can see the tables with synthetic Keys:
Now we will remove the synthetic keys by creating a Link Table. The below screenshot displays the Link Table:
In the above screenshot, you can see the Link Table that includes the common fields like Product_Code, Branch, Employee_ID, key.
On wrapping up, usually, people want to remove Synthetic keys as it might impacts the application in terms of speed and memory. Also, they don't do any harm to the data, but it may provide low performance as if there are more Synthetic keys. However, Creating synthetic keys helps to improve the data model. In this article, we have learned what the synthetic key is? How are they created? And how are they resolved? I hope you find the required information.