Check out my new blog at

Friday, February 22, 2008

Microsoft Project 2003 to 2007 Migration Workbench Part I

We are moving from a rudimentary Microsoft Project 2003 implementation to a Microsoft Project 2007 implementation complete with an enterprise resource pool and full use of Project Web Access.  I say our Microsoft Project 2003 implementation is rudimentary because we manage all our activities through a single MPP file stored on a network share.  A small team using Microsoft Project 2003 Professional makes changes to the MPP file and periodically, using a variety of techniques and technologies, project information is published to the appropriate systems.

I cannot go into too much detail about what we use Microsoft Project for and why, but I will say that we currently manage close to 5,000 activities.  This singular MPP file houses a variety of activity types including project and non-project work, as well as personal time off, holidays, and other slices that consume resource availability.

With Microsoft Project 2007, we are moving to a multi-project scheme and resource availability will be housed in the enterprise resource pool.  It is possible to use a shared resource pool or the enterprise resource pool in previous versions of Microsoft Project but for historical reasons, we have not done this.

We use custom fields extensively to store task-specific information.  In particular, we have an Event Type that indicates the type of event or activity on a per-task basis.  A event type might be a holiday, personal time off, internal project work, or any number of activity types that model our most fundamental business activities such as holding a class or delivering an e-learning session.  We use 27 of the 30 available local custom text fields, a few flags, and a few numeric custom fields.

I have been tasked with migrating our existing activities into Microsoft Project 2007 in a fashion that looks ahead to how we will manage our business further down the road but gets us migrated relatively quickly.   Most critically, we need to get our project information into our data center and globally accessible via Project Web Access.  A quick sketch of the requirements looks like this:

  1. Migrate from Microsoft Project 2003 to Microsoft Project 2007
  2. 100% fidelity in data migration
  3. Move from local resources to enterprise resources
  4. Automatically move custom fields from the MPP into enterprise custom fields in 2007
  5. Using lookup tables wherever possible
  6. Unpack custom field values in the MPP and push them into the appropriate enterprise custom fields' lookup tables
  7. Migrate activities to the appropriate project dependant on Event Type (instead of a single project there are multiple projects containing like activities)
  8. The migration method must be something we can back out of if it looks like there are issues

The first step was to figure out the natural partitions in our 5,000 activities and create projects along those lines.  Second was to implement all of the custom fields we require (there are quite a few) but leave the associated lookup tables empty.  My intention was to automatically add items to the lookup tables during a migration pass (I call it a "pass" because the scheme I came up with allows the old system to stay in place and functioning, so many "passes" will occur right up to the last day). 

Once the projects were created and the enterprise custom fields defined, it was time to start building something to process the MPP and migrate the data to the new system.  Again, keep in mind, that I needed to be able to take snapshot of our current MPP and merge it into the new system periodically as we reviewed our requirements and experimented with PWA and the enterprise resource pool.

The figure below is the Migration Workbench, a .Net Windows application written in C# using Microsoft Visual Studio 2008 (icons came from FamFam).  The Migration Workbench makes heavy use of Microsoft Project 2007 PSI web services to do its work.


From the image, you can see that several "caches" and "maps" are loaded when the migration pass is kicked off.  First, I iterate through the resources in the MPP's local resource sheet and check to ensure a corresponding enterprise resource exists in Microsoft Project 2007.  If it doesn't, I create it and add the new RES_UID (Microsft Project 2007 uses UIDs (GUIDs) all over the place for unique identifiers which is just great) to an in-memory cache of the enterprise resource pool called the Resource Map).  The Resource Map looks like this:

private Dictionary<string, Guid> _resourceMap = new Dictionary<string, Guid>();

Where the dictionary key is the Microsoft Project Resource Name and the dictionary value is the corresponding resource guid.  Reading the enterprise resource pool is easy using the PSI.  As needed, I have implemented a C# class library that wraps the PSI web services and provides additional services where required.  A partial class diagram can be seen here.

The object model allows for calls like such as ProjectServer.Resources.GetMinimiumInformation() , which returns a ResourceDataSet containing the resource's name, type, and RES_UID:


GetResources is the actual call to the PSI resources web service:


Creating a resource (and adding authorization is also straightforward):


After caching resource pool information, I load the enterprise lookup tables into a local lookup table library.  My ProjectIntegration library provide the following call to load a lookup table into a library:

_projectServer.LookupTables.AddLookupTableToLibrary(_lookupTableLibrary, LookupTableGuids.City);


The following shows the detail behind the call.


I created several maps that relate structure and data in Microsoft Project 2003 with structure and data in 2007.  Each task in the 2003 MPP has an Event Type that corresponds to a separate project in 2007.  The Event Type Map maps a task's event type to a project GUID so I can write the task to the appropriate 2007 project.

Another map relates Project 2003 custom fields to Project 2007 enterprise custom fields.  This map is called the Field Map.  See the image below that shows the relationships between various custom fields and enterprise custom fields


I analyze each task in the MPP file to ensure that lookup table values exist in Project 2007 for each custom field before I create or update the task in Project 2007.  Remember that I maintain a local cache of each lookup table?  I also keep a very lightweight Value Cache for fast lookups of a value's corresponding Guid (used later on):


So, we have three tiers associated with a lookup table value:  first, look to the value cache to quickly find out the value's Guid.  If not found there, then go to the lookup table cache and look for it.  If found in the lookup table, then add it to the value cache and be on our merry way.  If it isn't located in either the value cache or the lookup table cache, then the value doesn't exist in the enterprise lookup table and must be added.   Keep in mind that the value cache doesn't contain any data and is created on the fly (this is why you see the call to __valueCache.ContainsKey(fieldGuid)--the value cache for the lookup table might not exist yet).

Also, this code is executing on its own thread.  Events are subscribed to and Form.Invoke is used to all back into the UI thread (that's what all the OnXXX) calls are.

 image   Adding a text value to an enterprise custom lookup table looks like this:


As we will see later on, I often add a lookup table value and immediately attempt to add or update a task's custom field data with the new lookup table value.  This was throwing CustomFieldInvalidUID errors so I had to implement a Wait operation by using the queue web service and calling GetJobCount on QueueMsgType.ReportingLookupTableSync (this seems to work, but I am not sure if there is a better queue message type to look for.

Okay, it is Friday and I have to get out of here!  More next week!

No comments :


Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.