In business terminology, a Key Performance Indicator (KPI) is a quantifiable measurement for gauging business performance. For example, a retail business may use “Year over year revenue growth” as a KPI to gauge its performance. KPIs are typically shown as part of a dashboard or business scorecard to provide managers and analysts with at-a-glance picture of the state of business.
Technically KPIs may be implemented using any tool or technology. For example, you may be able to create a dashboard using Excel or SQL Server Reporting Services by retrieving the relevant data from a relational database or even entering the data manually into the spreadsheet and creating necessary calculations or formulas based on the data. However, this approach relies on the KPIs being created consistently by different users and developers, which may not be the case.
There are several advantages of using SQL Server Analysis Services to implement KPIs. First, it enables you to define a KPI once and use it in multiple dashboards and reports in multiple contexts. Second, it ensures that the users see the single version of the truth without you having to rely on the users and developers for the correct calculation.
Let us see how it would work using an example. You can create the “Internet Sales Growth” KPI using the following MDX expression in SSAS:
IIF(
SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],
ParallelPeriod([Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember)), [Measures].[Internet Sales Amount]
)=0,
"NA",
SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])/
SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],
ParallelPeriod([Date].[Fiscal].[Fiscal Year], 1, [Date].[Fiscal].CurrentMember)), [Measures].[Internet Sales Amount]
) - 1
)
The above expression takes advantage of the MDX function ParallelPeriod to get a member from a prior period in the same relative position as the current member (month, quarter, semester, etc.). For example, a parallel period for FY 2004 Q3 is FY 2003 Q3, which will be used to calculate the year over year sales growth.
Once you have created a calculated measure and a KPI based on this calculation, developers and end users can use it in any spreadsheet, report or dashboard in any context. SSAS calculates the KPI automatically as the users slice and dice the data.
For example, users can use this KPI in any of the following:
- report showing year over year sales growth by territory that displays years across the columns and sales territories down the rows
- dashboard showing querterly sales and sales growth over the same quarter in the previous year by product category
- Excel PivotTable showing top 10 customers by sales that also includes sales growth rates for those customers. When the user slices the data further by product, SSAS automatically calculates the sales growth by customer and product.
