Building Hierarchy and Bridge Table for Section Access

The below document can be helpful and describes on how to implement section access on hierarchy data.

Requirement:

One of our project assignment, we have to facilitate the reporting manger need to view how their Sales Reps are making product sales and make possible discussions on price trends in relation to Guidance from BI analytics team.

Managers should be able view the data authorized to him and all his sales Reps. Also, the first level managers should be able to view information authorized and his reporting managers and their sales Reps.

We have received the below information from Source team, which associates Manager to Sales Reps. and assigned territory.

Sales Manager -> Sales Reps -> Territory Assigned

Manager Code

Manager Name

Reps. Code or Sub Ordinate Code

Rep. name

Territory Assigned

Mgr001

Michael John

Sub001

Robert Clive

Ter0001

Mgr001

Michael John

Sub002

Travis

Ter0002

Sub001

Robert Clive

Reps0001

Oliver John

Ter0001

Sub002

Travis

Reps0002

Petar

Ter0002

We have Sale information extracted from Source system and linked to a customer and associated territory details.

Sales -> Customer -> Territory

Sales No

Sale Price

Recommneded Price

Sale Amount

Deal Customer

Territory

9304223

456,000

500,000

457,000

Cust001

Ter0001

9304224

4,500

50,000

5,500

Cust002

Ter0001

9304225

65,000

76,000

66,000

Cust003

Ter0001

9304226

24,000

25,000

25,000

Cust004

Ter0002

Solution:

Basically We need to build a Bridge table to link Sales to Respective Manager/Rep. Group key and ensure that the authorized people can view the sales details.

Sales Id -> Territory ->Manager/Rep. Group Key

Sales No

Territory

Sales Group Key

9304223

Ter0001

Sub001\Mgr001

9304223

Ter0001

Reps0001\Sub001

9304224

Ter0001

Sub001\Mgr001

9304224

Ter0001

Reps0001\Sub001

9304225

Ter0001

Sub001\Mgr001

9304225

Ter0001

Reps0001\Sub001

9304226

Ter0002

Sub002\Mgr001

9304226

Ter0002

Reps0002\Sub002

Hierarchy table has been used within Qlikview Section Access to ensure that only Authorized people can login to the QV dashboard and able view the authorized sales details based on his reporting structure.

In the above example, Mgr01 is the first level manager, can be able to view all the sales details associated to his reporting manager and their sales Reps.

Manager <- Sub Ordinate01 <- Sub Ordinate02…

We have implemented the below Sales Hierarchy table which links L0 to Ln reporting structure and used within Section Access script for Access control as shown below:

Manager L0

L0 Name

L1 Code

L1 Name

L2 Code

L2 Name

L3 Code

L3 Name

Territory

Deal Group Key

Mgr0001

Michael John

Sub001

Robert Clive

Ter0001

Sub001\Mgr001

Mgr0001

Michael John

Sub001

Robert Clive

Reps0001

Oliver John

Ter0001

Reps0001\Sub001

Sub001

Robert Clive

Reps0001

Oliver John

Ter0001

Reps0001\Sub001

Mgr0001

Michael John

Sub002

Travis

Ter0002

Sub002\Mgr001

Mgr0001

Michael John

Sub002

Travis

Reps0002

Petar

Ter0002

Reps0002\Sub002

Sub002

Travis

Reps0002

Petar

Ter0002

Reps0002\Sub002

The above Sales hierarchy table facilitates the Mgr0001 login to the system & view all his subordinates and associated sales details aggregated.

Similarly, the managers Sub001 and Sub002 can be limited to see only authorized and reporting person sales details.

The above bridge table and hierarchy table facilitates the manager Michael John could be able to see all subordinates who has been reporting to him and their performance.

Analysis Reports using Qlikview:

Another interesting feature of Qlikview product is as it internally handles 1:M relationship between entities Sales and Bridge constructed using Associate data model. The aggregation eliminates the duplicate values while displaying sales total across all the L0 mangers. In addition, the Section access can be implemented using L0 code as employee id and L0 Name as employee name using Sales Hierarchy L0 to Ln table. It ensures that Manager can be able to view only authorized sales information.