In the last article, we talked about a very common issue of synthetic keys in the QilkView data model and discussed its various resolution techniques. In this article, we gonna talk about another common scenario which leads to a bad data model and causes certain performance issues while setting up the data model, which is Circular References in QlikView.
The good thing about Qlikview data model is that tables get automatically connected to each other based upon certain key names in there, called as associative data modelling. This feature is very useful, but produces certain structures which are not considered good & degrade the report performance. One of them is these unwanted structure is known as circular reference. This logical loop i.e. circular reference occurs when there is two or more path of association among three or more tables i.e. from one table to another can be reached via multiple paths.
Let’s consider an example where we have three tables
a. Clients – ClientID, ClientName, ClientLocation AS Country
b. Orders – OrderID, ClientID, ProductID, Sales
c. ProductRiskValue – ProductID, RiskValue
Data model will look something like:
This looks fine as of now. Let’s add ‘Country’ column in the ProductRiskValue table.
When we load these set of tables, below will be prompted:
This warning clearly specifies two things, 1. Due to the circular reference / loop this may result the ambiguous results and 2. Qlikview will automatically handle the situation by setting one or more tables as loosely coupled table(s). Look at the data model:
Here, relation between Orders & Clients tables is via ClientId whereas you can get from orders to the ProductRiskValue either directly via ProductID or via the Clients tables (ClientID, Country) and therefore a circle is formed among these three table which is called as circular reference.
As seen in the table view, Orders table is appearing as loosely coupled table which means there can be ambiguous results around it. Let’s put the fields into a table box:
Sales for (example) ‘ABC Bank Ldt.’ is reported wrong because ideally this client is associated with UK which in turn to ProductID=10 & hence sales of only 10000 should have been listed there.
More about Loosely coupled table – As seen, QlikView marks one of the tables as “loosely coupled”, which means that the logical inference cannot propagate through this table. Qlikview usually sets the loosely coupled table as the largest table in the loop (most often a fact table). However this automatic behavior can be overridden by a setting in the script as below: