Parent-child dimensions are a very useful feature of Analysis Services to model recursive hierarchies, such as an organizational hierarchy. Recently, I had to apply dimension data security to a parent-child dimension. The allowed set expression used the factless fact table approach (see my Protect UDM with Dimension Data Security article for more details), as follows (translated to the Adventure Works schema):
IIF
— check if the user has full access
(StrToMember(“[User].[Login].&[” + Username +”]”).Properties(“FullDimensionAccess”,TYPED)=True , [Employee].[Employees].[All],
Generate
(
Exists ([Employee].[ Employee].[ Employee].Members, StrToMember(“[User].[Login].&[” + Username + “]”), “Bridge Employee Security”)
, {LinkMember (Employee.Employee.CurrentMember, Employee.Employees)}
)
)
To avoid loading the factless fact table with all dimension members for users with full dimension access, the expression checks if the user is flagged as such. Since Analysis Services doesn’t provide a way to ignore dimension data security from the allowed/denied set, the expression returns the hierarchy top member [Employee].[Employees].[All] and this is where the issue is. Returning the All member causes the server to scan recursively the entire hierarchy and perform AutoExists against all other attributes within the P-C dimensions. Unlike regular dimensions where the server can infer attribute relationships quickly, a P-C hierarchy requires dynamically checking each member. This can slow the user connect time significantly. In my case, the P-C dimension had about 300,000 members and the connect time for users with full dimension access exceeded two minutes. Needless to say, users were not happy.
There are several workarounds (none of them ideal):
- Move users with full dimension access to a separate role to avoid the IIF check. This wasn’t an option in our case.
- Remove as many attributes as possible from the P-C dimension to reduce the number of attributes for the AutoExists check.
- Add a new hidden P-C dimension that has the dimension key and P-C attribute. Apply dimension data security on this dimension and leave the original P-C dimension intact.
- Refactor the P-C dimension and move its leaf members to a new dimension in order to reduce the number of members in the P-C dimension.
I logged a new wish on connect.microsoft.com and requested the ability to ignore dimension data security. Please vote so we can have a more elegant way to address such requirements without refactoring the cube design.
