KPIs (key performance indicators) and RAG (red/amber/green) are two acronyms that lie at the heart of BI (Business Intelligence). At a glance, they show if things (such as sales, profits, repeat customers, quality control rejects, financial balances, etc.) are good or indifferent or bad. They are more immediately understood than either figures or charts.
A KPI is a measure of how well business is doing. The visual indicator it uses is often called RAG – often in the form of green, amber, or red traffic lights. Conventionally, a KPI is composed of four settings – value or actual, goal or target, status, and trend.
KPI Composition
The value/actual is the current state of the metric of interest (in our example we will use the number of customers).
The goal/target is how we would like things to be.
The status is how well the value compares to the goal. The trend can be used to show how either the value or, less often, the status changes over time.
The all-important part of the indicator is the status – the status is the setting that is usually shown as RAG traffic lights.
KPI Example
In the following table for the number of customers, you can see a simple example (with a static rather than a changing goal).
| Value/Actual | Goal/Target | Status | Trend | |
| 2011 | 900 | 1000 | -1 | NA |
| 2012 | 1100 | 1000 | 1 | Up |
| 2013 | 1000 | 1000 | 0 | Down |
Although this is a simple example, a number of points are worth making. The number of customers for each year is recorded in the Value/Actual column. The term Value is used in SSAS (SQL Server Analysis Services) multidimensional cubes, the term Actual in PPS (PerformancePoint Server) scorecards.
The desired number of customers for each year is shown in the Goal/Target column – Goal in SSAS and Target in PPS.
The Status column contains a number – a positive number (e.g. 1) shows that the value exceeds the goal, a negative number (e.g. -1) shows the value is less than the goal, and a zero shows the value is equal to the goal. These are typically shown visually as red, green, or amber traffic lights. The numbers used for the status can be more complex – for example a range from -100 to +100. It is also possible to have more than three status settings – for example, 0.5 might be used to indicate ‘quite good’ as opposed to 1 for ‘very good’. The rule used for the status here is known as ‘increasing is better’ – but you can opt for ‘decreasing is better’ or ‘closer to goal is better’ (frequently used for manufacturing tolerance limits).
Designing and Displaying KPIs
Within the whole Microsoft Business Intelligence stack, there are a number of places where you can design and display KPIs with their associated RAG graphics. You can design KPIs in PPS Dashboard Designer (part of SharePoint 2013 Enterprise), in SSRS (SQL Server Reporting Services), in Excel, or in SSAS cubes (both multidimensional and tabular). At present, Power View does not directly support KPIs. You can display the KPIs in a PPS dashboard in SharePoint, in an SSRS report, or in an Excel workbook. So many options?
Possibly the most powerful option is to base a PPS KPI on a KPI defined in an SSAS multidimensional cube. Many readers will not have PPS (part of SharePoint Enterprise) to hand, so we will concentrate on using Excel for the display and SSAS for the design. It is recommended that you centralise your KPIs in an SSAS cube (multidimensional or tabular – as of this writing in February 2014, multidimensional KPIs have much more functionality than tabular KPIs, though this may change as Microsoft extend tabular in the future). Centralising a KPI means that it is reusable by any client – it means you don’t have to recreate them on an ad-hoc basis. To follow this example, you will need the SSAS Adventure Works multidimensional cube (download from codeplex.com) and Excel 2013 (or Excel 2010).
Designing a KPI – Example
First of all, let’s take a look at a KPI in the Adventure Works cube. Start SSDT (SQL Server Data Tools) – this used to known as BIDS (Business Intelligence Development Studio), BIDS is fine if you don’t have SSDT yet. Click File, Open, Analysis Services Database… and open the Adventure Works multidimensional database. In the Solution Explorer window double-click the Adventure Works cube to open Cube Designer and navigate to the KPIs tab.
Select the Growth in Customer Base entry in the KPI Organizer pane. You will see the definitions for Value, Goal, Status, and Trend. The Value Expression is:
[Measures].[Growth in Customer Base]
This measure Growth in Customer Base, in itself, is a Calculated Measure which you can examine on the Calculations tab:
([Date].[Fiscal].CurrentMember, [Measures].[Customer Count] - [Date].[Fiscal].PrevMember, [Measures].[Customer Count]) / [Date].[Fiscal].PrevMember, [Measures].[Customer Count]
This MDX is working out the difference between the customer count for the current Fiscal period (whether year or quarter or month etc.) and the previous fiscal period and dividing by the previous figure. The result, when formatted, gives the percentage growth in customers from one period to another.
Now, back on the KPIs tab, take a look at the Goal. The Goal Expression is:
Case When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Year] Then .30 When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Semester] Then .15 When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Fiscal Quarter] Then .075 When [Date].[Fiscal].CurrentMember.Level Is [Date].[Fiscal].[Month] Then .025 Else "NA" End
So, for example, we are hoping for a 7.5% quarterly increase in number of customers and a 2.5% increase month-on-month. Finally, we’ll show the MDX for the all-important Status (for reasons of space, we will not cover the Trend setting which uses the MDX ParallelPeriod function). Here’s the Status Expression:
Case
When KpiValue("Growth in Customer Base") >= KpiGoal("Growth in Customer Base")
Then 1
When KpiValue("Growth in Customer Base") >= KpiGoal("Growth in Customer Base") * .90 And KpiValue("Growth in Customer Base") < KpiGoal ("Growth in Customer Base")
Then 0
Else -1
End
This MDX means, for example, that if the Value is 90% or more of the Goal, but less than the Goal, then the Status is 0 (or an amber light).
Displaying a KPI – Example
Now to put all of this into Excel (please note, the Status Indicator you can see in the KPIs tab of the cube design has no effect on Excel). In Excel, click on the Data tab and choose From Other Sources, From Analysis Services, and bring in the Adventure Works cube as a Pivot Table Report. In PivotTable Fields, add Date.Fiscal (under the Date dimension Fiscal folder) to Rows and Growth in Customer Base (under the Internet Sales measure group) to Values.
Now add the KPI – turn on the check boxes next to Value, Goal, and Status for the Growth in Customer Base (under KPIs, Customer Perspective, Expand Customer Base). In the pivot table itself, right-click on any number for Growth in Customer Base Goal and choose Number Format … (not Format Cells …) then Percentage.
Click on the Row Labels report filter and turn off FY 2002, FY 2005, FY 2007 leaving only FY 2003 and FY 2004 (these are the only two years for which there is both full data and a previous year to compare to – please note that the years may be different depending upon the version of the Adventure works cube that you have). Then turn off grand totals for both rows and columns – PivotTable Tools, Design, Grand Totals. Finally, expand FY 2004 then H2 FY 2004. The final result is shown below:
In Conclusion
The whole point of KPIs and RAG is to highlight good, bad, and indifferent results. Here, it’s immediately apparent that there is a problem in Q3 FY 2004. You need to expand H1 FY 2004 to compare to the previous quarter – the indicator is red and the actual growth in customer numbers was 2.89% against a target of 7.50%. The situation improved dramatically in Q4 FY 2004 and FY 2004 as a whole was outstanding – a massive 440.41% growth compared to FY 2003!
