Hi Guys,

I have developed security cube for dynamic security in SQL Server 2005 (June CTP).

Dimension are
Users (ID and name of users)
Organization - It is parent child dimension. It includes 3 levels: Enterprise, Teams and Agent.

SecurityFact (UserID, OrgID, SFAccess).

I have given only Agent12 in SecurityFact table. So it should show only Ent2, Team7 and Agent12 (As Team7 and Ent2 are its parent). But it shows all the agents in Team7 instead of just Agent12. Why so??

Role includes following AllowedSet
FILTER([ORGANIZATION].[HIR].members,( STRTOTUPLE( MID( USERNAME, INSTR(1, USERNAME, "\") + 1, 128 ) ), [SFAccess] ) = 1)

If dimension is not parent-child nature dimension, then above works nicely. Is there anything that we should keep in mind while dealing with parent-child dimension in customized dimension security.

Thanks