Dynamic Row Level Security in a historical environment

[don_writer]

There have been a number of questions about Dynamic Row Level Secutiry and I just could not figure out how to make it work in my environment.

Apologies this project is too big to replicate:

Here is the structure:

Active Employee table

Data_as_of (last day of each month for 7 years)

Employee ID

Division

DivisionCode (Date and Division combined)

Headcount (generally always a 1)

Status (Active, Contractor, leave of Absense, etc)

Email

Terminations table

Data_as_of (last day of each month for 7 years)

Employee ID

Division

DivisionCode (Date and Division combined)

Termination Date

Hires table

Data_as_of (last day of each month for 7 years)

Requisition ID

Position ID

Start Date

Division

DivisionCode (Date and Division combined)

Organizational table

Data_as_of (last day of each month for 7 years)

Division

DivisionCode (Date and Division combined)

Company

Division name

DivisionCode column is generally linked to every table

Standard Date Dimension table

Date column is generally linked to Data_as_of in each table

I have four companies

Each company has 6-22 Divisions

I have a CEO of all companies

I have a Company Lead for each company

I have a Division Head for each division

I have two report pages that includes YTD (end of month) and Quarterly numbers:

1. I have an all Companies page that shows standard HR metrics (headcount, turnover, hires, etc) across all four companies.
2. I have a My Group page that displays the same visuals only by Division instead of by Company. (usually in the form of a Legend)

IMPORTANT: everyone sees everything in the All Companies page.

• When the CEO looks at the My Group I want them to see everything
• When the Company Lead looks at the Dashboard I want them to see just their company and all its Divisions
• When a Division Head logs in I want them to see just their Division.
  What they are is largely manual - there is no field that captures this.

The complication comes in because we're displaying 4 years worth of data. And positions have changed but I want everyone to be able to see all the historical metrics. For instance our Divisional Head of HR is new to the position but I want them to be able to see the headcount of his Division back to 2016. AND I want to filter the My Company page but not the All Companies page.

I get that there is some manual effort and I will likely have to make a Managers Table, but do I have to do one for every month for the past four years of the entire structure?

Please help me create a model and roles that make sense.

Thank you all so much for your time and effort.

Best regards,

~Don

[dax]

Hi don_writer,

If possible, could you please inform me more detailed information (such as your sample data and your expected output)? You could use simple data to replace your real data and upload it.

In addition, if you want to use RLS, I think you need to create an user table, then create relationship with your other table, and set RLS  basedon user table.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[don_writer]

Expected output:

• When the CEO looks at the My Group I want them to see everything
• When the Company Lead looks at the Dashboard I want them to see just their company and all its Divisions
• When a Division Head logs in I want them to see just their Division.
  What they are is largely manual - there is no field that captures this.