SSAS Key Performance Indicator (KPI) - Checking business success

Reposted from Amit Gupta's blog with the author's permission.

KPI (Key Performance indicators) in analysis services represents data to measure business success at very high level(mostly at organization level/regional level). 

Consider sales team want to know:

1) How are we doing in comparison of last year/month/week sales?
2) What is the trend of revenue in last one year?
3) Are we matching sales targets set by higher management?

These questions can be answered by KPIs in well structured and graphical manner. Here is the example of KPIs when you are using Excel 2007 as client tool to view Analysis Services data:

Key Performance Indicators in Excel 2007

Before we start on "How to implement SSAS KPIs". Let me explain my cube data. I have taken only two dimension Product and Time and only one measure group "Fact Internet Sales" from "Adventure Works" sample project. I have added one more measure group which has target revenue  for each year. We have scripts below to load data into new "Target" table:

1. CREATE TABLE Targets (CalendarYear VARCHAR(4), TargetInternetSalesRevenue BIGINT)
2. INSERT INTO Targets values (2001,3000000)
3. INSERT INTO Targets values (2002,7593423)
4. INSERT INTO Targets values (2003,9000000)
5. INSERT INTO Targets values (2004,11770000)
6. INSERT INTO Targets values (2005,10500000)

CREATE TABLE Targets (CalendarYear VARCHAR(4), TargetInternetSalesRevenue BIGINT)

INSERT INTO Targets values (2001,3000000)
INSERT INTO Targets values (2002,7593423)
INSERT INTO Targets values (2003,9000000)
INSERT INTO Targets values (2004,11770000)
INSERT INTO Targets values (2005,10500000)

My cube will look like:

Cube Data Source View

Now we are ready to create KPIs in the cube. Use the following steps to create KPIs in cube:

1) Open Solution and click to "KPIs" tab.Click on "New KPI" and it will open one template for filling the desired information.

2) Name: Provide name to new KPI. For example, we have used "Internet Sales Revenue" for KPI.

3) Associated Measure Group: KPI belongs to one particular measure group. Here you can specify particular measure group name like "Fact Internet Sales"(in our example).

4) Value Expression: This is actual value of KPI. This may be raw measure or MDX numeric expression.

5) Goal Expression: Here our new created measure group will come in picture. We will get "Value Expression" from "Fact Internet Sales" measure group's measure "Sales Amount" but goal value will be from "Target" measure group's measure "Target Internet Sales Revenue". 

6) Status:This checks state of KPI at any point. Status MDX expression should return value between -1 and 1.

In this example, if KPI Value is meeting 90% target then it is positive status and status is average for range 85-90%. If it is less then 85% then it is alarm to senior management and they should take some actions to make it positive.

 

KPI Status Expression

 

7) Status Indicator: Status can be represented by multiple ways. check below figure for available options in SQL Server 2005 and SQL Server 2008:

 

Status Indicators

8) Trend:Trend evaluate value of KPI over the period of time. You can specify values between -1 and 1 for trends. For example, You want to compare current year and last year's revenue for checking the trend. if revenue growth is greater than 10% then it is positive trend and revenue drop more then 10% then it is negative trend.

KPI Trend Expression

7)Trend Indicator: Status can be represented by multiple ways. check below figure for available options in SQL Server 2005 and SQL Server 2008:

Trend Indicators

Lets see cube data so we will be able to check if KPIs are behaving same as expected:

Cube Browser data

Calender year 2003 has achieved its own target and shows positive growth from last year. Lets put filter on calendar year for 2003 in "Browser View" of KPI tab and view data.

Cube KPI Browser Tab data

I hope you get fair amount of idea on implementation of KPIs and when it will be required. Please share your views so i can make learning articles more useful.

 

---

Amit Gupta is working in BI space from last 7 years and he has started working in MSBI from Jun 2006. He specializes in business intelligence services and solutions and holds Microsoft certifications in BI space. His personal blog site can be found at http://www.msbiconcepts.com/.

---