Create SQL Server Dashboard in SharePoint

Posted by: Pravinkumar Dabade , on 3/9/2011, in Category SharePoint
Views: 34821
Abstract: In this article, we will create a SharePoint dashboard using SQL Server Data Source. This Dashboard will contain KPIs and a Scorecard and we will deploy this dashboard to our SharePoint Site.

In this article, we will create a SharePoint dashboard using SQL Server Data Source. This Dashboard will contain KPIs and a Scorecard and we will deploy this dashboard to our SharePoint Site.

The steps to create a SharePoint Site using a Template Business Intelligence Center remains the same as shown in my previous article SharePoint Dashboard with Common Filters using PerformancePoint Services 2010. I also assume you have downloaded the Dashboard Designer tool at your end. If you have not yet done so, please check my previous article and check the initial steps.

This dashboard designer tool will help you to design the Data Sources, KPIs, Scorecards, Reports, Filters and Dashboards. The tool is shown below :

On the left hand section, you will see the Workspace Browser. In this window, you will see two settings:

1) Data Connections -

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

b. For our demonstration, we are going to use SQL Server

2) PerformancePoint Content –

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

For this demonstration, we are going to use SQL Server Table Data source. For doing so, we are going to use ‘vStoreWithDemographics’ view from SQL Server database ‘AdventureWorks2008’.

Now let’s create a Data source using our Dashboard Designer tool. For creating SQL Server Table Data source follow these steps :

Step 1: Right click to Data Connection > Click to New Data Source as shown below:

From ‘Select a Data Source Template’ window, make a choice of ‘SQL Server Table’ as shown below:

Rename this data source to ‘SQL DS. Now let’s configure the data source for fetching the data from SQL Server. In my case, SQL Server is installed on my local machine as shown below:

Once you configure the data source, test the data source by clicking ‘Test Data Source’ button shown in above figure and save the data source.

Step 2: Now let’s create a Scorecard and KPIs from our SQL Server Data source which we have created in the previous steps. To do this, follow the steps shown below:

Right click > PerformancePoint Content > New > Scorecard. From the Tabular category choose ‘SQL Server Table’ as shown below:

Step 3: Click the OK button. It will take you to the wizard. The first step is to choose the Data source. Now let’s make a choice of ‘SQL DS’ data source which we created a few steps back. Click on the ‘Next’ button.

Create Scorecard and KPI’s

Now let’s create a Scorecard and KPIs from our SQL Data source, which we just created in the above steps. To do this, follow these steps:

Step 1: Let’s add two KPIs as shown here:

Your wizard should look similar to the following:

Keep all the default options and complete the wizard. Rename your scorecard with ‘SQL Scorecard’.

Step 2: After these steps, we are going to modify our Scorecard. From the ‘Details’ window on the right hand side, collapse the dimensions and drag and drop ‘CountryRegionName’ dimension to ‘Yearly Sales’ and ‘Yearly Revenue’ KPIs as shown below:

Once you drag and drop the ‘CountryRegionName’ dimension, it will show you a dialog box of all the available countries. Make a choice of the countries ‘Canada’ and ‘United States’.

Step 3: Now go to the ‘Edit’ Ribbon and click on ‘Update’ button. Next step is to make the ‘YearOpened’ column from the data source as a ‘Time Dimension’. By default it is a ‘Fact’ colunm. To do this, open your ‘SQL DS’ data source’ and then click on ‘View’ tab as shown below:

Now look for a column ‘YearOpened’ and from the ‘Details’ window and choose the ‘TimeDimension’ from the column type dropdown list, as shown in the above diagram.

Open our scorecard and from the ‘Details’ window drag and drop ‘YearOpened’ dimension on the scorecard as shown below:

Once you drag and drop the YearOpened dimension, it will show you a dialog box of all the available years. Make a choice of year ‘2000’ and ‘2001’.

Step 4: Now let’s modify our KPIs as per our need. To do so, click on ‘Yearly Sales’ KPI from workspace browser:

On the right side, it will show you the 'Actual and Targets' of the ‘Yearly Sales’ KPI. From the ‘Target’ click on ‘AnnualSales (SQL DS)’ as shown below:

Doing so will now show you a ‘Dimensional Data source mapping’ dialog box. Click on ‘New Dimension Filter’ and make a choice of ‘YearOpened’ dimension from the list and choose the ‘default’ ‘1986’ as shown below:

Now repeat the above steps for ‘Yearly Revenue’ KPI for year ‘1990’. After these steps, your scorecard should look similar to the following:

Now you are ready with your Scorecard.

Create a Dashboard using Scorecard

Let’s create a Dashboard which will contain our scorecard. To create a Dashboard, follow the steps given below:

Right click PerformancePoint Content > New > Dashboard. Make a choice of ‘One Zone’ dashboard template from the pop-up dialog box and Drag and Drop SQL Scorecard from the ‘Details’ windows as below:

Now rename your dashboard ‘SQL Dashboard and Save it. Then deploy it to your SharePoint Site. After deploying it to SharePoint Site, it will look similar to the following

Conclusion

In this article, we have seen how to make use of SQL Server data for creating SQL Server Table Data Source in Dashboard Designer tool. We have also seen how to create KPIs, Scorecard and a Dashboard using SQL Server Data Source. For this demonstration, we have used SQL Server as a source of our data for creating our Dashboard.

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 Prakash on Thursday, February 23, 2012 3:19 AM
This is good but how do I create a new database from scratch. We currently do everything in excel and wait for feedback from all managers and then populate the excel spreadsheet. I essentially need to take our static info and covert it into a database.
Comment posted by Mo on Monday, March 26, 2012 5:59 AM
Excellent article showing very good details and screen shoots, thank you. One big question I have is what if I want to show only one country (Canada) and have a filter in the dashboard where the user can select a country from a pull down list and have the scorecard update once a country is selected?  So, from the filter, I selected Germany and the scorecard will show the German data. Can this be done using a SQL table datasource (I do not have analysis services) and if so please provide the deatiled steps as above.

Thank you.
Comment posted by Ranjan on Wednesday, August 1, 2012 10:18 AM
Thank you, It was helpfull
Comment posted by NC on Thursday, April 10, 2014 1:06 PM
Best Article on Dashboard creation!

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