Geeks With Blogs
James Rogers BI from the trenches...
 As I have moved forward with PerformancePoint I have made extensive use of queries for filter building. This approach has a couple of distinct advantages over the other options.
  1. Allows filters to be dynamic and pick up new data as it becomes available. One good example of this is a date filter. We use it to keep our filters as a dynamic set of n time periods from the current time period back (i.e. lastperiods(52,exists([Date].[Week].[Week].members,[Date].[Current Date].&[1]).item(0)))
  2. It allows the query to be tuned or tweaked to exclude empty members (there are some pitfalls here, noted later).
If you need to build a tree filter based on a hierarchy, a good query is as follows: HIERARCHIZE([Dimension].[Hierarchy].members).
A couple of things to keep in mind.
  1. Keep your query results below 500 members. PerformancePoint doesn't seem to handle more than that very well, although I have seen cases where we have gone above that.
  2. Be careful using MDX funtions that require cell inspection (such as NONEMPTY, filter, etc). This is problematic if you have a large cube/measure group. This can cause a scan of the measure group partitions to evaluate your query. NONEMPTY is especially bad about this and is compounded because you cannot chain filters (i.e. I can't pass a date filter into an item filter).
    1. Use an additional member filter in the query that causes the query to be more specific (i.e. nonempty items for the current week's sales)
      1. NONEMPTY(HIERARCHIZE([Dimension].[Hierarchy].members),(exists([Date].[Week].[Week].members,[Date].[Current Date].&[1]).item(0),[Sales Amount]))
    1. Use a very small measure group (at the query's required granularity) to satisfy the request. This requires cube changes and may not be an option. I don't recommend this as a first option because those measure groups would have little usefulness to other analysis being done in the cube as well as the down stream impact on the ETL process and cube processing.
Posted on Wednesday, November 4, 2009 1:14 PM PerformancePoint | Back to top

Comments on this post: PerformancePoint Filter Query suggestions

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

Copyright © James Rogers | Powered by: