business intelligence consulting
 

Creating a simple Key Performance Indicator (KPI) using SQL Server Analysis Services (SSAS) 2012

by Hans Esquivel 16. December 2012 11:04

In this article we'll show you how to create a simple Key Performance Indicator (KPI) using SQL Server Analysis Services 2012.

What are Key Performance Indicators?

A KPI is a quantifiable measurement for gauging business success. In Microsoft SQL Server 2012 Analysis Services (SSAS), a KPI is a collection of calculations, which are associated with a measure group in a cube, that are used to evaluate business success. Typically, these calculations are a combination of Multidimensional Expressions (MDX) expressions and calculated members. KPIs also have additional metadata that provides information about how client applications should display the results of a KPI's calculation.

What we'll cover in this session:

  • Defining a Calculation - Calculated Member
  • Define a KPI
    • Measure Group
    • Value Expression
    • Goal Expression
    • Status Indicator
    • Status Expression
    • Trend Indicator
    • Trend Expression
  • Browser View KPI
  • Query KPI using MDX

This article assumes you have the following:

  • Installed SQL Server 2012 or 2008 R2, with Analysis Services.
  • Installed AdventureWorks Multidimensional Models SQL Server 2012 sample database.
  • Experience with SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). Note: For this session we'll be using SSDT.
  • Experience with deploying and attaching to an exisitng Analysis Services database using SSDT or Business Intelligence Development Studio (BIDS).
  • Some experience developing with Multidimensional Expressions (MDX) language.

Let's get started!

 

Step 1 - Attaching to the AnalysisServicesMultidimensionalTutorial database

1. Open SQL Server Data Tools from Start >> All Programs >> Microsoft SQL Server 2012

2. From the Menu bar, navigate to File >> Analysis Services Database...

3. The Connect To Database window will render. Type in the Server name of the Analysis Services server and select the AnalysisServicesMultidimensionalTutorial datbase from the Database dropdown list. In the New Solution textbox, select or enter in the path you would like for the solution to be located.

4. Click OK to attach. You should see the following...

 

Step 2 - Creating a Calculated Member

In this step we will create the Calculated Member Gross Profit Margin (GPM) for Products. The GPM calculated member will be referenced later as the Value Expression for the KPI. You can easily enter the calculation for GPM in the Value Expression field, but we prefer to create a calculated member and reference it from the KPI. This approach promotes reusability and data integrity for other reporting/analytic needs.

In this example, the Gross Profit Margin is caculated as follows: (Internet Sales Amount - (Total Product Costs + Sales-Tax Amount + Internet Sales-Freight)) / Internet Sales Amount. Your organization may have similar requirements for calculating Gross Profit Margin.

1. Navigate to the Solution Explorer. From within the Cubes folder, double click Adventure Works DW cube. This will open the Cube Designer.

2. Click the Calculations tab. You see something similar.

3. From within the Script Organizer pane, right click the CALCULATE command and select New Calculated Member. Note: You may also select the New Calculated Member button  from the Calculation tab tool bar.

 

4. Enter [Product Gross Profit Margin] for the Name of the calculated member.

Note: Remember to enclose the name with brackets. Brackets are required when names have a space or other characters.

5. Ensure Measures is selected for the Parent Hierarchy dropdown value.

6. Parent Member should be greyed out.

7. Copy and paste the following MDX into the Expression textbox.

([Measures].[Sales Amount] - ([Measures].[Total Product Cost] + [Measures].[Tax Amt] + [Measures].[Freight]))/[Measures].[Sales Amount]

8. Select "Percent" for the Format String value.

9. Visible = True

10. Leave Non-Empty behavior blank

11. Associated Measure Group = Fact Internet Sales

12. You can leave Display Folder blank.

13. Right click [Product Gross Profit Margin] and move it below the first Script

( [Account].[Accounts].&[95], [Measures].[Amount] )

14. Click the Save icon from the Menu bar. You should see something similar.

 

Step 3 - Creating the Key Performance Indicator (KPI)

1. Click the KPIs tab.

2. From within the KPI Organizer pane, right click and select New KPI.

3. Enter "Product Gross Profit Margin" for the Name field (without quotes).

4. You can leave the Associated Measures Group default value of <All>.

5. From within the Measure Group pane, expand Measures >> Fact Internet Sales and drag Product Gross Profit Margin to the Value Expression textbox in the KPI designer.

6. Next, we will set our 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. Enter the following MDX expression for the Goal Expression value:

Case

    When [Dim Product].[Product Category Key].CurrentMember Is [Dim Product].[Product Category Key].[Accessories]

    Then .40                

    When [Dim Product].[Product Category Key].CurrentMember Is [Dim Product].[Product Category Key].[Bikes]

    Then .12               

    When [Dim Product].[Product Category Key].CurrentMember Is [Dim Product].[Product Category Key].[Clothing]

    Then .20

    When [Dim Product].[Product Category Key].CurrentMember Is [Dim Product].[Product Category Key].[Components]

    Then .10

    Else .12            

 

End

 

7. Next we set 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. 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.

  • For the Status Indicator select Gauge.
  • Enter the following MDX for the Status Expression value:

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

8. Finally, we will set 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 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.

  • Select Standard Arrow for the Trend Indicator value.
  • Enter the following MDX for the Trend Expression value:

Case

When IsEmpty

  (ParallelPeriod

   ([Dim Date].[Calendar Date].[Calendar Year],1,

       [Dim Date].[Calendar Date].CurrentMember))

  Then 0 

   When VBA!Abs

    (

      KpiValue( "Product Gross Profit Margin" ) -

       (

         KpiValue ( "Product Gross Profit Margin" ),

          ParallelPeriod

          (

            [Dim Date].[ Calendar Date].[ Calendar Year],

            1,

            [Dim Date].[ Calendar Date].CurrentMember

          )

        ) /

        (

          KpiValue ( "Product Gross Profit Margin" ),

          ParallelPeriod

          (

            [Dim Date].[ Calendar Date].[ Calendar Year],

            1,

            [Dim Date].[ Calendar Date].CurrentMember

          )

        ) 

      ) <=.02

  Then 0

  When KpiValue( "Product Gross Profit Margin" ) -

       (

         KpiValue ( "Product Gross Profit Margin" ),

         ParallelPeriod

         (

           [Dim Date].[ Calendar Date].[ Calendar Year],

           1,

           [Dim Date].[ Calendar Date].CurrentMember

         )

       ) /

       (

         KpiValue ( "Product Gross Profit Margin" ),

         ParallelPeriod

         (

           [Dim Date].[Calendar Date].[Calendar Year],

           1,

           [Dim Date].[Calendar Date].CurrentMember

         )

       )  >.02

  Then 1

  Else -1

End

9. Click the Save icon from the Menu bar. You should see something similar.

 

 

Step 4 - Viewing the Key Performance Indicator in Browser View

1. From the menu bar, select Build and Process.

2. When the process is complete, from the KPIs tab toolbar, click on Browser View .

3. You should see the following KPI results.

 

 

Step 5 - Querying Key Performance Indicator using MDX

1. Open Microsoft SQL Server Management Studio (SSMS) and connect to Analysis Services sever where your AnalysisServicesMultidimensionalTutorial dtabase is located.

2. Richt click on the AnalysisServicesMultidimensionalTutorial database and select New Query >> MDX.

3. Expand the KPIs folder and you'll see the Product Gross Profit Margin KPI.

4. Expand the Product Gross Profit Margin KPI folder and you'll see items Value, Goal, Status and Trend. These are the available KPI values you can query.

5. Enter the following MDX in the query eidtor:

 

SELECT {KPIValue("Product Gross Profit Margin"),

KPIGoal("Product Gross Profit Margin"),

KPIStatus("Product Gross Profit Margin"),

KPITrend("Product Gross Profit Margin")} on 0

FROM [Adventure Works DW]

 


Add addtional dimensions to the query to provide more meaningful results.

 

You have completed creating a simple Key Performance Indicator (KPI) using SQL Server Analysis Services 2012.

 

Cheers!

 

Reference:  Microsoft - Key Performance Indicators (KPIs) in Multidimensional Models

A KPI is a quantifiable measurement for gauging business success. In Microsoft SQL Server 2012 Analysis Services (SSAS), a KPI is a collection of calculations, which are associated with a measure group in a cube, that are used to evaluate business success.

SQL Server | Analysis Services | Multidimensional Expressions (MDX)

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud