Using SQL Datafeed to Integrate Third-party Data into BrightWork

Peter Doyle
By | Updated January 3, 2021 | 5 min read

Business Intelligence (BI) is becoming a more and more popular concept that enables access to and analysis of information to improve and optimize decisions and performance. SharePoint is an ideal platform for BI because of its inherent collaboration features and also its ability to connect to other systems using “external lists”.

This article details how you can use BrightWork to connect to other systems for BI purposes by leveraging SharePoint technology.

The scenario covered here will use a BrightWork Reporting Engine feature called “SQL Datafeed” that will send BrightWork project data to SQL Tables thereby enabling it to connect to other systems e.g. ERP or CRM and then connect back into SharePoint as an external list using SharePoint’s Business Data Connectivity (BDC) feature.

This article contains the scripts and other collateral to get you started. The relevant scripts are indicated in each step and are available to download in the zip file at the end of the article.

 

Using SQL Datafeed to Integrate Third-party Data into BrightWork

1. Create a BrightWork Report Definition to Query the Project Data

It’s recommended to have a separate BrightWork report definition to query the source data that you want to feed to SQL tables. This is a standard Custom BrightWork Report Definition, located in the BrightWork Reporter Library and queries all list data (activities) in the Site Collection. It’s important to test this by running it as a report to ensure it captures all the data required.

Relevant file: SQL_AllWork.xml

Description: Upload this file to the BrightWork Reporter Library

 

 

2. Create a Table to host the data in SQL

The next step to create a dedicated Database and Tables in SQL to host the exported data from BrightWork. You can use your existing SQL Server that is hosting the SharePoint Config and Content DBs. Define the Columns in the Table to match the data being fed from BrightWork (Figure 2).

Relevant file: SQL Work Table.sql

Description: Use this SQL script to build a table that will correspond with the data being fed from BrightWork

 

 

SQL table

Figure 2 – SQL Table Design

 

 

3. Create a BrightWork Datafeed Manifest

In order for correct data to be mapped from your BrightWork Site Collection and fed into the corresponding SQL Table, a manifest file needs to be built. This is a script that will read the Report Definition defined in Step 1 and write the Data to a SQL Table built in Step 2. There are 3 main components to the script.

  1. Define the BrightWork Report and your BrightWork Site Collection

 

define your BrightWork collection

 

2. Define the SQL Server, Target Database, Table and Security

 

SQL

 

3.  Map the Source BrightWork Report Columns to the Target SQL Columns

target SQL columns

 

Relevant file: DatafeedManifest.xml

Description: Copy this script to the Drive:Program Files(x86)BrightWork15ExportReportToDbsample manifestsDataFeedManifest.xml

 

4. Create an External Content Type in SharePoint Designer to connect the table back into BrightWork

Once you have the BrightWork Data being fed into a SQL Table, you can connect this data back into SharePoint as an External List.

You can follow the same procedure outlined by Microsoft where you use SharePoint Designer to create and configure the external list as shown in Figure 3 below.

Ensure you set the UniqueID Column from SQL as the Primary Key when you configure the list. When the External List is configured you can create it on the root of your Site Collection and it should return all the records from the SQL Table (Figure 3).

 

SharePoint designer

Figure 3 – Configure an external list in SharePoint Designer

 

external list in SharePoint

Figure 4 – The External List in SharePoint

 

 

5. Report on the External List using BrightWork Reporter

Finally, you can use the BrightWork Reporting Engine again to report on the External List. This enabled you to connect the Project Information with other Systems (ERP, CRM etc.) and leverage BrightWork to create Dashboards from the data. Figure 5 shows an example of how the report would look.

However, you can use the other reports types (Chart/Gantt/Resource) to display the information.

 

external list

Figure 5 – BrightWork List Item Report on an External List

 

 

Relevant file: Cache_AllWork.xml

Description: Copy this file to the BrightWork Reporter Library and configure the Webpart to use this Report Definition.

 

Editor’s Note: This post was originally published in 2016 and has been updated for freshness, accuracy, and comprehensiveness.

 

Image credit 

Peter Doyle
Peter Doyle

Project Management Consultant, BrightWork

Read Full Bio
Don't forget to share this post!