Linking Section Access to multiple dimensions

Exclusive offer: get 50% off this eBook here
QlikView for Developers Cookbook

QlikView for Developers Cookbook — Save 50%

Discover the strategies needed to tackle the most challenging tasks facing the QlikView developer with this book and ebook

$26.99    $13.50
by Stephen Redmond | June 2013 | Cookbooks Enterprise Articles

In this article by Stephen Redmond, author of QlikView for Developers Cookbook, we will look at how Section Access security works by linking a user to the data model. When that user logs in, the data is reduced to only show them the data that they are associated with.

If you have been working with QlikView for a while, you may have come across loops in the data model, causing Loosely Coupled associations. That means you might find it counterintuitive to think about connecting a security table to multiple dimensions in a way that might normally cause a loop. However, QlikView will allow it.

(For more resources related to this topic, see here.)

Getting ready

Load the following script:

Product:
LOAD * INLINE [
ProductID, ProductGroup, ProductName
1, GroupA, Great As
2, GroupC, Super Cs
3, GroupC, Mega Cs
4, GroupB, Good Bs
5, GroupB, Busy Bs
];

Customer:
LOAD * INLINE [
CustomerID, CustomerName, Country
1, Gatsby Gang, USA
2, Charly Choc, USA
3, Donnie Drake, USA
4, London Lamps, UK
5, Shylock Homes, UK
];

Sales:
LOAD * INLINE [
CustomerID, ProductID, Sales
1, 2, 3536
1, 3, 4333
1, 5, 2123
2, 2, 4556
2, 4, 1223
2, 5, 6789
3, 2, 1323
3, 3, 3245
3, 4, 6789
4, 2, 2311
4, 3, 1333
5, 1, 7654
5, 2, 3455
5, 3, 6547
5, 4, 2854
5, 5, 9877
];

CountryLink:
Load Distinct
Country,
Upper(Country) As COUNTRY_LINK
Resident
Customer;

Load Distinct
Country,
'ALL' As COUNTRY_LINK
Resident
Customer;

ProductLink:
Load Distinct
ProductGroup,
Upper(ProductGroup) As PRODUCT_LINK
Resident
Product;

Load Distinct
ProductGroup,
'ALL' As PRODUCT_LINK
Resident
Product;

//Section Access;

Access:
LOAD * INLINE [
ACCESS, USERID, PRODUCT_LINK, COUNTRY_LINK
ADMIN, ADMIN, *, *
USER, GM, ALL, ALL
USER, CM1, ALL, USA
USER, CM2, ALL, UK
USER, PM1, GROUPA, ALL
USER, PM2, GROUPB, ALL
USER, PM3, GROUPC, ALL
USER, SM1, GROUPB, UK
USER, SM2, GROUPA, USA
];

Section Application;

Note that there is a loop error generated on reload because there is a loop in the data structure.

How to do it…

Follow these steps to link Section Access to multiple dimensions:

  1. Add list boxes to the layout for ProductGroup and Country. Add a statistics box for Sales.

  2. Remove // to uncomment the Section Access statement.

  3. From the Settings menu, open Document Properties and select the Opening tab. Turn on the Initial Data Reduction Based on Section Access option.

  4. Reload and save the document. Close QlikView.

  5. Re-open QlikView and open the document. Log in as the Country Manager, CM1, user. Note that USA is the only country. Also, the product group, GroupA, is missing—there are no sales of this product group in USA.

  6. Close QlikView and then re-open again. This time, log in as the Sales Manager, SM2. You will not be allowed access to the document.

  7. Log into the document as the ADMIN user. Edit the script. Add a second entry for the SM2 user in the Access table as follows:

    USER, SM2, GROUPA, USA

    USER, SM2, GROUPB, UK

  8. Reload, save, and close the document and QlikView. Re-open and log in as SM2. Note the selections.

How it works…

Section Access is really quite simple. The user is connected to the data and the data is reduced accordingly. QlikView allows Section Access tables to be connected to multiple dimensions in the main data structure without causing issues with loops.

Each associated field acts in the same way as a selection in the layout. The initial setting for the SM2 user contained values that were mutually exclusive. Because of the default Strict Exclusion setting, the SM2 user cannot log in.

We changed the script and included multiple rows for the SM2 user. Intuitively, we might expect that, as the first row did not connect to the data, only the second row would connect to the data. However, each field value is treated as an individual selection and all of the values are included.

There's more…

If we wanted to include solely the composite association of Country and ProductGroup, we would need to derive a composite key in the data set and connect the user to that.

In this example, we used the USERID field to test using QlikView logins. However, we would normally use NTNAME to link the user to either a Windows login or a custom login.

Resources for Article :


Further resources on this subject:


QlikView for Developers Cookbook Discover the strategies needed to tackle the most challenging tasks facing the QlikView developer with this book and ebook
Published: June 2013
eBook Price: $26.99
Book Price: $44.99
See more
Select your format and quantity:

About the Author :


Stephen Redmond

Stephen Redmond is CTO of CapricornVentis Limited ( http://www.capventis.com ), a QlikView Elite Partner. He is the author of several books, including  QlikView for Developers Cookbook ,  Packt Publishing , and the popular DevLogix series for SalesLogix developers.

In 2006, after working for many years with CRM systems, reporting and analysis solutions, and data integration, Stephen started working with QlikView. Since then, CapricornVentis have become QlikView's top partner in the UK and Ireland territory, and with Stephen at the head of the team, they have implemented QlikView in a wide variety of enterprises and large-business customers, across a wide range of sectors, from public sector to financial services to large retailers.

Stephen regularly contributes to online forums, including the Qlik Community. His QlikView blog is at http://www.qliktips.com , and you can follow him on Twitter (@stephencredmond), where he tweets about QlikView, BI, data visualization, technology in general, and occasionally marathon running.

Books From Packt


Practical Data Analysis and Reporting with BIRT
Practical Data Analysis and Reporting with BIRT

Business Intelligence Cookbook: A Project Lifecycle Approach Using Oracle Technology
Business Intelligence Cookbook: A Project Lifecycle Approach Using Oracle Technology

QlikView 11 for Developers
QlikView 11 for Developers

Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial
Oracle Business Intelligence Enterprise Edition 11g: A Hands-On Tutorial

Instant Drools Starter [Instant]
Instant Drools Starter [Instant]

Oracle BPM Suite 11g Developer's cookbook
Oracle BPM Suite 11g Developer's cookbook

Unity 3.x Scripting
Unity 3.x Scripting

Unity Game Development Essentials
Unity Game Development Essentials


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
G
K
F
a
g
v
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software