Search code examples
ssasmdxolap

MDX Exclude results where a value exists in a hierarchy

Please excuse me if I get some of the terminology wrong, I am relatively new to MDX/OLAP.

I have a dimension for determining ethical walls. The dimension looks like this:

Ethical Wall (dimension)
   --> Matter ID (hierarchy)
   --> Walled (hierarchy)
   --> White Listed Initials (hierarchy)
   --> Black Listed Initials (hierarchy)

The [Walled] hierarchy contains either true or false, depending on if the matter has a wall applied.

The White and Black list hierarchies contain user initials of people who either have or do not have access to a matter, respectively. Note, matters are either white listed or black listed, it is never a combination of the two.

I have been able to account for the no wall scenario and white list scenario relatively easily, but am having lots of issues with the black list scenario. This is the where clause I have come up with so far:

({
    (
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[XXX]
        ,[Ethical Wall].[Black Listed Initials].&[]
    )
    ,(
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[]
        ,-{[Ethical Wall].[Black Listed Initials].&[XXX]}
    )
    ,(
        [Ethical Wall].[Walled].&[False]
        ,[Ethical Wall].[White Listed Initials].&[]
        ,[Ethical Wall].[Black Listed Initials].&[]
    )
})

Stripping down and representing it in a table the initial dataset, from which I am filtering down, looks something like this:

enter image description here

I want to select only the Ids to which the user with initials XXX has access. Applying the filter from above I get back all 3 Ids. The result set I am looking for is only Id 1 and 2. The filer from above matches like this:

enter image description here

I understand why my filter is retrieving all 3 Ids, but I don't understand how to revise the middle portion of the filter to exclude Ids properly. This is the offending portion of the filter:

    ,(
        [Ethical Wall].[Walled].&[True]
        ,[Ethical Wall].[White Listed Initials].&[]
        ,-{[Ethical Wall].[Black Listed Initials].&[XXX]}
    )

How can I revise my filter to match on that data set like this?

enter image description here

Solution

  • We have found the solution!!

    Utilizing sets:

    SET notwalled AS exists( 
       selectedmatters, 
       { 
         [Ethical Wall].[Walled].&[False] 
       } 
     ) 
  SET whitelisted AS exists( 
       selectedmatters, 
       { 
         [Ethical Wall].[White Listed Initials].&[XXX] 
       } 
     ) 
  SET blacklisted AS EXCEPT( 
       selectedmatters, 
       exists( 
         selectedmatters, 
         { 
           [Ethical Wall].[Black Listed Initials].&[XXX], 
           [Ethical Wall].[Black Listed Initials].&[] 
         } 
       ) 
     )

    And then union:

    UNION(notwalled, whitelisted, blacklisted)

    No more crying for me.