One of our customers had read Nick Barclay’s post on dynamic sets in SSAS 2008 and was hoping that this would mean that you could create KPI’s with dynamic dimension members. Well the answer is yes and no. It’s already possible to do this in Monitoring (or should I say PerformancePoint Services) using custom sets in the scorecard designer (more on this below). However in PPSM these sets are resolved at the point the Scorecard is rendered in the browser.
This is fine as long as the members of your sets are not affected by the filters applied to your scorecard (member.children for example) – unfortunately the set does not get re-queried when you change a filter. For instance if you were to create a set of your top 10 customers and drag that onto the rows of your scorecard, changing a time filter will not cause the KPI dimension members to change even if you’ve used time.currentmember in your set definition. So you may end up displaying the Top 10 customers for the current month which may be different to the Top 10 for the selected time period.
Update: Please see Nick Barclay’s comment below for a very neat solution to this issue using filter link formulas. (Wish I’d thought of that!)
Custom sets in the scorecard designer aren’t the most obvious thing to use nor are they very user-friendly. Your best bet is to use a tool like SQL Management Studio/Mosha MDX Studio to design a query that you know works then paste out the MDX for the set into the custom set formula editor. You access this by dragging the Custom item in the Details pane onto the relevant position on your scorecard:
Paste your set query in to the pop-up dialog. For Example:
TOPCOUNT(
[Product].[Product Model Categories].[Subcategory].members,
10,
[Measures].[Internet Sales Amount]
)
You can then use the update button on the edit tab of the ribbon to see the results. Unfortunately there’s no way to edit the custom set once you’ve added it. You have to delete the dimension members and then add a new custom set.
