KPI(Key Performance Indicators)-SSAS (Part II)

Hello All,

We will continue with the KPI topic further trying to consider some complex scenarios.
In case you want to know about the KPI basics, you can visit the earlier post at http://vivekrohra.blogspot.in/2014/03/kpikey-performance-indicators-ssassql.html


So in the last post, we saw what are KPI's, what are actual value, Goal, Status and Trend of KPI.

Now  Goals/Target  is something that companies define for a period of time. It may be for a period of month or quarter or a year.
In last post, we took example for goal defined over period of year. So each year,goal changes.
But the expression used to define kpi goal for each year was case expression where goal value was mentioned for each year. This is hardcoding which makes life difficult in long run as there are lot of KPI's company has and as time progresses, case statement has to be modified to include goal values for new year.


So I create 2 new tables in the warehouse, dimension table for KPI and fact table for KPI Goal.

I have added one primary key called KPIKey in DimKPI table which will have integer values like 1,2,3..
The other column is KPIName.

Now, data is populated in DimKPI

The FactKPIGoal table will have the goal values for each KPI and period.
KPI_Goal will store the goal value. KPIKey is foreign key to table DimKPI.
DateKey is foreign key to DimDate.

So assuming the example in previous post, KPI is defined for each year. But to link FactKPIGoal and DimDate, DateKey has to be used which is at date level. So in FactKPIGoal, we will just take first date of each year in DimDate and will populate goal data in FactKPIGoal for the DateKey for that Date. This will get clear with the sample data populated.

In previous post, goal expression used was:


CASE
WHEN [Order Date].[Date Hierarchy].currentmember IS
[Order Date].[Date Hierarchy].[Calendar Year].&[2006]
THEN 0.54
WHEN [Order Date].[Date Hierarchy].currentmember IS
[Order Date].[Date Hierarchy].[Calendar Year].&[2007]
THEN 0.34
WHEN [Order Date].[Date Hierarchy].currentmember IS
[Order Date].[Date Hierarchy].[Calendar Year].&[2008]
THEN 0.25
ELSE 0.70
END

So while joining DimDate and FactKPIGoal, for year 2006, there should be just one record in FactKPIGoal. If i have one more record for another DateKey of 2006 in FactKPIGoal, cube will sum up the goals for both records when we browse the cube at the Year level which we don't want.

That is why we are taking first DateKey of each year and populating goal data in FactKPIGoal.



Now, its time to add the tables in the Cube. To do that, first add them in the Data Source View(DSV).

Then add the dimension by right clicking dimensions in Solution Explorer.

Select DimKPI table from DSV as source for new dimension. Keep KeyColumn as KPIKey as it is the primary key in the table and NameColumn as KPIName. NameColumn is the value that will be displayed to the user.

Select the attributes which needs to be shown in Dimension. In our case it is just one.

Now, dimension is added to the database. But I need to add it in the cube. Go to Cube, Right Click Cube Name in Dimensions tab and Click Add Cube Dimension.Select DimKPI from the list shown on next pop-up.

Let's now add the FactKPIGoal to the measure groups in the cube.

Now, since we are using Order Date dimension to view the KPI Goals, we need to specify relationship between Order Date dimension and FactKPI Goal measure group in Dimension Usage Tab.Both are linked by DateKey columns in both table.

Now, we create new KPI by going to KPI tab in the Cube.

As you can see above, the Goal Expression is ([Measures].[KPI Goal],[Dim KPI].[KPIName].&[1])
so it is getting the goal value for Profit % KPI which has KPI Key 1.

The Display Name will be Profit %( as the NameColumn specified earlier was KPIName) but the way cube stores it is [Dim KPI].[KPIName].&[1]( as the KeyColumn specified earlier was KPIKey).

So now KPI is defined, deploy and process the cube.

Now we will go to excel and connect to the cube.

I have selected the KPI which was created in Last Post( Gross Profit) and the one created now(GrossProfitDynamic). I have taken the Order Date hierarchy at year level.

As I have defined goals for 3 years(2006,2007,2008) , I am able to see goals for those 3 years in KPI GrossProfitDynamic while in KPI Gross Profit we had else statement in case where we had mentioned if it is any other year apart from the above 3, put goal as 0.7

Now if I have to Add goal for new year, I need not go to cube and modify case statement. I will add the data in FactKPIGoal table in the database.

As you can see above,I have added Goal value for year 2013 in FactKPITable.
Now just process the cube.

Now refresh the data in excel and here it is, the goal value pops up for year 2013.

I hope you gained some knowledge out of it. But to make things more complex, what if i have my company in different countries and now I have to populate KPI goal for each Year and each country.

Get your Thinking Hats ON :-)

KPI(Key Performance Indicators) - SSAS(SQL Server Analysis Services)

KPI's - Key Performance Indicators are the signs which helps companies to understand how their business is  doing, whether it is meeting the targets and whether it is performing better than earlier.

So what can be the KPI's of companies? It can be Profit %, Inventory Stock, Cost, Revenue,etc.

So, let's start how to implement these in SSAS(SQL Server Analysis Services).

We will take Profit % as example, it is not a direct field in AdventureWorks  Datawarehouse. So we will create a calculated field in SSAS cube.

Now lets go to the KPI tab, click New KPI button.

We will name it Gross Profit.

For the Value Expression, it will be the calculated measure we created above.

So we now have the value for Profit %.  Every company defines its targets for each KPI . It may be a fixed target or targets change every month/quarter/year.

Lets consider the scenario where targets to be achieved are defined every year.

We will write the expression for it in Goal expression in the KPI.

In the above expression we specify if the user is looking at the year level and if the year is 2006, then goal is 0.54(that is 54%) and similarly for the year 2007,2008. And if it is any other year ,goal is 0.7(70%)

So in simple words, company has to achieve 54 % profit in year 2006,34 % profit in year 2007, 25% profit in year 2008,otherwise 70% profit .


Now, we have our actual values of Profit and the goal values of Profit % with us.
We need to know now if the company is achieving the target or not.
 We have Status expression for it.

So to get the actual value of KPI, we have KPIValue() function and to get the goal value, we have KPIGoal function which gets the values from expression we have defined in Value Expression and Goal Expression.

Now, you can see Status Indicator as Traffic Light.

So from Status expression, we need to pass 1,0,-1 values which will show indicator as Green,Yellow,Red respectively.

From expression defined above, if  Actual Profit % is greater than 90 % of the goal defined, then pass value 1 and show Green Indicator.It means business is performing very well and close to the targets.
If Actual Profit lies between 80% and 90% of the goal defined, then pass value 0 and show Yellow Indicator.It means business is performing decent enough.
If Actual Profit is less than 80% of the goal defined, then pass -1 value and show Red Indicator. It means business is not doing good and it needs attention.


Ok, so we got idea if business is meeting goals or not. But it may happen it is close to goals for eg.  profit % is 90% of the goal but profit is less than the profit achieved in the last Year.

So the business wants to know the trend. That's why we have the Trend Expression.

Here, we compare the Profit value to the previous Year Profit value.
PARALLELPERIOD() function helps to get the measure value for previous year.
So I am checking the currentmember User has selected.For eg. user has selected Jan 2008.
So currentmember is Jan2008 and CurrentMember.level is Month. I subtract 1 month from Jan 2008 and  Dec 2007 is returned.
If year is selected, 1 year is subtracted in PARALLELPERIOD function.

So now if am checking year 2006 value and 2006 is the first year in the database and so in cube, then the ParallelPeriod function will throw an error, so we check in first WHEN statement if parallelperiod() function returns Empty, return 0 value.

Similar to the Status, we have indicator for Trend as well and we have selected Standard arrow.

So from Trend expression, we need to pass 1,0,-1 values which will show indicator as Up Arrow,Horizontal Arrow, Down Arrow respectively.

So if current Profit value is greater than the previous Profit value then pass 1 and it will Show Up Arrow.It means business is on upwards trend.

If current Profit value is equal to the previous Profit value then pass 0 and it will Show Horizontal Arrow.It means business is on same level.

If current Profit value is less than the previous Profit value then pass -1 and it will Show Down Arrow.It means business is on downwards trend.


So we are done with defining KPI. Lets deploy and process the cube.
Now to check the KPI value,goal,status and trend, we can browse KPI in KPI tab itself by clicking on Browser View button(marked Black) .


Other way is to browse cube in SSMS(SQL Server Management Studio).
The one way I like to browse is Excel.

Open Excel, Connect to the cube.

Select the Calendar Hierarchy defined in the cube and the KPI(Value,Goal,Status,Trend).

Gross Profit is showing in % value, as we had define Format String in Calculated Member for Profit Margin to be %. For Goal, we can change Number format in Excel to %.

Now for 2007, profit is 28.58%( that is 0.2858) which lies between 80 and 90 % of goal(0.34)
So Yellow indicator shown for Status.
for 2008, profit is 28.51%(0.2851) which is greater than goal (0.25) .So Green Indicator for Status.
We dont have values for other years in database. So Red Status.


Now to check Trend, we need to check value with previous year. Since 2008 value (28.51%) is less than
2007(28.58%) trend is downwards and so Down Arrow for Trend.

So we are done with KPI basics.
But there are scenarios where KPI can be more complex.

For eg, in Goal expression we have hardcoded values  for different years. So if I have to change or add goals for years to come I must go to each KPI and each Goal Expression and change Case Statement there which will be troublesome if I have many KPI's.
And if I have my companies in different countries, so my goals will be different for each company and for each Year.
So how to deal with such situations, I will try to cover up in next post.

Hope you gained some knowledge from this one!!

Thanks :)