topic Complex Dynamic Data Reduction in QlikView

Complex Dynamic Data Reduction

Thu, 25 Mar 2010 22:57:35 GMT

I am loading below data table in QV section application

REGION_5,REGION_4,REGION_3,IND_5,IND_4,IND_3,YTD_REV

My section access looks like -

USERID,PASSWORD,ACCESS,SERIAL,LINK
U042711,*,ADMIN,*,U042711

I am using below table in section application to link the LINK field

LINK,REGION_5,REGION_4,REGION_3,IND_5,IND_4,IND_3
U042711,AMERICAS,,,,FIG,
U042711,EMEA,,,,SVG,

Now the problem is when I login with a user who has access to few regions and few industries, QlikView seems to be doing correct data reduction for those regions and industries. But I dont see any revenue amount that is being loaded as part of YTD_REV field

How do I achieve dynamic data reduction in situations like this? Above is just an example. In actual application I have 3 dimensions - region, industry, product - with 5 levels each!

Complex Dynamic Data Reduction

Fri, 26 Mar 2010 15:14:11 GMT

Bump!

Complex Dynamic Data Reduction

Oleg_Troyansky — Sat, 27 Mar 2010 04:07:02 GMT

If you need to define access rights at different levels, you should link your users to each level separately. If you have Region, Industry and Product, then you need 3 separate tables where you link your file "LINK" to each one f those fields. Then it should work.

Complex Dynamic Data Reduction

Sat, 27 Mar 2010 18:28:26 GMT

Thanks Oleg..

I followed the direction of creating 3 seperate tables.. and tried with 2 approaches -

1) Defined SECTION ACCESS as - USERID,PASSWORD,SERIAL,ACCESS,IBANKER_REGION_LINK,IBANKER_INDUSTRY_LINK,IBANKER_PRODUCT_LINK

And each of these links points to tables which defines their access to individual region/industry/product

Data loaded fine and this seems to work if a user is given access to either region(s) or industry(s) or product(s). But I cannot define combined access levels. For example, if I try to set a user with access to NORTH AMERICA region & few industries(Insurance, Banks) then that user gets restricted to NORTH AMERICA but still can see all the industries..

2) Defined SECTION ACCESS as - USERID,PASSWORD,SERIAL,ACCESS,IBANKER_LINK

And loaded tables as shown in attached image.. But this approach results in circular reference!

Appreciate any suggestions for a possible workaround!

Complex Dynamic Data Reduction

Oleg_Troyansky — Sat, 27 Mar 2010 20:09:21 GMT

Here is an example of a security script for multiple levels:


section access;

ODBC CONNECT TO...;

.
.load
 USERID as USERID,
 QVPASSWORD as PASSWORD,
 ACCESS as ACCESS,
 SERIAL as SERIAL,
 USERNAME as USERNAME,
 upper(USERID) as SMUSERCODE,
 upper(USERID) as SGUSERCODE,
 upper(USERID) as VPUSERCODE

;

SQL SELECT
*
FROM UserPassword;


section application;

star is *;

// Sales Group
load
 upper(UserID) as SGUSERCODE,
 `Sales Group` as [Sales Group Name]
;

SQL SELECT
 `Sales Group`,
 UserID
FROM SalesSecurity
;

// Sales Manager
load
 upper(UserID) as SMUSERCODE,
 `Sales Manager` as [Sales Manager]
;

SQL SELECT
 `Sales Manager`,
 UserID
FROM SalesSecurity
;

// VP Sales
load
 upper(UserID) as VPUSERCODE,
 `VP Sales` as [VP Sales]
;

SQL SELECT
 UserID ,
 `VP Sales`
FROM SalesSecurity
;