Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Hierarchies are very common in all database and business intelligence solutions. Often they are used for authorization purposes, i.e. the permissions to see data follows a hierarchy.

 

One example is an organizational hierarchy. Each manager should obviously have the right to see everything pertaining to their own department, including all its sub-departments. But they should not necessarily have the right to see other departments.

 

Organogram2.png

 

This means that different people will be allowed to see different parts of the organization. The authorization table may look like the following:

 

Authorization.png

 

In this case, Diane is allowed to see everything pertaining to the CEO and below; Steve is allowed to see the Product organization; and Debbie is allowed to see the Engineering organization only. Hence, this table needs to be matched against sub-trees in the above hierarchy.

 

Often the hierarchy is stored in an Adjacent Nodes table, and if so, the above problem is easy to solve: Just load the Adjacent nodes table using a HierarchyBelongsTo and name the ancestor field Tree. See the blog post Unbalanced, n-level hierarchies how this is done.

 

If you want to use Section Access, you need to load an upper case copy of Tree and call this new field PERMISSIONS. Finally, you need to load the authorization table. These two last steps can be done using the following script lines: (The TempTrees table is the table created by the HierarchyBelongsTo.)

 

Trees:

Load *,

     Upper(Tree) as PERMISSIONS

     Resident TempTrees;

Drop Table TempTrees;

 

Section Access;

Authorization:

Load ACCESS,

     NTNAME,

     Upper(Permissions) as PERMISSIONS

     From Organization;

Section Application;

 

 

When you have done this, you should have a data model that looks like the following:

 

Data model.png

 

 

The red table is in Section Access and is invisible in a real application. Should you want to use the publisher for the reduction, you can reduce right away on the Tree field, without loading the Section Access. In either case, this solution will effectively limit the permissions to only the sub-tree as defined in the authorization table.

 

But what if you have the hierarchy in a horizontal hierarchy? Then you cannot use the HierarchyBelongsTo.

 

Horizontal hierarchy.png

 

The solution is not very different from the above one. The only difference is that you need to create the bridging table manually, e.g. by using a loop:

 

Let vHierarchyDefinition = 'Board level,Director level,Department,Unit';

Let vNumberOfLevels = Len(KeepChar(vHierarchyDefinition,',')) + 1 ;

For vAncestorLevel = 1 to vNumberOfLevels

     Let vAncestor = Subfield(vHierarchyDefinition,',',vAncestorLevel);

     Trees:

     Load distinct

          Upper([$(vAncestor)]) as PERMISSIONS,

          DepartmentID

          Resident [Horizontal Hierarchy]
               Where Len([$(vAncestor)]) > 0;

Next vAncestorLevel

 

Having done this, you will have the following data model:

 

Data model2.png

 

Bottom line is that it is fairly straightforward to implement a hierarchical authorization scheme. See more about Hierarchies in the Tech Brief Hierarchies.

 

HIC

 

Further reading related to this topic:

A Primer on Section Access

Data Reduction Using Multiple Fields

Tips and tricks for section access in Qlik Sense (2.0+)

18 Comments
Partner
Partner

HIC,

Very Interesting and intelligent post

Thanks and Best Regards,

Kalyan

4,865 Views

Very Interesting ..

Thanks

Eduardo

0 Likes
4,865 Views
Contributor II

A different approach to a common issue.

Thanks Henric.

CB

0 Likes
4,865 Views
Partner
Partner

This is interesting, might use it in future development. Kudos!

0 Likes
4,865 Views
Partner
Partner

Excellent post , Thanks HIC ..

0 Likes
4,865 Views
Not applicable

Hey guys, 

I am given a task to calculate the frequency of calls across a territory. If the rep called a physician regarding the sale of the product 5 times, then frequency is 5 and HCP count is 1....I generated frequencies from 1 to 124 in my pivot table using a calculated dimension which is working fine. But my concern is :

My manager wants frequencies till 19 in order from 1..2..3..4...5..6.....19...

And from the frequency 21-124 as 20+.

I would be grateful if someone helps me with this.....Eager for the reply....

0 Likes
4,865 Views
Contributor III

Thanks,That's very useful for section access.

0 Likes
4,865 Views
Not applicable

Very use full

0 Likes
4,865 Views
Honored Contributor II

Hi HIC, Nice document.

Thank you for sharing.

4,865 Views
Contributor

Nice Post

0 Likes
4,865 Views
Not applicable

Is there an example application for this explanation anywhere?

4,865 Views
New Contributor III

hic‌ Great blog, i have just one question, how did we get permission column in authorization table, is that an input we need from user?

0 Likes
4,865 Views

Yes, you need that table, including "Permissions", as input when you create the document.

HIC

0 Likes
4,865 Views
Partner
Partner

Hi All,

Can any of you suggest me the way to implement the Section Access based on the Employee-Manager Table Logic ? When the Employee and Manager are there in the same Table then how can we implement the Section Access Logic ?

Thanks

0 Likes
4,865 Views
New Contributor

Thanks for the free education

0 Likes
4,684 Views
Partner
Partner
Can you share the example file please?

 

0 Likes
4,612 Views
New Contributor

Very helpful. Thanks.

0 Likes
3,777 Views
Partner
Partner

Thank you for the information

0 Likes
962 Views
Labels