Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
.png "Former Employee"){kind=icon}
When designing a data model, connection traps are sometimes built into the data model. It could be that the source data has been misinterpreted, or it could be that some relations are missing in the data. Usually the traps should be avoided. However this is not always possible. But as you will see, it is not a problem.
There are two main types of connection traps: The fan trap and the chasm trap.
Fan Trap:
“Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous” (1)
Example of a fan trap:
In this model a branch has several sales people. A branch also has several customers. But the above data model says nothing about which sales person is responsible for which customer, although such an assignment may exist. Instead, the data model links all sales people to all customers within the branch.
Joining the three tables will increase the number of records - every combination of sales person and customer will get a record of its own - which means that aggregations will result in incorrect numbers. A single sales person will be counted several times. This is a problem with SQL and many other database tools.
The Qlik engine is however different: Since the three tables are stored as three different tables, the Qlik engine is able to aggregate correctly anyway. A count of a non-key field from the Customers table will count just the records in the Customers table. As long as the aggregation function contains fields from only one table, the aggregation will be correct.
Hence, a Fan trap is not a problem.
However, if you have information about assignments between customers and sales people, you should of course change the data model and load this information, e.g.
But what if a customer is assigned to a branch, but has not yet bought anything? This question takes us to the next trap.
Chasm Trap:
“Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences” (1)
While a fan trap can be identified by looking at the data model only, a chasm trap can be more difficult to spot. The above data model (Branches - Sales people - Customers) may in fact contain a chasm trap. But the data model looks perfectly fine.
The chasm trap appears only if there is missing data in the middle table, e.g. if you have a customer who belongs to a branch but has not yet been assigned a sales person. Then the link between the customer and the branch will be broken and it will not be possible to see to which branch the customer belongs.
But if you don't need this link, the data model will still work fine. However, if you want this link, you can create it by adding dummy records labelled 'No sales person' to the Sales people table – one record per branch – and link unassigned customers to these. An additional advantage is that these customers will then be easily selectable. If you click on ‘No sales person’, you will immediately find all unassigned customers.
Hence, a Chasm trap can easily be handled.
Bottom line: Connection traps are not a problem in the Qlik engine.
PS On internet you sometimes find incorrect descriptions of Fan trap and Chasm trap where the two are confused with each other. The definitions I use come from the original description of traps:
[1] Thomas Connolly, Carolyn Begg: Database Systems: A Practical Approach to Design, Implementation and Management (Addison-Wesley, 1998).
Further reading on Qlik data modelling: