Note : This is a step by step explanation of creating Calculations and KPI in SSAS based on a sample cube created out of adventure works DW 2012 which contains a few Dimensions and measures as shown below. We can also try this example on sample Adventureworks cube downloaded from codeplex.
What are calculations?
•Calculations in SSAS
extends the
cube capabilities and
make BI applications
more powerful.
•Calculations can
be defined using scripts or Multidimensional Expressions (MDX).
•Calculations consists of New Calculated
members, New named set,
or a New
Script Command.
•It consists of two views : Form
View and Script
View.
Calculations Tab and Creating Calculations :
Option 1 : Click on Calculations
Tab and
then Click on New Calculated Member Icon
Option 2 : Right click any Calculations under
Script Organizer and Click on New Calculated Member
Calculations : Script
View
Calculations : Form
View
What is KPI?
•In business terminology, a Key Performance
Indicator or
KPI is a
quantifiable measurement for gauging business success.
•In SSAS we can add KPIs to a database cube to evaluate business performance, as
reflected in the cube data.
•KPI is associated with a measure group.
•KPI is made up of a set of properties,
calculations and MDX Expressions.
•On KPI Tab we
have two views : Form View and
Browser
View
KPI Tab and Creating New KPI
Option 1 : Click on KPI Tab;
then Click on New KPI Icon
Option 2 : Right Click in KPI Organizer
section and Click on New KPI
KPI : Form View
KPI : Browser View
Four Main properties of KPI :
1.Value expression
A value expression is a physical measure
such as Sales, a calculated measure such as Profit, or a calculation that is
defined within
the
KPI by using a Multidimensional Expressions (MDX) expression.
2.Goal
expression
A goal expression is a value, or an MDX
expression that resolves to a value, that defines the target for the measure
that the value
expression defines. For example, a goal expression could be the amount by which
the business managers of a company want to increase sales or profit.
3.Status
expression
A status expression is an MDX expression
that Analysis Services uses to evaluate the current status of the value
expression compared
to
the goal expression. A goal expression is a normalized value in the range
of -1 to +1, where -1 is very bad, and +1 is very good. The status expression
displays a graphic to help you easily determine the status of the value expression
compared
to the goal expression.
4.Trend
expression
A trend expression is an MDX expression
that Analysis Services uses to evaluate the current trend of the value
expression compared
to
the goal expression. The trend expression helps the business user to quickly
determine whether the value expression is becoming better or worse relative to
the goal expression. You can associate one of several graphics with the trend
expression
to help business users be able to quickly understand the trend.
STEPS to create Calculated Members and KPI :
STEP 1: Create New Calculated Members
1. [TotalSalesAmount]
Expression
:
[Measures].[Sales
Amount]+[Measures].[Sales Amount - Fact Reseller Sales]
2. [TotalCostAmount]
Expression :
[Measures].[Total
Product Cost]+[Measures].[Total Product Cost - Fact Reseller Sales]
3. [Profit]
•Name : [Profit]
•Parent Hierarchy : Measures
•Value Expression : ([Measures].[TotalSalesAmount]-[Measures].[TotalCostAmount])*100/[Measures].[TotalCostAmount]
•Format String : "#,##0.00 %;-#,##0.00
%“
•Color Expression(Fore Color) :
IIF(([Measures].[TotalSalesAmount]-[Measures].[TotalCostAmount])*100/[Measures].[TotalCostAmount]
<
0,255 /*Red*/,65280
/*Lime*/)
4. [Goal]
Goal
Expression : 10
5. STATUS : Against the goal
When
Less than Zero THEN assign -1(Poor Business); WHEN between 0 to 10 THEN assign
0(OK); WHEN greater than 10 then assign 1(Good Bussiness)
Status Expression
:
CASE
WHEN
[Measures].[Profit] < 0
THEN -1
WHEN
[Measures].[Profit] >= 0 AND
[Measures].[Profit] <=10
THEN 0
ELSE 1
END
6. TREND : Against the Past Value(Date, Year, Quarter, Month etc.)
Trend
Expression :
CASE
WHEN
[Measures].[Profit]
<
([Order Date].[Calendar Year].PREVMEMBER, [Measures].[Profit]
)
THEN
-1
WHEN
[Measures].[Profit]
=
([Order Date].[Calendar Year].PREVMEMBER, [Measures].[Profit]
)
THEN
0
ELSE
1
END
STEP 2 : Save & Process the Cube
•Click on Build à Deploy AdventureWorks_Sales (Name of your Project)
Now we can see that the following Calculations has been created :
STEP 3 : Create New KPI named Profitability
1.Name
:
Profitability
2.Value
Expression :
[Measures].[Profit]
3.Goal
Expression :
[Measures].[Goal]
4.Status
Indicator :
Select any à Shapes,
Traffic Light, Road Signs, Gauge, Reversed Gauge, Thermometer, Cylinder, Faces,
Variance Arrow
5.Status
Expression :
[Measures].[Status]
6.Trend
Indicator :
Select Any à
Standard Arrow, Status Arrow, Reversed Status Arrow, Faces
7.Trend
Expression :
[Measures].[Trend]
STEP 4 : Save & Process the Cube
Click on Build à Deploy AdventureWorks_Sales
STEP 5 : Click on Browser
View icon à
Reconnect
Create some filters to try out and view value, goal, status and trends as shown below:
Hope you have liked this step by step explanation of Calculations and KPI in SSAS. Keep practising.
Note : This is a blog based on my learnings and understanding of the topics of MSBI and subject to further editing based on my explorations.
Please Comment, Like and suggest improvements.
