Geeks With Blogs
James Rogers BI from the trenches...

In my earlier post NON EMPTY Filtering on PerformancePoint Scorecard, I mentioned that you may be able filter scorecards and/or reports by dimensions that are not the same as the filter by using a cross join or other MDX querying technique.  This is true if the other dimension is in the same cube as the query or member selection used by the filter.  Consider the following:

Cube:                 Sales

Dimensions:     Organizational Hierarchy (lowest level is Site)

                            Site (this contains attributes that only apply to the site)


Filter: Org (Member Selection of Organizational Hierarchy)


Report: Sales by State/Province


Approach 1:  We could put the Organizational Hierarchy dimension into the Sales by State/Province and pass the Org filter value directly into that.  However, this may limit the flexibility of the report and its applicability to other dashboards.  Additionally, if we were to use the report without passing a value into it we may complicate the resulting query and unnecessarily degrade the report’s query performance.


Approach 2: We apply a MDX function to the filter when it is mapped to the report filter to only show States/Provinces that apply to the Organizational Hierarchy (which are often geographically defined).  The resulting Filter Formula would look something like the following:




A key cube design reminder here – make sure your cubes have a high performing default measure if you do not specify a specific measure in the NONEMPTY filter expression like the following:


NONEMPTY(NONEMPTY([Site].[Geography].[State].members,<<SourceValue>>),[Measures].[Site Count])


 I like to use something that only returns one row in the base fact table per granular dimension value (i.e. one row per site).   I generally set aside a measure group just to support this type of filtering or I set up a cube just to support PerformancePoint filters (to be discussed in a later post).  This requires additional education on the part of publisher so they understand that the measure group or cube has very little analytical value.  At a bare minimum our aggregation design needs to support the dimensions involved in the filter expression unless we have very small cubes.

One thing that would be nice would be if we could filter filters (no that is not a typo). Ideally, if we had a date filter, we would want to filter a MDX query or Member Selection filter (such as org) to only show members that have values for the applied date filter.  This has value in industry Same-Store or Comparables dashboards.


Posted on Tuesday, March 25, 2008 9:09 PM PerformancePoint | Back to top

Comments on this post: Filtering PerformancePoint Reports and Scorecards by Dimensions Different Than the Filter

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © James Rogers | Powered by: