Dynamic Security in a denormalized Parent-Child dimension Table
-
Hi guys, I need your priceless help again:
I have a parent child relationship in a table with a fixed depth, let´s say Region-->Area-->Country
I denormalized the table to have something like this
Then, to implement dynamic security, I think in a bridge table with the userId and the CountryId, then with a measure group and a measure which count the combination of user/country I can proof the security using the non empty function.
My question is how can I also set security for the levels above the leaf members, let´s say, I want to assign an user to the Area level or Region Level. I don't know exactly which key could I include in the bridge table.
I may want to keep the id´s of the original table in the different levels.
Any comment will be appreciated.
Kind Regards,
Paul Hernández -
I haven't ever had the need to implement dynamic and custom security for a cube, but in my opinion you would have to do it all at the lowest level.
You would most likely need a process that populates a bridge table of some kind which contains the combination of country and user id's. If you also want to assign permissions on a high level of granularity (like area), it really depends on what process you are going to use for that assignment. It may require additional bridge tables for the relationship between country and area/region...but it depends on exactly how you want to implement that.
Comment though: In SSAS, you can assign permissions on any attribute of a dimension. If you know that regions/countries will be limited and you are most likely going to assign those permissions on an area/region level, it may make more sense to build the relevant hierarchies in SSAS and manage the permissions there. It wouldn't be dynamic at the lowest level, but your table solution wouldn't be either as you would still need some process to populate the bridge table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply