hi i'am new to QlikView i just wanted to find out whats the best way to make sure that my Data model is perfect,and does not contain a lot of Synthetic keys?
Before I discuss about Synthetic Keys, let’s look at a typical QV data model (in the diagram on right hand). Here, we can see three tables “Sales”, “Branch Mapping” and “$Syn 1″. But, I had loaded only two tables (Sales and Branch Mapping) in QlikView. Another surprise pops up, when I look closely at “Sales” and “Branch Mapping” tables. There is an additional variable “$Syn 1” in both the tables.
When I encountered this effect in the data model for the first time, I was not able to identify why this was happening? To some extent, I was not bothered about this because the output of the dashboard was working fine. But, when my mentor looked at this model, I came to know that “$Syn 1” is a synthetic key and “$Syn 1 Table” is a synthetic table.
When we load two tables with a common field name in both tables, the tables get associated automatically based on the common fields. This is the associative functionality of QlikView.
However, when we have more than one common field between two or more tables, QlikView creates “SYNTHETIC KEYS” and “SYNTHETIC TABLE”. QlikView adds synthetic table (as $Syn table) and synthetic key (as $Syn symbol) to the data model. The keys are added to the user uploaded tables and are used to join with synthetic table.
Synthetic key is QlikView’s method to deal with composite keys. The Synthetic table contains a synthetic key that is a composite of all the combinations of the multiple key fields connecting the tables. In our data model, we had two fields common “Branch” and “Region” so QlikView created synthetic key and synthetic table on its own. If we look at the source table view of table viewer, it shows that two fields are connected between these two tables.
As per QlikView reference manual:
When the number of composite / Synthetic keys increases, depending on data amounts, table structure and other factors, QlikView may or may not handle them gracefully. QlikView may end up using excessive amount of time and/or memory. Unfortunately the actual limitations are virtually impossible to predict, which leaves only trial and error as a practical method to determine them.
As discussed above, Synthetic keys may get created due to poor data modeling. Whenever an unexpected synthetic key is created, we should look at data model again, make necessary changes and as a result we end up with a good data model. Please note that the important keyword in last statement is UNEXPECTED. Otherwise, synthetic keys don’t do any harm.Second reason, which I realized over time is whenever I create my own composite keys instead of synthetic keys, I have far higher clarity about the data model.Othewise, I can say that synthetic keys are good and provide ease to handle composite keys if you are working on a good data model.
To remove synthetic keys, we first look at our data model and make necessary changes, if required. We have multiple methods to remove synthetic keys but it depends upon the requirement
In general, developers want to remove synthetic keys because it is believed that they lead to negative impact on performance and memory utilization. However, this is not true. They handle composite keys in a more efficient way compared to manual keys, if we have a good data model. If you have experienced the difference in performance and memory utilization with or without synthetics, please share your thoughts through the comments below as it will benefit the community.
Synthetic keys will greatly impact the performance and it is better to avoid or removing it. This can be done by removing the unnecessary links and join the tables explicitly in the script. This is a good description on synthetic keys by Henric Cronström: Synthetic Keys
Attached document is from Qlikview and can help to understand the basic Data modeling issues and solutions.
Feel free to ask any other specific questions!
Top 10 best practices in Data Modeling. listed below
Removing Synthetic Keys are the below ways
-->Create alias Name for some columns
-->Concatenate Multiple columns into one column(Concatenated column)
-->Remove/Drop unnecessary columns and tables at the end of the script
If you got the solution for your query ,please close this thread by clicking "Correct Answer".
You can implement the link table in data modelling which will resolve synthetic key issues.