Hi everyone, hope everyone is well. I am back doing a million different things for all those projects I work on. Recently I have been working with the Business Data Catalog (BDC), specifically for use with Oracle. I wanted to share with you how this works by using the BDC MetaMan tool. This is a great tool for generating the XML file(s) needed for the BDC. You can visit the website below to get a copy.
http://www.bdcmetaman.com/default.aspx
It comes in a developer version you can try for free or you could purchase the professional version, this would be my recommendation! J
So let’s look at how we can get Oracle data into MOSS2007. Firstly my environment is set up as one Virtual Machine with the following software:
1. Windows 2003 Standard R2
2. Active Directory
3. SQL Server 2005
4. Oracle Express 10g
5. Microsoft Office SharePoint Server 2007 (Enterprise)
6. Microsoft Office 2007 Enterprise
7. Visual Studio 2005
The Oracle server has a sample HR database that comes with it that I will use for this demonstration. So firstly let’s open up BDCMetaMan. To do this simply access the “BdcMetaMan.Application.exe” from the program files directory shown below.
Once this has loaded it should look as below:
Firstly you need to configure the output locations and the Shared Service Provider locations. To do this simply press the “Configuration” menu and select either of the following options:
If we select the “XML Output File” option first it should let you select the location for this file. This XML file is the physical file that you need to load into MOSS2007 after you have created it.
You must also now select the “MOSS SSP” option and change to suit your environment. In mine I have used a host header within a Web Application called “SSP.LABS.LOCAL” so my settings are as below:
Before we can continue we must select the output format from the dropdown box.
Now that we have saved these settings we can now connect to our database and start creating the output file. To start this press the “Connect to data source” button and select the relevant option that you need. In this demonstration we will use Oracle.
Once you have selected the Oracle option a dialog will appear asking for server details and security details as shown below:
My settings are shown below:
Once you have completed these details and press connect you should then be presented with something similar to the below:
Obviously for this demonstration there are not many tables at all. Now we have our Oracle tables exposed to us here we can now start to create the relevant connections. To do this, simply drag the relevant table from the left tree structure onto the design surface on the right.
Once it is dragged onto the design surface it should render the fields as a table.
Now if you take a look at the left structure at the bottom of the application you can see that it has started to build the required XML structure. These can be renamed to suit what you need for your own system.
To change the name of an item simply right click on an element and select the “Edit” option.
Once you select the “Edit” option you are presented with a dialog that asks for the following details:
To change any element of this simply overtype it or select a new value from the dropdown lists. My completed dialog is shown below:
Once you have saved this the left navigation should then display the new names. By default you will notice that by adding the table to the design surface that is generates a single method called “GetEMPLOYEES”. This is the base one that we will use but we also need a method that we search for values of the employees. This means we will need to add an ID Enumerator. To do this right click on the “GetEMPLOYEES” method and select the “Edit” option. The following window should appear:
You can see from the windows that we can simply press the “Add ID Enumerator” button and this will add the relevant method.
Once you have accepted this the window should then look as below:
What you will see here is the fields that are available for this method. By default an ID Enumerator only has the primary key field, but you can add more fields if you wish. I have added all the fields.
You will also see you can add actions that will appear on the item once it is exposed within MOSS2007. From the actions windows we can either create our own or add one of the predefined list shown below:
The actions are simple menu items that appear next to a selected field, similar to the menu that appears on a list item within MOSS2007. We will add a custom one that is basically the email one but we will add some different fields. To do this select the “Create Custom Action” button and complete the window as below:
Now that we have created the custom action we can now look at associating other tables with the employee list. To do this we will need to drag the relevant table to the design surface as shown before. For this demonstration we will use the “Departments” table.
Once we have this on the design surface we will also add an ID Enumerator method. The full method list should look as below:
Now we need to associate the two tables together so we can create almost like a master and detail view within MOSS2007. To do this simply right click on the “Employees” table and select the “Add Association” menu item.
It doesn’t matter which field you right click on for this as the next dialog window allows you to select the relevant fields. In the new windows you need to select the following:
1. Parent Table
2. Parent Field to use for Linking
3. Child Table
4. Child Field to use for Linking
My settings are shown below:
Once this is set your design surface should look as below:
Now we have created all the methods we need we can simply output this file and then import into MOSS2007. To do this press the little green arrow next to the output type item on the main screen. Once this outputted you will get prompted as below. Select the “NO” option.
Now we are ready to import this definition into MOSS2007. To do this simply select the “SSP Administration Site” tab, and then press the “Load SSP Administration Web Site”. The SSP Site should then load within the application.
Now we need to press the “Import Application Definition” from the Business Data Catalog menu items. The window should then load as below:
All we need to do is browse to our newly outputted file and press the import button. Once it has been imported it should then redirect you to the page to show you the relevant methods and fields you have access to. If you experience any errors on import, in my experience this is due to the tables not having corresponding primary and foreign keys within the tables of the database. So we have created our XML file, imported it and are now ready to use this inside MOSS. For the purposes of the demonstration I already have a definition that I will use. This definition uses the Job history and employee table instead of the departments table one. It will still give us the same effect.
So to continue we need to launch our portal and add some web parts to our page. In this demonstration I have created a new web part page and added this to the navigation. Once your page is loaded change it to “Edit” mode and select the “Add a Web Part” button. From the list select the “Business Data List” web part.
Once the web part is on the page, press the “Open Tool Pane” link. This will load the properties for the web part into the right hand pane. From the properties window press the address book icon next to the “type” field.
The following window will load.
In this demonstration I am selecting the “Job_History” method as the main table to expose first. Once this is done and you apply these changes it should render the web part as below:
As you can see it is really easy to expose database content through using the Business Data Catalog. Using BDCMetaMan is the easiest way to build all your methods, actions and all the interfaces you need for the Business Data Catalog. Go get a copy and give a try!! In the next post we will look at joining the data together within the MOSS2007 UI. 
