Authorization bridge on multiple columns

[vchuprina]

  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

[Anil_Babu_Samineni]

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

[vchuprina]

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>'

[marcus_sommer]

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

[Anil_Babu_Samineni]

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?