Section access on multiple fields

[roisolberg]

Hi All,

i am trying to create a section access based on 2 fields

some background, this is a big model that includes many entities
the main fact table is SalesDocs - concatenated table of Sales orders/ Sales Invoices / Price quotes/etc.
so for the main fact table i have many dims (we have 4 companies around the world and this model includes them all ) all connected to common fields such as Key_Part_Company (a combination of the partID and company)

the first field in the section access(CompanyName) is already in use without any problems* (some users can see only a single company)
*every entity has a key to this Companies Dim table (Company_Key)

the second field that i would like to add to the section access(PrimaryManufacturer) is used only in 2 tables (Orders/Invoices)

I've tried to create a common field using the same logic, for each table that the field exists add it as a common field and link it to a dim table

I've tried the following code:

Section Access;
AUTHORIZATION:

LOAD * INLINE [
ACCESS, USERID, COMPANYDB,PRIMARYMANUF
ADMIN, DOMAIN\USER1,, // Should see everything
ADMIN, DOMAIN\USER2 ,COMPANY1,PRIMARYMANUF1 // Should see only Company1 and PRIMARYMANUF1

..

];
Section Application;

when it was only company it worked, after adding the second field, everything goes wrong and even user1 is getting a data reduction
reminder - some table such as pricequote - does not have this field

i've also tried the * instead of leaving blank

any ideas/articles for such a case?

Many thanks,
Roi

[roisolberg]

Anyone?

[hic]

First of all, you have marked both users as 'ADMIN'. This is not good, since ADMIN sometimes overrides the data reduction. (An ADMIN should always see some data.) So, I suggest changing this to 'USER'.

Secondly, you should use wildcards for seeing everything. So try the following:

LOAD * INLINE [
ACCESS, USERID, COMPANYDB, PRIMARYMANUF
USER, DOMAIN\USER1,*,*
USER, DOMAIN\USER2,COMPANY1,PRIMARYMANUF1];

Good luck!

[roisolberg]

Thanks for your replay @hic ,

I've tried to so but the thing is all of the users, even those who have * in both field

sees only the data for COMPANY1 + PRIMARMNYMANUF1

Also, for objects that don't have the data for PRIMARMNYMANUF1, Non of the users see it

(for example, Price Quotes, don't have a primary manufacturer, so nobody sees price quotes at all )

[hic]

Yes, sorry, my mistake. You need to have a complete list of values in the authorization table in order for the wildcards to work. E.g.

LOAD * INLINE [
ACCESS, USERID, COMPANYDB, PRIMARYMANUF
USER, DOMAIN\USER1,*,*
USER, DOMAIN\USER2,COMPANY1,PRIMARYMANUF1
USER, DOMAIN\NOBODY,COMPANY1,PRIMARYMANUF1
USER, DOMAIN\NOBODY,COMPANY2,PRIMARYMANUF2
USER, DOMAIN\NOBODY,COMPANY3,PRIMARYMANUF3
USER, DOMAIN\NOBODY,COMPANY4,PRIMARYMANUF4
];

No user will log in using DOMAIN\NOBODY but the entries are necessary to define the wildcard.

[roisolberg]

Thanks again @hic ,

It made some progress but still i am facing 2 issues

1. if Company1 don't have primary manufacturers at all (Null values for all of the field)

it gets drop from the data so there is no Company1 at all

2. Price quotes, an entity without primary manufacturers, also gets dropped and i don't see them at all

😕

[hic]

Yes, NULL values in the reducing fields is not a good idea. The reduction in Section Access will remove such combinations.

Solving this problem can be a challenge. You can approach it in two ways: Using a single composite key, or converting NULLs to blanks. Both approaches means that you may have to change your data model slightly.

Single composite key:
You need to create a key like
COMPANYDB & '|' & PRIMARYMANUF as %KEY
and use this for authorization. If you include also combinations where one of the fields are NULL or blank, it will work properly. It could be a challenge to figure out in which table the key should be created, though.

See also https://community.qlik.com/t5/Qlik-Design-Blog/Basics-for-complex-authorization/ba-p/1465872

Converting NULLs to blanks:
You need to load extra rows to your tables. For example, if you introduce a 'Dummy' primary manufacturer and link this to all companies, you will solve the first problem. The following will add rows to the Orders table:

Concatenate (Orders)
Load distinct
COMPANYDB,
'DUMMY' as PRIMARYMANUF,
Null() as OrderAmount
Resident Companies ;

[roisolberg]

Again, many thanks it is much appreciated @hic  ,

replacing nulls and blanks to dummy did work,

but I am getting data reduction for the dimension tables that are associated to the main fact table..

also, i've done it just on a sample of the real model, so there are many more dimensions and also some stand alone tables..

Is this not a standard request? I couldn't find any threads or articles that cover this scenario..