In today’s world, information is scattered across multiple environments. Amidst the fast-paced change of digital transformation and the world at large, external data is likely to hold the insights one needs to understand their environment and navigate the market of tomorrow. It is now imperative for the decision makers of a company to have access to information which resides in different applications.
With the move to connect all the information into one single enterprise platform such as SharePoint, the question most often asked is how external data can be integrated with it. As you use SharePoint products to help streamline your business activities, you may need to incorporate external data, such as databases, Line-of-Business applications, and Web services. Quite often this external data is difficult to access. There are also factors to consider before connecting to external systems, such as security, reliability and regulatory compliance.
How to Connect External Data Sources in SharePoint
External data is often business critical data which is present and managed by external systems like Siebel, Oracle and SAP. SharePoint can act as a single user interface to these external data assets providing a consolidated view, interface and functionality to end users. It has the capability to integrate with the external applications by using its built-in platform functionality such as Business Connectivity Services or the new REST Data Access services.
Let’s look at some of the ways to integrate external data with SharePoint:
1. Business Connectivity Services:
Business Connectivity Services (BCS) enables secure, efficient, read/write access to a wide variety of external data. This can be done by creating a comprehensive framework that provides standard user and programming interfaces. You can also create a wide range of business solutions, including no-code, SharePoint Composite solutions for simple to intermediate activities and code-based solutions for advanced needs.
The types of external data to which BCS can connect include:
- SQL Server Databases
- SAP Applications
- Web Services – including Windows Communication Foundation (WCF) Web Services
- SharePoint based web sites
- Third-party applications
An association is created using BCS, which allows mapping of fields from the external data source to columns in an external content type in SharePoint. External content types provide the potential for reading, writing, updating and deleting operations on the associated external data. Moreover, it allows for the external data to be displayed on pages using web parts and external lists that are based on external content types
BCS data can be surfaced within the SharePoint interface in a way that is completely transparent to end users, promoting usability and compliance. With full CRUD capabilities, multiple connectors shipping with the platform (Database, WebServices, WCF, .NET and custom connectors) and full lifecycle control by administrators, BCS offers a wealth of integration possibilities.
2. Representational State Transfer (REST):
The SharePoint REST service provides a way to access external data using a specially constructed URL. REST Services can be simply accessed using HTTP requests, utilizing standard HTTP verbs such as GET, PUT, DELETE and POST to perform CRUD operations against the service.
Figure 1. REST Mechanics
These interfaces allow the support of remote systems with SharePoint, allowing organizations to create custom interfaces to SharePoint or customizing existing systems to interact with the platform. A very common scenario is the ability to use SharePoint workflows or InfoPath forms to query an external system, and based on the result, perform an action within SharePoint.
3. Client Object Models:
Client Object Models allow developers to write and execute code from client machines utilizing the three client object model flavors. These object models are frequently used to integrate SharePoint and external systems. Users can access information from both SharePoint and external data assets using a single interface that does not necessarily have to be the SharePoint interface (think custom-built Web Applications for example).
The three Client Object Models are:
- SilverLight Client – Created specifically to use with Microsoft’s Silverlight technology, this Object Model provides asynchronous communication capabilities that is a part of the Silverlight framework.
- .NET Managed Clients – Allows remote access from .NET Managed clients such as console application, windows forms applications and others.
Figure 2. SharePoint 2013 Remote API Architecture
4. Third-Party Data Integration Tools:
There are various third-party tools available in the market that allow SharePoint to connect to external data such as SQL, Salesforce and Oracle by creating the required external content type. Two specific tools we have used are Layer2 and BCS Meta Man. They provide the features available in a SharePoint list for viewing your external data such as list views, search, forms, workflows and many others. As an example, one of our clients wanted the documents stored in their database to be available for viewing on their internal SharePoint site such that any updates made to them would automatically reflect on SharePoint. They also wanted the documents to be tagged in a way that they could be pulled on different pages based on their metadata fields.
As an example, one of our clients wanted the documents stored in an external application database (i.e., MRI) to be available for viewing on their internal SharePoint site such that any updates made to them would automatically reflect on SharePoint. They also wanted the documents to be tagged in a way that they could be pulled on different pages based on their metadata fields.
To accomplish this, we integrated their external data (or documents in this case) with SharePoint utilizing the Client Object Model. We created a custom console application in .Net C#. The data source was exposed to a set of APIs that could be consumed and called using a URL. The users can now read data from their external data source, MRI, without having to switch applications, and they can search for data in their external database using the SharePoint in-built search functionality
With a wide array of options available from both a platform and end–user perspective, it is interesting to see the approach that many organizations are starting to adopt. Which is the best solution for integrating external data with SharePoint? Here are some of the pros and cons of these approaches to help you determine the appropriate solution for based on your requirements.
- The Business Connectivity Services reduce the cost of setting up additional infrastructure and provides single sign-on experience to the users. It allows for no-code connectivity to external systems. However, it takes more time to query data from external source.
- The REST Services approach is generally faster and provide good performance and most developers consider it easier to work with. However, REST using HTTP constructs creates certain restrictions, specifically, the limitations of HTTP are shortcomings of REST architectural style.
- The Client Object Model approach does not require that you deploy code on the server. It is also relatively faster since the requests are processed in batches. But the results retrieved using COM are always security trimmed by default and their permissions cannot be elevated.
- Most of the third-party data integration tools provide all the SharePoint list features for the external data and are easy to set up. However, in order to use these tools, the company needs to buy a license for the product and have a special machine to host application.
This tip written by Abel Solutions Associate SharePoint Consultant Farha Akhter.