Project Server BI at SharePoint Saturday Houston

In Houston on May 7?  Come on out to SharePoint Saturday – probably one of the best SharePoint Saturdays in the nation if last year’s event was anything to judge it by. 

I’ll be dusting off my Project Server BI spiel – now with at least 10% more content.  If that’s not enough to draw you out, I believe it comes with a free lunch.

http://www.sharepointsaturday.org/houston/default.aspx

SPSHOU 2011 will be on 5.7.11

Project Server BI at SharePoint Saturday Houston

Taking Timephased Snapshots of Project Server Data

Ask Project Server implementers what the “killer app” for Project Server would be, and most would tell you it would be adding the ability to take timephased snapshots of project data.  Think of it….each week, your organization takes a snapshot of key project metrics, and adds those to a database, which may then be used as the backend for a reporting function.

image

I’ve long been an advocate of keeping an Excel spreadsheet in parallel with my project schedule.  Then, on each status update cycle, I add the key metrics to my spreadsheet.  It’s not that difficult, and requires a custom view or two within Project to facilitate the process,  This allows me to generate ad hoc reports as needed.  The challenge however is that the information is locked in an Excel spreadsheet and not easily accessible to the organization.

The enterprise-wide solution for accomplishing this is typically to implement some mechanism within the SQL database to take a snapshot of each project on a specific timed interval.  That requires some SQL wizardry to keep running, but is not overly complex.

One possible objection I might have to this approach however is that it assumes the project manager has updated the project schedule and published it prior to the deadline.  If the project manager misses the Friday deadline, and comes in on Saturday to update the project, that data does not make it into the snapshot.  I want a snapshot mechanism that is triggered by my own action.

My other concern would be that as a project manager, I often take that snapshot using a manual process, recognize potential discrepancies, go back into the schedule to update it, and then redo the snapshot.  So I want a mechanism where I can preview the results, and then override them as required.  SQL doesn’t quite provide that.

So what to do?  Well, as I was playing with External Content Types (ECT) for a totally different purpose, I realized that they may also be used to develop timephased snapshots.  Here’s how to do so:

Create the ECT

Follow the instructions I laid out in this post to create the ECT.  The main difference is that when it comes to the Read List operation, you need to add a wildcard filter on the ProjectName field.  Follow that post until you get to the section with text marked <<bookmark>> and insert the following instructions.

When creating the Read List operation, the first screen will ask for the Filter Configuration.  Select the ProjectName field, and add the wildcard operation as depicted below.

image

Click OK, finish configuring the Read List operation and click Save to deploy the updated ECT back to SharePoint.

Create the Custom List

We’ll now create a custom list to capture the project snapshots.  Navigate back to the PWA site and select the option to View All Site Content under the Site Actions menu.

image

Create a custom list.  I’ll call this list the Project Data Snapshot.

image

Once the list has been created, we need to configure the columns for the snapshot data.  Click on the List Settings under the List tab.

image

Select the option to create a new column.

image

In the screenshot below, I show how I configured the new column of the External Data type.  Note that I can select any of the fields from the ECT to include in the list.

image

Next, configure the Title field so it defaults to Project Snapshot.  Click on the Title column within the List Settings and set the default value to “Project Snapshot.”  You could probably make it calculate today’s date, or even use InfoPath to add a complicated function to determine the title.  Simple text will suffice for this example.

image

Let’s test to see if the list works.  Navigate back to the list and select the option to Add a New Item.

image

Type the project name into the Project Data field.  Click the first icon to resolve the name.  Alternately, click the second icon and search for the project name in the pick list that is displayed.  Click Save.

image

Confirm that the correct data is populated within the list.

image

Configure List Security

As discussed above, as a project manager, I would like to have the option of taking a snapshot, reviewing it, and then making edits to the project schedule before revising the snapshot.  As an administrator, I also want to make sure that each item is timestamped and cannot be edited after creation.  How to resolve these concerns?  Let’s set security on the list so users may create and delete, but not edit.  In essence, we lock down each line of data, but allow the PM to potentially replace one with a more recent snapshot.

As an administrator, I can create alerts on each of these activities to notify me of potential issues or users in need of supplemental training.

Go back into List Settings.  Click on the Permissions option.

image

Select the option to Stop Inheriting Permissions from the Site.

image

…now go back to Project Web App.  Select Site Settings under the Site Actions menu.

image

Within the Site Settings, select the option to set Site Permissions.

image

Click on the permission levels option.

image

Select the option to create a new permission level.

image

Give the permission set Add, Delete and View permissions, but not Edit.

image

Go back to the List permissions under List Settings and add the users to the list with the new permission set.  Actually, from a best practices standpoint, add the users to a security group, and then add the group to the list with the appropriate permissions.

image

….you now have a list for taking timephased snapshots of project data.  Each snapshot is time stamped, tagged with the project unique ID field and is 100% controllable by the project manager.

Taking Timephased Snapshots of Project Server Data

Customizing External Lists of Project Data

In my previous post, I talked about using External Content Types to generate an External List within SharePoint 2010.  The main question, as always, is why would you want to do this?

….a couple of reasons:

  1. Your end users are used to SharePoint lists for managing projects.  I see this more and more in companies, where projects are managed in a list of projects.
  2. You desire to give end users permissions to create and save customized Project Center views.  With External Lists, your users gain the ability to create personal views of Project Server data.
  3. The organization requires the capability to create dynamic views of project data to, for instance, display all projects that have not been updated in the last two weeks.
  4. The Project Server data must be searchable within the SharePoint search engine.
  5. Specific fields must be totaled within the project portfolio (as of this writing, the Project Center view only totals free number entry fields, but will not total calculated fields.)

…and last but not least, it’s actually pretty easy.  I would contend that using External Lists is easier than developing a custom SSRS report – and gives your end users much more control over their individual user experience.

image

…so let’s walk through a couple of use cases….

Creating Personalized Views

Assuming permissions have been set appropriately, users may click on the Create View option under the Lists tab.

image

This opens a screen to configure the view.  Users may set the view to “personal” at the top of the page.  This means that others will not have access to the same view.

image

The user may now configure the specific fields, totals, grouping, filters and sorting as per individualized requirements.

image

The results appear as follow:

SNAGHTML1a60536

Creating Dynamic Filters

If you hang out on the Microsoft Project newsgroups long enough, this question invariably pops up….”How do I create a view that shows all projects that should have completed within the last week?” 

That question can be answered within Project Center, but it requires a little more work from the end user.   The challenge with using Project Center to answer this question is that the fields within Project Center are not dynamically recalculated whenever the view is refreshed.  To answer the question above, typically you could employ two options:

  1. Expose the Finish date and sort the view in descending order by date.  Then rely on the user to navigate through the list to identify the projects with Finish dates prior to today’s date.  Users may also manually add a filter to that column, but will have to revise the filter the next time the same query is run as it does not automatically update to today’s date.
  2. The “easier” method would be to create a custom enterprise field that flags a project with a slipped Finish date as “Yes.”  Then create a Project Center view that filters on the custom field.  This is easier for the casual end user as the work is done for them, but assumes the project manager has opened the schedule recently, recalculated fields, and republished the schedule.  Otherwise, that field will not be recalculated.  Since we’re looking for projects that may not have been updated, this technique is less than ideal.

To solve the challenge with External Lists is a much simpler story.  Simply select the option to edit the view under the List tab.  Add a filter with the following syntax to find projects that should have completed by today’s date:

image

If I wish to see all projects that have not been updated in two weeks, I might use the following filter.

image

External Lists provide another powerful tool for individualized reporting on Project Server data.

….next up…..using Enterprise Content Types to take timephased snapshots of project data – the Holy Grail of Project server killer apps.

Customizing External Lists of Project Data

Using External Content Types with Project Server 2010

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.

image

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.

image

Click on the option to create a new ECT.

image

The following screen will be displayed.

image

Enter the name of the new ECT and click on the link to configure the data source.

image

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.

image

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.

image

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.

image

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.

image

Click on Business Connectivity Services and then click on the new ECT.

image

Click on the button to set the Object Permissions.

image

Add the appropriate users or AD Group to the appropriate permissions.

image

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.

image

Create a new External List.

image

Add the ECT to the External List.

image

The External List should now render a complete list of all projects within the system.

image

When clicking on an individual item, the information from the list is displayed in an easy to use format.

image

Stay tuned as I explore potential use cases for ECT in the next couple of posts.

Using External Content Types with Project Server 2010