Create SQL Server Dashboard in SharePoint

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.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

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