Section Access: Circular References Are (Maybe) OK!

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.
This entry was posted in Development, Security, Tips & Tricks and tagged , , , . Bookmark the permalink.

9 Responses to Section Access: Circular References Are (Maybe) OK!

  1. Jasleen Puri says:

    Very neat Vlad! I’ll try this out for sure.
    I’m guessing we can add more than one column in the second Section Access table alongwith the #ONLINESALEFLAG. Say there’s a ProductLine that needs Section Access as well. Then PRODUCTID could be added on to this table as well. Will have to check with some data how multiple Section Access columns get resolved.

  2. Jasleen, you can definitely add more than one column. However, as I mentioned in the article, watch out for synthetic keys. Those are problematic even in Section Access. I ran an experiment and found that adding synthetic keys significantly slowed down application opening time. Of course, each app is different, but keep this in mind.

    Regards,
    Vlad

  3. HIC says:

    Be careful here… Yes it will work, but it will not always do what you want. An example: If you have a user that is allowed to see product X in Germany and product Y in France, you might create an authorization table like

    User Product Country
    A X DE
    A Y FR

    This will reduce data, but not the way you want it to. You want to see transactions fulfilling
    (Product=’X’ AND Country=’DE’) OR (Product=’Y’ AND Country=’FR’)

    but you will get
    (Product=’X’ OR Product=’Y’) AND (Country=’DE’ OR Country=’FR’)

    HIC

  4. Thanks for that correction. On further testing, it turns out that QlikView DOES loosely couple the section access tables behind the scenes, it just doesn’t warn you that that’s what it is doing! Interesting, since you will get notified about synthetic keys caused by section access, so I’m not sure why it won’t warn you for a circular reference. For the scenario that I tested at my own clients, this wasn’t a problem. But your advice to use this cautiously is absolutely correct.

    Regards,
    Vlad

  5. Henric, I’ve just updated the article to reflect your cautionary advice. Thanks again!

    Vlad

  6. HIC says:

    It is really not loosely coupled either. It is rather that QlikView makes “implicit selections” before it opens the document – it first makes a selection in the first field (e.g. Product is X or Y) and then in the second field (e.g. Country is DE or FR). Finally it reduces the data before it displays the app to the user.

    So it is in fact “loyal” to the selection principles used in normal situations: OR between values within a field, and AND between different fields.

    HIC

  7. Vlad Gutkovsky says:

    Henric, I ran a test where I loaded the section access table as a regular section application table (so it became loosely coupled), and it behaved the same as you are describing. So maybe that’s what loosely coupling does? I’ve uploaded the example here: http://www.infinityinsight.com/downloads/SA_CircularTest.qvw

    Regards,
    Vlad

  8. Aravind says:

    Hi Vlad

    I have a strict requirement with my application for row level access for set of users.
    A user will have a set of records(recordid) he has access to on Table A & Table B and the second user has access to same or different set of records on Table A, B & C.
    In my application i might have as much as 10+ contatenated into fact table and another 5-8 dimension tables.
    I am trying to cover the security to be implemented @ every row on every table.
    When i implement the section access there are a lot of synthetic keys & circular references.

    Any inputs/ideas on handling this situation.

    Appreciate any guidance

    Thanks
    aravind

  9. Aravind,

    I’m not sure how Tables A – C are connected in your data model, so my answer will need to be somewhat general. First, if a particular user does not have a reduction value on a particular field, it’s perfectly acceptable to leave it null. A null value in Section Access will mean that the user won’t have reductions on that field at all (as opposed to a ‘*’, which does something else).

    Second, as mentioned above regarding synthetic keys, I would suggest that you avoid these in Section Access. My testing has shown that they work, but significantly hurt performance. If you truly need to reduce on 2 fields in the same table, concatenate those fields together and use the single field as a reduction field.

    Third, regarding circular references, that is what this entire article is about, so can you be more specific?

    Regards,
    Vlad

Leave a Reply to Aravind Cancel reply

Your email address will not be published. Required fields are marked *

Notify via email when new comments are added

Blog Home
Categories
Archives