Data Level Security for more than 1K regions belonging to one manager

Question

Hi Team,

I came across a scenario where one manager might be assigned for more than 1K cities. The combination of the cities can be different for each manager, so we cannot map cities to any regions and then map those regions to each managers like we do it in general data level security configuration. To explain this, let me put it this way,

Manager A : City 1, City 2, City 3,...., City 1500

Manager B: City 1, City 3, City 4, ....., City 2000

Manager C: City 10, City 15, City 40, ....., City 5000

Likewise, I need to implement a data level security in OBIEE 12c where every manager should be able to see the data for the assigned cities only. Whereas, if we use row-wise initialisation of a CITY variable, Oracle won't permit us more than 999 values with IN clause of a physical query.

Kindly let me know your suggestions.

Thanks in advance,

Sagar Tipe

Gianni Ceresa · Answer

Turn it into a join of your table with the mapping manager-city to the cities dimension, in that way you can set the security on the manager column and the join between the 2 tables will automatically filter the cities even if they are millions.