Home Forums Data Warehousing Analysis Services Dynamic Security in a denormalized Parent-Child dimension Table RE: Dynamic Security in a denormalized Parent-Child dimension Table

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