Synthetic keys in Qlikview
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.
- 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:
- Renaming fields for unique names , using Alias, AS, or Rename Fields.
- Renaming fields (Using QUALIFY): When common fields causing synthetic keys are not same field (not containing similar values), these are actually different fields with the same name. Renaming can be done by using “AS” clause. We can also achieve this by using QUALIFY statement. With qualified statement, field names are converted in the “TableName.FieldName” format.
- Removing fields: When common fields causing synthetic keys are not required in data model and doing so will not affect the relationship between two tables. Removing fields can be done by commenting or removing fields from load script.
- Commenting out one of the connected table’s key fields.
- Autonumber/ Composite Keys: When we know common fields causing synthetic keys are important for data model, then we can create our own key to handle composite keys. We can also use Autonumber/Autonumberhash128/ Autonumberhash256 functions to create composite keys. This will create a unique bit value for each distinct combination of the concatenated columns. Autonumberhash128 and Autonumberhash256 creates 128bit and 256bit values respectively. Please note Autonumber may be problematic in applications generating the QVD files for use in other QlikView applications.
- Usage of Hash128 function to create unique key fields.
Enroll for Instructor Led Live Mindmajix Qlikview Training if you are interested