Data Reduction – Yes, but How?

[hic]

I recently wrote a blog post about authorization using Section Access and data reduction. In the example, a person was associated with a country and this entry point in the data model determined whether a record was visible or not: Records associated with the country were visible. “Country” was the reducing field.

The data reduction was made using row-level security. But there are other ways of limiting access to data. This post is about how you limit access to the data:

Row-level access: You have a reducing field that determines whether a user can see a specific piece of data. If you use Country as reducing field and the user is allowed to see ‘Spain’, this will mean that only rows associated with Spain will be visible: E.g. sales transactions to customers in other countries will not be visible.

Aggregation-level access: This is similar to the above, however with the difference that all data are in principle visible but the aggregation level changes depending on country: A user that is allowed to see ‘Spain’ will see the detailed information about Spain, but only high-level aggregated information about other countries. For other countries detailed information will be hidden.

Column based access: Instead of limiting per row, you can limit per column. Here you can define that only some users are allowed to see specific fields, typically fields like Salary or Bonus.

Object based access: You can also limit access to a specific sheet, graph or pivot table depending on which user it is.

An application can use a combination of the four different methods.

Both Section Access and the loop-and-reduce in publisher use row-level access to allow one single (master) file to be used in different security scopes. It is by far the best way to limit access to data, and should be the one you normally aim for.

It is difficult to achieve aggregation-level access within one single application, so it is better to solve this problem using two applications: One with detailed data that you reduce using a reducing field, and a second unreduced with aggregated data for all countries.

The column-based access can be achieved using two applications, one that includes the sensitive fields and the other that doesn’t. It can also be achieved in one single application using the OMIT field in Section Access.

Finally, the object based access: This method has in my mind very little to do with security: If a chart is hidden for a specific user, he can still see the same data through other objects. Or even worse – if you allow collaboration, he can create an object that shows the same thing. A show condition could be convenient to use anyway, but it is a poor tool for security.

Bottom line: If you want security, you should use Section Access or the loop-and-reduce of the Publisher. You should also consider having your data in several applications. But you should not use show conditions for security purposes.

HIC

Further reading related to this topic:

A Primer on Section Access

Tips and tricks for section access in Qlik Sense (2.0+)

[#2]

Hi Ethan,

i had once the same problem to solve.

I did attach specifics aggregated tables to geographic levels (like areas, regions, countries). It makes the model a little bit more complex when you see it.

best regards

Chris

[#3]

Thanks @christianj.  From what I was thinking, the aggregates would definitely have to be incorporated into the data model.  But from what Henric is stating with the separate documents, that may work as well.  I will have to try it.

Ethan

[#4]

HIC, Fantastic article. I loved it.

Ram

[#5]

Henric, you mention an application can use a combination of the four different methods.

How would you combine row level access and column based access? For example, if I need to make the value of a field hidden on certain rows, for a particular user, but I do not want to hide the whole row?

[#6]

Alex, you would use the OMIT field in Section Access for Column based data reduction.  This can be combined with Row based data reduction in the same table.  See OMIT multiple fields post.

[#7]

Thanks Thomas, but the OMIT function totally hides a field. Doesn't it? I don’t want to do that.

I want to hide the value of a field only when the Department field is a certain value. The department field is in my section access table and also my main data table.

[datanibbler]

Hi Henric,

we would need something similar - but not for the apps, but for the QMC:

- We have the plan (currently on ice, but it's going to reappear) to roll out QlikView across all sites in
   Germany.

- We are probably going to do that using a central approach from this site -> we are going to be the
  lead_developers and admins, but we will need "site_admins" in the other plants
=> These "site_admins" should have access to their site's licenses and apps -  but no more.

Of course when we get to the point of doing that, we'll have the enterprise_server.

Is there a way to do that?

Thanks a lot!

[Anonymous]

Hi DataNibbler,

what you have described here is an architectural solution rather than Data Reduction. If you have questions regarding the appropriate architecture I would contact Qlik Consulting Services in your region.

Depending upon whether you have a central server or each site will have it's own server will depend upon the degree with which you can restrict access. Assuming a central server then licenses are administrated by the Central Administrators group for all regions. You would then assign document administrators to the regions so they can manage the tasks associated with their apps. In addition you would need to consider your operations model for deploying files to the server. This is nothing difficult however again I would recommend you discuss the options with Qlik Consulting Services.

[chriscammers]

Henric,

We're working on a solution for a medical billing service and we have a situation where the security is based on two fields. We are trying to use the "Star is *;" command and wildcards in the fields.

I'm working on a sample since we are having difficulty granting access to our users

Load * Inline [

USERID, ACCESS, GROUPID, PROVIDERID

USER1, USER,111,*

USER2, USER,*,*

USER3, USER,*,123

ADMIN, ADMIN,*,*

DUMMY, NONE,111,123

DUMMY, NONE,100,124

DUMMY, NONE,111,125

];

Load * inline [

GROUPID, PROVIDERID, WhoShouldHaveIt

100,124,DUMMY ADMIN user2

100,125,Nobody

100,123,USER3

111,123, dummy admin user1 user2

];

As you can see USER1 should have access to GROUPID = 111 and any provider within that group but I am seeing that the association is not working correctly and the last row of the second table is not accessible to USER1. So does this strategy not work? Do I need to explicitly define all the possible providers for user1 and then make a concatenated key for groupid and providerid?

Thanks for your comments

Chris

[hic]

Data reduction using multiple fields is not straightforward...

First you have the problem that you sometimes want the reduction in one field (USER1 > GROUPID) and sometimes in another (USER3 > PROVIDERID). The solution is to combine the two reducing fields into one single field - an Authorization Key.

But then you get a second problem: How to represent "Any field value"? The "Star is" will not work. The solution is to use Generic Keys.

See more on Data Reduction Using Multiple Fields

and Basics for complex authorization

and Generic keys

HIC