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

[jonathandienst]

@Gautham

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

Exists works across the entire data set and is not limited to a single table. Did you try scrapping the preceding load having the where clause on the base table? How did it perform?

[gauthamchilled]

Sorry for the confusion Jonathan,

[Auth key] is coming from Fact table,

In Auth Bridge table, we have two keys, one is [Auth key] and other is [~Auth Key]

In Authorization table, we have [~Auth Key]

Authorization table is connected to Fact table via Auth Bridge table.

So, Auth bridge table is a 32 load statements with all key combinations.

Since [~Auth key] is created in Auth bridge table, i cant use it in where clause, thats why preceding load is.

If i ignore preceding load and use where clasue in the bridge table, it throws me an error 'Field not found'

Please help...thanks.

[stabben23]

That the way to go, thats why there is a preceding load, it will keep all unique key combinations within all (32) resident loads.

You could use it like this instead, because [~AUTH KEY] does not exists before Load

Load

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

[AUTH KEY]

resident Fact

Where Exists(Company & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>')

[gauthamchilled]

is the below statement works?

Load

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

[AUTH KEY]

resident Fact

Where Exists(Company & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>')

because auth bridge table dont load any records...

[stabben23]

I'm Not sure if this Works, that why you should do as in HIC document With preceding Loads

[gauthamchilled]

Reload time is reducing after removing the preceding load...but not sure why this table is not loading any records after moving the exist condtion in the load statment

[stabben23]

Use just Company in the exists

Where Exists(Company)

And if you have Company & '|' & Office & '|' & '<ANY>' & '|' & '<ANY>' & '|' & '<ANY>'

Where Exists(Company& '|' & Office )

I'm not sure this will work

[gauthamchilled]

This didnt work. Anyone have idea to replace the preceding load to avoid the long reloading time?

[gauthamchilled]

Bump

[Siva_Sankar]

How about using incremental reload for Auth table alone??