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:
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.
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.
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?
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.
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?
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.
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
LOAD * inline [
USER, QLIK-TESTING\QLIKUSER,...............with all the 50k records from Fact A,
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.
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.
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