Row-level access in Qlik Sense using custom properties in QMC

Unlike its predecessor QlikView, QlikSense has a very robust and flexible rule-based security engine that allows setting up access restrictions in a centralized fashion using QMC (Qlik Sense Management Console). However, one important thing is still missing, and it's row-level data access. You're supposed to use Section Access, a QlikView relic which has only one good feature -- it works.

Luckily, it is possible to set up row-level access right from QMC, leveraging all the power of centralized security management. It's not completely official, since it requires accessing QSR (Qlik Sense Repository) directly, although it is possible to encapsulate the logic in a reusable script thus making future maintenance easier.

Here is how it works:

In QMC we create a custom property (e.g. MyCustomProperty) with some possible values (e.g. "MARKETING" and "FINANCE"). Now, the QMC will have a new property on a user page - MyCustomProperty, which can be defined as MARKETING, or FINANCE, or their combination (or nothing at all). These values are stored in QSR (the repository).

Since QSR is a PostgreSQL database we can connect to it in a Qlik Sense application and create Section Access that will use the custom property values for dynamic data reduction.

To make it work, first, connect to your Qlik Sense repository.

The repository data model contains 100+ tables however we need only three of them:

• Users
• CustomPropertyDefinitions
• CustomPropertyValues

Subset of QSR data model

Here is the SQL query, needed to extract and join all the three tables to form Section Access.

SQL SELECT
    CASE WHEN
        //RootAdmins can always see everything
        usr."RolesString" LIKE '%RootAdmin%' THEN 'ADMIN'
        ELSE 'USER'
    END as "ACCESS",
 
    upper(val."Value") as "REDUCTION",
 
    upper(usr."UserDirectory") || '\' || upper(usr."UserId") as "USERID"

FROM "QSR"."public"."CustomPropertyDefinitions" def 

    LEFT JOIN "QSR"."public"."CustomPropertyValues" val
        ON def."ID" = val."Definition_ID"
       
    LEFT JOIN "QSR"."public"."Users" usr
        ON usr."ID" = val."User_ID"
       
    WHERE def."Name" = 'MyCustomProperty' and val."Deleted" is false
    ;

The query creates Section Access with three columns:

• ACCESS
• USERID
• REDUCTION

For every combination of user and its custom property value it creates a new row in Section Access, where REDUCTION corresponds to the values. The query also assigns ADMIN access to all RootAdmins in QMC (just in case). Everyone else gets USER access.

Now, include field REDUCTION (make sure its values are upper-cased) into your application data model, and Section Access will leave only rows where REDUCTION in the data model is the same as REDUCTION in Section Access for the current user, thus enforcing row-level data access.

A few gotchas:

1. To change permissions for a user -- change his/her MyCustomProperty in QMC, and reload the application (Section Access will keep old permissions until reloaded).

2. If you need to have users that have to access all data, but they are not RootAdmins, you will need to explicitly add all possible values of the custom property for the user, because in Qlik Sense its Section Access works in strict mode. You can do it manually, in QMC, but that's not convenient. Much easier is to create a new value (e.g. EVERYTHING), and then for users with this value assigned automatically create rows with all possible values (excluding EVERYTHING). QSR stores all possible values of a custom property in field "ChoiceValuesString" of table

"CustomPropertyDefinitions". All properties are stored together in one text field, separated as below:

FINANCE:,:MARKETING:,:EVERYTHING

In order to create a list from this line of text the following query can be used:

LOAD
    Subfield( [ChoiceValuesString], ':,:') as REDUCTION
;
SQL SELECT
    "ChoiceValuesString"
FROM "QSR"."public"."CustomPropertyDefinitions" def

    WHERE def."Name" = 'MyCustomProperty';

Once you get the list of values you can append it to the Section Access created previously.

3. To make Section Access work in scheduled reload tasks you must add the scheduler's account to Section Access:

CONCATENATE (YourSectionAccessTable) LOAD * INLINE [
ACCESS, USERID,REDUCTION
ADMIN,INTERNAL\SA_SCHEDULER,*
];

 4. You can wrap entire Section Access script into a common reusable script and insert it into applications using something like:

$(Must_Include=lib://SectionAccess/section_access.qs)

In this case even if Qlik will change QSR's data model you can only adjust your reusable script to make it work for all applications that use it.

UPDATE 12/14/2015

5. Before reloading an application to apply changes in Section Access make sure you close all browser tabs opened with the application (Load Editor, Data Model, etc.). Otherwise, you might get unpredictable behavior. Also make sure you reload applications with Section Access every time you changed custom properties for a user because Section Access keeps a subset of old repository data.

Transformational data analysis

Single step analytics
The vast majority of BI or Data Discovery tools follow the same approach for data visualization and analysis. I call it 1-step analytics or single pass analytics. In the foundation of this approach there is a single logical data set consisting of one table, or several linked tables. Sometimes it can span across several data sources, but from a user's perspective it's still a single data set or cube. In order to perform analysis a user interacts with different parts of this data set. Visual objects of a report or dashboard are usually built on top of a subset of this data model, or microcube. In another words, under any chart or table in a report or dashboard there is a microcube. In a standard case, every microcube is obtained by filtering or aggregating data from the main cube, usually on the fly. Some BI tools allow users freely change measures and dimensions of microcubes on the fly (query&analysis tools like BusinessObjects or Tableau). Sometimes microcube metadata is fixed during design time and users can only filter data in it (Qlik). But in any case these microcubes are derived from the main cube. So there is only one step (pass) -- derive a microcube from the main cube.

Single-pass analytics

To derive a micro-cube typically 4 operations are used:

• Select columns -- take only certain columns from the main cube
• Filter -- keep only rows that satisfy some rule or condition
• Aggregate -- calculate aggregates like sum or count, grouped by some dimensions
• Calculate new columns -- calculate new measures or dimensions using a formula

 When users perform analysis they actually force BI tools to recalculate microcubes on the fly, using different parameters. For instance, when you drill down from year to month you tell your BI tool to select different columns (add month), apply a different filter (fix year), and aggregate by a different dimension (month instead of year). The power of BI is in the fact that all these operations are performed automatically by a single click. Whatever you, as user, do -- your BI tool automatically does these 4 operations under the hood.

So far, so good. Is anything wrong with this?

Multi-step analytics
For many users and cases, single step analytics is totally sufficient. Remember the times when all users had was just static reports. Probably we could've called that zero step analytics :) So the current state of affairs is a great improvement compared to those times.

But for many cases (and users) it's not enough. In the real life there are many business rules and types of analysis that can not be covered with only single pass. They need two or more passes in which microcubes are derived from another microcubes and not only from the main cube. For instance different kinds of customer behavior analysis require analyzing customer performance over a period of time (one pass), then grouping them based on this performance (another pass), and finally obtaining performance of the groups over the same or different time period (third pass). Example: for a retail chain it's important to see growth of their customer base and not just growth of sales. For this, the organization might analyze how many new customers every month became permanent buyers. But in order to understand if a customer that joined in, say, March is permanent, it's necessary to analyze his/her purchases from April to, say, October. And only then we can come to conclusion that, for example, 3% of new customers joined in March became permanent. And then calculate the same for all months. And then let the user experiment. For instance, modify the rule how we consider a customer permanent -- change it from "1 purchase over 3 months" to "2 purchases over 6 months", and see what happens.

Multi-step analytics

Number of cases when users need ad hoc multi-pass analytics will only grow because as organizations learn more about their customers, they slowly move from basic metrics like number of orders or total revenue to more sophisticated performance indicators that monitor key drivers of business.

You might argue that the type of tasks I described above is not unheard of, and is typically solved by the means of ETL. That's true. The common wisdom across BI developers is try to pre-calculate everything that cannot be calculated on the fly. Move it into ETL. But this doesn't really work because ETL is static. Well, it's not static for a developer, but for a user it's static. The reason it's static for the user is because designing ETL requires technical skills and users don't have them (and shouldn't have). You know how users get around this problem? They do ad hoc ETL in Excel. Yes, it's horribly inconvenient, very error prone, and requires a lot of tedious manual work, but what else can they do? They simply don't have a choice. At least Excel is something they are comfortable with.

ETL today is the same thing as static reporting was before BI started to prosper. I believe it will change with transformational data analysis.

Transformational data analysis
We've used to the fact that ETL and BI are different beasts. It's like a dogma. But what if the borderline between BI and ETL is somewhat artificial and shouldn't be that distinct? I'm not talking about low-level ETL that for instance integrates legacy systems. I'm talking about these parts of business logic that are moved to ETL simply because current BI tools can't handle it.

So, I'm proposing a new term -- "transformational data analysis". It's a way to analyze data through ad hoc data manipulation and multi-step analytics built as series of transformations. It effectively eliminates the difference between BI and ETL (in its business logic part).

EasyMorph (http://easymorph.com), the tool I've been working on since 2013, follows the concept of transformational data analysis. It allows non-technical users to build multi-step analytics using more than 20 various transformations (not just 4), and then re-run it with different parameters and modify it on the fly. At this point EasyMorph doesn't look like a traditional BI tool in that sense that it doesn't have interactive charts or pivot tables (yet!), but we're getting there.

PS.

Can Qlik do multi-pass analytics?
QlikView (and Qlik Sense) is somewhat different from most BI tools (including Tableau) in that sense that it allows going slightly beyond the 1-step limit. The set analysis feature allows building rather complex filtering conditions that might include the element functions ( P() and E() ) which can be considered a 2-step logic. Also, with the help of aggr() function you can add an extra aggregation step. But there are plenty cases where they can't help. And even if they can help, designing expressions with set analysis and aggr() is a huge hassle (I did it for years) and hardly can be considered user friendly or suitable for ad hoc workflow. To be fair we can assume that Qlik allows 1.5-step analytics.

UPDATE 16/3/2016
Starting from version 2.8 EasyMorph offers dynamic drag-and-drop charts for interactive data visualization and more than 50 transformations.

UPDATE 9/8/2015
Interesting comment from Christophe Vogne:

Hi Dmitry,

Reading again your article I understand better what you mean.
From my perspective you are highlighting the fact that BI is moving from multidimensional database to graph database.
Multidimensional database need to be strongly structured and this design break the hidden links between information if they haven't been considered.
With kimball/Inmon BI method, there's always somebody that decide for you what you are supposed to discover: the datawarehouse team and their cascading cubes, datamarts...
QlikView is 1.5 because it's a tabular database (the fields values storage) on top of a graph database (the pointers layer that make the links between values). So some lost associations can be rebuilt by users.

So one aspect of the BI future is graph database because users need to link any information in any context at any time.
But to aggregate values on graph database is a tough challenge (the Set Analysis) and to manage multiple edges between vertex (the synthetic keys) is tougher.

Interesting article indeed

12 similarities between EasyMorph and QlikView loading script

Here are a few analogies that can help QlikView users become more familiar with data transformation concepts in EasyMorph:

In-memory data transformation

Both QlikView and EasyMorph don't require an external database to transform data. Instead, they rely on their own in-memory engines. In QlikView the data transformation part of the engine is single threaded. EasyMorph's engine is multi-threaded but less memory efficient. Both EasyMorph and QlikView use data compression.

Mixed value types in one column

Like QlikView, EasyMorph can mix text values and numbers in the same column, which makes it an excellent visual tool for parsing Excel spreadsheets. Although, type system in EasyMorph is a bit different.

Transformations in EM are similar to preceding loads in QV

In QlikView's preceding loads one load statement is based directly on the result of another load statement, which sometimes is convenient. Transformations in EasyMorph are somewhat similar to preceding loads in QlikView in that sense that a transformation directly uses the output of its previous transformation as input, without any intermediate (resident) tables. While in QlikView you can't do much in preceding loads -- only calculate new columns, and de-duplicate data (using DISTINCT), in EasyMorph you can use any of 25+ transformations (including filters, sorts, and even joins) in any order. Basically, all transformation process in EasyMorph is designed like super-powerful preceding loads.

Little things like the ability to replace existing column with an expression (instead of calculating new column, dropping the old one, and renaming) make life easier. And no annoying script errors because of a missed comma that cause aborting and reloading entire application.

Variables

Variables are ubiquitous in QlikView loading scripts and sometimes used up to the point where entire parts of script are replaced with a variable. The closest (although not exact!) analogue to variables in QlikView are project parameters in EasyMorph. Parameters are global. They don't change from transformation to transformation. Parameters can be used in transformations instead of file names and other properties, and also in expressions as constants.

Loops and iterations

Loading several files or processing a list of dates can be done using loops in QlikView, or iterations in EasyMorph. Although, the loops are imperative (as is the loading script syntax), while the iterations follow the functional programming style.

QVDs

No wonder QlikView can read/write QVD files. But so does EasyMorph. QlikSense QVDs are not supported yet (as of version 1.8) -- we're expecting Qlik to publish a respective API.

Calendars

Almost every QlikView / QlikSense application requires a calendar. If you're lucky to use Rob Wunderlich's QlikView Components then calendars might be easy for you. But for EasyMorph users it's even easier as it has built-in Calendar transformation that creates a calendar in a few clicks. See below an example of Calendar transformation, and its result.

Click to zoom

Mapping tables

Mapping table is a special temporary two-column table in QlikView. In EasyMorph any table can be a mapping table when it's used in Lookup transformation (equivalent to MAP USING... in QlikView). At this point (ver.1.8) in EasyMorph there is no analogy to ApplyMap function in QlikView.

Resident tables

In QlikView you can create new tables based on previously loaded, so called resident tables. In EasyMorph for the same purpose you can use derived tables. A derived table is like a dynamic copy (or view) of another table. It updates automatically if the original table changes. You can derive multiple tables from one table.

Derived tables in EasyMorph. Click to zoom.

Keep / Exists

Sometimes it's necessary to filter one table based on values in another table. In QlikView it can be done using either KEEP statement or WHERE clause with EXISTS() function. In EasyMorph the same can be achieved using Keep Matching or Keep Mismatching transformations.

Crosstable Load

Unpivot transformation in EasyMorph is the equivalent of CROSSTABLE LOAD in QlikView and can be used for transforming matrix tables into straight tables.

Command-line mode

Both QlikView and EasyMorph can be run in command line mode. Both applications allow defining variables (parameters) from command line. Here is EasyMorph's command-line syntax.

As you can see there are quite a few similarities. While in general QlikView loading script is more flexible (as you would expect from a programming language), designing transformations in EasyMorph is simpler, faster, and can be done by people without programming skills at all.

To learn more how EasyMorph works take a look at our illustrated PDF tutorials.

PS. One more similarity will appear in future releases -- the analogue of subroutines.

PPS. Totally forgot about resident/derived tables. Now it's 12 similarities :)

Data transformation in QlikView without the loading script

While I admire QlikView's genius associative in-memory engine I'm not a big fan of QlikView's loading script. If QlikView wasn't meant for departmental use, it would not have been a problem. But since QlikView is heavily oriented on business departments with limited IT supporting staff, the necessity to deal with transformation logic encoded in a proprietary script syntax is an obstacle because of several reasons:

1. It makes it harder to promote QlikView in organizations, since readability of QlikView script is no better than of Visual Basic macros in Excel spreadsheets or SQL.
2. Since business users usually can't write/read the scripts (they are not supposed to have programming skills), it inflates development and maintenance costs because even minor changes to the logic have to be made by developers and developers have to answer all questions about how this or that is calculated.
3. Total lack of metadata lineage. To make it even worse, the dollar-sign expansions make it impossible to parse scripts and rebuild abstract syntax tree in external applications.

With QlikSense the situation is even worse, because QlikSense is targeted as a self-service data discovery platform, but the need in loading script makes it dead on arrival.

I believe that data transformation can be done without programming in the vast majority of cases. That's why I designed EasyMorph that can be integrated with both QlikView and QlikSense.

Today we've published an example of QlikView Integration. It shows how data transformation logic can be moved from a QlikView loading script into an EasyMorph project, where it can be visually designed, modified and viewed. While it doesn't replace the loading script completely (yet) it demonstrates how core transformation logic can be made accessible for non-technical users, who can now explore and modify the logic in a visual way, without dealing with the script syntax.

Here is its transformation logic. Input and output transformations are marked on the screenshot.

The logic loads a text file (one file per state), maps state names from another file, does a few calculations and aggregations, and exports the result into a QVD file. Here is the loading script with EasyMorph project integration (comments removed, download the example for the full version):

States:
LOAD * Inline [
State
California
New York
Texas
];

LET pathEasyMorph = GetRegistryString('HKEY_CURRENT_USER\Software\EasyMorph', 'ApplicationPath');

let vCount = NoOfRows('States');

FOR I = 0 to vCount - 1

    LET State = Peek('State', I, 'States');
   
    //create input file for the EasyMorph project
    EXECUTE cmd /C copy /Y "Inc5000 $(State).csv" input.csv;
   
    EXECUTE $(pathEasyMorph) /c /run "Inc5000 for QlikView.morph";   

    //read the QVD generated by the EasyMorph project
    LOAD
        *,
        '$(State)' as State
    FROM output.qvd (qvd);

NEXT I

Since EasyMorph can't process a list of files yet (this feature will be added in future releases) a simple workaround is used -- a cycle, that iterates through a list of file names, and renaming.

For convenience the EasyMorph project can be opened right from desktop QlikView (in example there is a button for this).

For server execution, you might need to specify the path to EasyMorph executable explicitly, since EasyMorph is installed under a user account and may not be available in the registry under QlikView Server's account.

While it's not shown in the example, you can also add a link to the HTML documentation automatically generated by EasyMorph when it runs a project with /doc parameter from command line (see help on this). In this case users will be able to see the documentation right in the web-browser, next to the application. Or, right in the application, using an extension for web page embedding.

Links:
EasyMorph website
QlikView Integration example

PS. Technical details are copied from EasyMorph Blog.