This is the first of my pre-TechEd 2008 postings – and you may already have seen one that Christophe posted a few days ago. This one is part of the same session I am doing with Christophe next Thursday afternoon (not Tuesday as I said earlier) in Orlando and the scenario here is accessing both AdventureWorks data (from the sample database) and Project Server 2007 data using the Business Data Catalog (BDC).
To connect the two sets of data I am using a Project level custom field to hold the Vendor ID, which is the same code used in AdventureWorks to identify vendors. More on this link in my next post where I have a database CLR Trigger in C# that updates Project Server with new vendors – and Christophe’s code mentioned above stops “unauthorized” users making changes to the specific lookup table.
A picture is certainly worth a thousand or more words – so here is a sample of the kind of this this scenario delivers. This is a web part page with a Business Data List and a Business Data Item part added. Selecting from the list allows display of more details of the specific project and associated vendor – as well as a few actions.
We can see details of the actual work and cost (from Project Server) and the vendor name, city and location (from AdventureWorks). The actions on the top web part allow us to search for the Project Owner on Live Search, Drill Down to Project on PWA, and finally to Map the Vendor Location using Live Maps – and here is what you get using that last option…
As you can see from the URL it is using data from the BDC as parameters in the search – the other actions use a similar process. In this example as the AdventureWorks street addresses are fictitious I just use the zip code and city.
I have attached the XML of the definition file, and you will need to make a few edits to fit your server names and you will also need to have a Project level Number custom field to hold the VendorID. I am pulling data from the reporting database views and cross joined to the AdventureWorks database as you will see from the XML.
When I started working with the BDC I found it easiest to look at the XML files from the AdventureWorks sample first, then also took a look at Christophe’s search example for project data and once I understood how it all worked it made it easier to work with the Catalog Definition Editor. The cool thong about the definition editor is that it allows you to execute the “finders” to ensure everything is working as planned.
and even the actions…
In the next week or two I aim to post a video walking through creation of some of this – but I am sure with the XML and looking at the other samples already out there you should soon be able to get this kind of thing working. As a final couple of ideas for you I also show a couple of list to which I have added a new column containing “Business Data” and then defined which elements I want to display. This then adds data from Project and AdventureWorks to list data!
First is a calendar list item
where my project meeting item has the actions available
including profile information
The same kind of thing can be surfaced in more regular custom lists…
and again all the actions are available for each project.
One thing to remember is that in this example I am just pulling data based on the account running the web application so you will want to consider security in more depth than I have. Using a web service instead of SQL as the datasource for the BDC may give you more control in this respect – although the standard PSI web services cannot be consumed directly. Finally the BDC is just surfacing the data – it isn’t replicated in any way and is not stored in the content database.