topic Re: Data Reduction & Section Access in QlikView

Data Reduction & Section Access

wonkymeister — Thu, 11 Jan 2018 11:09:21 GMT

Morning!

I have a rather complex problem to solve, any advice/help greatly appreciated! I’ve never used Section Access at anything other than a basic level.

I need to restrict users as to what data they can see at various levels within an application.

I need to restrict users to only see data for their department (this is easy using publisher to distribute the app for only their department – no problem)
Some users cannot see their entire departments data, only data for their sub department or for the team that they line manage (various levels of restriction here and this is what I’m struggling with)
Sheet restriction for certain users within the application

There (probably) going to be 5 levels of users

Executive (see all departments – possibly with data at an aggregated level)
Department head (restricted to their department)
Department senior managers (restricted to their staff in their department)
Line managers (restricted to their staff in their department)
Other

It’s a pretty simple data set in that the data is all in one table, with everything at row level. I just don’t know how to approach reducing the dashboards/app within a department depending on the users level of access.

Any help/advice greatly appreciated!

Re: Data Reduction & Section Access

Peter_Cammaert — Thu, 11 Jan 2018 11:22:24 GMT

The most simple solution would be to map every individual with permissions to access this document to the list of employees of which he/she should be able to see the data. If your hierarchies are present somewhere in your data sources, you can assign a (sub)department to each individual and write some Section Access script code to extract the list of employees from the hierarchies. An Executive will be linked to a synthetic department "ALL" that is translated by the SA script code to a list of all employees.

More specific hints would require more details from you of your data model and the Company-Department-Subdepartment-Employee relation.

User category 5) is an interesting one. You don't provide any specifics. Does that category encompass "everything else and every other combination"?

Re: Data Reduction & Section Access

wonkymeister — Fri, 12 Jan 2018 14:36:19 GMT

Thanks Peter,

helpful pointers.

I've started looking at data reduction with Section Access, i.e restricting what a user can see based on field values in the dataset specified in the Section Access table in the QMC.

Am i right in thinking that as column names in Section Access must be uppercase, that the same column names in my dataset need to be uppercase also?

Apologies if it sounds like a daft question - i normally lowercase all field names in any app that i build. If i'm to use data reduction with Section Access then i guess i'm going to have to Upper() the relevant fields?

Cheers

Re: Data Reduction & Section Access

thomaslg_wq — Fri, 12 Jan 2018 15:03:01 GMT

Hi Phillip,

You may already have seen this but just in case :

Basics for complex authorization

Regards,

Re: Data Reduction & Section Access

thomaslg_wq — Fri, 12 Jan 2018 15:26:16 GMT

Here enclosed are some fake_data and parameter file.

And here is the script that works with :

////////// Section access tab

Section Access;

Section_Access:

LOAD

ACCESS,

USERID,

FieldLink1&'-'&FieldLink2&'-'&FieldLink3 as %SECURITY_LINK,

upper(ConditionalShow) as ConditionalShow

FROM [SectionAccess.xlsx]

(ooxml, embedded labels, table is SectionAccess2);

Section Application;

ConditionalShow:

LOAD Distinct

upper(ConditionalShow) as ConditionalShow

FROM [SectionAccess.xlsx]

(ooxml, embedded labels, table is SectionAccess2);

////////// All your data tabs

Fake_Data:

LOAD

Analytic1,

Analytic2,

Analytic3,

Analytic1&'-'&Analytic2&'-'&Analytic3 as %Security_Link,

Date,

Ventes

FROM [FakeData.xlsx]

(ooxml, embedded labels, table is Data);

////////// Security link tab

// Real link

SecurityBridge:

LOAD Distinct

%Security_Link,

Analytic1&'-'&Analytic2&'-'&Analytic3 as %SECURITY_LINK

Resident Fake_Data;

// One ALL

Concatenate(SecurityBridge)

LOAD Distinct

%Security_Link,

'ALL-'&Analytic2&'-'&Analytic3 as %SECURITY_LINK

Resident Fake_Data;

LOAD Distinct

%Security_Link,

Analytic1&'-ALL-'&Analytic3 as %SECURITY_LINK

Resident Fake_Data;

Concatenate(SecurityBridge)

LOAD Distinct

%Security_Link,

Analytic1&'-'&Analytic2&'-ALL' as %SECURITY_LINK

Resident Fake_Data;

// Two ALL

Concatenate(SecurityBridge)

LOAD Distinct

%Security_Link,

'ALL-ALL-'&Analytic3 as %SECURITY_LINK

Resident Fake_Data;

Concatenate(SecurityBridge)

LOAD Distinct

%Security_Link,

Analytic1&'-ALL-ALL' as %SECURITY_LINK

Resident Fake_Data;

Concatenate(SecurityBridge)

LOAD Distinct

%Security_Link,

'ALL-'&Analytic2&'-ALL' as %SECURITY_LINK

Resident Fake_Data;

// Three ALL

Concatenate(SecurityBridge)

LOAD Distinct

%Security_Link,

'ALL-ALL-ALL' as %SECURITY_LINK

Resident Fake_Data;

Re: Data Reduction & Section Access

wonkymeister — Fri, 12 Jan 2018 15:35:10 GMT

Thanks Thomas, i'll have a look over the weekend.

cheers,

Phil.

Re: Data Reduction & Section Access

Peter_Cammaert — Mon, 15 Jan 2018 12:47:07 GMT

Not only the names of fields that connect across the Section Access-Section Application border must be in UPPER case, also their values must be in UPPER case. QlikView does this automatically for everything that remains in Section Access. If you don't follow suit in Section Application, you won't have any linked fields and no data reduction. Your document will fall back to simple document access control using Section Access entries. As a result, everyone who manages to get in will see everything.

Peter

Re: Data Reduction & Section Access

wonkymeister — Mon, 15 Jan 2018 14:46:28 GMT

Hi Peter,

thanks for the reply. I'm a bit concerned that i'm not understanding this correctly. Am i right in thinking that for data reduction at a user level using Section Access to work that i need the field name from the dataset in the section access table also, and that the field name in section access must be uppercase as it must be in the dataset that i load?

So in the example below, i'd need FIELD_NAME_TO_REDUCE to be uppercase in my Section Access script, and it would also need to be uppercase as a field in the qvw? And that this will also mean a key join (when looking at the table viewer to see the data model) between my ACCESS_TABLE and the loaded dataset?

Therefore, in the example below the user loaded in from the Section Access table on the QMC (john_someone) would only be able to see data in the model that has the value "Human Resources" in the field FIELD_NAME_TO_REDUCE?

example of my code:

// Hidden Script in QVW

SA_ACCESS_TABLE:
LOAD * INLINE
[
NTNAME ,ACCESS ,USERNAME, FIELD_NAME_TO_REDUCE

YYY\ABCDE ,ADMIN ,IT ACCOUNT, *
YYY\BCDEF ,ADMIN ,IT NPrintAcc, *

YYY\CDEFG ,ADMIN ,BOB SMITH, *

];// FIELD_NAME_TO_REDUCE is the field name in the dataset that needs reducing

Concatenate (ACCESS_TABLE)

// join to SA table on the QMC to load in users that have access to this qvw
LOAD
NTNAME,
ACCESS,
USERNAME,
FIELD_NAME_TO_REDUCE
FROM
[http://URL_Etc](html, utf8, embedded labels, table is DEV\MY_QMC_SECTION_ACCESS_TABLE);

Section Access;
LOAD * Resident SA_ACCESS_TABLE;
Section Application;

// example of Section Access table on the QMC

NTNAME	ACCESS	USERNAME	FIELD_NAME_TO_REDUCE
YYY\XYZA	USER	JOHN_SOMEONE	Human Resources

// example of dataset

staff_name	staff_id	user_id	FIELD_NAME_TO_REDUCE
john_someone	123456789	xyza	Human Resources

Re: Data Reduction & Section Access

Peter_Cammaert — Mon, 15 Jan 2018 16:50:58 GMT

No that won't work. The field names are OK, and they will link and guide the data reduction into Section Application on opening the document.

However, as I said in my previous post: Section Access will convert the Field Names AND the Field Values to upper case (behind the scenes). So you will have a Section Access value "HUMAN RESOURCES" that will not match a Section Application value "Human Resources". If you reload this document, you will effectively shut out everyone (except for the Developers using QV Desktop) Remember that QlikView is extremely case sensitive.

When loading your table into Section Access, the conversion to upper case will happen automatically. The problem is in Section Application. Either convert all FIELD_NAME_TO_REDUCE values to upper case, or - if you -don't like that - create FIELD_NAME_TO_REDUCE as an internal copy of the original column, but this time filled with upper case values only.

And remember that a star ('*') in Section Access means: "all values in this column in the Section Access table", NOT "all fields in this column and in all Section Application columns with the same name".

Peter

Re: Data Reduction & Section Access

wonkymeister — Tue, 16 Jan 2018 13:33:38 GMT

Peter,

what do i pass in my Section Access table for ADMIN in order to see all?

i.e. * means only all those values listed in the table

my reduction is working, for a specific user in that they can now only see what is specified in the section access column. Problem is, ADMIN now can see no data?

i'm currently leaving the reduction column blank for ADMIN, what value should i put in the column in order that ADMIN can see everything in the dataset?

sorry to be a pain, finding my feet with this one.

Cheers.

Re: Data Reduction & Section Access

wonkymeister — Wed, 17 Jan 2018 15:50:17 GMT

Thanks Peter, got sorted. Not sure its coded correctly (Strict Exclusion doesn't allow ADMIN access)

but on the whole it works 100%, ADMIN sees all, reduced user sees as expected.

complex authorization will be next