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]

It happens when the logic for the section access is not optimized. It happen to me, once i have optimized the code by correcting the joins and logic, it ran smooth.

[agni_gold]

thanks vj  ,

Can you please tell me how you done the optimization for section access ?

[agni_gold]

gwassenaar‌ any help here please

[parimikittu]

Can you past ur code here? i can take a look and wil be able to help u.

[agni_gold]

//Defines the * as ALL value

Star is *;

//This specifically sets the mapping name based off of the users login name(NTNAME) and creates a Country and Area Username mapping

// 2 mappings are needed to avoid circular ref and the creation of synthetic tables

Section Access;

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

  Upper([DOMAIN]) AS [DOMAIN],

     Upper(NTNAME) AS  NTNAME,

     Upper([NTNAME]) as [ZONE_NTNAME],

     Upper([NTNAME]) as [COUNTRY_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

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';

[parimikittu]

since you code is using two columns for data reduction. Better concatenate them and also create a link table with three columns(one for concatenate column,two for individual columns.Check the code below

Section Access;

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

  Upper([DOMAIN]) AS [DOMAIN],

     Upper(NTNAME) AS  NTNAME,

    Upper([NTNAME]) &'|'& Upper([NTNAME]) as [Map_NTNAME]

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY);

Section application;

mapping_table:

load

Upper([NTNAME]) &'|'& Upper([NTNAME]) as [Map_NTNAME],

Upper([NTNAME]) as [ZONE_NTNAME],

     Upper([NTNAME]) as [COUNTRY_NTNAME]

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY);

ZONE_SECURITY:

LOAD DISTINCT

  Upper([NTNAME]) AS [ZONE_NTNAME],

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

FROM [$(vDataPath)\SectionAccess.xlsx]

(ooxml, embedded labels, table is SECURITY)

WHERE UPPER(NTNAME) <> 'SRVQLIK_DEV'

AND   UPPER(NTNAME) <> 'SRVQLIK_PROD';

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]

when i applied this code to my file , it is failing to open from access point

[agni_gold]

It is taking same time no improvement

[agni_gold]

Any help guys ?