Overview
As per Microsoft, Business Data Catalog (BDC) is a “new business integration feature available in Microsoft Office Sharepoint Server 2007. It is a shared service and it enables MOSS 2007 to surface business data from back-end server applications without any coding.”
Why BDC?
All the data entered into the Sharepoint system gets stored in the content database of that particular Sharepoint application. So by default, a Sharepoint site will get information from its content database. But it is quite natural for an organization to have information stored in multiple databases by numerous applications throughout the organization. For example, inventory software stores its data in a database that is different from a CRM software storing data in a database. But people will find is extremely useful if provided with a space where they can have every bit of information from each and every department in the organization ranging from the up-to-minute logistics information to the up-to-date sales analysis. The BDC feature of Microsoft Office Sharepoint Server 2007 provides the needed business integration for integrating the line of business (LOB) applications with the Sharepoint.
What is BDC?
Business Data Catalog provides built in support for displaying data from two data sources.
· Databases
· Web Services
Microsoft promises a “no coding” approach for integrating LOB applications with Sharepoint. Business Data Catalog provides access to the data sources through a metadata model that provides a consistent and simplified client object model. So it is the metadata authors who describe the API of the business applications in xml. After this, the administrators will associate this metadata with the Sharepoint application after which the LOB data is available in the Sharepoint system.
Development Environment
The walkthrough below will guide in developing a simple BDC application in MOSS 2007. The following states the database and MOSS settings used by this walkthrough.
MOSS Settings
· Microsoft Office Sharepoint Server 2007 Enterprise Edition Installed.
o Standard Edition of MOSS 2007 does not have BDC feature
· Created a new web application named “KnowMax-1234” from Central Administration.
· Use the default Shared Services Provider (SSP) associated with the created web application or create a new SSP for the web application KnowMax-1234.
Database details of LOB application
· Database Server : TARGETMC-VM\SQLEXPRESS
· Database Name : Test
· Table Name : dbo.Product
· Snapshot of the LOB Database :
Walkthrough: Creating a product BDC from LOB database
Objective
The objective of this walkthrough is, “To access the LOB application’s data through BDC and display it in the Sharepoint site inside the Business Data Lists Webpart.” This Webpart will get the ‘ProductID’ as input from the user and gets the corresponding product details from the LOB database. The following is the snapshot of the output.
The “ProductID” is given as 1. And the product detail for this product id is obtained from the LOB database.
Authoring Metadata
Authoring the metadata forms the heart of the development process involved in creating the BDC application. Though there are many tools that promises to simplify this process, most of them are ambiguous or non freewares. Some of those tools are
· Microsoft Business Data Catalog definition editor
o Gets shipped with the Sharepoint 2007 SDK which is available free for download
· Metaman
o Metaman is supposedly good but it comes with a cost as Metaman is not a freeware
So avoiding all those ambiguities, let us start writing the BDC metadata in xml. To start of the process, open a notepad and follow the steps below
1. Since the metadata is an xml file, it starts off with the following lines just like all xml documents
<?xml version=“1.0“ encoding=“utf-8“ standalone=“yes“?>
2. The LobSystem tag defines the namespaces, the name of the LOB system, the type of the system (Database or Webservice) and the version of the system
<LobSystem xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance“ xsi:schemaLocation=“http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd“ Type=“Database“ Version=“1.0.0.0“ Name=“ProductBDC1“
xmlns=“http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog“>
3. Then create a new instance for this Lob system using the following lines
<LobSystemInstances>
<LobSystemInstance Name=“ProductBDC1“ >
4. Define the database, connection and authentication properties of the Lob system using the properties tag.
<Properties>
<Property Name=“rdbconnection Data Source“ Type=“System.String“>TARGETMC-VM\SQLEXPRESS</Property>
<Property Name=“rdbconnection Initial Catalog“ Type=“System.String“>Test</Property>
<Property Name=“rdbconnection Integrated Security“ Type=“System.String“>SSPI</Property>
<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 Pooling“ Type=“System.String“>false</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
5. Now, create an entity and define its properties
<Entities>
<Entity EstimatedInstanceCount=“100“ Name=“Product“>
<Properties>
<Property Name=“Name“ Type=“System.String“>Name</Property>
</Properties>
<Identifiers>
<Identifier Name=“ProductID“ TypeName=“System.String“ /></Identifiers>
These tags define the entity called Product and its two members, Name and ProductID.
6. There should be a method that gets the details of the entity based on the input parameters. The following tags define a method called “GetProducts” which has a query that gets the name and productid of the products entity based on the entered productid. Also the input parameter is defined using the parameter tag with Direction attribute as “In”. The output is the product entity having two members namely name and productid which is defined in the parameter tags with the Direction attribute as “Return”.
<Methods>
<Method Name=“GetProducts“>
<Properties>
<Property Name=“RdbCommandText“ Type=“System.String“>
SELECT ProductID, Name FROM Product WHERE ProductID = @ProductID
</Property>
<Property Name=“RdbCommandType“ Type=“System.Data.CommandType“>Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type=“Comparison“ Name=“ProductID“>
<Properties>
<Property Name=“Comparator“ Type=“System.String“>Equals</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction=“In“ Name=“@ProductID“>
<TypeDescriptor TypeName=“System.String“ IdentifierName=“ProductID“ AssociatedFilter=“ProductID“ Name=“ProductID“>
<DefaultValues>
<DefaultValue MethodInstanceName=“ProductFinderInstance“ Type=“System.String“ > 1 </DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction=“Return“ Name=“Products“>
<TypeDescriptor TypeName=“System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089“
IsCollection=“true“ Name=“ProductDataReader“>
<TypeDescriptors>
<TypeDescriptor TypeName=“System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089“
Name=“ProductDataRecord“>
<TypeDescriptors>
<TypeDescriptor TypeName=“System.String“ IdentifierName=“ProductID“ Name=“ProductID“>
<LocalizedDisplayNames>
<LocalizedDisplayName LCID=“1033“>ProductID</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName=“System.String“ Name=“Name“>
<LocalizedDisplayNames>
<LocalizedDisplayName LCID=“1033“>Name</LocalizedDisplayName>
</LocalizedDisplayNames>
<Properties>
<Property Name=“DisplayByDefault“ Type=“System.Boolean“>true</Property>
</Properties>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
7. Then create an instance for the “GetProducts” method like this.
<MethodInstances>
<MethodInstance Name=“ProductFinderInstance“ Type=“Finder“ ReturnParameterName=“Products“ />
</MethodInstances>
The complete metadata definition xml is as follows. Copy it into a notepad and save as “ProductBDC.xml”.
<?xml version=“1.0“ encoding=“utf-8“ standalone=“yes“?>
<LobSystem xmlns:xsi=“http://www.w3.org/2001/XMLSchema-instance“ xsi:schemaLocation=“http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd“ Type=“Database“ Version=“1.0.0.0“ Name=“ProductBDC1“ xmlns=“http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog“>
<LobSystemInstances>
<LobSystemInstance Name=“ProductBDC1“ >
<Properties>
<Property Name=“rdbconnection Data Source“ Type=“System.String“>TARGETMC-VM\SQLEXPRESS</Property>
<Property Name=“rdbconnection Initial Catalog“ Type=“System.String“>Test</Property>
<Property Name=“rdbconnection Integrated Security“ Type=“System.String“>SSPI</Property>
<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 Pooling“ Type=“System.String“>false</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity EstimatedInstanceCount=“100“ Name=“Product“>
<Properties>
<Property Name=“Name“ Type=“System.String“>Name</Property>
</Properties>
<Identifiers>
<Identifier Name=“ProductID“ TypeName=“System.String“ />
</Identifiers>
<Methods>
<Method Name=“GetProducts“>
<Properties>
<Property Name=“RdbCommandText“ Type=“System.String“>
SELECT ProductID, Name FROM Product WHERE ProductID = @ProductID
</Property>
<Property Name=“RdbCommandType“ Type=“System.Data.CommandType“>Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type=“Comparison“ Name=“ProductID“>
<Properties>
<Property Name=“Comparator“ Type=“System.String“>Equals</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction=“In“ Name=“@ProductID“>
<TypeDescriptor TypeName=“System.String“ IdentifierName=“ProductID“ AssociatedFilter=“ProductID“ Name=“ProductID“>
<DefaultValues>
<DefaultValue MethodInstanceName=“ProductFinderInstance“ Type=“System.String“ > 1 </DefaultValue>
</DefaultValues>
</TypeDescriptor>
</Parameter>
<Parameter Direction=“Return“ Name=“Products“>
<TypeDescriptor TypeName=“System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089“
IsCollection=“true“ Name=“ProductDataReader“>
<TypeDescriptors>
<TypeDescriptor TypeName=“System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089“
Name=“ProductDataRecord“>
<TypeDescriptors>
<TypeDescriptor TypeName=“System.String“ IdentifierName=“ProductID“ Name=“ProductID“>
<LocalizedDisplayNames>
<LocalizedDisplayName LCID=“1033“>ProductID</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName=“System.String“ Name=“Name“>
<LocalizedDisplayNames>
<LocalizedDisplayName LCID=“1033“>Name</LocalizedDisplayName>
</LocalizedDisplayNames>
<Properties>
<Property Name=“DisplayByDefault“ Type=“System.Boolean“>true</Property>
</Properties>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name=“ProductFinderInstance“ Type=“Finder“ ReturnParameterName=“Products“ />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>
After authoring the metadata for the Lob system, we have to just associate this with a web application to make use the BDC.
1. Go to the central administration and select your SSP.
2. Under Business Data Catalog, click Import application definition
3. Choose your metadata file and click “Import”
4. A warning message will come. Click “Ok” as of now.
5. You can see your imported application by clicking “View Application” under Business Data Catalog for your SSP
6. Now, go to your Sharepoint site and click “Edit Page” under Site Actions
7. Click “Add a Web Part”
8. Select the “Business Data List” Web Part under Business Data and click Add
9. In the newly added Business Data List Web Part click Edit and select Modify Shared Web Part
10. In the type textbox, click Browse
11. In the Business Data Type Picker dialog box, select your BDC file and click Ok.
12. Click Ok in the business data list properties window (screen shot in step 10.)
13. Now the Webpart will look like the following.
14. Enter 1 in the productid textbox and click “Retrieve Data”.
15. The product name associated with the product id in the LOB database gets retrieved like the following.