Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

In data modelling and in Business Intelligence there is something called connection traps. These are inconsistencies in the data model that sometimes cause problems. This blog post is about describing the fan trap and the chasm trap and how these should be handled in a Qlik data model.

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:

 

Fan trap.png

 

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.

 

Fan trap relation.png

 

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.

 

Fan trap corrected.png

 

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.

 

Chasm trap relation.png

 

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.

 

HIC

 

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:

Circular References

Synthetic Keys

To Join or not to Join

8 Comments
qlikviewwizard
Honored Contributor II

Hi HIC,

Nice post. Same concept is also there in SAP BO. Thanks for information.

0 Likes
2,709 Views
Partner
Partner

Your blog highlighted how smart Qlik engine is, compared to SQL engines

2,709 Views
gautik92
Valued Contributor III

Nice Post..very Helpful..Thanks for sharing

0 Likes
2,709 Views
Partner
Partner

Hi HIC,

Your blogs help developers to understand and appreciate the power of Qlik engine. This blog is another example of the power of Qlik engine.

Thanks

2,709 Views
nishant_jadhav
New Contributor III

Thanks HIC,

I learned this concept when I worked on SAP BO. But trap names are exactly vice-verse. Great thanks for clarification. It was a question in my mind since long time. Good to know how to handle traps.

0 Likes
2,709 Views
ecolomer
Honored Contributor II

Very Helpful...

Nice Post

Thanks for sharing

0 Likes
2,709 Views
Partner
Partner

Bookmarked to be used when people ask about the advantages of the Qlik engine.

0 Likes
2,709 Views

Hey, What's Up. I am bit confusing here. In real time oriented where we can use these two Traps. Can i know your information about this. Thanks.

0 Likes
2,709 Views