topic Re: Authorization bridge on multiple columns in QlikView

Authorization bridge on multiple columns

vchuprina — Wed, 29 Nov 2017 13:28:54 GMT

Good Morning,

I've read Henric blog about authorization bridge. It's very useful and interesting, but in example Henric used only two fields: REGION and PRODUCT. And when you reduce data based on two fields it's not a problem to create authorization bridge table with all combinations, because you have only four combinations. How make data reduction if you should use 10 or more fields?

Load Region &'|'& Product as AuthID, Region &'|'& Product as %AuthID From OrderDetails ;

Load Region &'|'& Product as AuthID, Region &'|'&'<ANY>' as %AuthID From OrderDetails ;

Load Region &'|'& Product as AuthID, '<ANY>'&'|'& Product as %AuthID From OrderDetails ;

Load Region &'|'& Product as AuthID, '<ANY>'&'|'&'<ANY>' as %AuthID From OrderDetails

Basics for complex authorization

Re: Authorization bridge on multiple columns

Anil_Babu_Samineni — Wed, 29 Nov 2017 13:35:26 GMT

Even, I don't like to create composite key using simple & (and operator). To get more buffer and excellent performance you could try this?

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber(Region &'|'& Product) as %AuthID From OrderDetails ;

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber(Region &'|'&'<ANY>') as %AuthID From OrderDetails ;

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber('<ANY>'&'|'& Product) as %AuthID From OrderDetails ;

Load AutoNumber(Region &'|'& Product) as AuthID, AutoNumber('<ANY>'&'|'&'<ANY>') as %AuthID From OrderDetails

Re: Authorization bridge on multiple columns

vchuprina — Wed, 29 Nov 2017 13:55:47 GMT

Anil,

You are right performance will be better.

Below you can see all combinations for 3 fields. Can you imagine how many combinations you we will have with 10 fields

Country &'|'& Region &'|'& Product

'<ANY>' &'|'& Region &'|'& Product

Country &'|'& '<ANY>'&'|'& Product

Country &'|'& Region &'|'& '<ANY>'

'<ANY>' &'|'& '<ANY>'&'|'& Product

'<ANY>' &'|'& Region &'|'& '<ANY>'

Country &'|'& '<ANY>'&'|'& '<ANY>'

'<ANY>' &'|'& '<ANY>'&'|'& '<ANY>'

Re: Authorization bridge on multiple columns

marcus_sommer — Wed, 29 Nov 2017 14:09:07 GMT

I'm not sure if it's really sensible to use 10 or more fields for the authorization. It's quite unusual to have so deep hierarchies - who is able to understand and handle them on the business side? If anyhow possible you should reduce the complexity maybe by using several applications for it.

Beside this if it's really too much work to write all load-statements manually you could create them on fly within some loops. I could imagine starting this by creating a cartesian product between all your fieldnames and ANY within a join and then looping through this table and picking each value with peek() into a variable(s) and combining/extending them to a complete load-statement and executing them.

Here a simple example for creating a fieldlist within a variable - you will need of course more efforts but it showed in which direction the task is going: Re: Interesting challenge: How to create a real table from a list of field names?

- Marcus

Re: Authorization bridge on multiple columns

Anil_Babu_Samineni — Thu, 30 Nov 2017 09:33:40 GMT

I really don't know what you are going to have. As already Marcus states "I wonder how this can be sensible" to create with 10 columns. Instead, Why can't append using in external source rather Qlikview?