I want to share an accidental discovery that will likely change the way you implement QlikView Section Access forever—it certainly did for us and our clients!
There is a simple principle in QlikView that even novice developers know: circular references are to be avoided like the black plague. Because of QlikView's unique associative data model architecture, circular references are strictly forbidden. In fact, QlikView itself will break any circular references it finds by turning one or more table links into "loosely coupled" links.
A circular reference occurs whenever it is possible to get from one table in the data model to another through more than one path. In the picture above, for example, you can get from Budget to Orders either directly (using ProductID as a key field) or through Customers. This results in a "circular" reference (and the word circular is also an apt description, since you can see that a "circle" is formed with the table links).
The important discovery that I want to share in this article is that circular references in QlikView are not a problem when Section Access tables are involved! Suppose you have a structure as follows, where the Section Access table is indicated by the dotted border. This is a pretty common Section Access use case, where a user has access to one or more geographic regions:
Now suppose that you also want to add a restriction on the user to allow him to see only online sales (as indicated by the #ONLINESALEFLAG in the Fact table). Many developers would, when faced with this problem, created a composite-key link between the FACT and GEOGRAPHY tables. And, indeed, this is generally considered a perfectly acceptable solution to this problem. The composite key would use both TerritoryID and #ONLINESALEFLAG, and might looks something like: "TERRITORY1|ONLINE". The GEOGRAPHY dimension would then double in size to create a match for all the #ONLINESALEFLAG permutations, and the section access table would link to a REGIONID/#ONLINESALEFLAG composite.
Imagine trying to implement something like that if you have more than 2 possible values, however! Suppose #ONLINESALEFLAG had 5 possible values; restricting your data model to a single table would quickly cause the size of that table to spiral out of control. Fortunately, you may not have to! As it turns out, the following data structure often works just fine:
For whatever reason, QlikView does not have a problem with circular references caused by section access tables (where the section access tables complete the "circle"). This, clearly, opens up a world of possibility when designing your data model for security. Essentially, you might now have total freedom to structure your section access to dynamically restrict users to any numbers of fields in your data model, even if those fields are in different tables!
Update, based on valuable input from the legendary Henric Cronström (see below): Use caution when implementing this technique with multiple fields. QlikView actually will loosely couple the section access tables behind the scenes, it will just not warn you that that is what it is doing. As a result of loose coupling, you cannot specify "AND" logic for multiple field reductions for those fields affected by the loose coupling. So, in my example above, you cannot grant a user access to Online-only sales for Region1 and Non-Online-Only sales for Region2. Rather, the result will be that the user will have access to (Online or Non-Online) and (Region1 or Region). The distinct values of each field will be evaluated separately, rather than together. This may be the result that you want, but it is not likely. The solution described in this article is, therefore, not likely a good fit if a user has more than a single record in the section access tables.
Note also that, for best performance and to ensure predictable results, synthetic keys still be avoided. Meaning, a section access table should not link to 2 fields within the same table. Note also that this example demonstrates a side lesson as well—that section access tables can be linked just like regular (i.e. section application) QlikView tables. In this case, we have 2 section access tables that are linked on the field NTNAME. This field will, in turn, be automatically linked by the system to the logged-on username of QlikView users.