Authorization load taking long time

[gauthamchilled]

Hi,

I've implemented a complex authorization logic provided by Basics for complex authorization‌

I have five fields used in authorization load for section access and it is taking long time to  reload the dashboard.

Load Distinct * Where Exists([~AUTH KEY]);

Load Year,

'<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' as [~AUTH KEY] ,

Country & '|' & Office & '|' & Region & '|' & Department & '|' &  Category as [AUTH KEY]

resident Fact;

I have 32 load statements like above with different key combinations.

Please help is there any way to sort out the long reload time to shorter? Thanks

Gautham

[stabben23]

Hi Gautham,

the [AUTH KEY] should be created already in the Fact table so you can reuse it in your resident load. This will save you some time here.

All these Fields must be in the Fact before you create Your generic keys. Otherwise you need to us applymap to get them in Your fact table.

Country & '|' & Office & '|' & Region & '|' & Department & '|' &  Category as [AUTH KEY]

Load Distinct * Where Exists([~AUTH KEY]);

Load

'<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' as [~AUTH KEY] ,

[AUTH KEY]

resident Fact;

[gauthamchilled]

Hi Jonathan,

This key [~AUTH KEY] is coming from fact table, and other [~AUTH KEY] are created in Auth table. So i can use [~AUTH KEY] in where clause without using precedling load.

[stabben23]

You should do it as in the document With preceding loads.

But With 5 Fields you will have to load Your Fact table resident 25 times(?) to get all possible combinations here.

[gauthamchilled]

Correct....32 times not 25 times

[stabben23]

Ok 32 times. Are you sure that you need all these combinations? Your policy document which will be an Excel? will contain a lot of names, dosent it? To manage this could be difficult for the admin.

Do you have a Office that can belongs to many Country? if not, Office could be removed from the composite key.

[gauthamchilled]

office not belong to many country, but i have different countries and different offices

i am using section access with 5 different levels...so i cant ignore office field from composite key..right?

[stabben23]

Ok, so if someone With country "UK" get Access, should he see all Office in UK? then Office could be ignored.

Maybe Office is the "lowest" here, so you can ignore country instead. All Office will only contain one Country.

"London" will always be Connected to "UK" as an example.

Just one question, do you have Publisher?

[gauthamchilled]

No we dont have publisher.

No, user in uk need to access specific office sometimes

Country, Office,user

UK,OfficeA,user1

UK,OfficeB,user2,

Spain,SpainOffice1,user3

Spain,SpainPOffice2,user4

Spain,<ANY>,user5

[stabben23]

Ok, looks like you need all 5 Fields.

Have you test to create Your composite key already in the fact table?

This is what you should do and then use it in the resident load.

Fact:

LOAD

Country & '|' & Office & '|' & Region & '|' & Department & '|' &  Category as [AUTH KEY],

..

..

From Your Factsource;

Then do all 32! resident Load here:

AuthBridge:

Load Distinct * Where Exists([~AUTH KEY]);

Load

'<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' as [~AUTH KEY] ,

[AUTH KEY]

resident Fact;

Load Distinct * Where Exists([~AUTH KEY]);

Load

Company & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' as [~AUTH KEY] ,

[AUTH KEY]

resident Fact;

Load Distinct * Where Exists([~AUTH KEY]);

Load

'<ANY>' & '|' & Office & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' as [~AUTH KEY] ,

[AUTH KEY]

resident Fact;

Load Distinct * Where Exists([~AUTH KEY]);

Load

'<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & Category as [~AUTH KEY] ,

[AUTH KEY]

resident Fact;

aso

[stabben23]

Authorization using a Hierarchy

Have a look here is this could help instead.