I've got a client who wants a Section Access security model, whichs filters the data model from multiple directions.
In brief, the scenario is very similar to the consulting notion of "Zip code revenue" vs. "Headcount revenue", in that securely filtering to either would exclude values from the other. The client wants security, but would like to see complete data from both approaches to be visible.
I imagine this is possible just by using two reduction field tables, which would intersect, but wanted to see if anyone has done this and can share some do's and don't's wisdom.
Yes, this is possible: If you use two reducing fields, QlikView will return the intersection of the two reductions. E.g. if a user in Section Access is associated with product A and zip code X, he will only see the records that pertain to A and X.
The only tip I can think of is to use strict exclusion. If you don't QlikView will return an incorrect data set in the cases where the reducing field value doesn't exist in data.
(If you would want a security model where the user should see records that pertain to A or X, this is also possible but more complicated.)
You are exactly right, and I was able to verify this in a testbed application. I based the two reduction fields on non-intersecting values, and was promptly locked out (strict exclusion).
So, I've learned that I need an "OR" security model, or a union of A+B+C in a Venn diagram.
I need to allow a user to select both all revenue for a specified geographic hierarchy value, but also all revenue for a person based in that user's geography, but not solely for revenue within that geography. For instance:
Say my region is the United States, and security filters lock out other non-U.S. regions. So, I can see all sales within the United States (A+B). Now, also show me sales for all salespeople based in the U.S. (B+C), even if the sale is outside the United States (C). (Area B for this example would be much larger.) So, the Section Access filter must expand to allow these exceptions. All other data should still be securely filtered.
You can accomplish this by creating a table between your Section Access table and your reporting data that contains the relationships you need. Here is a simple example of how you could do it.
It would have to be created in two loads: one containing the Order ID and Sales Region and another concatenate load containing the Order ID (again) and Salesperson Region. The Order ID assocates to your reporting data and the Region field associated to your security table. (The third column is included below for informational reasons and wouldn't be needed in practice. You could then make it a distinct load and reduce the number of rows, when the Sales and Salesperson Regions are the same.)
I have a very similar requirement, the section access needs to be applied with OR logic (UNION of the reduction fields) but also needs to have some exclusion (some particular information are restricted to a few users). What would be the best approach to achieve that? Using generic keys?