Tim

Tim Kent's Blog

Dynamic Dimension members on a PerformancePoint KPI

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:

image

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.

image

Comments (4) -

  • NickBarclay

    3/5/2009 6:05:28 PM | Reply

    Hi Tim,

    Adding dimension members derived from a set (of any kind) within the scorecard element will be static, as you have written. It has always been that way. New features within SSAS cannot not change the layout of those members when added directly to a scorecard within DashboardDesigner. I believe this is by design.

    But what about applying the top N formula logic in a filter link formula? Your scorecard remains simple i.e. no static dimension members on the axes. Instead the set is generated and applied to the scorecard axis at run time by the filter link formula.

    Here's the filter link formula MDX that I tested. The <> in this example is being passed in by a Date filter.

    TOPCOUNT(
        [Product].[Product Categories].[Subcategory].members,
        10,
        ([Measures].[Internet Sales Amount], <>
        )

    Cheers,
    Nick

  • Tim Kent

    3/5/2009 6:27:48 PM | Reply

    Hey Nick

    Thanks for the elegant solution.  I have updated the post to make note.

    Hope all is well with you Smile

    Tim

  • NickBarclay

    3/5/2009 8:12:45 PM | Reply

    No problem, glad to help Smile

    All is well over here. Cold, but well.

    Cheers,
    NB

  • Anonymous

    7/26/2010 4:43:43 PM | Reply

    Another question, or twist... imagine a single filter on the dashboard page...based on a simple date hierarchy.  Also - a top 'high-level' scorecard to display KPIs - filtered by the date...and a second scorecard that shows descendants at a level below the selected dimension element in the high-level scorecard...  essentially - two scorecards where one provides a navigation point to allowing of more detailed records lower in the hierarchy to prevent a huge expand/collapse scenario.  The problem is - I want to order the lower scorecard based on a KPI value...and have it filtered by the date.  I have created connections from the date to both parts - just normal with no formulas.  I have a connection from the upper scorecard to the lower with a formula:

    order(descendants(<>,  [Customer].[Customer Hierarchy].[Customer Name]),measures.[Savings Amount],desc)

    but this seems to ignore the date selection...or the order of evaluation is off...in effect, things are ordered, but across all time...not the selected date in the filter that should have been selected for the page...

    Thoughts?

    Thanks,
    Dean

Loading