Solved: Authorization load taking long time - Qlik Community

gauthamchilled · ‎2017-01-19

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

Siva_Sankar · ‎2017-02-06

If you are not changing the section access file everyday, then there is no need to create Auth key everyday. You could try creating fresh Auth keys only when the new user is added or section access file is changed. Create a condition to check something like

Load Date(filetime()) as ModifiedDate

FROM

SectionAccess.Xlsx;

Let vActualDate =Today();

Let VLastModifiedDate = peek ( 'ModifiedDate', 0 , ' tmp ');

if $(VLastModifiedDate)=$(vActualDate) Then

Full Reload to create keys

Else

Load from QVDs.

Hope it helps.

Regards,

Siva

View solution in original post

gauthamchilled · ‎2017-01-20

Any suggestion or help?

stabben23 · ‎2017-01-20

Hi Gautham,

how much is "long time"? in these case when use key combination you often reload the fact table With resident load.

To reload the fact table to get all possible combination will take some time and thats the only way.

How many rows in Your Fact table?

If you have Publisher you can use loop and reduce on the possible combinations and create documents from the Source file.

Without Your script it will be hard to find a way to optimize Your reloadtime.

avinashelite · ‎2017-01-20

what is the data size you have ?? and how the data is associated with that because I feel because of the combination its taking long time to calculate the key for linking the fact and section access table

jonathandienst · ‎2017-01-20

Looks like you are trying to create generic keys for section access. Did you read this Generic keys?

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

gauthamchilled · ‎2017-01-23

Yes, I followed the documents by HIC as i mentioned in my first post.

I have around 9 million records. When i analyzed the document log file, i found that it is taking time only for creating the keys.

sample load statement:

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;

just wanted to sort out how i can reduce this time

jonathandienst · ‎2017-01-24

What happens if you move the where clause to the main body of that load?

Load Year,

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

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

resident Fact

Where Exists([~AUTH KEY]);

Not sure if that will make much difference, though.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

gauthamchilled · ‎2017-01-24

Let me try and update you.

rwunderlich · ‎2017-01-24

Jonathan, I think you are on the right track as preceeding load is remarkable slow these days.

Gautham, what is the point of the Where Exists([~AUTH KEY])? It looks to me like it would keep all rows.

-Rob

gauthamchilled · ‎2017-01-24

I just followed the instructions from HIC document on generic keys..it is mentioned to use exist key to load only keys that exist in fact table.. please point me if i am wrong.