Key Performance Indicator

Organizations can use key performance indicators (KPIs) to gauge the health of their enterprise and their performance by measuring their progress toward their goals. KPIs are business metrics that can be defined to monitor progress toward certain predefined objectives and goals.

A KPI usually has a target value and an actual value, which represents a quantitative goal that is critical to the success of the organization. KPIs are usually displayed in groups on a scorecard to show the overall health of the business in one quick snapshot.

An example of a KPI is to complete all change requests within 48 hours. A KPI can be used to measure the percentage of change requests that are resolved within that time frame. You can create dashboards to represent KPIs visually. 

For example, you might want to define a KPI target value for completion of all change requests within 48 hours to 75 percent.

Adding a Key performance Indicator 

In SQL Server Analysis Services (SSAS), you can add key performance indicators (KPIs) to your database cube in order to evaluate business performance, as reflected in the cube data. A KPI is associated with a measure group and is made up of a set of calculations. Typically, the calculations are a combination of calculated members and Multidimensional Expressions (MDX) statements.

A KPI consists of four main properties that are important to evaluating business performance:

• Value Expression. An MDX expression that returns the KPI’s actual value.
• Goal Expression. An MDX expression that returns the KPI’s target value.
• Status Expression. An MDX expression that returns the KPI’s state at a specific point in time.
• Trend Expression. An MDX expression that returns the KPI’s value over time.

In addition to these components, there are other properties that you can configure, but these four components make up the heart of your KPI.

As you work through the process of creating a KPI, you’ll get a better sense of what each of these properties means and how they relate to one another. 

We  have to Create following database components

• A data source that points to the AdventureWorksDW2008 database on a local instance of SQL Server 2008.
• A data source view that includes the tables shown in Figure 1.
• Database dimensions based on each dimension table in the data source view.
• A cube based on the database dimensions as well as on the two fact tables in the data source view.

Creating a Calculated Member

When you create a KPI, you base one or more of your expressions on members in a measure group or dimension. However, in some cases, the existing members don’t support the type of KPI you want to create, at least not in their current form.

 If that’s the case, you can create a calculated member, which is similar to creating a computed column in a SQL Server database.

To create a calculated member, open your Analysis Services project in SQL Server Business Intelligence Development Studio (BIDS), and then open the cube in which you want to create your KPI. (For this article, I’m adding the KPI to the Sales cube.) In Cube Designer, click the Calculations tab, and then click the New Calculated Member button. A new calculation form opens in the right pane, as shown in Figure 2.

You should first name the calculated member by typing the name in the Name text box. For this example, I use the following name:

[Profit Margin]

Notice that I enclose the name in brackets. If your name includes a space, as mine does, you must use the brackets.

Next, you should verify the setting for the Parent hierarchy property. By default, the property is set to Measures. Because we’re creating a calculated measure, this is the hierarchy we want to use.

After you select the hierarchy, you must define an MDX expression that determines the value for your calculated measure. In this case, I want to create a measure that provides the profit margin for each sale. The following expression calculates the margin by dividing the net profit by the sales amount

([Measures].[Sales Amount] -

  ([Measures].[Total Product Cost] + [Measures].[Tax Amt] +

    [Measures].[Freight])) /

  [Measures].[Sales Amount]

After you create your expression for the calculated member, you can set additional properties. For this example, I set the Format string property to "Percent" and then select Fact Internet Sales in the Associated Measure Group property because I want to associate the calculated member with that measure group. Figure 3 shows what the form should look like after you’ve configured all the properties.

That’s all there is to creating a calculated member. Be sure to save the project and then process the cube so the measure is available to your KPI. After you process your cube, you can verify that the measure has been successfully added by browsing the cube data and viewing the Profit Margin measure.

Creating a Key Performance Indicator

Now that your calculated measure is set up, you’re ready to create your KPI. In Cube Designer, click the KPIs tab, and then click the New KPI button. A new KPI form opens in the right pane, as shown in Figure 4.

To configure the KPI, first provide a name. (I use Gross Profit for our example KPI.) Then select a value for theAssociated measure group property. (I use Fact Internet Sales.) You’re now ready to add the necessary expressions to your KPI.

Adding the Value Expression

Your value expression should reflect the basic measure by which your KPI is gauged. The value returned by the expression serves as the foundation for your KPI. It’s the only one of the four expressions that’s required. For our example KPI, I use the following expression:

[Measures].[Profit Margin]

As you can see, I’m simply calling the Profit Margin calculated measure. Notice that, as you saw when creating your calculated member, I’m using the fully qualified member name. Also, as with the calculated member, you can drag the name from the hierarchies listed in the lower-left pane to the expression text box.

Adding the Goal Expression

As the name suggestions, the goal expression indicates what your organization is trying to achieve. For example, your profit margin might currently be at 25%, but your goal might be to reach 30%. And you can also set your goal to match more specific criteria. For instance, in the example KPI, I set the goal to vary depending on the specific sales territory group, as shown in the follow MDX expression:

Case

  When [Territory].[Sales Territory Group]

    Is [Territory].[Sales Territory Group].[Europe]

      Then .34

  When [Territory].[Sales Territory Group]

    Is [Territory].[Sales Territory Group].[North America]

      Then .36

  When [Territory].[Sales Territory Group]

    Is [Territory].[Sales Territory Group].[Pacific]

      Then .32

  Else .30

End

Notice that I use a Case statement to define my criteria. The Case statement includes three When expressions, one for each territory group. Each When expression identifies the member on which to base the expression and the member value, following the Is keyword. This is followed by a Then expression which defines what action to take. For example, the first When expression states that if the sales territory group is Europe, then the profit margin goal is 34%. However, the goal for the North American group is 36%, and the goal for the Pacific group is 32%. The Else clause then specifies that all other groups have a 30% goal.

Adding the Status Expression

Your status expression determines the current status of the KPI by comparing the goal expression to the value expression. For example, if your KPI value returns a 20% profit margin, but your goal is 30%, the status will indicate that you are below your goal. However, to arrive at the status, your status expression must return a value in the range of -1 to +1, where -1 indicates bad performance and +1 indicates good performance. For our example KPI, I use the following MDX expression to determine the status of performance:

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


Adding the Trend Expression

The trend expression lets you compare how your KPI is performing over time. Although the status value can tell you how well you’re achieving you goal at a fixed point in time, it doesn't tell you how those achievements compare to another point in time. For example, your organization might have reached 92% of its goal this year, but reached 95% the year before. Although the performance looks good when just viewing this year’s total, that performance measure doesn't reveal that this is actually a downward trend. And this is what the trend expression allows you to find out.

For the Gross Profit example KPI, I use a trend expression that compares this year’s totals to the previous year, as shown in the following Case statement:

Case
  When IsEmpty(ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
      1, [Order Date].[Calendar Year]))
    Then 0
  When [Measures].[Profit Margin] >
    (ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
      1, [Order Date].[Calendar Year]), [Measures].[Profit Margin])
    Then 1
  When [Measures].[Profit Margin] =
    (ParallelPeriod([Order Date].[Calendar Year].[Calendar Year],
      1, [Order Date].[Calendar Year]), [Measures].[Profit Margin])
    Then 0
  Else -1
End

Completing the Key Performance Indicator

A KPI supports properties in addition to the ones I’ve described so far. Although configuring those properties are beyond the scope of the article, you should have some idea how they work. If you click the Additional Properties down arrow at the bottom of the KPI form, you can view and configure the following properties:

• Display folder. The folder in which the KPI can be found when browsing the cube.
• Parent KPI.> A KPI that acts as the parent of the current KPI so the parent KPI can use the value of the child KPI.
• Current time member. An MDX expression that returns a member that identifies the KPI’s temporal context.
• Weight. An MDX expression that assigns a weight to a child KPI to indicate its relative importance in the parent KPI.
• Description. A description of the KPI.

After you’ve completed your KPI, you can then view its results, based on the current values in the cube data. To view the KPI, click the Browser View button on the KPIs tab. The browser view includes two panes. The top pane lets you define filters that determine what data the KPI uses, and the bottom pane displays the KPI. By default (before any filters are defined), the KPI calculates the KPI value for the entire data set. However, you can create filters that let you define the data for which you want to run the KPI.