Using BCS to report on Raiser's Edge information in SharePoint - Part I

Following up on our earlier post of Some Possibilities to Display Raiser's Edge data in SharePoint, I am going to turn my attention to the Business Connectivity Services (BCS) functionality.  BCS, formerly known as the Business Data Catalog, is intended to provide "out-of-the-box" integration capabilities with other data sources. This integration provides external data in a SharePoint list, with it looking and acting like a SharePoint list, even though the data continues to "live" in the external database.  Once this data is consumed in SharePoint, the end-user gets the benefit of SharePoint views, column sorting, filtering, plus much more.

The integration also means view, edit, add, and delete capabilities on these non-SharePoint datasources (think of SQL Server databases).  The Raiser's Edge runs as a SQL Server database, thus we can grab this information and leverage BCS to provide that information to our end-users.  This same concept can be applied to any other SQL Server database, but we will focus on The Raiser's Edge for this post.

Raiser's Edge Proposals shown in SharePoint via BCS

At first blush some may say, wow, that is pretty awesome.  Others, myself included, may become very wary of all of those capabilities.  For instance, I have found that most COTS solutions that leverage SQL Server have a highly normalized database with many relationships, or foreign keys, throughout the database tables.  So to pull information from the source database into a format that is consumable and makes sense to the end-user, views may need to be created that join these many tables to get at the actual values that are important (a text value instead of numeric ID for example).  When you think in these terms, the "View" capabilities are much more powerful than the Create, Update, and Delete (from CRUD).  I know there will be instances, probably in more homegrown databases designed to work with the SharePoint BCS, where all CRUD capabilities can be pretty powerful.

With all that being said, lets take the BCS for a spin to pull Raiser's Edge Proposal data into SharePoint so we can provide that information to our wider business community.  The first thing we need to do is to create a view in SQL Server to select the proposals from The Raiser's Edge. The Proposal table in the Raiser's Edge has lots of foreign keys (or references to other reference data or code table data) and alone is insufficient.  By creating a view in SQL Server we can join the Proposal table to these other tables (Tableentries, Campaigns, etc.) to display the textual values for these columns.

After setting up the view in SQL Server, we can open up SharePoint Designer to perform the next series of tasks.  External data is exposed via External Content Types in SharePoint.  We created a new external content type and named it REProposal.  After the initial creation (as seen below) we have a base external content type from which to expose this Raiser's Edge proposal information in SharePoint.  We still need to do some configuration of this external content type.

Now that we have the external content type defined, we need to configure the connection to the SQL Server Raiser's Edge database.  There are multiple connection types for the external content type, but I have chosen the SQL Server.  This connection type allows for the extraction of information directly from the SQL Server database.  After establishing the connection, we then have the Raiser's Edge tables and views, as seen at the right, available for selection.

Because I created the view that exposes the Raiser's Edge proposal information in the format that I want, I navigate to the view I created by expanding the Views folder.  When we find our view, we right-click on the view to see which operations we can then include in our external content type.  As I mentioned previously, we could have full CRUD capabilities but I believe this is not appropriate,  at least for what we are trying to accomplish today.  We need to select two operations: Read Item Operation and Read List Operation.  Read List provides the capabilities to pull the entire dataset and Read Item exposes an individual item in the list for view (such as an item dialog form to see the item's details).  These operations are seen at the left.  There are some further configurations that need to take place with the columns.

Now we have the external content type created, we must create a List with this external content type.  This option is available in the Ribbon of the external content type in SharePoint Designer.
Once the list is created, lets navigate to the list in SharePoint.  So I open Internet Explorer and navigate to the URL that the list should have been created at.  Uh-Oh.  Take a look at the right:  I must have forgot something, I am getting an "Access Denied" message when I was expecting the list of Raiser's Edge proposals. 

I forgot to set the permissions on the external content type in SharePoint Central Administration.  I forgot to navigate to the Application Management, BCS service application management section to set the permissions, as seen below on the external content type I created.

Now after setting the permissions, I am able to navigate to the list in SharePoint and I have my list of Raiser's Edge proposals.  I can now use this list as if it were a SharePoint list, even though the data itself resides in the Raiser's Edge SQL Server database.  I can create custom, filtered views, add a web part to a web page with a specific view of the RE Proposals, or do any number of things with the list of proposals, as if we were managing it in SharePoint.  This type of functionality can be created on many types of datasources, including other SQL Server databases.  Coming soon, another post that then takes this external list of data to show how it behaves like a normal SharePoint list.  Contact me at Tucamino Solutions if you need help leveraging this kind of functionality at your organization or want to find out how else we can help you.