Creating Section Access on Multiple Fields in one Dimension

[AnnaRodriguez]

Our client has requested to change security on data to include multiple levels - which corresponds to the fields in Product Dimension.

Product Dimension has multiple fields, including ID1,ID2,ID3. Our current security is on ID1 only. The request is to change it so different combinations of access based on these 3 columns can be done. For instance:

1. client A should have access to ID1 = 100 and all ID values within ID2 and ID3
2. client B should have access to ID1 = 100 and ID2 = 200 and all ID3 values
3. client C should have access to ID1 = 100 and ID2 = 200 and ID3 = 300

I have created section access table that looks like below and tried testing ClientC access, but getting Access Denied message. Is there a limitation in Qlik that section access cannot be created on multiple fields w/n the same dimension (except only when those multiple fields are concatenated into 1 field)

In our process, we only get ID (or IDS) to be used for security. For instance, Client A was provided with ID1 = 100, which implied that all values available under ID2 and ID3 need to be accessible.

These IDs (especially ID2 and ID3) are not static and new IDs can be added at any time. How do I account for such setup to ensure that access is granted on different combinations of these IDs?

[Chanty4u]

Yes you can't use by default in qliksense

Try this 

Create a access composit key in your data model 

LOAD 

    *,

    ID1 & '|' & ID2 & '|' & ID3 AS ACCESS_KEY

RESIDENT Product;

SECTION ACCESS;

LOAD * INLINE [

ACCESS, USERID, ACCESS_KEY

USER, CLIENTA, 100|| // All values under 100 for ID1

USER, CLIENTB, 100|200|

USER, CLIENTC, 100|200|300

];

SECTION APPLICATION;

[AnnaRodriguez]

Thank you for your suggestion, but unfortunately it did not work. 

When i added a composite key column ( ID1 & '|' & ID2 & '|' & ID3 ) to Product Dim, it created key with values for ID2 and ID3; which are never null.

Using ACCESS as ''100||' does not exist in DIM Products, and as such, access is denied.

[marcus_sommer]

I assume that I would start with a resolving of the crosstable-structure and then creating a composite key of the resulting ID-Type and Value - probably within a dummy-app without a section access just creating an appropriate table-box + few list-boxes to see if I could select my wanted scenarios respectively which values might be needed to populate. It wouldn't be a direct working section access solution but the main-logic should be working.

[AnnaRodriguez]

Thank you all for contributing to my question. I wanted to know if there is any other way (besides building a composite key) to manage section access using multiple fields from one dimension (as i presented in the image). Apparently no, and we always need to build a composite key for such access. I had no issue building section access using composite key by using left join from bridge table to DIM product on ID1. 

Question, I used AutoNumber on composite key to manage section access. Is there any difference in performance of the app if the section access table using a varchar value (ID1|ID2|ID3) or a numeric value (1,2,3,etc). The app is 4GB in size.

[marcus_sommer]

A composite key isn't mandatory else an appropriate hierarchy might be applied. If none exists and/or they isn't complete it will need some efforts to prepare the data. It's not trivial and is quite probably much more expensive as the above hinted approach. As a starting point you may look here: Authorization using a Hierarchy - Qlik Community - 1476319

Using (autonumber) numeric values have always theoretical benefit against string-values but I doubt that this would be noticeable in this case. You may go in this direction after you have found and implemented as working solution and a real need to optimize the opening times.

[krishna20]

Hi @AnnaRodriguez ,

The below url may helps you,

https://community.qlik.com/t5/QlikView-App-Dev/Section-Access-with-multiple-fields/td-p/452720