Integrating External Data Sources in SharePoint

Many companies use SharePoint primarily for document collaboration, whether for simple sharing or in concert with workflows for document processing and approval. Sure, they use team calendars and other collaboration features, but their main use for SharePoint is as a document repository.

But document collaboration is just one of the struts in SharePoint's framework. SharePoint provides not only a rich portal environment, but also one with the capability to integrate with back-end systems for data rollup and publishing. Let's take a look at some examples of how SharePoint can help you display and manipulate external data sources.

Simple Data Connections
BMC Software's BMC Remedy Action Request System is a popular incident and task management system. My team relies on Remedy, at least in part, to service task requests and incidents in support of the applications and systems that we support. With the exception of approving change requests, the majority of my time isn't spent in Remedy because I don't actively work tickets. However, I do need to keep track of what's going on in the queue for ongoing tasks and incidents. That's where SharePoint comes into play.

Remedy uses a Microsoft SQL Server back-end database to store its data. Because SharePoint can connect to and query SQL Server databases, it's a relatively easy process to pull Remedy data about tasks and incidents into SharePoint. In this case, we pull those items from our team queue into our team SharePoint site. Thereafter, team members and managers can see what's going on in the queue at a glance without needing to open Remedy. The team site also rolls up task assignments from SharePoint, targeted to the current user. So, for example, when I visit the team site, I see the contents of the Remedy queue and a list of any SharePoint tasks assigned to me.

To integrate external data sources in SharePoint without the use of third-party add-ons or writing custom page code, you'll need Microsoft Office SharePoint Designer 2007. You'll also need to know the credentials you'll use to connect to the back-end database, as well as the schema of that database so that you can build appropriate queries to pull data from it. Finally, before you start connecting willy-nilly to every database in your environment, take performance into account, particularly when hitting critical back-end production systems. For example, we don't connect directly to our Remedy production instance; instead, we connect to a reporting server that is real time plus 15 minutes. We give up an acceptable amount of data currency to ensure that we aren't affecting the production Remedy system—which would make a lot of other teams mad at us!

To integrate external data, regardless of type, you need to create a data connection that defines how to connect to the external data source. In SharePoint Designer, open the page on which you want to display the data. Select Data View, Manage Data Sources to open the Data Source Library in the right pane. Expand the Database Connections node and click Connect to a database to open the Data Source Properties dialog box. Click Configure Database Connection to start the wizard of the same name.

In the wizard, you specify the database server name, provider type (in this case, the SQL Server provider), and the authentication credentials the connection will use, as Figure 1 shows. Based on these properties, SharePoint Designer builds a connection string to the database. If necessary, you can select the Use custom connection string check box to create your own connection string. When your connection is set, click Next to select the target database and the table or view from which your data will come. Click Finish to create the connection and return to the Data Source Properties dialog box.

Next, you need to specify the fields to be included in your query, along with filter and sort settings, if any. Click Fields to open the Displayed Fields dialog box. You can add or remove fields as needed, then click OK. Unless you want all records from the database, you need to set a filter, so click Filter, then click in the Filter Criteria dialog box to add a filter. In the example that Figure 2 shows, there are two filters: Assigned_Group Contains 'Collab' and Issue_Status Not Equal 'Closed.' These two filters give us a data set of all items assigned to our group that aren't closed (i.e., all open items for our team). If you want to sort the records, click Sort on the Data Source Properties dialog box, set the sort order, and click OK. Then click OK to close the Data Source Properties dialog box.

With the connection in place, you're ready to add a Data View Web Part that will use the data. Locate the cursor on the page where you want the Web part inserted. Choose Data View, Insert Data View. Next, we need to pull fields into the Web Part, so click the drop-down menu beside your newly created data source and choose Show Data to open the Data Source Details task pane. You should see a recordset with fields in the Data Source Details pane. Start by dragging one field to the Web Part, then click the Web Part to select it, click the small right arrow in the upper right corner of the Web Part, and click Edit Columns from the pop-up menu. Add fields and arrange their order as desired, then click OK.

At this point, you should see live records from the data set displayed in the Web Part. You can apply conditional formatting (such as highlighting the Issue_ID field in red for SEV1 incidents), specify how many records to display, enable sorting and filtering on column headers, and modify other properties.

Using Linked Data Sources
The previous example used a single data source—one SQL Server table. With SharePoint Designer, you can also link multiple data sources and display the results in a Data View Web Part. For example, you might display a couple of SQL Server database tables, or an XML file and a SQL Server database, or a couple of XML files, and so on.

When you link data sources in SharePoint Designer, you have two choices: merge or join. You would choose to merge the data when the data sources are similar in structure. For example, assume you have four inventory databases, one from each of your four warehouses. You want to display a combined view of the data in SharePoint, so you merge the data sources. You would join data sources when the data sources are dissimilar in schema but have a field in common. For example, you might use join for a customer database and an orders database on a common CustomerID field to show a list of customers and recent orders.

You start by defining the data connections for the multiple sources as described in the previous section. When the data connections are defined, expand the Linked sources node of the Data Source Library pane and click Create a new Linked Source. As Figure 3 shows, you choose the data sources in the resulting wizard; thereafter you'll choose whether they'll be merged or joined and select other options based on the data connection types. You have more options with databases than with other types of data.

The process for adding data to a page is much the same as for a single, nonlinked data source. You can drag fields to a Data View Web Part or click Insert Selected Fields As and choose a single item view or a multiple item view, as needed. For more information about creating and inserting linked data sources, search SharePoint Designer Help for (you guessed it) "linked data sources."

A key point to understand at this point is that you don't need Microsoft Office SharePoint Server (MOSS) 2007 to integrate back-end data sources. Everything we've covered so far can be accomplished with Windows SharePoint Services (WSS).

Using Excel Services
Excel Services is a feature that works only with MOSS 2007; it includes three components: Excel Calculation Services (ECS), Excel Web Access (EWA), and Excel Web Services (EWS). ECS loads the workbook, handles calculations, refreshes external data, and maintains sessions. EWA is a Web Part that enables interaction with the Excel data in SharePoint. EWS lets developers build custom applications that integrate with Excel workbooks. Excel Services is another tool that gives SharePoint a means to integrate external data, in this case from Microsoft Excel into SharePoint.

Consider an example: Your company uses a complex Excel workbook as a project management tracking tool to track key issues and milestones. Twice a week, 20 people have a conference call to review project status and discuss individual project items in the workbook. How do all of those participants get the updated version of the workbook each time? At the very least, the workbook should be uploaded to SharePoint so that each participant can download a copy for the meeting. More likely, the workbook is sent by email to each recipient, filling up the mail store and Inboxes alike. A much better solution would be to expose the spreadsheet in SharePoint through Excel Services.

In this scenario, the project manager uploads the workbook to a file server or to a SharePoint document library. Then, the project manager or a SharePoint administrator or developer creates a portal page or set of pages in SharePoint to expose the data from the workbook. Instead of passing workbooks around twice a week, or even downloading the workbook from SharePoint, participants can simply browse to the project portal page and view project status. Figure 4 shows an example of a project-tracking spreadsheet exposed with Excel Services.

In this situation, Excel Services clearly reduces the amount of data flowing through the company's email system. Perhaps more important, there is a single source of truth for the data—a single Excel workbook managed and updated by the project manager. No longer does each person have to worry about whether they have the most current version—they just need to visit the portal.

Using the BDC
The Business Data Catalog (BDC) is a set of components in MOSS that lets SharePoint integrate with a broad range of external data sources, including database applications, SAP, Siebel, and other line of business (LOB) applications. In effect, the BDC not only serves as the communicator between SharePoint and the external data system but also provides the components that display the data in SharePoint. Figure 5 shows a high-level example of BDC architecture (adapted from the MSDN website).

The BDC supports several mechanisms for retrieving data from back-end databases, including ADO.NET, OLEDB, and ODBC. The BDC also retrieves data from other systems that can expose their data through Web services. Creating a connection to an external data system isn't a point-and-click process with the BDC; it requires that you first describe the connection using metadata in an XML file called an application definition file (ADF). This process requires an understanding of the back-end data system's APIs and the content structure.

Although you can technically create a BDC connection to a back-end system using the ADF file and no custom coding, implementing a solution with the BDC isn't a trivial task—certainly not as trivial as connecting a Data View Web Part to a SQL Server database to pull fields from a table. I'm not trying to scare you away from using the BDC; just understand that the average SharePoint administrator might not have the background to establish such a connection and will likely need to work collaboratively with the team managing the backend systems.

When the connection is established between SharePoint and the back-end system, the data from that back-end system can be exposed in SharePoint using several mechanisms, not least of these being custom coding. However, SharePoint includes several Business Data Web Parts that let it display data using BDC connections without having to create custom code. These include:

In addition to using the Business Data Web Parts to display BDC data, you can also create a new column in a SharePoint list to display data. When you add the column, you specify the BDC entity and its related properties. Then, when you add a new item to the list, you pick the instance of the entity that you want to include in the list. SharePoint copies the data from the back-end system to the list. Because the actual data is copied to the list, rather than displayed as an external reference or link, you might occasionally need to refresh the data from the back-end system to the list. SharePoint provides a Refresh icon for the column name that you can click to return the data from the back-end system.

In addition to displaying data from back-end systems in SharePoint portals, you can use SharePoint enterprise search to crawl back-end systems and return search results from those systems. This process involves registering the data source with the BDC, defining the appropriate metadata properties, adding the content source in search, mapping crawled properties, and optionally creating a search scope or customized search pages specifically for the data. As with integrating external BDC data into a SharePoint portal, this process is certainly not point-and-click. Nevertheless, you can potentially incorporate search of your back-end systems into SharePoint without writing any custom code. This capability can have a significant impact by enabling users to search across not just SharePoint or file servers but also multiple back-end LOB systems within a unified search interface.

Pick the Best Method
You can see that SharePoint provides a rich framework for integrating external data sources, whether it's as simple as bubbling up some SQL Server data in a Data View Web Part or as complex as pulling in data from your SAP or other LOB systems using the BDC. Some integration efforts can be as easy as clicking through a few wizards in SharePoint Designer and others could potentially require a business analyst, XML guru, and one or more subject matter experts on the back-end system's APIs and data structure.

Regardless of the complexity, the first step, as in any development effort, is to clearly define the requirements. Having a clear understanding of what data you want to pull into SharePoint, how it needs to be displayed, and how users will interact with it will help you better plan the mechanics behind the scenes.