.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Sandeep Singh
Melody Anderson
Eminent IT

Home >> Articles >> SharePoint >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Key Performance Indicator using SSAS in SharePoint 2010

Posted By:Sunil Yadav       Posted Date: October 02, 2010    Points: 75    Category: SharePoint    URL: http://www.dotnetspark.com  

This article explains how to create a Key Performance Indicator(KPI) by using Sql Server Analysis Services(SSAS) and displaying the KPI in Sharepoint 2010.

Part 1: Creating a Key Performance Indicator(KPI) using Sql Server Analysis Services.

Step 1: Create a new Analysis Services Project.

Step 2: Select Data Sources.

Right Click on a Data Sources and select New Data Sources.

Step 3: Enter data source information

Select New from Data source Wizard and in the connection Manager enter Sql Server instance name and select database.

Data connection properties shown in the below figure.


Step 4: Select Impersonation Information.

Click next and select Use the Service account for Impersonation Information as Service Account.

Step 5: Create Data Source Views

Right click on Data source Views and select New Data Source View.

Select the existing data source from the relational data sources and click next.

Step 6: Select Tables and Views for the data source.

 Provide a name for data source view and click finish.

Below is the diagram from the Adventure Works DW2008 data source view.

Step 7: Create a Cube.

  To create a cube, right click on the Cubes and select New Cube.

Select Use existing tables for cube creation method and click next.

Select measures which are required for the cube. Here ideally you should select only required measures.

Figure below shows the dimensions. It is recommended to select only required dimensions. Click next to proceed.

Below the cube completion wizard, enter the cube name and click finish. Before finishing the wizard make sure you have checked all the structures required for the cube and click finish.

Below figure shows the structure we have created in the earlier steps.

Step 8:  Process and Deploy cube.

Process and deploy the cube on the server. Right click on the Adventure Works DW2008 Cube and select process as shown below.

Select run to start the processing the cube.

On successful completion of cube processing, close the wizard. Now we are done with creating a cube.

Step 9: Add Calculated Members

Add new calculated member which will be used for the setting up value expression and goal expression. Right click on the Script Organizer and select New Calculated Member.

Create a calculated member as "ResellerValueExpression" to define the Value expression in the KPI. In the expression drag the "Sales Amount" from Measures under calculation tools.

Similarly create another calculated member "SalesQuotaGoal" for defining the Goal expression in the KPI as shown below.

Step 10: Create a Dimension Structure

Create a dimension structure to display hierarchical data.

More info: http://technet.microsoft.com/en-us/library/ms174537%28SQL.90%29.aspx

Step 11: Create Key Performance Indictor (KPI)

To create a KPI right click in the KPI Organizer and select New KPI.

Enter the Name as "Reseller Revenue" and Drag the "ResellerValueExpression" from the measures group in the value expression.
Similarly drag the "SalesQuotaGoal" in the Goal Expression from the measures group.

Select status indicator icon and in the status expression enter the following MDX query.

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

Select the Trend indicator icon and enter the below MDX query in the Trend expression.

 When IsEmpty
  ([Dim Date].[Calendar Date].[Calendar Year],1,
  [Dim Date].[Calendar Date].CurrentMember))
 When  (
  KpiValue("Reseller Revenue") -
  (KpiValue("Reseller Revenue"),
  ([Dim Date].[Calendar Date].[Calendar Year],1,
  [Dim Date].[Calendar Date].CurrentMember))
  (KpiValue ("Reseller Revenue"),
  ([Dim Date].[Calendar Date].[Calendar Year],1,
  [Dim Date].[Calendar Date].CurrentMember)))
  Then 1
  KpiValue("Reseller Revenue") -
  (KpiValue ( "Reseller Revenue" ),
  ([Dim Date].[Calendar Date].[Calendar Year],1,
  [Dim Date].[Calendar Date].CurrentMember))
  (KpiValue("Reseller Revenue"),
  ([Dim Date].[Calendar Date].[Calendar Year],1,
  [Dim Date].[Calendar Date].CurrentMember)))
  Then -1
  Else 0
Browse the cube.

For more info about creating KPI in SSAS visit: http://msdn.microsoft.com/en-us/library/ms166869%28v=SQL.90%29.aspx

Part 2: Displaying KPI in SharePoint status list.

Step 1: Create Office Data Connection file

Create a Data Connection Library in SharePoint.

To display SSAS KPI in SharePoint, create and export a new data connection to SharePoint file using MS excel.
Open excel 2010 and on the Data tab expand the data from other sources and select "From Analysis Services".

Enter SSAS server name and Log on credentials from Data Connection Wizard.

Select database and Cube created earlier.

Enter file name or click browse to save file, description and check the "Always attempt to use this file to refresh data" checkbox. Click finish save data connection file to a specified location.

By default the Data connection file is store at %windir% \Users\sunil\Documents\My Data Sources
Click "Finish", followed by a "Web File Properties" and save it in order to publish the .odc file to a trusted data connection library on the SharePoint server. The path to the connection file is mentioned in the File Name.

The published data connection file is uploaded in SharePoint.

Step 2: Display KPI using WebPart

Add status list to display goals.

Open newly created status list. Click on New toolbar menu and select "SQL Server Analysis Services based Status Indicator".

Add new status list item by providing details such as Data Connection and KPI to display goals as shown below.

Select Reseller Revenue as KPI which we created using SSAS.

Indicator showing Goal and Value along with status.

Add a Status List WebPart on the page and fill in the details for KPI.

Status list showing indicator for year 2002.



Happy SharePointing!!!!!

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend