Section access with multiple dimensions - how to design

[#1]

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.

[hic]

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

[#3]

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.

[hic]

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

[#5]

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

Thanks!

[#6]

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.

[hic]

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.

See also http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/02/data-reduction-using-multiple-fields

HIC

[#8]

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

[hic]

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

[#10]

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?