Section Access On Multiple Tables with different dataset using Same field

[krishgk_ispot]

HI All,

I am trying to  implement section access for different 'grains' of the same data set.to apply Section Access on a Qliksense app.

I have Department Dimension table which has all the departments data Which has been joined with Sales and Orders table using DeptID key field.

I would like to apply section access for

User1 Need to have access to DeptID's - 1&2 from Sales table but only DeptID - 3 from Orders table.

User2 Need to have access to All the DeptID from Sales table but only DeptID - 3,4,5 from Orders table.

User3 Need to have access to All or some of the DeptID's from Sales table but No access to Orders table.The fields from Orders table shouldn't be visible for User3. If possible hide whichever sheets uses fields from Orders table.

reanfadyl

Can you please help me with the sample app  you were using to test this and inputs/advise on achieve this section access.

[reanfadyl · Accepted solution]

Hi Krishnan,

Attached is my solution for this problem.  I combined three concepts to create the solution...

1. Concatenated Star Schema

2. Section Acess

3. Generic Keys

These have all been talked about a lot before see:

https://community.qlik.com/blogs/qlikviewdesignblog/2014/06/02/data-reduction-using-multiple-fields

https://community.qlik.com/blogs/qlikviewdesignblog/2014/05/26/section-access

https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

In my Demo App Sam Big the CEO can see the detail and summary salaries of ALL staff in ALL cost centres.  Jo Ops (Sam's right hand woman) can see detail salary information for her direct reporting in the Packing Cost Centre, and summary information for the Packing, Exec and HR cost centres.  She can't see the CEOs salary though, and he has his own Cost Centre, but she has an idea of where all the money goes.

Any questions let me know.  If you mark the answer as Correct that would be awesome.

Regards

Rean

[krishgk_ispot]

reanfadyl‌

Thank you so much for the solution and the solution works for users who access to subset of detailed or Summary data.

But i would like to grant access for all the Detailed data to a user but no access to summary data and the vice versa.

(Ex)

1.Grant access for all the Detailed data to a user but no access to summary data

2.Grant access to all the summary data and no access to Detailed data.

* will give access to all to both Detail and Summary data.

Can you tell me how to grant access for a user to either all of the Detailed Data Or All the Summary Data alone?

[reanfadyl]

‌Hi Krishanan,

The scenario you have described is a bit simpler, the Generic key you are using in each row of your concatenated fact table will only have two options, lets call them D and S. The users who need detail will be assigned the D key and those who get summary will get the S.

please mark this as helpful , and just ask if you have further questions or need an example.

regards

rean

[krishgk_ispot]

reanfadyl

I have 50k unique Cost Center records under Detailed Table.I have 500 users who needs access to Detailed data and 400 users to Summary data.

So the security table size becomes really huge. (50k*500 Detail Users + 50k*400 Summary USers)

Is there any other option than listing All the Cost Center ID's with D or S to the user in the section access table?

[reanfadyl]

‌I think you are misunderstand the way the solution works.

your data model should have the following

Section Acess table: 900 Users (D 500 + S 400) - this is only temporary of course because the SA table dissapears.

Fact Table (50k Dentail records + x Summary records).  x being what ever the level of the data you are aggregating e.g. if you have 100 Cost centres that you are summerising for, then you will have 50100 total records in your concatenated fact table.

[krishgk_ispot]

reanfadyl

I understand that the Section access table won't be visible to users but it will still be there in the app and consumes space when there are more records in the section access table.

My use is slightly different from yours.

I have 2 different detailed fact tables and the security needs to be different for both the fact tables based on a specific column which more than 50000 unique records.

The user may have access to the data as below

1. Some set Customers from Fact A  and Different set of customers from Fact B

2. All data from Fact A and some customers /No data from Fact B

3.Some/No data from Dact A and All data from Fact B

To Achieve second use case ,if the QLIKUSER has access to All data from Fact A and some access to fact B

section access;

LOAD * inline [

ACCESS, USERID,CUSTOMER,OMIT

USER, QLIK-TESTING\QLIKUSER,30077|A

USER, QLIK-TESTING\QLIKUSER,30077|B,

USER, QLIK-TESTING\QLIKUSER,30078|A,

USER, QLIK-TESTING\QLIKUSER,30079|A

USER, QLIK-TESTING\QLIKUSER,20717|A,

USER, QLIK-TESTING\QLIKUSER,19646|A,

USER, QLIK-TESTING\QLIKUSER,19646|B,

USER, QLIK-TESTING\QLIKUSER,21078|A,

USER, QLIK-TESTING\QLIKUSER,21078|A,

USER, QLIK-TESTING\QLIKUSER,21078|B,

USER, QLIK-TESTING\QLIKUSER,...............with all the 50k records from Fact A,

ADMIN, INTERNAL\SA_SCHEDULER,*,*,

];

Section Application;

[reanfadyl]

Hi Krishnan,

Everything you have described is possible provided you are using a Concatenated Fact Table (Fact Table with Mixed Granularity) and Generic Keys (Basics for complex authorization). 

If you post up an example .QVF or .QVW where you have tried this (and are having difficulties) I will be happy to take a look at it for you.

Best regards

Rean

[krishgk_ispot]

reanfadyl‌ Please find attached the sample app with Data.

My Security needs are below.

USER3 Need access to All Orders (All Customer ID's with |1) and specific Customers Sales Data

Do i need to Map all those 1800+ Order Customer ID's under USER3 in section access table

USER4 Need access to  Specific Customers Orders Data and All Sales (All Customer ID's with |2)

Do i need to Map all the 1700+ Sales Customer ID's under USER4 in section access table.

[reanfadyl]

Hi Krishnan,

Attached is how I would do it approximately.  I needed to split out and append you Generic Concatenated Key to accommodate all your criteria.

In the attached you will see that

User 3 sees all Orders for Customer category |1

User 5 sees all Sales for Customer category |2

Everybody else sees the Orders or Sales for the Customers they have individually keyed against their ID.

Hope this helps

Regards

Rean