Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Filtering Unneeded Dimension Members in PerformancePoint Filters

By Boyan Penev,

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
- 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:

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

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
North America

thus satisfying our requirements.

Total article views: 2274 | Views in the last 30 days: 1
Related Articles

PerformancePoint ‘Named Set’ Filter

PerformancePoint provides several options to create filters.  One of the options available is ‘Named...


Filtering Unneeded Dimension Members in PerformancePoint Filters

Comments posted to this topic are about the item [B]Filtering Unneeded Dimension Members in Performa...


Calculated member question

Filter by dimension members?


Dimension Calculated Member Problem

Dimension Calculated Member Problem


Configuring PerformancePoint Data Source for Time Intelligence

PerformancePoint provides functionality to map several time dimensions across multiple data sources ...


Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones