Sometimes we need to utilise dynamic dimension security in an Analysis Services solution and we also need to display dynamically the allowed members in a PerformancePoint filter.

In case our hierarchy is multi-level and in case we can expect to have security on multiple levels, PerformancePoint will display the full path upwards to the root member of the dimension. So, in the case where in a Business Unit hierarchy we have access to some third level members, in PerformancePoint we will see all their parents. In example if our hierarchy looks like this:

All Business Units
 - Europe
   - UK
   - France
   - Bulgaria
 - North America
   - USA
   - Canada

and we give someone access to France, they will in fact see:

All Business Units
- Europe
   - France

Indeed, when they select All Business Units or Europe they will still see only France data but this may be confusing. To eliminate the top levels we need to change the way we create our PerformancePoint filter.

To achieve this, first we need to create a MDX Query filter. For more information about PerformancePoint filters and dynamic dimension security you can read the following brilliant post on Nick Barclay's blog: PPS Data Connection Security with CustomData. Nick explains how to set up PerformancePoint to work with Analysis Services dynamic dimension security and related topics. I will now concentrate on actually filtering the members of the already set-up MDX Query filter.

Instead of requesting all dimension members with a simple statement like:

DESCENDANTS([Business].[Business Hierarchy].Members,, SELF_AND_AFTER)

we can write some fairly simple MDX which means:

Get me the descendants of all dimension members whose ascendants (excluding themselves) have no more than one child.

And the MDX code is:

DESCENDANTS(
 FILTER([Business].[Business Hierarchy].Members AS a,
 ((FILTER(ASCENDANTS(a.CurrentMember) AS a_asc,
 a_asc.CurrentMember.CHILDREN.Count > 1).Count = 1)
 And
 a.CurrentMember.Children.Count > 1)
 Or
 ((FILTER(ASCENDANTS(a.CurrentMember) AS a_asc,
 a_asc.CurrentMember.CHILDREN.Count > 1).Count = 0)
 And
 a.CurrentMember.Children.Count = 0))
,,SELF_AND_AFTER)

The result will be France being displayed as the only available member in the PerformancePoint drop-down. Also, if we had two different allowed members, the code above would show us the top common parent of the two. Therefore, if we had France and Canada as the allowed set of dimension members, the drop-down would consist of the following hierarchy:

All Business Units
 Europe
   France
 North America
   Canada

thus satisfying our requirements.