Row Level Security on hierarchy column

Question

Hi,our customer has a dimension table with a parent-child architecture he wants to use for RLS. The table looks something like this:cost_center, name, parent_cost_center1, management, NULL2, teamlead_1, 13, teamlead_2, 14, worker_1, 2In Power BI I would use the PATH() function to create the hierarchy as an additional column for every row (e.g. “1 | 2” for teamlead_1)  and then use the PATHCONTAINS() function in the RLS settings to check, if the current user’s cost_center number is in the hierarchy column.I have not figured out how to implement this is TimeXtender. Does anybody have some experience on this topic?

f_kefer · Accepted Answer

Hi@rory.smith,thanks for your advice but I solved this task a little bit different.My solution was to flat the parent-child table rowwise, so the transformed table looks something like this:name, cost_centermanagement, 1management, 2management, 3management, 4teamlead_1, 2teamlead_1, 4…Then I used this table to set up a Dynamic Row-Level Security on the cost_center column of the table I want to filter.I wasn’t aware that TimeXtender supports this kind of flattened table for dynamic RLS until Itried it.

rory.smith · Answer

Hi @f_kefer,how to approach this depends on what your exact needs are. If you know the exact number of levels in your hierarchy or have a low upper-limit on the number of levels you can usually get things set up quite easily:https://stackoverflow.com/questions/68865866/translate-a-table-with-parent-child-hierarchy-into-a-table-with-parent-name-inorhttps://stackoverflow.com/questions/45661314/how-to-create-query-from-parent-child-hierarchy-table/45661741?noredirect=1#comment121704882_45661741should give you some ideas.I tend to deal with situations where you do not know the depth of the hierarchy and it may change over time, so I tend to use recursive CTE's. Those get into “it depends” territory pretty quickly for exact syntax, but this is an example for inspiration:https://stackoverflow.com/questions/63897337/flattening-of-hierarchy-in-sql-server-dynamicallyThere are some nice tricks with CROSS APPLY, PIVOT/UNPIVOT and FOR XML syntax possible that allow you to derive the Level X column from a “path” you build up out of the ancestry of an item. Whichever you choose will depend on sizes and complexity, i.e. sometimes you get ranges of elements pointing to parents or you have explicit ordering between siblings.

Row Level Security on hierarchy column

3 replies

Reply

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded