How to use Data Reduction to secure part of the Data Model?

[#1]

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

[Colin-Albert]

>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.

[swuehl · Accepted solution]

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.

[jonathandienst]

I found these articles helped with a similar requirement

Basics for complex authorization

Authorization using a Hierarchy

Generic keys

and

Data Reduction Using Multiple Fields

[#5]

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...