Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The security requirement for my application is such that I have both privileged and unprivileged users looking at the same data. However, the privileged user can see further details on that data. However, my issue is that privileged users come from different departments, and should only be able to see the further details relating to their department.
Lets say I have a large table of Customer information. An unprivileged user can see all the customers. However, some of those customers have a sale against them (only ever one) an this is what a privileged user can see. The details of that sale are contributed by multiple departments, and a privileged user may have access to one or more of those departments' information. The sale details are held as additional rows.
At the moment, this is modeled as 2 fact tables:
Customer_FACT:
Customer ID | Customer Name |
---|---|
1 | Customer 1 |
2 | Customer 2 |
3 | Customer 3 |
4 | Customer 4 |
5 | Customer 5 |
Sale_FACT:
Sale ID | Customer ID | Sale Detail | Sale Detail Department |
---|---|---|---|
A | 2 | adfasfd | Dept 1 |
A | 2 | fgfvbnm | Dept 2 |
B | 3 | asfgdsg | Dept 2 |
B | 3 | jfjrtjujg | Dept 3 |
C | 4 | rtukjfgjh | Dept 1 |
Qlikview will link the two fact tables on the [Customer ID] column.
I need to be able to hide the [Sale_FACT] rows from unprivileged users, whilst being able to restrict which of the [Sale_FACT] are available to the privileged user, without affecting their visibility of [Customer_FACT].
The problem is that by using Data reduction using [Sale Detail Department] on [Sale_FACT] row, it then hides the linked [Sale_FACT] and [Customer_FACT] rows from that privileged user.
Is there any way I can tell Qlikview not to perpetuate the data reduction throughout the model, and only apply to a single table?
All the examples of data reduction that I can find hide data based on a high level attribute, rather than restricting visibility of subsets of data, based on attributes of the lowest level data.
Many thanks
Mark
You can add rows to your Sale_FACT table
Sale ID, Customer ID, Sale Detail, Sale Detail Department
, 1 , , DUMMY
, 2 , , DUMMY
etc. for all customers.
Then add DUMMY Sale Detail Department value for all users in your section access table.
>Is there any way I can tell Qlikview not to perpetuate the data reduction throughout the model, and only apply to a single table?
The data reduction is applied to the whole data model.
One possibility is to load the data that is visible to all users to a second table using different field names that are not part of the data reduction.
You can add rows to your Sale_FACT table
Sale ID, Customer ID, Sale Detail, Sale Detail Department
, 1 , , DUMMY
, 2 , , DUMMY
etc. for all customers.
Then add DUMMY Sale Detail Department value for all users in your section access table.
I found these articles helped with a similar requirement
Basics for complex authorization
Authorization using a Hierarchy
and
Data Reduction Using Multiple Fields
Thanks for the suggestion.
I have refactored the FACT tables such that required data is denormalised onto any row that requires it, and that the controlled rows exist as separate rows in the same FACT table. The Uncontrolled rows have a DUMMY department value to ensure they are visible to all.
This approach is proving promising...