topic Data reduction - Different permissions in QlikView

Data reduction - Different permissions

Thu, 26 Jan 2012 20:33:24 GMT

Good afternoon to everybody,

I work in a chemistry company and I am developing a BI dashboard draft in Qlikview (personal edition).

The idea is that the key account managers (KAM), country managers, regional managers, world account managers and segment managers have the same report but just only their area of interest.

I have in my database several field with "KAM","Country","Region", "customer", "segment" among them.

I want to write a "section access" that grants me the confidentiality of the info. For make it clearer:

KAM: Only can access to their local accounts (the lines in the DB that the field "KAM" has their name on it)

Country managers: Only can access to their countries (the lines in the DB that the field "country" has the countries they manage)

Regional managers: Only can access to their regions (the lines in the DB that the field "region" has the regions they manage)

World account managers: Only can access to their local accounts and their global accounts (the lines in the DB that the field "KAM" has their name on it and the lines that customer has the name of the global account they manage)

Segment managers: Only can access to their segments (the lines in the DB that the field "segment" has the segments they manage)

I connect the Qlikview to a ODBC and I want to store the "access" table in the same server.. is this feasible?

Please, any help will be good received.

Thanks in Advance!

Alex

Data reduction - Different permissions

Fri, 27 Jan 2012 11:57:52 GMT

With a simplier example I will be very grateful too!

Re: Data reduction - Different permissions

rohit214 — Fri, 27 Jan 2012 12:36:33 GMT

Hi alex

just go through example

may it helps you

thanks

rohit

Data reduction - Different permissions

jagan — Fri, 27 Jan 2012 15:30:34 GMT

Hi,

Go through the Section Access topic in the Qlikview Reference Manual document, you will find the example and explanation for you requirement.

or just go through this document

http://community.qlik.com/docs/DOC-1853

Hope this helps you.

Regards,

Jagan.

Data reduction - Different permissions

Fri, 27 Jan 2012 17:00:06 GMT

Thanks for the examples! As I have the personal edition, I can not open qvw files . Please could you paste the script code here?

Thanks a lot!!

Data reduction - Different permissions

Fri, 27 Jan 2012 17:01:06 GMT

Thanks Jagan!

I have read the info, but I don't understand how to filter by various fields.

Regards!

Data reduction - Different permissions

Tue, 31 Jan 2012 17:39:18 GMT

Please, I need some help.

I want to reduce the data depending on who access the dashboard.

Data reduction - Different permissions

Miguel_Angel_Baeyens — Tue, 31 Jan 2012 17:57:55 GMT

Hi Alex,

Check some working code in this post and this post among many others. Check the documents mentioned above by Jagan as well.

Hope that helps.

Miguel

Re: Data reduction - Different permissions

Tue, 31 Jan 2012 18:24:11 GMT

Thanks I have read them, and I still dont have a solution. Sorry for the insistent, but I like qlikview and this security problem may me go in other decision.

I will reduce the problem

Brand	Segment	Sales
A	1	2000
A	2	100
A	3	12000
B	1	3155
B	1	4654
B	2	321321
C	2	544
C	3	4897
C	4	21321
C	1	513463

I have 7 users -> 3 Brand manager and 4 segments analysts

They should have access as the following table indicates, where the * means all.

USER	BRAND	SEGMENT
ADMIN	*	*
U1	A	*
U2	B	*
U3	C	*
U4	*	1
U5	*	2
U6	*	3
U7	*	4

Do you understand my problem? is it feasible?

Re: Data reduction - Different permissions

Miguel_Angel_Baeyens — Tue, 31 Jan 2012 19:20:45 GMT

Hi Alex,

Your script has some syntax errors. Since you are using Personal Edition I cannot attach any applications for you, but use this script instead. Notes to bear in mind:

In section access, "*" does not mean "all" but "listed values". That means that you need at least one user with all possible values, one per line, as the ADMIN user below.
QlikView is case sensitive, fields in section access must have the same exact spelling in the data model than in the section access table
It's not mandatory but highly recommended to add an ACCESS field so you can later on set additional security settings and make sure you don't lock yourself outof the document when testing
Fields in section access have fixed names, ACCESS, USERID (USER is not valid)

Data:
LOAD * INLINE [
BRAND, SEGMENT, Sales
A, 1, 2000
A, 2, 100
A, 3, 12000
B, 1, 3155
B, 1, 4654
B, 2, 321321
C, 2, 544
C, 3, 4897
C, 4, 21321
C, 1, 513463
];

SECTION ACCESS;

Security:
LOAD * INLINE [
ACCESS, USERID, BRAND, SEGMENT
ADMIN, ADMIN, A, *
ADMIN, ADMIN, B, *
ADMIN, ADMIN, C, *
ADMIN, ADMIN, *, 1
ADMIN, ADMIN, *, 2
ADMIN, ADMIN, *, 3
ADMIN, ADMIN, *, 4
USER, U1, A, *
USER, U2, B, *
USER, U3, C, *
USER, U4, *, 1
USER, U5, *, 2
USER, U6, *, 3
USER, U7, *, 4
];

SECTION APPLICATION;

Hope that helps.

Miguel

Data reduction - Different permissions

Wed, 01 Feb 2012 11:27:33 GMT

Miguel,

Thanks!!

If I want to store the "security" table in the DB, can I do it?

Can I use your example to many fields as long as I fill all the options in the manager?

Re: Data reduction - Different permissions

Miguel_Angel_Baeyens — Wed, 01 Feb 2012 11:48:10 GMT

Hi Alex,

Section access table is actually in loaded into memory but hidden from all users and not accessible. If you want to write the section access table to a database you will need to load it in a different part of the script with different field names and then you can use a QVD file to store it or you can create a chart in a different file and export to csv or xls file and import this file into your database.

And yes, you can use the ADMIN user to add all new values in BRAND and SEGMENT as displayed above.

Hope that helps.

Miguel

Data reduction - Different permissions

kuba_michalik — Wed, 01 Feb 2012 13:55:26 GMT

Alex,

If I understand your question correctly, you want to keep the Section Access table in a database, edit it there and load it from there (not from inline definition) - if that is so, then you certainly can do it, it loads just like any other table.

As to whether you can just keep expanding the Section Access table with more fields, sure you can, but if you want to restrict access to the data based on values of more than one field for a single user, then it might get problematic, depending on what exactly would you like to achieve.

Re: Data reduction - Different permissions

Wed, 01 Feb 2012 14:46:29 GMT

Thanks to all of you!

Miguel,

Gracias!!

I have a doubt if I change a little the access i have errors

Data:

LOAD * INLINE [

BRAND, SEGMENT, Sales

A, 1, 2000

A, 2, 100

A, 3, 12000

B, 1, 3155

B, 1, 4654

B, 2, 321321

C, 2, 544

C, 3, 4897

C, 4, 21321

C, 1, 513463

];

SECTION ACCESS;

Security:

LOAD * INLINE [

ACCESS, USERID, BRAND, SEGMENT

ADMIN, ADMIN, A, *

ADMIN, ADMIN, B, *

ADMIN, ADMIN, C, *

ADMIN, ADMIN, *, 1

ADMIN, ADMIN, *, 2

ADMIN, ADMIN, *, 3

ADMIN, ADMIN, *, 4

USER, U1, A, *

USER, U2, B, *

USER, U2, C, *

USER, U3, C, *

USER, U4, *, 1

USER, U5, *, 2

USER, U6, *, 3

USER, U7, *, 4

USER, U8, A, 1

USER, U8, B, 1

USER, U8, C, 2

USER, U9, A, *

USER, U9, B, 1

];

SECTION APPLICATION;

I want the user "U8" to have access to the segment 1 of the Brands A and B and also to the segment 2 of the Brand C.

But with this code I can see segments 1 and 2 of the brands A B and C.

Happens sth similar in the case of U9

Kuba,

Thats exactly what I want to do! load directly from a DB and filter by more than one field!

Do you know a way to do it!?

Re: Data reduction - Different permissions

kuba_michalik — Wed, 01 Feb 2012 15:26:00 GMT

As I said, you can load directly from DB without problem - LOAD statements in Section Access can use every source they can use anywhere else. Just make sure the special Section Access fields are named correctly (ACCESS, USERID, and so on).

For filtering on more than one field, the problem is in how the initial data reduction works internally. This data in Security table:

ACCESS, USERID, BRAND, SEGMENT

...

USER, U8, A, 1

USER, U8, B, 1

USER, U8, C, 2

...

means actually this: "for user U8, select A, B, C in field BRAND, select 1, 2 in field SEGMENT, reduce data keeping possible values". The user can see every row of data where BRAND is either A, B or C AND SEGMENT is either 1 or 2. As you can see, how the values are combined in particular rows of Security table has no meaning here.

For your requirement of restricting access to records with particular combinations of the fields, the only way I am aware of is to create the combinations in data and assign them to the user in Security table. It can get somewhat hard to manage if there is plenty of possible combinations. Here is what I mean:

Data:

LOAD *, BRAND&'+'&SEGMENT as BR_SEG INLINE [

BRAND, SEGMENT, Sales

A, 1, 2000

A, 2, 100

A, 3, 12000

B, 1, 3155

B, 1, 4654

B, 2, 321321

C, 2, 544

C, 3, 4897

C, 4, 21321

C, 1, 513463

];

SECTION ACCESS;

Security:

LOAD * INLINE [

ACCESS, USERID, BRAND, SEGMENT, BR_SEG
...
USER, U8, , , A+1

USER, U8, , , B+1

USER, U8, , , C+2
...
];

(please note - because you are making a selection in the combination field BR_SEG you can leave the values for BRAND and SEGMENT empty, this means there will be no selection on these fields. However, if there are no matching selections in ANY of the fields, the user will get locked out of the document if Strict Exclusion is enabled)

Re: Data reduction - Different permissions

Wed, 01 Feb 2012 16:10:07 GMT

Now I understand perfectly! THANKS!!

One question more, can I use wildcards? For example if I create a concatenate of the fields like

"ARGENTINA-FORD-SEGB"

Can I reduce the DB to the rows that has in the concatenate the string *ARGENTINA* ?

THANKS AGAIN to all of you!

Data reduction - Different permissions

kuba_michalik — Wed, 01 Feb 2012 16:15:25 GMT

The answer is "no, we can't" as far as I know (it is necessary to list every combination explicitly - it should be possible to create them programmatically in script if there is too much of them, though), but if somebody knows otherwise, I'd love to know too...