Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
hic
Former Employee
Former Employee

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
Anonymous
Not applicable

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
8,776 Views
hic
Former Employee
Former Employee

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
8,776 Views
Not applicable

Thanks Henric .its very helpfull

0 Likes
8,689 Views
francescopuppin
Contributor III
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
8,689 Views
nenadvukovic
Creator III
Creator III

Hi Francesco,

of course, we are interested in your solution

Thx

0 Likes
8,689 Views
francescopuppin
Contributor III
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
8,688 Views
cmorri1988
Creator
Creator

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
8,688 Views
krishgk_ispot
Partner - Contributor III
Partner - Contributor III

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
8,688 Views
reanfadyl
Partner - Creator
Partner - Creator

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
8,617 Views
krishgk_ispot
Partner - Contributor III
Partner - Contributor III

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
8,617 Views