Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Data Reduction – Yes, but How?

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.

Selection.png

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+)

29 Comments
Not applicable

Hello HIC,

This was fantastic blog.. I like very Much.

Thank you for Posting.

Regards,

Ramana

0 Likes
260 Views
MCampestrini
Valued Contributor

Hi Henric

Thanks for clarify this point!

0 Likes
260 Views
anantmaxx
Contributor III

Thanks very Useful !!

Anant

0 Likes
260 Views
Not applicable

Henric,

Very well said.  "Security by obscurity" is never any good.  Similar to putting a padlock on a paper bag!

'Show conditions' have their place in Qlikview - for clarifying screens, removing extraneous objects etc until a condition dictates they should be visible, but not security.

0 Likes
260 Views
Not applicable

Thanks HIC,

What is the workaround for Object level security?

0 Likes
260 Views
Employee
Employee

There is no security in "object-level security", so what you should do instead is to use row-level security.

HIC

260 Views
Not applicable

Great Post Henric.  You touched on an area that I'm very interested in learning more about which is Aggregation Level Security.

A company I did work for wanted functionality that allowed Market Managers see how they compare to other Market Managers without having the ability to drill down into details.  I successfully implemented complex authorization but couldn't figure out what that next step would be for Aggregation level.  I can see this being handy for applications across multiple fields for comparison purposes.


I see you explained to achieve this by using two different applications but what would I do to link the high level data application with the reduced data application without having security compromise what I'm trying to accomplish?  Store the aggregated data in variables or QVDs and pull them in?


Thanks,

Ethan



0 Likes
260 Views
Employee
Employee

One straightforward way to link the two documents is to open document 2 from document 1 and transfer the selection.

But optimally you would of course want to have both detailed and aggregated numbers in the same app so that you can compare them. I need to investigate a little before I can give good answer, but my gut feeling is that you should create a loop in the publisher that creates one file per Market Manager with the appropriate aggregation.

HIC

0 Likes
260 Views
tanelry
Contributor II

Isn’t Object based access still fine for special cases, as long as I restrict user freedom by unchecking security options in document and sheet properties, (add new sheets=no, add sheet objects=no, access object properties=no)?

Recently I had specific requirement to show full company-level data on a dashboard to everyone, but details and analytic charts had to be reduced by department - user should only see their own deparment(s).

I solved it using unlinked authorization table and additional SET analysis on restricted detail views’ expressions.

In data model there is a separated „UserDepartment“ table with two fields:

- USERID (linked to section access)

­- UserDepartment (department names, same as Department in transactional data but not linked to this field)

This way only UserDepartment table is being reduced by authorization, while transactional data is not affected by reduction.

On dashboard I use normal chart expressions, like sum(Amount). No drill to details there.

On detail reports I use SET analysis to reduce the data: sum({<Department=p(UserDepartment)>} Amount).

Of course document and sheet security options need to be restricted properly - basically uncheck all options.

0 Likes
260 Views
Employee
Employee

No, I don't think object based access is a good solution.

It is true that you can prevent the user from seeing unauthorized data, if you keep the file on a server, if you disable server objects and collaboration, if you have the appropriate object show conditions, if you make sure there are no other objects or sheets through which these data are visible, if you every time you make a change to the file make sure that none of the above has been broken, etc. I just think there are too many ifs for it to be a good, robust, manageable solution...

If you allow users to download the file, the data is still available for a malicious user - also if you have unchecked all security options in document properties. Remember that the file is not encrypted. I will not go into how you would hack such a file, but rest assured that a skilled malicious user is able to. So the file needs to stay on the server.

But my main concern is manageability. The file will be changed from time to time, and sooner or later the developer will forget to put the appropriate show condition into an object. So it will be very easy to publish confidential data by mistake.

HIC

260 Views
Not applicable

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

0 Likes
260 Views
Not applicable

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

0 Likes
260 Views
Not applicable

HIC, Fantastic article. I loved it.

Ram

0 Likes
260 Views
Not applicable

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?

0 Likes
260 Views
Not applicable

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.

0 Likes
260 Views
Not applicable

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.

0 Likes
260 Views
datanibbler
Esteemed Contributor

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!

0 Likes
260 Views
Employee
Employee

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.

0 Likes
260 Views
chriscammers
Contributor III

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

0 Likes
260 Views
Employee
Employee

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

0 Likes
260 Views
chriscammers
Contributor III

Thanks!

That confirms what we suspected. We're going to create the authorization field. which I think will solve the problem.

Chris

0 Likes
260 Views
chitemerere
Valued Contributor

Hi HC

How does one implement Aggregation-level access?  Would give an example on how to do it.  Will need someone to have access to their own territory information in addition to the whole market in order to determine their performance relative to the market.

Regards.

Chris

0 Likes
260 Views
chriscammers
Contributor III

I was thinking about this and I know there are a ton of details to work out but basically I would create a concatenated fact table with separate rows for the detail and the summary. Then you have to build your user table so the user has access to the summary rows for the territories he does not belong to and the detail for the territories he does belong to. I'd suggest a field that concatenates the fact type and the territory value.

0 Likes
260 Views
chitemerere
Valued Contributor

Many thanks Chris for your response, most appreciated.  I am a beginner in QV so implementation of your suggestion would be difficult as i can not comprehend your recommendation above.

Regards

0 Likes
260 Views
chriscammers
Contributor III

I baked up a quick sample, I did not implement the section access but selection of a user Id will simulate the effect of data reduction. I cannot seem to add a file here so here is a link to my dropbox where you can download it.

Dropbox - AggregateSecurity.qvw

I fear you will not be able to download it so here is the load script.

UserSecurity:

Load * Inline [

USERID,REGION_FACT

NORTH_USER,NORTH_DETAIL

NORTH_USER,SOUTH_AGGREGATE

SOUTHUSER,SOUTH_DETAIL

SOUTHUSER,NORTH_AGGREGATE

MANAGER,NORTH_DETAIL

MANAGER,SOUTH_DETAIL

];

//Build the fact table

//Add the aggregate data

FactTable:

Load * Inline [

REGION_FACT, Region, Fact Type, Sales Amount

NORTH_AGGREGATE, North, Aggregate, 10000

SOUTH_AGGREGATE, South, Aggregate, 15000

];

//Add the Detail Data

Concatenate(FactTable)

Load * Inline [

REGION_FACT, Region, Fact Type, Invoice, Sales Amount

NORTH_DETAIL, North, Detail, 1234, 2500

NORTH_DETAIL, North, Detail, 1235, 2500

NORTH_DETAIL, North, Detail, 1236, 2500

NORTH_DETAIL, North, Detail, 1237, 2500

SOUTH_DETAIL, South, Detail, 1238, 2500

SOUTH_DETAIL, South, Detail, 1239, 2500

SOUTH_DETAIL, South, Detail, 1230, 2500

SOUTH_DETAIL, South, Detail, 1231, 2500

SOUTH_DETAIL, South, Detail, 1232, 5000

];

0 Likes
260 Views
chitemerere
Valued Contributor

Hi Chris

Thanks for the input.  i have managed to download the QVW.  I will work on it and advise accordingly.  We are on different time zones so there will be delays in response.

Once again, thank you very much and most appreciated.

Regards.

0 Likes
260 Views
kashjaniqlik
New Contributor III

Hi Henric

Thanks for the useful article.

We are currently developing an MI dashboard for our clients and the requirement is that high level numbers are shown globally to all users although the detailed data is restricted based on the user countries. So if I'm part of UK, I will see other countries high level data but can only see detailed data for UK.

Our client requires both sheets (high level and detailed) to be part of one solution i.e. one QVW.

How would you go about applying section access in this situation?

Many Thanks for your help

Kash

0 Likes
260 Views
arethaking
New Contributor III

Hi HIC,

Please give an example to

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.

0 Likes
260 Views
tanelry
Contributor II

Aggregation-level access:

One way to achieve this is to create two data sets in the fact table: Aggregated and Detailed.

Then create a link table to map Aggregated data to all users, but Detailed data to country-specific user.

As a result, data reduction should only apply to Detailed data set.

For example, data model:

SA_Datamodel.png

And data in the tables:

SA_Data.PNG

Both CountryLink and Facts table contain two data sets: Aggregated and Detailed data.

The LinkType field is technically useless, but I brought it here for better understanding.

SalesAmount field has been aggregated by Country and Date, while SalesAmount_Detail field is also available by SalesPerson and Customer.

So, for output you also need to create two measures:

  • sum(SalesAmount) - for country level comparisons with all countries
  • sum(SalesAmount_Detail) - for detailed analysis with user specific country

Regards,

Tanel

0 Likes
260 Views