Qlik Community

Qlik Design Blog

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

Last week I wrote about authorization using Section Access and data reduction. In the example, a user was associated with a country and this entry point in the data model determined whether a record was visible or not: Only records associated with the country were visible. COUNTRY was the reducing field.

 

But if you want several reducing fields? You may have a user that should see one product group in one country, and another product group in another country. Then both COUNTRY and PRODUCTGROUP should be reducing fields. Is that possible?

 

Yes and No.

 

Yes, it is possible. But No, you cannot just add a second reducing field. In simple cases, just adding a second reducing field will work fine, but as soon as the logic is slightly more complex, it will not work. Below I’ll explain why.

 

Let’s use the following example: A user MARKUS should see the product group Machines in Germany and Food in France – but not the other way around. Then the obvious approach would be to create an authorization table like the following:

 

FoodNMachines2.png

 

I.e. two reducing fields – COUNTRY and PRODUCTGROUP – defining the following logic:

 

     (‘GERMANY‘ AND ‘MACHINES’) OR (‘FRANCE’ AND ‘FOOD’)

 

However, this will not work in QlikView.

 

If you do the above, you will get a data model like the following (where the red table is the Section Access table).

 

Incorrect data model.png

 

This means that the selections in the real data model will be made in COUNTRY and in PRODUCTGROUP. But remember that QlikView always uses OR-logic between values of the same field and AND-logic between fields. This means that QlikView will interpret the selection in these two fields as

 

     (‘GERMANY‘ OR ‘FRANCE’) AND (‘MACHINES’ OR ‘FOOD’)

 

which is not the same as the initial requirement. The selection made will include Machines in France and Food in Germany, which is against the initial requirement. In fact, it is impossible to make a selection that corresponds to the initial requirement using only these two fields.

 

So what should you do?

 

The solution is to create a new, single reducing field based on COUNTRY and PRODUCTGROUP, e.g. through

 

     COUNTRY & ‘|’ & PRODUCTGROUP as AUTHORIZATIONKEY

 

The first challenge is to find the table where this key should be created. It must be in a table with a grain fine enough that both country and product group are uniquely defined on each record. In the above data model this is the Order Details table: Each order line has only one product and one customer – thus one product group and one country – associated with it. So, the authorization key should be created here.

 

Correct data model.png

 

There are other challenges also, e.g. how to get the country and product group information into the load of the Order Details table (Solution: ApplyMap) and how to handle the concept of Any Value (Solution: Generic Keys), but these can all be solved. For details, see the blog post about Complex Authorization.

 

Bottom line: You can use multiple reducing fields in Section Access, but only if each user has only one record in the authorization table. If a user has several records, you need to create one single authorization key.

 

Also, the above example clearly shows that authorization is part of the data modelling and should be thought of at an early stage in the development process.

 

HIC

 

Further reading related to this topic:

A Primer on Section Access

Basics for complex authorization

Authorization using a Hierarchy

20 Comments
Marcio_Campestrini
Valued Contributor

Henric, thanks for sharing this.

0 Likes
6,203 Views
aaroncouran
Contributor III

Useful info.

I would add that the whole conversation about concatenated keys,  where they should go,  etc. is really important to understand for all qv developers outside of the topic of section access.

0 Likes
6,203 Views
Partner
Partner

hi HIC,

very useful info, and good conceptual too

0 Likes
6,203 Views
kirankkk
Contributor II

Thanks HIC.

Very useful article.

0 Likes
6,203 Views
jaygarcia
New Contributor III

Thanks for this! This will be of a great help for many of us!

J

0 Likes
6,203 Views
nenadvukovic
Contributor III

Hi Henric,

I've been using the "wrong" approach to segregate between our sites and salesmen. Fortunately I have had such a case only with a salesmen manager which was harmless. Thank you very much for this yet another brilliant insight of yours. I've immediately enabled the working security using the complex key.

Cheers and all the best,

Nenad

0 Likes
6,203 Views
Partner
Partner

Great writeup, this works well in the QlikView local client.  However we are seeing some issues when reloading and publishing on the server - can't open the document in Access Point (access denied).  Henric are you aware of anything that might cause difficulty on the server?  It reloads fine.  We just can't open the published document.

0 Likes
6,203 Views

The most obvious difference between the Desktop and the Server is the difference between ADMIN and USER. On a server, no one gets ADMIN rights... And on the Desktop, QlikView will let you open the file if you're an ADMIN, also if the data reduction doesn't allow it.

So, change your Section Access so that everyone (except one single general ADMIN that you use only as a safety backdoor, and the publisher account) has USER rights and make sure Strict Exclusion is on. Then the Desktop and the Server should behave the same (except for the general ADMIN).

HIC

0 Likes
6,203 Views
Partner
Partner

Thank you, it seems that upon reloading and saving, data reduction occurs for the Publisher service account - and the only username left in the AuthorizationTable, for the published version, is the username for the service account - locking out all other users.  This feed was helpful:  Issue in Using NTNAME in Section Access and reloading the application using Publisher.
A few people suggested keeping the service account user in Section Access, but removing it from the AuthorizationTable / Section Application.  That seems to work for me.

0 Likes
6,203 Views
Partner
Partner

Hi Henric,

Thank you for your post it is very helpful.  I am dealing with a situation where we want the users to have access to different 'grains' of the same data set.

For example a User can access the detailed transaction $ for Machines sold in Germany, but only see the Total $ value by Country for machines sold in France and Finland.


Do you have any suggestions for how you would handle this? 


Regards


Rean


PS. The data model I am working with is using a single concatenated Fact table.

0 Likes
6,203 Views
Employee
Employee

Hi,

I think you may need to create Facts at both the detailed level and summary level. This will allow you to define section access at different levels. This may make the front alot more complex though.

Damian

0 Likes
6,203 Views

It is difficult to create this authorization model within one app, so I would suggest two apps: One with aggregated data, and one with detailed data. Then it is straightforward to apply the appropriate authorization scheme for each app.

HIC

0 Likes
6,203 Views
Not applicable

Thanks Henric .its very helpfull

0 Likes
6,203 Views
francescopuppin
New Contributor III

Hi,

I have received this week a similar request from my client, and I have found this post.

The post is useful, but at the end I have followed a simpler approach.

In the final solution, based on (‘GERMANY‘ AND ‘MACHINES’) OR (‘FRANCE’ AND ‘FOOD’), MARKUS can only see the German Machines or the French Food, but not the German Food, nor the French Machines, nor anything in other Countries, nor anything in other Categories, as per my client's request.


All this was done without the usage of generic keys


If someone is interested, pls let me know!


Regards

Francesco

0 Likes
6,203 Views
nenadvukovic
Contributor III

Hi Francesco,

of course, we are interested in your solution

Thx

0 Likes
6,203 Views
francescopuppin
New Contributor III

Ok! I will transform my dashboard into a small self-standing app, so it's easier to be re-used by the Community 🙂

In the meantime I have noticed that my solution is practically identical to the one shown by Hic in the post "Basics for complex authorization", which I had not seen before. Also, I am indeed using the "Generic Keys", just I did not know that this solution had such name. The only difference is that I am associating the "authorization bridge table" with the main fact table (in this case "OrderDetails") through an Integer. The reason is not about integers using less memory, no.. It is rather because this integer, a simple Rowno(), is unique in the OrderDetails... And this way, the association becomes "one-to-many"!

People talk a lot about optimization, referring often to the Symbol table, which is a great topic. But almost nobody talks about "unique identifiers". In the solution of Hic, the authorization table is associated to OrderDetails through a many-to-many association: the combination AUSTRALIA|A is very likely to appear multiple times in OrderDetails, and consequently also multiple times in the bridge table. This makes it a "many-to-many" association.

But the "many-to-many" associations (n-n) can be real "performance killers", although this topic seems to be rarely mentioned in the Qlik Community. QlikView handles n-n better than SQL, because SQL is generating duplicate rows, while Qlik is not. But still, I cannot count the times that I have inherited a client's dashboard containing "many-to-many" associations, I have transformed them into "one-to-many", and the speed of the dashboard has drastically improved. My recommendation, for data modeling in general, is to use always "one-to-many": all you have to do is to make sure that the key field is unique in one of the 2 sides. You will see, dashboard will be faster!

I will try to work on it in the weekend. In the meantime, I will appreciate any comment to my thoughts, hoping to get some new learning from this Community! 🙂

0 Likes
6,202 Views
cmorri1988
Contributor

We are seeing issues with application cache not being global due to section access with data reduction, and this causing issues with RAM usage, does anyone have any suggested alternatives other than bookmarks.

Thanks

0 Likes
6,202 Views
Partner
Partner

reanfadyl

Did you figure out the solution for implementing section access for different 'grains' of the same data set.

Can you post the solution/workaround you used to solve this use case.

0 Likes
6,202 Views
Partner
Partner

Hi Krishan,

Yeah I did.  I have a .QVF (Qlik Sense) and Excel (Datasource) based example I'd be happy to share, but the option for adding a file doesn't seem to be appearing on this thread?  Are you able to post a question on the community and I will answer.


Cheers

Rean

0 Likes
6,202 Views
Partner
Partner

reanfadyl

I have posted a thread in the below link. Can you please upload the app.

Section Access On Multiple Tables with different dataset using Same field

0 Likes
6,202 Views