Using the BDC feature of MOSS 2007 to interact with information stored in external data sources, for example SQL Server and Oracle data bases - Part 2

Many enterprises manage a lot of information stored in data bases that are distinct that the ones used by SharePoint (SQL Server), for example internal applications like CRM’s, ERP’s or any other application developed by themselves, we could say “in house”. Now, we know that all the documents we upload to SharePoint Sites are stored in SQL Server. So, we have SharePoint data from one hand, and external information stored in other data sources on the other hand.Is there any chance to provide final users the possibility to have only one web site to work with SharePoint data and also to interact with information stored in external data sources, through the same SharePoint Site? Guess What!!, we have now the power of the Business Data Catalog, that allow us to interact with information stored in distinct sources and not necessarily need to be in SharePoint data bases. What’s the beauty of this, we can provide to our users the possibility to have one site (site collection) working with SharePoint data, from document libraries, lists and everything you know from SharePoint, but also with other kind of data…external data.This could help us:·         Reducing time developing custom applications to access data and interact with (this functionality it’s also to view the info stored in other sources, not to update or delete the info).·         Users could have only one point of access to interact with their info.·         Having the possibility to relate info stored in other sources with the data contained in our SharePoint Sites, how about that!·        

……What else can you think the BDC could help in your company?

Imagine that you have already your SharePoint Site created (using MOSS 2007). You can add a BDC Web Part (to know how to do this you can check my previous post about BDC) and connect it to your Business Data Definition Application previously created on the SharePoint Administration Central Web Page (The Business Data Definition Application was explained in my previous post).For this sample I used MOSS 2007 B2TR in Spanish (which the purpose to show SharePoint with Oracle capabilities to a client in Mexico City), but it works just great in English to. If I make a mistake in some Web Part name or something else at the moment of my translation I apologize…To take advantage of this post I’ll include some topics also related to BDC functionality:

1.       Connecting two BDC Web Parts to show SQL Server Information.

2.       The power of adding actions to BDC Application Definitions.3.       Connect BDC Web Part to show Oracle Information.4.       Integrate your SharePoint data with Information from external sources with your BDC Application Definitions5.      Searching data from External Sources through BDC Application Definitions. 

 

1. Connecting two BDC Web Parts to show SQL Server Information.

Once you’ve added the first BDC Web Part (BDC Web Part List), you can add another BDC (from many others), as the BDC Web Part Element, and connect them, so when you select one element from the BDC Web Part List, this will send the properties of it to the BDC Web Part Element Details to show for example its profile, or the details corresponding to that element.First, you’ll to need to connect both BDC Web Parts to the same BDC Application Definition.I have previously created two XML Application Definitions:

1.       The first one called “AdventureWorksSample”; this one connects to the AdventureWorks SQL Server database sample. You can find more inform about it here: http://msdn2.microsoft.com/en-us/library/ms519241.aspx)2.       The second one called “Oracle-BDC”; this one connects to an Oracle database: OracleDb.We can verify the Application Definition Section from the MOSS 2007 Shared Services Provider at the Central Administration Web Page. Well, we can continue…You can specify which fields from the XML Application definition will be showing the BDC Web Part Element as the details for the product specified.After that, you’ll only need to connect the BDC Web Part Element to the BDC Web Part List.In the next image you’ll see the Web Parts already added and connected. After this, I can select a Product from the List of the Web Part from the Left and automatically I’ll see the details (Profile) from that Product on the other Web Part, The BDC Web Part Element.This has been made with SQL Server 2000 but work exactly the same for SQL Server 2005.Ok, so far we’ve added two BDC Web Parts and connect them each other…No, I’m going to explain how to add interactivity with third applications, for example, email, CRM, ERP, etc… We can achieve this creating what is known as “Actions” for an XML Application Definition.But, how can we do that?

2. The power of adding actions to BDC Application Definitions.We can add interaction with other applications and our BDC, how? When we create the XML Application definition we can add by code or by the UI in the Central SharePoint Administration a specific action.For example, let’s review our XML Application Definition for SQL Server.There’s a section called Actions when we view the view the entity defined (Product). This image means that if the user clicks on some product it will load a little menu with two options:

1. to search on MSN the info provided of that Product previously selected (the Product name), and

2. The default action for every XML Application Definition, to see the profile details of the selected Product.Lest try on the web site… And if we click on the “Search on MSN” option, what we’ll see…And so on. You can add any interaction with in house web applications, all you need to do is specify in the Actions section from the XML Application definition section. For example we can add another action…and send Product ID and List Price to some application of the company so we could validate me other info…And after create a new Action, If you return to the SharePoint site when the BDC of that XML Application Definition is been used, you’ll see something like this…

3. Connect BDC Web Part to show Oracle Information.I’m going to add another pair of BDC Web Parts but know connected to an Oracle database. I previously install Oracle on the same server (for testing purpose), and create a database for this sample called “OracleDB”, I’ve also create an ODBC with the Oracle utilities and created a new XML Application definition added to the BDC XML applications directory (already mentioned in the article).You can see that we have a database with some info for this test…And our ODBC created with the Oracle utilities…Ok so once we have tested a successfully connection and everything work fine…We can construct our XML App def to connect and interact with Oracle. Finally upload that XML To have this working properly you’ll have to configure Single Sign-On on your MOSS Server (and in any server running Excel Services with other data sources not SQL, and in your index server in case you have more than one boxes), to do that you could go to the properly TechNet documentation (http://technet2.microsoft.com/Office/en-us/library/841080ca-3e3b-4dbc-a081-43c29c76b3551033.mspx?mfr=true).And check this out to…http://msdn2.microsoft.com/en-us/library/aa673236.aspx Information about Oracle and BDC in MSDN. You can also check other interesting Blogs about this:http://planetmoss.blogspot.com/2006/11/using-single-signon-with-database.htmlhttp://www.xmilk.com/Default.aspx?tabid=36&EntryID=15Well, we’ll continue…If we review the properties of our Depto entity defined in the XML App Def (we’ve another one for the BDC Web Parts that use SQL Server connection).We can see that we have and action already defined (see profile) and some fields or entities to interact with like DeptNo, DName and LOC as location…Ok, well next step, we should add the BDC web Parts to connect to Oracle data base…Once added to the WebPage on design Mode, we can specify the Application to use; in this case we will use OracleDb. And now, we can configure with a specific query… Finally we can interact with Oracle info though a SharePoint Site. We can add another Web Part and link it to the first added and see details to the item selected. Finally have something like this…4. Integrate your SharePoint data with Information from external sources with your BDC Application Definitions Ok, until know we’ve added Oracle interaction through your SharePoint 2007 Site (using a XML Application Definition that specifies the parameters needed for an Oracle connection). Know let see how we can add value relation the data source info with info from our portal into a document library. If we want to add relation with info stored in the data sources, we need to add “Business Data columns”. What should we do if we need to add some information related to the customer?When creating the new column based on Business Data Catalog, we’ll select know the Customer entity (also defined in the XML Application Definition that has the SQL Server connection). And you could specify the name of that column (formed by the distinct fields of the databases specified to interact with users). You finally will have a doc library with rows as info with SharePoint sites and info stored physically in external data sources, but for users will be transparent. And as you can see in the previous image, I add an action (like for example sending an email to some customer).  And this can be done with any XML App definition configured in SharePoint 2007, no matter what data source you use, SQL, Oracle… 5. Searching data from External Sources through BDC Application Definitions.How can I search not only SharePoint content but also data stored in external data sources, like SQL Server or Oracle?Well, we need to add to our XML App Definition some kind of Method called “IdEnumerators”, this kind of data tells SharePoint what info to index and to be ready for search…In this post I’m not going to show how to do that, for that you can check this web page from TechNet http://msdn2.microsoft.com/en-us/library/ms577629.aspxTo add an IdEnumerator Method is not the only thing you can do with your XML Application Definition, and for construct your XMl you can use certain cool tools that some MVPs and SharePoint Lovers have been working on…(I apologize If I forget someone, please ping me If I’m letting someone out, so I can add him here).

The BDC Meta Data Manager by Todd Bagisnki and Nick Swan

So If we would like to search from a SharePoint Site, to a specific information in SQL Server (to have the possibility to search through a BDC some other steps you need to do, not specify in this article, but you can also check this web reference in TechNet http://msdn2.microsoft.com/en-us/library/ms495033.aspx).For this example I have only specify to crawl the Product Name and the Product Number. So If I want to look for certain Product let say, Lock Nut. And click on Search button or enter I’ll have the search results from that query, and as I previously defined in my XML Application Definition, I can search in this sample by Product Number, let’s say I want to search for LN-1224 that corresponds to the Product Name: Lock Nut 7 Specifying the query…and execute it And we’ll have the information we need to interact with…This Search Sample was made creating a new Site Collection with the Portal Corporate Site (Intranet), and using the Search Page of that Site Definition Template. Know it’s your turn to imagine the possibilities of this…Have a great SharePointing with the Business Data Catalog Feature!!!Thanks to Todd Baginski [a fellow SharePoint MVP] for the tips…

For more information also visit SharePoint 2007: BDC - The Business Data Catalog and SharePoint 2007 Shared Services Provider User Profile Importer

Finally, If you'ld like to download the complete post with images included you can do it click the link bellow and save the document.

Enjoy!

---

Posted 12-22-2006 4:36 AM by Luis Du Solier G.