Designing Dashboards and Scorecards using SharePoint PerformancePoint Services 2010 and SSAS – Part 1

Posted by: Pravinkumar Dabade , on 2/11/2011, in Category SharePoint
Views: 79615
Abstract: In this article series, we are going to see how to create KPIs (Key Performance Indicator), Scorecard, Reports (Chart Report and Grid Report) and Dashboard using SharePoint PerformancePoint Services 2010 Dashboard Designer Tool. For this demonstration, we will be using the sample data from SQL Server Analysis Services (SSAS). Once our Dashboard is ready, we will then deploy it to our SharePoint Site.

In this article series, we are going to see how to create KPIs (Key Performance Indicator), Scorecard, Reports (Chart Report and Grid Report) and Dashboard using SharePoint Performance Point Services Dashboard Designer Tool. For this demonstration, we will be using the sample data from SQL Server Analysis Services. Once our Dashboard is ready, we will then deploy it to our SharePoint Site.

This article series is divided in three different parts –

Part I – In the first part, we will see how to create a SharePoint Site using Business intelligence Center and design a Scorecard with KPIs using SQL Server Analysis Services (SSAS) Data Source.

Part II – In the second part, we will see how to create a Chart report and Grid Report using SQL Server Analysis Services Data Source. Update: Read Part II Designing Dashboards and Scorecards using SharePoint PerformancePoint Services 2010 and SSAS – Part 2

Part III – In the third part, we will see how to create a Dashboard and consolidate the Scorecard, Chart Report and Grid Report which we have designed in Part – I and Part – II. We will then deploy this Dashboard to a SharePoint Site.

So, let’s get started with Part I and see how to create a SharePoint Site using Business intelligence Center and design a Scorecard with KPIs using SQL Server Analysis Services (SSAS) Data Source.

We will first create a SharePoint Site using a Template Business Intelligence Center as shown in Step 1.

Step 1: Click on Start button and go to SharePoint 2010 Central Administrator tool as shown below –

sharepoint central administrator

This step will open a web site. From this website, click on ’Create site Collections’ from ‘Application Management’ group as shown below :

application management

This will open a Create site Page. From that page, choose a web application under which you want to create a site collection. Once you choose a Web Application, give the title to your site collection and choose a URL. Optionally you can write a site description as shown below:

site description

Now select a site template from Enterprise Group as shown below:

business intelligence center template

And at the end, provide your site collection administrator primary account with ‘Domain\Administrator’ , check the domain account and click OK as shown below:

site collection administrator

This will create a site collection as shown below:

site collection

Now click on the link and you will see your Business Intelligence center site as shown below :

business intelligence center site

Step 2 : Once your site is ready, browse the site and hover your mouse on the ‘Create Dashboard’ in right hand section. You will see the links as shown below –

create dashboard

Click on the link ‘Start using PerformancePoint Services’. This link will take you to a page where you can download a Dashboard Designer Tool by clicking a button ‘Run Dashboard Designer’ as shown below:

run dashboard designer

This will download a Dashboard Designer tool at your client side. This is a Click Once Application. This tool now will help you to design the Data Sources, KPIs, Scorecards, Reports, Filters and Dashboards. The tool looks sishown below:

dashboard designer tool

In the left hand section, you will see Workspace Browser. In this window, you will see the following –

1) Data Connections -

a. It is used for creating a connection with different types of data sources as shown below

data connections

b. For our demonstration, we are going to use a multidimensional Data Source that is Analysis Services.

2) PerformancePoint Content –

a. Using PerformancePoint Content, we can design KPIs, Filters, Reports, Dashboards, Indicators and Scorecards as shown below

performance point content

b. For our demonstration, we are going to design Scorecard, KPIs, Reports and Dashboard.

Step 3: For this demonstration, I am using a Pro-clarity Data warehouse database which is a demo database provided by Microsoft. This database was given by Microsoft when PerformancePoint Server 2007 was released in year 2007.

You can download this database from here

Restore the backup of the database with your SQL Server Analysis Services. Now let’s create a data connection for our PDW (Pro-clarity Data warehouse) database in our Dashboard Designer tool.

Step 4: Right click ‘Data Connections’ > Click on ‘New Data Source’ as shown below:

data connections new source

From ‘Select a Data Source Template’ window, make a choice of ‘Analysis Services’ and configure your PDW database as shown below:

pdw database

Once you configure the connection to your Analysis Services and Database PDW, choose a cube ‘Sales’ and then click the ‘Test Data Source’ button. You should see a Connection Successful window.

Now rename the data source by ‘PDW’ and save it.

Step 5: Now we are ready with our Analysis Services Data Source. Let’s create a Scorecard which will hold our KPIs as shown below :

KPIs to be created –

1) Sales Amount.

2) Days Late.

3) Gross Profit %.

Right click on ‘PerformancePoint Content’ > New > Scorecard as shown below:

performancepoint content score card

This will show a dialog box to select a template for a Scorecard, as shown below:

scorecard template

Make sure you select the Analysis Services template from Microsoft for our scorecard. We will use a Wizard option for our scorecard. This wizard will guide us to select a data source. The next step is to add a new KPI in our scorecard or select the existing KPIs from our data source (in case you have any). The steps are shown below :

Step 6: First select the PDW data source which we have created in our previous steps:

analysis services scorecard

Once you select the data source, click the Next button in the wizard. This will take you to a step where we can either import existing KPIs from our data source or we can create new KPIs. Choose ‘Create new KPIs from SQL Server Analysis Services Measures’ and click the Next button.

Now let’s add the three KPIs which we have listed above:

kpis

Your KPIs should look similar to the one shown below:

imported kpis

Step 7: Now click Next button until you reach the Finish wizard button. Keep all the default settings. Rename your Scorecard as ‘PDW Demo Scorecard’. You are now done with a basic scorecard with three KPIs. It should look like the one shown below:

pdw demo scorecard

Now let’s modify the scorecard by adding our Products and timeline. You will see a ‘Details’ window on your right hand side screen, as shown below:

details window

Step 8: Now expand the Dimensions node. You will see Product. From Product, drag and drop Family to Sales Amount, Days Late and Gross Profit % , as shown below:

scorecard dimensions

Once you drop a Product Family, you will get a popup window to choose your products. Make a choice of your products as shown below:

select members

Click OK. Now go to Edit Ribbon and click on the ‘Update’ button and your scorecard will display the data as shown below:

edit ribbon update

Step 9: Now let’s expand the Time node from Dimensions and select a Quarter. Drag and drop this on top of Actual and Target and make a choice of a specific time (say Q1-06, Q2-06) respectively as shown below:

actual target dimensions

Once you have dragged and dropped the Time dimension, go to Edit ribbon and click on the ‘Update’ button and your scorecard will look as shown below:

pdw demo scorecard with kpi dashboard

That’s all. Your scorecard is ready.

Conclusion

In this article, we have seen how to create a SharePoint site, KPIs and a Scorecard using PerformancePoint Services Dashboard Designer Tool. For this demonstration we have used Microsoft Multidimensional Data Source that is SQL Server Analysis Services Database. In the second part, we will extend this example and see how to create a Chart report and Grid Report using SQL Server Analysis Services (SSAS) Data Source.

Update: Part 2 of this article - Designing Dashboards and Scorecards using SharePoint PerformancePoint Services 2010 and SSAS – Part 2

Give me a +1 if you think it was a good article. Thanks!
Recommended Articles
Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar[attherate]gmail[dot]com


Page copy protected against web site content infringement by Copyscape


User Feedback
Comment posted by Syed on Saturday, February 12, 2011 1:41 AM
Hello Pravin,

  i wanted to know how do i create a connection with other data sources like Oracle 11G. Kindly Suggest. i'll wait for your reply.

thank you for any reply.
Syed.
Comment posted by Pravinkumar on Saturday, February 12, 2011 1:54 AM
Hi Syed,

You can not create a connection directly to Oracle. However, There are two ways of doing this.
1) You can use SQL Server Integration Services and get Oracle data into SQL Server. Then you can get connected with SQL Server Data Source.
2) you can create a Business Connectivity Service App and then you can take the Data from SharePoint List. This link may be helpful for you.

http://technet.microsoft.com/en-us/library/gg247614.aspx

Pravin
Comment posted by Wesley on Saturday, February 12, 2011 6:42 AM
Good article. I enjoyed reading it over a cup of coffee :)
Comment posted by Manish on Monday, February 14, 2011 3:04 AM
Very nice way of explanation, Keep it up Pravin.
Comment posted by Ranjani on Wednesday, April 6, 2011 8:07 AM
hi,

I created KPI using the sql server datasource . i want to know how to incorporate in scorecard and dashboard. my requirement is if a sql column is equal to another sql column then green indicator should be shown or else red if source is greater than target.
Comment posted by Ravi on Friday, April 8, 2011 5:38 AM
Hi Pravin,

Very good article. I have a Score Card created using SQL Server. The Aggregation column in data source is always creating KPI for only one row (either max, min,count, sum, statistical etc) but i want to compare each and every row of the sql (that is all 10 -15 rows available in the table ). Can you please let me know how this can be achieved.

thanks in advance
Comment posted by rasul on Friday, November 4, 2011 7:24 AM
hi,
while doing BI am getting error while connecting to datasource that This datasource cannot be used because peformance point services is not configured correctly.Additional details have been logged for your administrator. please send me the solution where i did mistake.
Comment posted by Wilson Fernandes on Sunday, December 11, 2011 9:49 PM
Hi Pravin, It is with reference to the Excel Services - Excel 2010 the Pivot features - what are the areas that are used in PPS?
Comment posted by Mikhail on Friday, March 2, 2012 6:44 AM
Would like to recommend HarePoint Analytics for SharePoint: the essential web-analytics solution for SharePoint-based sites and portals. More 70 reports, wide customization, great features.
Homepage: http://www.harepoint.com/Products/HarePointAnalyticsForSharePoint/Default.aspx
Comment posted by SafeStick on Thursday, March 22, 2012 6:13 AM
Hi Parvin,
How do you change row width in dashboard because i cant see my full data in dashboard frontend .
Thanx in advance
Comment posted by SafeStick on Thursday, March 22, 2012 6:22 AM
Hi Parvin,
How do you change row width in dashboard because i cant see my full data in dashboard frontend .
Thanx in advance
Comment posted by Manet on Saturday, May 26, 2012 4:53 AM
Hi Pravin, can you run analysis services referring to an excel sheet? I was tying to get the KPI's and  charts side by side with my data source from excel.
Comment posted by gurdeep kaur on Monday, August 6, 2012 4:32 AM
If i creates a dashboard by selecting option for design as 2 Rows which gives me as Top Row and Bottom Row for the new page in Dashboard
why we are unable to chnage the width for top row
Comment posted by Gurdeep Kaur on Monday, August 6, 2012 4:33 AM
Please reply of my above question as i am facing issues
Comment posted by Gurdeep Kaur on Monday, August 6, 2012 6:19 AM
Please reply of my above question as i am facing issues
Comment posted by Gurdeep Kaur on Monday, August 6, 2012 11:11 PM
Please reply of my above question as i am facing issues
Comment posted by can i create chart using sharepoint list on Thursday, August 23, 2012 11:46 AM
Can i create chart by using sharepoint list as datasource?
Comment posted by Suja on Friday, February 8, 2013 10:27 PM
Hi Pravin, Thanks a lot for this great article. I am basically a SharePoint developer. I have all data stored in SharePoint List. How can I create reports/dashboards using the data from different lists?
Comment posted by hung on Wednesday, March 20, 2013 5:46 AM
hi, i got error:

An error has occured attempting to contact the specified SharePoint site.  Please check your connection and settings and try again.

can you help me
Comment posted by Durga Ramesh on Saturday, December 7, 2013 5:25 AM
Hi Pravin,

Very good article. I have a Score Card created using SQL Server. The Aggregation column in data source is always creating KPI for only one row (either max, min,count, sum, statistical etc) but i want to compare each and every row of the sql (that is all 10 -15 rows available in the table ). Can you please let me know how this can be achieved.

thanks in advance
Comment posted by Neena on Sunday, February 2, 2014 12:32 PM
Great effort
Comment posted by Rajanikanth on Friday, February 21, 2014 12:09 AM
Nice article..

IN SP performance Point services, I am only able select the datasource at first on report creation. how to change  report datasource.

Post your comment
Name:  
E-mail: (Will not be displayed)
Comment:
Insert Cancel