Building Hierarchy and Bridge Table for Section Access

    Agenda:

    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.

    Sales Rep     -> Reporting Manager L0 ->Report Manager L1 -> Report Manager L2…L6

    Mgr

    Rep

    Mgr01

    Sdf01

    Sdf01

    Rep01

    Mgr01

    Sdf02

    Sdf02

    Rep02

     

    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.

     

    Section Access Model.png

     

     

    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.