External Content Types (ECT) are yet another function with SharePoint 2010 that allow users to create simple powerful solutions with Project Server data. ECTs allow users to pull data directly from the Reporting database and surface it within SharePoint lists. I plan to spend the next couple of posts talking about the possibilities of using ECT within a Project Server 2010 setting. In the meantime, however, this post is designed to address how to create the ECT itself.
First off, ECTs and the Business Connectivity Service that they reside upon are well documented throughout the Web. Feel free to plug those terms into your favorite search engine to identify a whole host of articles and posts on the topic. One of the main questions that comes up is often related to security and configuring the Secure Store Service to work with BCS, for which I will refer you to this blog on the topic: http://blogs.msdn.com/b/bcs/archive/2010/03/12/authenticating-to-your-external-system.aspx
Creating the ECT
To create the ECT, we need to open a non-PWA site within the Project Server farm. This site will serve as the repository for our ECTs – which may be consumed throughout Project Server. Generally, I’d recommend placing the ECT on the top level site within the PWA web application – or a subsite under PWA – although that recommendation is subject to review by a more technically savvy SharePoint expert. (If you’re planning a Project Server implementation, note that PWA should never really be provisioned as the top level site in a web app.)
For this example, I am using the Contoso demo image, and will use the http://intranet.contoso.com site to host my ECT. I open that site with SharePoint Designer, a free download. As you may have already realized, SharePoint Designer may not be used against the main PWA site. This is a restriction placed by Microsoft to prevent folks from inadvertently bringing the entire Project Server implementation to its knees using SharePoint Designer.
Once SharePoint Designer has opened the site, click on the menu item for External Content Types.
Click on the option to create a new ECT.
The following screen will be displayed.
Enter the name of the new ECT and click on the link to configure the data source.
Point to the Reporting database, and select the table with the appropriate fields. In this case, I will be using the MSP_EPMProject_UserView view as that includes almost all of the project level metadata.
We now need to define two operations: the Read List operation and the Read Item operation. Right click on the selected table to define those two operations. First we’ll create the Read Item operation. Follow the default settings until you get the option to select the fields returned by the operation.
Uncheck all of the fields, then gradually add the fields you would like included back into the ECT. Make a list of each field added, as the second operation must include exactly the same fields. Once the appropriate fields have been selected, click on the Finish button.
Right click on the table again and create the Read List operation. This will kick off by asking you to create a filter. Filters are not required, but may be very powerful. In later posts, I’ll address uses of the wildcard filter.
(For this reason, I’ll insert a <<boomark>> here to reference back to in future posts.)
For now, let’s skip the filter discussion and move on. In the next screen, select exactly the same fields as were configured in the Read Item operation.
After selecting Finish, click “Save” in SharePoint Designed to deploy the ECT back to SharePoint. Close SharePoint Designer.
Configuring ECT Security
If you didn’t review the blog post above, at this point, I would strongly recommend taking a look now: http://blogs.msdn.com/b/bcs/archive/2010/03/12/authenticating-to-your-external-system.aspx. After the ECT has been created, we need to configure security. Your best bet is to point it to the right Secure Store Service while creating it within SharePoint Designer.
The second option is to go into the Business Connectivity Service application within SharePoint Central Admin to configure security.
Click on Business Connectivity Services and then click on the new ECT.
Click on the button to set the Object Permissions.
Add the appropriate users or AD Group to the appropriate permissions.
Close Central Admin and navigate back to the main PWA site.
Validating the ECT
There are a couple of ways to determine if the ECT is now working properly. The easiest way is perhaps to create an External List. An External List is a SharePoint list that surfaces external data within the familiar interface of a list. I’ll be writing more about that topic in a future post.
In the meantime, go back to the main PWA site (or another subsite), and under the Site Actions menu, select the option to View All Site Content.
Create a new External List.
Add the ECT to the External List.
The External List should now render a complete list of all projects within the system.
When clicking on an individual item, the information from the list is displayed in an easy to use format.
Stay tuned as I explore potential use cases for ECT in the next couple of posts.