Create KPI using ssas (Sqlserver analysis services)

KPI is a quantifiable measurement for gauging business success. One of the most useful features of an Analysis Service cube is the ability to define key performance indicators (KPIs) within the cube that allow for a graphical representation of the current state of your business

1. create cube as below post
http://j4info.blogspot.in/2014/05/6-hierarchy-using-dimproduct-category.html

upto this our cube using existing datawarehouse columns, here we not have any goal set column which can gave us the value on which we can set KPI.
Now we create a "CALCULATED MEMBER"


When you create a KPI, you NEED one or more 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.

2. 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.
In Cube Designer, click the "Calculations" tab, and then click the "New Calculated Member" button. A new calculation form opens in the right pane.

- 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]
- Select measure group from "Parent hierarchy" drop down column.
- In "Expression" box set expression which will give us the profit percentage. Later this calculated member used for goal calculation.
  copy and paste below code in "Expression" box.
 ( [Measures].[Sales Amount] - [Measures].[Total Product Cost]) /
 [Measures].[Sales Amount]
- Process it.
- If you want to see the script for same, "script view" button.
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" measure.

3. Now we create KPI
- In cubeMove to the tab "KPI"
- Click on button "New KPI"
- Now if you see the window on right hand side
- Name: GIve any name for KPI
- Value Expression: An MDX expression that returns the KPIs actual value. 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. Add below in "Value Exression" box:

  [Measures].[profit]
 
  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.

- 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.
       
    case
    when [Dim Product].[Product Category Key]
        is [Dim Product].[Product Category Key].&[1]
            then .40
    when [Dim Product].[Product Category Key]
        is [Dim Product].[Product Category Key].&[3]
            then .20
    when [Dim Product].[Product Category Key]
        is [Dim Product].[Product Category Key].&[4]
    then .10
else .15
end
   
  where [Dim Product].[Product Category Key] = [Dim Product].[Product Category Key].&[1] is for bike, i just pick and drop product category MEMBER from left pane to GoalExpression pane box.

   
- The status expression: A status expression is 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.

  i use trafic light signal from here, You can select any other status signal type from drop down menu. Use below code in Status Expression box.
 
  case when  kpivalue("KPI") / kpigoal("KPI") >.60 then 1
when  kpivalue("KPI") / kpigoal("KPI") = .60 then 0
when kpivalue("KPI") / kpigoal("KPI") < .60 then -1
end
 
 

- The trend expression: A trend expression is 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.

 i use face as trend indicator here, if the profit is > .60 then smile is big.
case when  kpivalue("KPI") / kpigoal("KPI") >.60 then 1
when  kpivalue("KPI") / kpigoal("KPI") = .60 then 0
when kpivalue("KPI") / kpigoal("KPI") < .60 then -1
end


Note*  - In above code kpivalue("KPI") "KPI" is the name of KPI
   
4. Now process the KPI and Browse the same from button "Browse View"