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.

Advertisements
Taking Timephased Snapshots of Project Server Data

3 thoughts on “Taking Timephased Snapshots of Project Server Data

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s