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. In this article, I show you how to add a KPI to a cube in an Analysis Services 2008 database. The example I demonstrate is based on the solution from an earlier article I wrote, Five Basic Steps for Implementing an Analysis Services Database". For that solution, I created the following database components:

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

Be sure to refer to the article mentioned above for more details about the solution. In addition, if you don’t know how to implement a basic cube in Analysis Services, read that article first and refer to SQL Server Books Online for additional information. Once you know how to implement an Analysis Services database, you’re ready to add KPIs to your solution. 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.

Full article...