Section Access enables qvw taking time to load at server

[agni_gold]

Hi Friends,

I have one qvw which takes 1 min 16 sec to reload at QMC , and when i am applying section access on the same file it takes 10 minutes .

Why ?

Is there any idea , please help.

[parimikittu]

Please try below logic. This should work fine,everything is good, Except *, Can try replacing * with ALL and create name holder for ALL which represent data of the column

Start is *;

Section Access;

LOAD Upper([ACCESS]) AS [ACCESS],

  Upper([DOMAIN]) AS [DOMAIN],

     Upper(NTNAME) AS  NTNAME,

     Upper([NTNAME]) as [ZONE_NTNAME]

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY);

Section application;

ZONE_SECURITY:

LOAD DISTINCT

  Upper([NTNAME]) AS [ZONE_NTNAME],

     if(isnull([ZONE_NAME]),'*',UPPER([ZONE_NAME])) AS  ZONE,

  Upper([NTNAME]) AS [COUNTRY_NTNAME],

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY)

WHERE UPPER(NTNAME) <> 'SRVQLIK_DEV'

AND   UPPER(NTNAME) <> 'SRVQLIK_PROD';

//Sets Country Security restrictions

//If the value is blank then the user/admin is given full view rights designated by the '*'

COUNTRY_SECURITY:

LOAD DISTINCT

  Upper([NTNAME]) AS [COUNTRY_NTNAME],

     if(isnull([COUNTRY_NAME]),'*',UPPER([COUNTRY_NAME])) AS  COUNTRY

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY)

WHERE UPPER(NTNAME) <> 'SRVQLIK_DEV'

AND   UPPER(NTNAME) <> 'SRVQLIK_PROD';

[agni_gold]

Not getting your point , what should i create , i have tried with * it is taking same time .

What you want to say for ALL , can you please elaborate.

[parimikittu]

Hi Agnivesh,

Have you tried to run the above code? i have made changes like remove the column[COUNTRY_NTNAME] and added new the relation btw tables ZONE_SECURITY , COUNTRY_SECURITY. with Column Upper([NTNAME]) AS [COUNTRY_NTNAME].

Apart from the Above code what i was saying is since u r using * which is in NTNAME Column and it mean all the values in NTNAME rite? Instead of * Replace with ALL(a string) a place holder and create a link table link below.

Correct the data in the NTNAME, replace * with 'ALL' and rename the filed with [ZONE_NTNAME]_MAPPING

load

Upper(NTNAME) AS  NTNAME,

     Upper([NTNAME]) as [ZONE_NTNAME]_MAPPING   // which contains * rite? replace it with 'ALL'

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY);

Add the below code which act as a mapping btw access and the rest of the tables ZONE_SECURITY , COUNTRY_SECURITY. and will be faster as Qlik doesn't have to caluculate for * at login, now it just have to refer to ALL and it will point to all valid entries.

MAPPING_TABLE:

load

     Upper([NTNAME]) as [ZONE_NTNAME],

     Upper([NTNAME]) as [ZONE_NTNAME]_MAPPING

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY) where NTNAME <>'ALL';

concatenate(MAPPING_TABLE)

load

     Upper([NTNAME]) as [ZONE_NTNAME],

     'ALL' as [ZONE_NTNAME]_MAPPING

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY)where NTNAME <>'ALL';

[agni_gold]

Hi Jk ,

when i am making more simple , like below , it is taking same time

i have written this much of code for section access ,

Section Access;

LOAD Upper([ACCESS]) AS [ACCESS],

     Upper(NTNAME) AS  NTNAME,

     Upper([COUNTRY]) as COUNTRY

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY);

Section application;

[parimikittu]

We are left with one last option. Do the upper in the Excel if possible, it will save a lot of time.

[agni_gold]

see , i have my data mode like below ,

Section Access;

LOAD Upper([ACCESS]) AS [ACCESS],

     Upper(NTNAME) AS  NTNAME,

     Upper([COUNTRY]) as COUNTRY

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY);

Section application;

Fact1

Load *,link from table 1;

Concatenate

Load *,link from table2;

Fact2

Load *,link from table 3;

Concatenate

Load *,link from table4;

[parimikittu]

can you also put the data model? by Cntl+T.

any synthetic keys?

[agni_gold]

no there are no synthetic keys present in my Data Model.

[parimikittu]

Then, i don't find a reason why it would take more time.

Have u tried making the cases changes to the excel?