Key Performance Indicator : A Key Performance Indicator (KPI) is a visual cue that communicates the progress made toward a goal. The important properties of a KPI are Current Value, Goal and Status.
Some examples of KPI are % of escalated Incidents, % of incidents which breached SLA, % of service requests through portal.
The SM data warehouse in System Center 2012 provides users an option to define KPIs on the cube using a simple MP element in the cube definition. Here is the XSD content view of a KPI element in the Management Pack schema.
- Caption : This is a description which will appear as tooltip.
- Value : This is an actual MDX expression which could either simply refer to an existing Measure (for example [Measures].[IncidentswithinSLA] or it could contain a more complicated MDX statement. If you do include an MDX expression, remember to encode for XML (i.e. use > rather than the > operator directly).
- Goal: An MDX numeric expression or a calculation that returns the target value of the KPI. This can lso be a decimal which represents the target. If you are targeting 80% of Incidents within SLA, this value would be .8.
- GreenThreshold, YellowThreshold & Direction: These set the inclusive boundaries for determining what color your status graphic should be. For example, let’s assume your GreenThreshold is .8, your YellowThreshold is .7 and your Direction is Up. This would mean that any value equal to or above .8 would be green, anything equal to or greater than .7 (but less than .8) would be yellow and anything less than .7 would be red. However, sometimes a lower number is better.For example, labor minutes per incident or hours of downtime. To account for this, set your Direction element to Down and adjust your GreenThreshold & YellowThreshold values accordingly.
- StatusGraphic: The standard list of SSAS KPI status graphics. These are the actual named icon sets which govern how the KPI status visually looks in Excel/Sharepoint etc.
The supported values are Shapes, TrafficLight, RoadSigns, Gauge, ReversedGauge, Thermometer, Cylinder, Faces, VarianceArrow.
Note: Excel won’t support all the status graphics values but will default if you provide a value it doesn’t support.
Example:
Let’s build a KPI “% of escalated Incidents” using Service Manager data warehouse 2012.
For the KPI “% of escalated Incidents”, let’s define the goal as less than .1 and Green Threshold as .3, Yellow Threshold as .4 . The direction need to be down as we want the escalated incidents % to be as low as possible.
1) Identify the Measure on which the KPI can be defined and provide it as the value. In this scenario Escalated Incidents\Total incidents count should provide the ratio
So here is the KPI element
<KPIS>
<KPI ID="EscalationsKPI">
<Caption> Incident Escalations </Caption>
<Value>IIF([Measures].[Incidents Escalated]>0,([Measures].[Incidents Escalated]/[Measures].[IncidentDimCount]),0)</Value>
<Goal>.1</Goal>
<GreenThreshold>.3</GreenThreshold>
<YellowThreshold>0.4</YellowThreshold>
<Direction>Down</Direction>
<StatusGraphic>Faces</StatusGraphic>
</KPI>
</KPIS>
2) Import the sealed Cube MP with the KPI and run the MPSync job so that the KPI is deployed to the Analysis Services Database.
3) Process the cube from the SM console or Cmdlet.
4) Now open the cube in the Excel from SM Console. The EscalationsKPI appears in the Excel field list as indicated below.
5) Check the Value, Goal, and Status fields, that should indicate the overall status, goal and the current values. The following should be the view then:
6) Now slice the KPI based on the source of the Incident. Check the IncidentSourceValue from IncidentDim_Incidentsource cube dimension and it should provide the following view. Also select the incident count:
7) Now slice by classification by checking the IncidentClassificationValue from IncidentDim_IncidentClassificationValue:
I have attached the cube MP for your reference.