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.
Databricks Vs Synapse Spark Pools – What, When and Where?
Databricks or Synapse seems to be the question on everyone’s lips, whether its people asking
May
Power BI to Power AI – Part 2
This post is the second part of a blog series on the AI features of
Apr
Geospatial Sample architecture overview
The first blog ‘Part 1 – Introduction to Geospatial data’ gave an overview into geospatial
Apr
Data Lakehouses for Dummies
When we are thinking about data platforms, there are many different services and architectures that
Apr
Enable Smart Facility Management with Azure Digital Twins
Before I started writing this blog, I went to Google and searched for the keywords
Apr
Migrating On-Prem SSIS workload to Azure
Goal of this blog There can be scenario where organization wants to migrate there existing
Mar
Send B2B data with Azure Logic Apps and Enterprise Integration Pack
After creating an integration account that has partners and agreements, we are ready to create
Mar
Incremental Group is acquired by Telefónica Tech
Incremental’s acquisition by Telefónica Tech powers the next phase of growth for the digital technology
Mar