Skip to main content

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_center
1, management, NULL
2, teamlead_1, 1
3, teamlead_2, 1
4, worker_1, 2

In 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?

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-in or https://stackoverflow.com/questions/45661314/how-to-create-query-from-parent-child-hierarchy-table/45661741?noredirect=1#comment121704882_45661741 should 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-dynamically

There 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. 

 


Hi @f_kefer 

Did Rory’s answer above, help answer your question? If so, please help us by marking a best answer above. If you have follow up questions please let us know


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_center
management, 1
management, 2
management, 3
management, 4
teamlead_1, 2
teamlead_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 I tried it.


Reply