Microsoft Office SharePoint Services (MOSS) 2007 is Microsoft’s innovative tool that provides the infrastructure to advance collaboration and communication within organizations based on the smooth flow of documents and information.
The Business Data Catalog (BDC), a fresh approach in SharePoint 2007, ensures the integration between SharePoint and back-end systems such as databases as well as Line of Business (LOB) processes like SAP and Siebel applications.
There are two approaches for working with BDC: creating the XML schemas manually or employing SharePoint’s Object Model to programmatically interact with the LOB system.
Because the BDC definition is based in an XML schema, apart from general knowledge of XML, no specialized understanding of programming or software development is required to access data from databases to display in SharePoint. For the first time, in a relatively undemanding manner, database administrators are able to present information buried in tables and views in a web interface.
Likewise, SharePoint’s Model Object provides the tools to work programmatically with schemas, giving developers control over all aspects of the Data Catalog.
Various possibilities exist in the presentation of data in SharePoint; applying SharePoint default WebParts, employing customized ASPX, and utilizing web applications or WebParts. This enables companies to ensure that their business information is more accessible, thus saving money, time, and human resources.
Introduction to the Business Data Catalog and Its Integration Within MOSS
An Intranet is the ideal platform to share all types of information within a business or enterprise. But, information is not restricted to structured materials as in Microsoft Office files (Word documents, Excel spreadsheets) or non-structured data as in lists of any sort, but also information originating from back-end systems and applications. SharePoint is Microsoft’s Intranet solution, and until recently, it was only capable of conserving and organizing its own structured and non-structured materials. To display back-end information, it was necessary to make custom-built, specialized software.
The latest version of SharePoint, Microsoft Office SharePoint Server (MOSS), offers an effective way to unlock information contained in back-end and Line of Business systems using a metadata model known as the Business Data Catalog. Although only the most advanced and expensive version of SharePoint 2007 (MOSS Enterprise) is equipped to utilize the BDC, individuals interested in testing and experimenting with the product can download a 180-day trial version from the Microsoft site.
Data from business applications that is unlocked using the BDC can be employed directly in MOSS using its WebParts infrastructure, Lists, and Content Types utilized in the user’s profile or searched by the SharePoint search engine.
MOSS 2007 is delivered with five out-of-the-box WebParts to service the BDC, but other WebParts can be purpose-built using the SharePoint Object Model. The following are the standard WebParts:
- Business Data List WebPart: The principal WebPart to display information in the user interface of SharePoint. It provides a way to display a list with the elements found by the BDC and a means to apply filters and activate the BDC actions. Figure 1 illustrates an example of the WebPart displaying data from a BDC entity.
- Business Data Item WebPart: Exhibits the details of one element. It is possible to connect this WebPart to the List WebPart, allowing the details of the selected element to be displayed in the latter.
- Business Data Related List WebPart: Presents a sub-list of elements from a main List of the first WebPart. For example, to display all the orders for a selected client in the Business Data List WebPart
- Business Data Action WebPart: With this tool, actions can be configured to redirect to a related place (a site, for example) or to initialize an application. This WebPart displays the available actions of the BDC
- Business Data Item Builder WebPart: Applied to create a Business Data Item based in one value in the QueryString of the BDC definition
Figure 1: SharePoint page displaying data from a BDC (“AnimalsEntity List”)
After creating and installing the BDC definition, SharePoint Administrators can install and configure each of the WebParts in a SharePoint site, making the data visible to users. Users with appropriate rights inside a site page also can apply the WebParts to display BDC data individually or for a subset of users. WebParts are an integral ingredient of SharePoint and the application and configuration of the BDC WebParts is no different from the exercise of any other type of WebPart.
Architecture and XML Structure
The Business Data Catalog is a “shared service” of MOSS. Shared Service Providers (SSPs) is a novel element of MOSS 2007 and grants a set of services that can be centrally configured and used by all SharePoint instances in a server farm. The BDC definitions are saved in one of the SSP repositories, and, using DataBase ODBC, ADO.Net, or WebServices connections, query the back-end or LOB information.
The BDC definitions are saved as XML structures based in a metadata-schema in the eponymous repository of the SSP. The final XML definition file describes all the necessary elements to connect with the LOB system (type and localization of the provider, data about the DataBase servers, authorization and authentication), to consult it (queries or Stored Procedures, input parameters), definition of the subsystems (actions, associations), and some metadata to describe the BDC (name, title).
Figure 2: Metadata model of the BDC schema (Source: MOSS SDK)
The BDC does not copy the data from the business application, but captures the data directly each time it needs to be displayed. To avoid delays in rendering the data, SharePoint uses a software caching mechanism that is refreshed each minute: If the data has been changed, the cache is deleted and refilled; the consequence is that rapidly changing data is restricted to one-minute snapshots. As a counterbalance, by utilizing the BDC Object Model, the caching of parts of the BDC can be shut down programmatically, permitting no-caching and accelerating the data display (for fast-changing systems) or releasing server resources (for never-changing systems).
Creation of a BDC
No specialized skills or knowledge of programming are required to design a BDC definition; a basic knowledge of XML is all that is needed. The definition consists of a XML (ASCII) file that can be constructed with almost any standard program, including Notepad-like software. Likewise, no compilation is necessary and the registration in SharePoint can be done by using the system’s user interface. Although no programming knowledge is essential, a firm grasp of the back-end structure and an awareness of business requirements is indispensable: To illustrate relevant information, the BDC designer needs to totally understand the construction of the LOB system.
To illustrate this point, a Business Data Catalog will be defined for a simple DataBase of a hypothetical zoo. The schema of the DataBase is displayed in Figure 2 and consists of two linked tables (one for the animals and another of their perceived danger to humans).
Figure 3: DataBase schema for the sample BDC
The principal parts of the metadata schema include the following:
The LobSystem describes the data source and is an obligatory element of the XML file. One LobSystem object contains at least one instance, but if it is necessary to define different systems, more than one instance is allowed. The properties of the LobSystemInstance define the data provider as well as the connection configuration and security for the LOB system to be used. The LobSystem and LobSystemInstance in the sample are as follows:
<LobSystem xmlns_xsi="http://www.w3.org/2001/XMLSchema-instance" xsi_schemaLocation="http://schemas.microsoft.com/office/ 2006/03/BusinessDataCatalog BDCMetadata.XSD" Type="Database"/p> Version="220.127.116.11" Name="MyZooLOBSystem"> <Properties> <Property Name="WildcardCharacter" Type="System.String">% </Property> </Properties> <LobSystemInstances> <LobSystemInstance Name="MyZooInstance"> <Properties> <Property Name="DatabaseAccessProvider" Type="Microsoft.Office.Server. ApplicationRegistry.SystemSpecific.Db. DbAccessProvider">SqlServer</Property> <Property Name="AuthenticationMode" Type="Microsoft.Office.Server. ApplicationRegistry.SystemSpecific.Db. DbAuthenticationMode">PassThrough </Property> <Property Name="RdbConnection Data Source" Type="System.String">SQLServerName</Property> <Property Name="RdbConnection Initial Catalog" Type="System.String">MyZoo</Property> <Property Name="RdbConnection Integrated Security" Type="System.String">SSPI</Property> <Property Name="RdbConnection Pooling" Type="System.String">false</Property> </Properties> </LobSystemInstance> </LobSystemInstances>
An Entity is an element of the process. An Entity consists of Identifier, Methods and Actions. In the example, there is only one Entity (“AnimalsEntity”), and the Identifier identifies the DataBase key field (type and name) for the BDC. The following is a possibility:
<Entities> <Entity EstimatedInstanceCount="0" Name="AnimalsEntity"> <Identifiers> <Identifier Name="AnimalID" TypeName="System.Int32" /> </Identifiers>
Methods are the operations that allow the Entity to be realized in the application. In the example, this element suggests how dangerous the animal is perceived to be. The metadata of the Method delineates the operation to render (DataBase query, StoredProcedure, method of a WebService) as well as the parameters and filters as shown below:
<Methods> <Method Name="AnimalsMethod"> <Properties> <Property Name="RdbCommandText" Type="System.String"> SELECT Animals.AnimalID AS AnimalID, Animals.AnimalSurename + ' (' + Animals.AnimalFamilyname + ')' AS Animal, DangerousLevels.DangerousLevelDescription AS DangerousLevel FROM Animals INNER JOIN DangerousLevels ON Animals.AnimalDangerousLevel = DangerousLevels.DangerousLevelID WHERE (Animals.AnimalID>[email protected]) and (Animals.AnimalID<[email protected]) ORDER BY Animal </Property> <Property Name="RdbCommandType" Type="System.Data.CommandType, System.Data, Version=18.104.22.168, Culture=neutral, PublicKeyToken=b77a5c561934e089">Text</Property> </Properties> <FilterDescriptors> <FilterDescriptor Type="Comparison" Name="AnimalID" /> </FilterDescriptors> <Parameters> <Parameter Direction="In" Name="@GenMinAnimalID"> <TypeDescriptor TypeName="System.Int32" IdentifierName="AnimalID" AssociatedFilter="AnimalID" Name="AnimalID"> <DefaultValues> <DefaultValue MethodInstanceName="AnimalsFinder" Type="System.Int32">0</DefaultValue> <DefaultValue MethodInstanceName= "AnimalsSpecificFinder" Type="System.Int32">0</DefaultValue> </DefaultValues> </TypeDescriptor> </Parameter> <Parameter Direction="In" Name="@GenMaxAnimalID"> <TypeDescriptor TypeName="System.Int32" IdentifierName="AnimalID" AssociatedFilter="AnimalID" Name="AnimalID"> <DefaultValues> <DefaultValue MethodInstanceName="AnimalsFinder" Type="System.Int32">9999999 </DefaultValue> <DefaultValue MethodInstanceName= "AnimalsSpecificFinder" Type="System.Int32">9999999 </DefaultValue> </DefaultValues> </TypeDescriptor> </Parameter> <Parameter Direction="Return" Name="Animals"> <TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="AnimalsDataReader" IsCollection="true"> <TypeDescriptors> <TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="AnimalsDataRecord"> <TypeDescriptors> <TypeDescriptor TypeName="System.Int32" IdentifierName="AnimalID" Name="AnimalID" /> <TypeDescriptor TypeName="System.String" Name="Animal" /> <TypeDescriptor TypeName="System.String" Name="DangerousLevel" /> </TypeDescriptors> </TypeDescriptor> </TypeDescriptors> </TypeDescriptor> </Parameter> </Parameters> <MethodInstances> <MethodInstance Name="AnimalsFinder" Type="Finder" ReturnParameterName="Animals" ReturnTypeDescriptorName= "AnimalsDataReader" ReturnTypeDescriptorLevel="0" /> <MethodInstance Name="AnimalsSpecificFinder" Type="SpecificFinder" ReturnParameterName="Animals" ReturnTypeDescriptorName= "AnimalsDataReader" ReturnTypeDescriptorLevel="0" /> </MethodInstances> </Method> </Methods>
Actions provide an avenue to the original application or any chosen resource. The implementation of a BDC Action is fundamentally a HTML command and is available for simple tasks only: for example, redirection to a web page, to send an eail, and so forth. In the Action for the case in point, the user will be redirected to the site of the San Francisco Zoo (http://www.sfzoo.gob/). The code for the Action element is:
<Actions> <Action Position="1" IsOpenedInNewWindow="false" Url="http://www.sfzoo.gob" ImageUrl="/_layouts/images/addressbook.gif" Name="Go to San Francisco Zoo" DefaultDisplayName="Go to San Francisco Zoo"> <ActionParameters> <ActionParameter Index="0" Name="AnimalID" /> </ActionParameters> </Action> </Actions>
Associations reflect the relationship between Entities in the BDC. For instance, a hospital will have a series of “users”, each of which generates a string of medical records which in turn indicate recommended treatments. The Association correlates users with records and treatments.
Installation, Configuration, and Application of the BDC
After constructing the XML file, the next step is to introduce it into the MOSS environment. From the Central Administration of MOSS, proceed to the active Shared Services and apply the link “Import application definition” in the “Business Data Catalog” section. This page prepares a text box and “Browse” button to select the physical locale of the XML definition, selection boxes to choose whether the file will be the Model (definition of the BDC) or other Resources (pictures, localizations, and so on for a predefined BDC), and indicates the selection of properties and permissions to be imported with the definition.
After initializing the import, SharePoint will validate the XML file, and if something is amiss, an error message will indicate the type of problem and the line where it is located. When all systems have been approved, the definition is ready to be used and the properties can be viewed (or altered) in the respective pages inside the Central Administration.
Figure 4: SharePoint Central Administration in the configuration page of the sample BDC definition
To employ the Business Data Catalog, a SharePoint Administrator can position the default BDC WebParts in one or more pages in the portal to be viewed collectively or an individual user with the appropriate rights can employ the WebPart and pass access to a subset of users. To continue the implementation, proceed to the SharePoint page where the data will be displayed, go to “Site Actions,” .Edit page. and add the Business Data List WebPart in the chosen zone.
Carry on by opening the Tool Pane of the WebPart to configure it. Using the “Display” button, located next to the “Type” BDC, the installed Catalogs will materialize and one of them can be selected. The other options permit configuring the display of the WebPart; the input parameters (if there are any); finally, using the XSL editor, change the default SharePoint uses to display data.
Figure 5: BDC WebPart installed with the open Tool Pane to configure it
After applying the configuration, the data will be recovered from the back-end system and revealed in the page (refer to Figure 1 for the output of the example).
Other Possibilities of BDC Within MOSS
WebParts are not the only means of utilizing data from the BDC. As well as using SharePoint’s Object Model to programmatically build custom WebParts, pages, and applications, MOSS is able to utilize out-of-the-box BDC output to provide data to Profile DataBase and lists.
The SharePoint Profile DataBase is an internal repository to store general information about users. The data can be synchronized with Windows Active Directory (AD) and, using the Business Data Catalog, it can utilize external sources as complement to the DataBase. Imagine a scenario where a DataBase is implemented using the default data from AD (names, IDs, telephone numbers, email, and the like) and business data from SAP or PeopleSoft (class data for students in a university, client contact data from a CRM system, and so forth.). In the same way, the non-structured information contained in SharePoint Lists can be supplemented with external data.
Finally, the SharePoint Search Engine is able to index the external data registered in the BDC. This opens up the possibility to make a full-text search of back-end data and, because security is configured in the BDC definition, only users with appropriate rights may retrieve the required information.
As illustrated in the article, the Business Data Catalog system has many advantages. However, as with many systems, there also are drawbacks. In the case of BDC, there are two that stand out; the flow of information is only one-way (back-end system to MOSS), preventing modification of information directly from MOSS. Microsoft is working to make it a “two-lane highway” and has announced that this modification will be introduced in the next version of SharePoint. The second point of contention is that the metadata schema is difficult to implement manually, even with very simple systems such as those used in the example. Various companies are researching solutions such as the creation of wizards where an XML file can be generated WYSIWYG-style and the latest version of the MOSS SDK comes with a tool for the generation of schemas.
Microsoft Office SharePoint Server Business Data Catalog offers an elegant means of accessing information contained in external, back-end enterprise systems. After the construction of the XML definition and respective configuration, data is available for use in different forms within MOSS. The BDC represents a major improvement in how information can be employed in an effective and efficient manner to enhance business processes within an enterprise.
Download the Zoo XML file that accompanies this article here.
About the Author
Gustavo Velez is a MCSD Senior Application Developer for Winvision (http://www.winvision.nl), a Microsoft Gold Partner in the Netherlands. He has many years experience in developing Windows and Office applications, and more than five years of daily programming experience with SharePoint. The author’s articles, in English, Dutch, and Spanish, can be found in many of the leading trade magazines and he’s pleased to be Webmaster of http://www.gavd.net/servers, the only Spanish-language dedicated SharePoint site. Spanish-language readers may want to consult the author’s new book; .Programacisn con SharePoint 2007 (http://www.dotnetmania.com/Libros/index.html).