MSBI # 42 – SSAS # 12 – How to Defining Key Performance Indicators (KPIs) in SSAS

Hi folks

Continuing from my last post on SSAS

• MSBI # 21 – SSAS # 7 – Understanding the Cube Designer Tabs in SSAS
• MSBI # 28 – SSAS # 9 – Modifying Measures, Attributes and Hierarchies in SSAS
• MSBI # 34 – SSAS # 10 – How to Defining Actions in SSAS
• MSBI # 41 – SSAS # 11 – How to Defining Calculations in SSAS

Following topics are covered in this post

• Defining and Browsing KPIs and few terms for explanation
• To define the Reseller Revenue KPI

In this article , we will learn to define Key Performance Indicators (KPIs) in our Microsoft SQL Server Analysis Services (SSAS) project. KPIs provide a framework for defining server-side calculations that measure your business, and they standardize how the resulting information is displayed. KPIs can be displayed in reports, portals, and dashboards, through data access APIs, and through Microsoft tools and third-party tools. KPIs are metadata wrappers around regular measures and other Multidimensional Expressions (MDX) expressions.

Defining and Browsing KPIs 

To define key performance indicators (KPIs) in Microsoft SQL Server 2008, you first define a KPI name and the measure group to which the KPI is associated. A KPI can be associated with all measure groups or with a single measure group. You then define the following elements of the KPI:

The 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.

The 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.

The 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, which a generally a normalized value in the range of -1 to +1. -1 is very bad, and +1 is very good. The status expression displays with a graphic to help you easily determine the status of the value expression compared to the goal expression.

The 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 getting better or worse relative to the goal expression. We can associate one of several graphics with the trend expression to help business users be able to quickly understand the trend.

In addition to these elements that We will define for a KPI, We can also define several properties of a KPI. These properties include a display folder, a parent KPI if the KPI is computed from other KPIs, the current time member if there is one, the weight of the KPI if it has one, and a description of the KPI.

In the tasks in this topic, you define the two KPIs in the Analysis Services Tutorial project, and We will then browse the Analysis Services Tutorial cube by using these KPIs. Following are the KPIs that We will define:

Reseller Revenue
This KPI is used to measure how actual reseller sales compare to sales quotas for reseller sales, how close the sales are to the goal, and what the trend is toward reaching the goal.

Product Gross Profit Margin
This KPI is used to determine how close the gross profit margin is for each product category to a specified goal for each product category, and also to determine the trend toward reaching this goal.

To define the Reseller Revenue KPI

1.Open Cube Designer for the Analysis Services Tutorial cube, and then click the KPIs tab.

The KPIs tab includes several panes. On the left side of the tab are the KPI Organizer pane and the Calculation Tools pane. The display pane in the middle of the tab contains the details of the KPI that is selected in the KPI Organizer pane.

The following image shows the KPIs tab of Cube Designer.

2.On the toolbar of the KPIs tab, click New KPI.

A blank KPI template appears in the display pane, as shown in the following image.

3.In the Name box, type Reseller Revenue, and then click Reseller Sales in the Associated measure group list.

4.On the Metadata tab in the Calculation Tools pane, expand Measures, expand Reseller Sales, and then drag the Reseller Sales-Sales Amount measure to the Value Expression box.

5.On the Metadata tab in the Calculation Tools pane, expand Measures, expand Sales Quotas, and then drag the Sales Amount Quota measure to the Goal Expression box.

6.Verify that Gauge is selected in the Status indicator list, and then type the following MDX expression in the Status expression box:

Case When KpiValue("Reseller Revenue")/KpiGoal("Reseller Revenue")>=.95 Then 1 When KpiValue("Reseller Revenue")/KpiGoal("Reseller Revenue")<.95 And KpiValue("Reseller Revenue")/KpiGoal("Reseller Revenue")>=.85 Then 0 Else-1 End

This MDX expression provides the basis for evaluating the progress toward the goal. In this MDX expression, if actual reseller sales are more than 85 percent of the goal, a value of 0 is used to populate the chosen graphic. Because a gauge is the chosen graphic, the pointer in the gauge will be half-way between empty and full. If actual reseller sales are more the 90 percent, the pointer on the gauge will be three-fourths of the way between empty and full.

7.Verify that Standard arrow is selected in the Trend indicator list, and then type the following expression in the Trend expression box:

Case When IsEmpty (ParallelPeriod ([Date].[Calendar Time].[Calendar Year],1, [Date].[Calendar Time].CurrentMember)) Then 0 When ( KpiValue("Reseller Revenue") – (KpiValue("Reseller Revenue"), ParallelPeriod ([Date].[Calendar Time].[Calendar Year],1, [Date].[Calendar Time].CurrentMember)) / (KpiValue ("Reseller Revenue"), ParallelPeriod ([Date].[Calendar Time].[Calendar Year],1, [Date].[Calendar Time].CurrentMember))) >=.02 Then 1 When( KpiValue("Reseller Revenue") – (KpiValue ( "Reseller Revenue" ), ParallelPeriod ([Date].[Calendar Time].[Calendar Year],1, [Date].[Calendar Time].CurrentMember)) / (KpiValue("Reseller Revenue"), ParallelPeriod ([Date].[Calendar Time].[Calendar Year],1, [Date].[Calendar Time].CurrentMember))) <=.02 Then -1 Else 0 End

This MDX expression provides the basis for evaluating the trend toward achieving the defined goal.

To browse the cube by using the Reseller Revenue KPI

1.On the Build menu of Business Intelligence Development Studio, click Deploy Analysis Service Tutorial.

2.When deployment has successfully completed, click Browser View on the toolbar of the KPIs tab, and then click Reconnect.

The status and trend gauges are displayed in the KPI Browser pane for reseller sales based on the values for the default member of each dimension, together with the value for the value and the goal. The default member of each dimension is the All member of the All level, because We have not defined any other member of any dimension as the default member.

The following image shows the KPI in the KPI Browser pane in the Browser View of the KPIs tab.

3.In the Filter pane, select Sales Territory in the Dimension list, select Sales Territories in the Hierarchy list, select Equal in the Operator list, select North America in the Filter Expression list, and then click OK.

4.In the Filter pane, select Date in the Dimension list, select Calendar Time in the Hierarchy list, select Equal in the Operator list, select Q3 CY 2003 in the Filter Expression list, and then click OK.

5.Click anywhere in the KPI Browser pane to update the values for the Reseller Revenue KPI.

Notice that the Value, Goal, and Status sections of the KPI reflect the values for the new time period, as shown in the following image.

To define the Total Gross Profit Margin KPI

1.Click Form View on the toolbar of the KPIs tab, and then click New KPI.

2.In the Name box, type Product Gross Profit Margin, and then verify that <All> appears in the Associated measure group list.

3.In the Metadata tab in the Calculation Tools pane, drag the Total GPM measure to the Value Expression box.

4.In the Goal Expression box, type the following expression:

Case When [Product].[Category].CurrentMember Is [Product].[Category].[Accessories] Then .40 When [Product].[Category].CurrentMember Is [Product].[Category].[Bikes] Then .12 When [Product].[Category].CurrentMember Is [Product].[Category].[Clothing] Then .20 When [Product].[Category].CurrentMember Is [Product].[Category].[Components] Then .10 Else .12 End

5.In the Status indicator list, select Cylinder.

6.Type the following MDX expression in the Status expression box:

Case When KpiValue( "Product Gross Profit Margin" ) / KpiGoal ( "Product Gross Profit Margin" ) >= .90 Then 1 When KpiValue( "Product Gross Profit Margin" ) / KpiGoal ( "Product Gross Profit Margin" ) < .90 And KpiValue( "Product Gross Profit Margin" ) / KpiGoal ( "Product Gross Profit Margin" ) >= .80 Then 0 Else -1 End

This MDX expression provides the basis for evaluating the progress toward the goal.

7.Verify that Standard arrow is selected in the Trend indicator list, and then type the following MDX expression in the Trend expression box:

Case When IsEmpty (ParallelPeriod ([Date].[Calendar Time].[Calendar Year],1, [Date].[Calendar Time].CurrentMember)) Then 0 When VBA!Abs ( KpiValue( "Product Gross Profit Margin" ) – ( KpiValue ( "Product Gross Profit Margin" ), ParallelPeriod ( [Date].[ Calendar Time].[ Calendar Year], 1, [Date].[ Calendar Time].CurrentMember ) ) / ( KpiValue ( "Product Gross Profit Margin" ), ParallelPeriod ( [Date].[ Calendar Time].[ Calendar Year], 1, [Date].[ Calendar Time].CurrentMember ) ) ) <=.02 Then 0 When KpiValue( "Product Gross Profit Margin" ) – ( KpiValue ( "Product Gross Profit Margin" ), ParallelPeriod ( [Date].[ Calendar Time].[ Calendar Year], 1, [Date].[ Calendar Time].CurrentMember ) ) / ( KpiValue ( "Product Gross Profit Margin" ), ParallelPeriod ( [Date].[Calendar Time].[Calendar Year], 1, [Date].[Calendar Time].CurrentMember ) ) >.02 Then 1 Else -1 End

This MDX expression provides the basis for evaluating the trend toward achieving the defined goal.

To browse the cube by using the Total Gross Profit Margin KPI

1.On the Build menu, click Deploy Analysis Service Tutorial.

2.When deployment has successfully completed, click Reconnect on the toolbar of the KPIs tab, and then click Browser View.

The Total Gross Profit Margin KPI appears and displays the KPI value for Q3 CY 2003 and the North America sales territory.

3.In the Filter pane, select Product in the Dimension list, select Category in the Hierarchy list, select Equal in the Operator list, and then select Bikes in the Filter Expression list, and then click OK.

The gross profit margin for the sale of Bikes by resellers in North America in Q3 CY 2003 appears, as shown in the following image.

Hope this helps !!

Hope We have understood basic aspect of Key Performance Indicators (KPIs) in SSAS and ready to use every aspects for same

If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog

If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog

Where todays links are

Link Resource « Dactylonomy of Web Resource