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).