topic Re: Authorization load taking long time in QlikView

Authorization load taking long time

gauthamchilled — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Authorization load taking long time

gauthamchilled — Fri, 20 Jan 2017 05:37:46 GMT

Any suggestion or help?

Re: Authorization load taking long time

stabben23 — Fri, 20 Jan 2017 06:49:10 GMT

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.

Re: Authorization load taking long time

avinashelite — Fri, 20 Jan 2017 07:07:09 GMT

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

Re: Authorization load taking long time

jonathandienst — Fri, 20 Jan 2017 09:35:32 GMT

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

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 03:03:57 GMT

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

Re: Authorization load taking long time

jonathandienst — Tue, 24 Jan 2017 05:01:51 GMT

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.

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 07:11:21 GMT

Let me try and update you.

Re: Authorization load taking long time

rwunderlich — Tue, 24 Jan 2017 07:25:12 GMT

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

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 07:38:50 GMT

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.

Re: Authorization load taking long time

stabben23 — Tue, 24 Jan 2017 07:56:12 GMT

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;

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 08:15:40 GMT

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.

Re: Authorization load taking long time

stabben23 — Tue, 24 Jan 2017 08:23:08 GMT

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.

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 09:02:15 GMT

Correct....32 times not 25 times

Re: Authorization load taking long time

stabben23 — Tue, 24 Jan 2017 09:15:03 GMT

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.

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 09:25:10 GMT

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?

Re: Authorization load taking long time

stabben23 — Tue, 24 Jan 2017 09:34:54 GMT

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?

Re: Authorization load taking long time

gauthamchilled — Tue, 24 Jan 2017 09:42:43 GMT

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

Re: Authorization load taking long time

stabben23 — Tue, 24 Jan 2017 09:58:44 GMT

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

Re: Authorization load taking long time

stabben23 — Tue, 24 Jan 2017 10:07:53 GMT

Authorization using a Hierarchy

Have a look here is this could help instead.