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:
- Business Data Actions—displays a list of actions associated with items in the BDC
- Business Data Items—displays an item from a data source in the BDC
- Business Data Item Builder—passes a business data item to other Web Parts
- Business Data List—displays a list of items from a data source in the BDC
- Business Data Related List—displays a list of items from one or more parent items from a data source in the BDC
- Business Data Catalog Filter—filters the contents of connected Web Parts using a list of values from the BDC
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.
Register
