Have you ever been asked to add a new KPI to a scorecard, only to find that there is not a corresponding calculation or measure in the cube? With PerformancePoint 2010 KPIs for SharePoint 2010 scorecards, custom MDX expressions can be created as actual and target values without requiring modifications to be made to the source Analysis Services cube.
For this example, let’s consider the following requirements for a PerformancePoint scorecard using healthcare data (de-personalized demo data):
- A KPI for Average Length of Stay (Avg LOS) with a target value that is the previous period. The calculation exists in the cube.
- A KPI for Total Visits compared to Median Total Visits for all hospitals as a target value. The calculation for Median Visits by Hospital does not exist in the cube.
If the Median Total Visits target metric cannot be built into the cube, it will require custom MDX to be written into a PerformancePoint KPI for a scorecard. The process is much simpler than you’d think. When building the KPI in PerformancePoint Dashboard Designer, there is an option under “Data Mappings” for Actual and Target values that reads “Use MDX Tuple Formula.” Here is where the MDX expression for Median can be run against the cube:
As illustrated above, the MDX expression for Median Total Visits at all Hospitals is written as:
The total visits for a hospital selected using a filter can then be compared to the median value for all hospitals. Hospitals can then be tagged green or red based upon which half of the total distribution curve for visits that they fall into. In the following dashboard Eisenhower Hospital had 1653 visits in December, for all DRGs. The median for all hospitals was 510 visits, so Eisenhower Hospital is tagged green on the scorecard:
Changing the DRG filter from “All” to “Chest Pain” yields different values. Eisenhower Hospital had 37 “Chest Pain” visits that month, while the median for “Chest Pain” at all hospitals was 11 visits:
In short, KPIs used within scorecards for PerformancePoint dashboards have the flexibility to add specialized MDX expressions. Specialized requirements within an organization can be met without adding additional calculated measures to an Analysis Services cube.