top of page

Using your Organisational Hierarchy as the basis for report security

Updated: May 10, 2022

Using your organisational hierarchy to set security rules saves time & creates a more maintainable, scalable, secure and transparent solution.


Data Modelling Row Level Security

There are many case when you want each individual in your organisation to see only the numbers which are applicable to them and anyone reporting into them directly or indirectly. This can included very sensitive data, for instance:

  • Commission Calculations

  • Sales Numbers

  • Employee performance


Rather than sending each individual their own report, you can use the organisational hierarchy layered in conjunction with security over the same report. The report then uses that users logged in identity (i.e., Chris.Barber@company.com) in order to determine which numbers they see. There are many benefits for adopting this approach, namely:


  1. Time saving - Maintenance of one set of data

  2. Scale - The model is flexible as more individuals get added to the organisation

  3. Secure - Leverages the current Microsoft infrastructure to maintain security by using the username of the logged-in individual

  4. Transparent - One set of data means that those who have the highest level of security can see exactly what everyone sees in the same report

 

Example of what this looks like in practice


In the below organisation Almeda Lanman is at the top of the organisation so would see everyone's information. In contrast, Daren Bussard reports into Almeda so when logged into the report would only see:

  1. Himself

  2. Direct reports - Loyce Hughley, Hertha Zank & Tyesha Deboer

  3. Indirect reports - Junie Fredrick who reports into Hertha


Data Modelling Organisational Hierarchy

 

RLS / OLS Video - London Business Analytics Group


If you'd like to know how to achieve this solution, please see the below video - the accompanying files can be accessed via the downloads section of this website.







345 views0 comments

Comments


bottom of page