Synthetic keys in Qlikview :
‘Synthetic keys occur when two or more tables have two or more fields in common.’
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.
The Table Viewer allows you to, use the Internal Table View / Source Table View drop-down list options in the Table Viewer menu bar, and to view both the internal (QlikView) tables created in the script and the source tables.
The default selection upon opening Table Viewer is the Internal Table view. This view displays the data model as created in the QlikView script, with all the tables and connections represented. The alternate view is the Source Table view, and this view displays the data tables as they are loaded.
The main difference between the two views is the presence or absence of synthetic tables and data fields that are automatically added by QlikView in order to make sense of the data. In the Internal view, the synthetic tables or fields displayed. In the Source view, synthetic fields are not present, but are represented as multiple connections between tables and data fields. See the next section, for more information on synthetic tables and keys.
1. Synthetic tables
Composite/ synthetic tables and keys:
If tables with identically named data fields are loaded into QlikView (and you do not QUALIFY or rename the fields to unique names), the data fields will automatically associate. The data fields that two tables have in common will link as key fields in their respective tables. This is the associative nature of QlikView.
When you have more than one pair of identical data fields (keys) spanning two or more tables, QlikView creates synthetic keys. When this scenario arises, QlikView creates another table (a synthetic table), and adds it to the data model: the $ Syn table. This synthetic table contains a synthetic key that is composite of all the combinations of the multiple key fields connecting the tables. This synthetic field, denoted again by the $ Syn symbol, is also placed in the original data fields connected by multiple fields. This new synthetic field is called a synthetic key.
Synthetic keys are undesirable, because they sometimes indicate poor data modeling and this can cause performance problems. Synthetic keys by themselves may not always cause obvious problems. They do not always lead to circular references. They can cause performance problems when there are many synthetic tables with multiple keys each. At times, QlikTech reports that synthetic keys can also cause erratic query results. If you have a synthetic keys and tables present in your data model, try to eliminate them as early as possible.
Eliminating Synthetic keys and Tables:
Synthetic keys are almost always undesirable in qlikview as the calculations in a data model involving Synthetic keys are always resource intensive. Also the data model will look clumsy and often is difficult to understand. So they must be eliminated or removed.
Synthetic keys and tables can be removed from the data model in one of the following approaches:
Free Demo for Corporate & Online Trainings.