Creating Complex KPIs with Calculated Metrics

  • Article

Many times, the metrics that make up your key performance indicators are not simple values from a data source. In PerformancePoint Server 2007 you could create two kinds of KPI metrics: Simple single value metrics from any supported data source or Complex multiple value metrics from a single Analysis Services data source using MDX.

When a user needed a calculation as simple as ‘Sales per Square Foot,’ both the sales value and the square footage values had to be available in an Analysis Services cube, and the calculation had to be written in complex MDX statements.

Luckily this is no longer the case in PerformancePoint Services for SharePoint 2010. This release we have introduced a new feature called “Calculated Metrics.” Calculated Metrics are powerful for several reasons:

  • Can use multiple data sources in the same formula.
  • Can use different data source types in the same formula.
  • Is expressed in simple Excel-like formulas.
  • Supports logic flow.

The possibilities are endless.

image

I quick view at the Calculated Metric dialog shows how simple the process really is. The grid at the top maps data sources to common names you can use in your formula. The Formula box contains the formula you have written and even uses simple code highlighting techniques to help you quickly understand what’s being calculated.

The formulas support all simple math operations as well as functions like MIN, MAX, SUM, AVG and ABS. You can create IF statements in your formulas to create logical operations and you can even use NULL to test for data or intentionally return a null result.

This topic gets much deeper and we will post examples of complex usage in the coming weeks. Until then, when you get your hands on the beta in November, use this feature and provide feedback. Enjoy!

Jason Burns, Program Manager
PerformancePoint Services