topic Re: Section access with multiple dimensions - how to design in QlikView

Section access with multiple dimensions - how to design

Thu, 09 Jan 2014 12:25:22 GMT

Hi,

I've already read the Generic keys document (technical brief), but I'm still not sure whether I need or not.

Currently the data model has a visible SECTION_ACCESS table that is connected to a link table called VIEW_ALL_ASSIST via a field called ID_A. This link table is then connected to the "old" data with the same ID.

Now I would like to enhance the section access with a new part of data, that is not (and shall not be) connected to the existing. Only the section access shall connect the user's rights to see both certain ID_As and newly introduced ID_Bs.

How can I achieve this? I thought of creating a second VIEW_ALL_ASSIST table that connects the section access with the new data. Is that solution possible without a generic key?

Thanks.

Re: Section access with multiple dimensions - how to design

hic — Thu, 09 Jan 2014 13:10:59 GMT

If you just add two reducing fields in the Section Access - no Generic Keys - it will work. Sort of.

It will work as long as a user only has one line in the authorization table:

User, Product, Country

A, X, DE

B, Z, SE

But the second a user has several lines, you will get problems. Or rather, QlikView will not always do what you want. So, if you have a user that is allowed to see product X in Germany and product Y in France, you might create an authorization table like

User, Product, Country

A, X, DE

A, Y, FR

This will reduce data, but not the way you want it to. You want to see transactions fulfilling

(Product=’X’ AND Country=’DE’) OR (Product=’Y’ AND Country=’FR’)

but you will instead get

(Product=’X’ OR Product=’Y') AND (Country=’DE’ OR Country=’FR’)

The solution is to use Generic Keys, as described in http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/02/complex-authorization

HIC

Re: Section access with multiple dimensions - how to design

Thu, 09 Jan 2014 14:25:02 GMT

Thanks for your help.

How can I set up my section access if a user has several entries per product and country, e.g.?

User Product Country

A 1

A 2

A DE

A AT

B 2 DE

C 2

C 3

C 4

C CH

To say it again, both dimensions are not connected.

Re: Section access with multiple dimensions - how to design

hic — Thu, 09 Jan 2014 14:54:43 GMT

Basically it is pretty much exactly like described in the blog post.

First, maintain the authorization table using generic symbols, like

Then load it like described in the blog post. You need to create a composite key of Product and Country from the table where these two exist, most likely the most detailed fact table.

HIC

Re: Section access with multiple dimensions - how to design

Thu, 09 Jan 2014 14:57:50 GMT

Ah ok, then I misinterpreted the <ANY> entries in the blog.

Thanks!

Re: Re: Section access with multiple dimensions - how to design

Tue, 17 Jun 2014 07:56:11 GMT

After a long time, I would like to come back to this topic again.

My problem is, that the section access is working, but only with a dirty workaround. I solved the problem by giving my own admin user rights for each and every entry in the access table, too, instead of using <VIEW_ALL> or <ANY>.

So the table looks like

User PARTY_ID TERRITORY_ID

A 1 a

A 2 a

B 2 b

C 2 b

C 2 c

(me) 1 a

(me) 2 b

(me) 2 c

Also, my data model does not look like the ones I saw here in the community. I have two reducing fields, but the data that is connected to those reducing fields are not connected. Hope that this is understandable.

So there are two bridge tables from the SECTION_ACCESS table to the data model, one for each part of the application. The data for each part is not connected, so the user either works on one sheet of the application with PARTY_ID related data or on another sheet of the application with TERRITORY_ID related data.

Question is now, how can I set up the section access correctly, so that my admin user does not need access to each and every single entry in the section access? Please be aware that a user can have access to e.g. 18 PARTY_IDs and only 2 TERRITORY_IDs.

Thanks.

Re: Section access with multiple dimensions - how to design

hic — Tue, 17 Jun 2014 08:39:19 GMT

You should not use ADMIN if you want data reduction. The logic for ADMIN users is that the reduction is made only if possible: If the reduction fails, it isn't made and the ADMIN gets to see everything.

Further, you should use UPPER case in your section access.

Since the PARTY_ID and TERRITORY_ID are uncorrelated in the data, I don't see why a simple section access the way you have it wouldn't work. An alternative is to use several tables in section access e.g.:

Main:

Load ACCESS, NTNAME, NTNAME as USERKEY From ...

T1:

Load USERKEY, PARTY_ID From ...

T2:

Load USERKEY, TERRITORY_ID From ...

Where T1 and T2 have different number of rows.

HIC

Re: Section access with multiple dimensions - how to design

Thu, 23 Oct 2014 12:19:19 GMT

I'm still having issues with the section access. I received a ticket that a user can only see 200 instead of 600 parties. As I still needed to redesign the SA, I did the following:

I removed both VIEW_ALL_TABLES that served as a bridge between SA and data.

That is the SA relevant code:

party_tmp:

LOAD

num#(PARTY_ID) as PARTY_ID

FROM ..\QVD\customers.qvd (qvd);

territory_tmp:

LOAD

TerritoryId as TERRITORY_ID

FROM ..\QVD\territory.qvd (qvd);

SECTION_ACCESS:

LOAD * INLINE [

ACCESS,NTNAME,PARTY_ID, TERRITORY_ID

ADMIN,DOMAIN\FUNC.QVTISE, *, *

ADMIN,DOMAIN\NAME1, *, *

ADMIN,DOMAIN\NAME2, *, *

....

];

concatenate(SECTION_ACCESS)

load distinct

if ([PARTY_ID_all]=1,'ADMIN','USER') as ACCESS,

NTNAME,

if ([PARTY_ID_all]=1,'*',num#(PARTY_ID)) as PARTY_ID,

if ([PARTY_ID_all]=1,'*',UPPER(TerritoryId)) as TERRITORY_ID

FROM ..\QVD\ACCESS_TABLE.qvd (qvd)

where NTNAME<>'*';

Trace Write SectionAccess;

section access;

LOAD

UPPER(ACCESS) as ACCESS,

UPPER(NTNAME) as NTNAME,

PARTY_ID,

UPPER(TERRITORY_ID) as TERRITORY_ID

resident SECTION_ACCESS;

section application;

drop table party_tmp;

drop table territory_tmp;

That is how it looks like (excerpt)

ACCESS	NTNAME	PARTY_ID	TERRITORY_ID
ADMIN	DOMAIN\VIEW_USER@COMPANY.COM	*	*
ADMIN	DOMAIN\FUNC.QVTISE	*	*
USER	DOMAIN\USER@COMPANY.COM	5323183	23TD00000000FLVMA4
USER	DOMAIN\USER@COMPANY.COM	5323186	23TD00000000FLVMA4
USER	DOMAIN\USER@COMPANY.COM	5323187	23TD00000000FLVMA4
USER	DOMAIN\USER@COMPANY.COM	5323188	23TD00000000FLVMA4
USER	DOMAIN\USER@COMPANY.COM	5323189	23TD00000000FLVMA4
USER	DOMAIN\USER@COMPANY.COM	5323199	23TD00000000FLVMA4
USER	DOMAIN\USER@COMPANY.COM	5323204	23TD00000000FLVMA4

Data model change:

New issue is now, that when I reload via MMC, the ADMIN users are locked out the document, normal users can access it. What might be the issue?

Thanks,

Jens

Re: Section access with multiple dimensions - how to design

hic — Thu, 23 Oct 2014 12:39:34 GMT

Why the ADMINs are locked out, I don't understand. So, to debug, I would remove the "Section Access" statement to see what the Section Access table really looks like.

Further, you should not use several reducing fields. See more in http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/02/data-reduction-using-multiple-fields

HIC

Re: Section access with multiple dimensions - how to design

Thu, 23 Oct 2014 13:14:01 GMT

I'm a bit confused now, because a little bit up you said that I don't need generic keys, as both reducing fields don't have a correlation. Or is my understanding wrong?

Re: Section access with multiple dimensions - how to design

hic — Thu, 23 Oct 2014 14:13:05 GMT

Having two reducing fields will cause some problems. First, if you have a user on two lines, e.g.

NTNAME, REGION, PRODUCT

X, A, NUT

X B, BOLT

you would expect that the user X can see NUTs in A and BOLTs in B, but not the other way around. But this is not how QlikView interprets the table. Instead X will see NUTs and BOLTs in both A and B.

In your case, the two fields are uncorrelated, so it should work anyway. So the above statement is correct. I just mentioned it since I think it is bad practice to use several reducting fields...

HIC

Re: Section access with multiple dimensions - how to design

Wed, 29 Oct 2014 06:39:20 GMT

Still no working solution .

I tried to follow your suggestion loading the section access in three tables, Main, T1, T2. This only works as long as the 'Section Access;' and 'Section Application' commands are commented.

As soon as I activate them, I receive a General Script error at the end of the load. Do you have an idea what this might be?

That's an excerpt of the data model if the section access tables are loaded as normal tables.

That is the script to load it (I added few comments that are not part of the regular code):

Temp:

LOAD

ACCESS,

NTNAME,

PARTY_ID,

PARTY_ID_all

TERRITORY_ID

FROM ..\QVD\ref_SF_ter_ACCESS_TABLE.qvd (qvd)

WHERE NTNAME<>'*';

SECTION Access;

Main:

LOAD DISTINCT

'USER' as ACCESS,

NTNAME,

NTNAME as USERKEY

RESIDENT Temp;

concatenate (Main)

LOAD * INLINE [

ACCESS, NTNAME, USERKEY

ADMIN,ROOT\FUNC.QVTISE, ROOT\FUNC.QVTISE //publisher user

ADMIN,QVD1HW\JENS, QVD1HW\JENS

ADMIN,QVD1HW\ADMQV, QVD1HW\ADMQV

ADMIN,QVD1HW\MARKUS, QVD1HW\MARKUS

... //more ADMINS here

];

T1:

LOAD DISTINCT

NTNAME as USERKEY,

if(PARTY_ID_all = 1,'*',num#(PARTY_ID)) as PARTY_ID //VIEW_ALL users (PARTY_ID_all flag set to 1) shall get a row with a '*', all others the PARTY_IDs in number format

RESIDENT Temp;

CONCATENATE (T1)

LOAD * INLINE [

USERKEY, PARTY_ID

ROOT\FUNC.QVTISE, *

QVD1HW\JENS, *

QVD1HW\ADMQV, *

QVD1HW\MARKUS, *

... //more ADMINS here

];

T2:

load distinct

NTNAME as USERKEY,

if([PARTY_ID_all]=1,'*',UPPER(TerritoryId)) as TERRITORY_ID //same as for PARTY_IDs. VIEW_ALL users (PARTY_ID_all flag set to 1) shall get a row with a '*', all others the TERRITORY_IDs

Resident Temp;

concatenate (T2)

LOAD * INLINE [

USERKEY, TERRITORY_ID

ROOT\FUNC.QVTISE, *

QVD1HW\JENS, *

QVD1HW\ADMQV, *

QVD1HW\MARKUS, *

... //more ADMINS here

];

SECTION Application;

drop table Temp;

Thanks a lot!

Jens

Re: Section access with multiple dimensions - how to design

Thu, 30 Oct 2014 13:03:48 GMT

Henric? Are you there?