Revisiting Project Trend Analysis and Project Change Logs

May 17th, 2012

The question always arises as to how to best take a snapshot of project data, and use that in timephased reports or change logs.  In fact, by my count, this is the third time I’ve addressed this topic in this blog:

1. Using External Content Types and SharePoint lists.
2. Using VBA to store data in spare baseline fields.
3. Using VBA to store data in a custom SQL table (keep reading).

In this blog post, I’d like to take a different approach than in the past and show how to use VBA to post snapshots of project data to a SQL database.  The value of storing the data in a custom SQL table of course is that the data is available on the enterprise level, subject to enterprise retention policies, and available for use with enterprise reporting solutions.

Now, I could always use automatically generated queries.  In fact, it would be quite simple to set up a timed job that runs within SQL every Friday evening and takes a snapshot of project data.  As I’ve mentioned before, I am not a big fan of such automated queries – generally because I know a lot of project managers who like to catch up on work over the weekend – or who often will submit the numbers only to immediately realize that they forgot to totally update their schedule, make adjustments and then resubmit.  Running a timed snapshot process may not work well for folks like this.

My preference is for the project manager to manually trigger that snapshot in time as part of the routine status reporting cycle.  You could even incorporate some workflow or alerts around the data when it’s submitted as part of the report.

Creating the Table

In this case, I’ll just use some code that Calin, one of our developers created for me to support some EVM reporting.  This query will generate a table within a SQL database.   Generally, you would add this table to a custom database.

CREATE TABLE [dbo].[ProjectStatusSnapshots](
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [Date] [datetime] NOT NULL,
      [ProjectName] [nvarchar](500) NOT NULL,
      [ProjectUID] [uniqueidentifier] NULL,
      [ProjectStatusDate] [datetime] NULL,
      [ProjectACWP] [money] NULL,
      [ProjectBCWP] [money] NULL,
      [ProjectBCWS] [money] NULL,
      [ProjectEAC] [money] NULL,
      [ProjectCPI] [money] NULL,
      [ProjectSPI] [money] NULL,
      [ProjectTCPI] [money] NULL,
      [ProjectCost] [money] NULL,
    [ProjectActualCost]  [money] NULL,
      [ProjectBaseline0Cost] [money] NULL 
CONSTRAINT [PK_ProjectStatusSnapshots] PRIMARY KEY CLUSTERED 
(
      [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Modify the fields to fit your needs.  You might also consider creating two tables, one for project header information, and one for task information.

Confirm that we have a new table in SQL.

Adding the VBA

The VBA then is relatively simple.  We just need to create a bit of code to generate the database connections and then run a SQL update query to insert the right data into the database table.

Note that I am grabbing the ProjectUID as well.  This will facilitate reports that will join the trend analysis data with the default Project Server reporting database.

As I mentioned above, you may want to consider adding code to capture task level metrics for trend analysis.  That would pretty much be the same concept.  Just make sure to grab a unique identifier for the tasks to use in generating reports.  Using that technique and a field to capture the current user, you could effectively implement a change log – especially if you embed the VBA code in the Project OnSave activity.

Sub UpdateSQL()

    'This macro will log specific field data into a customized SQL database.

    'Create connection to SQL table.'

    Dim Conn As New ADODB.Connection

    Dim SQLInstance As String
    Dim SQLDatabase As String

    'Set the connection parameters here

    SQLInstance = "Demo\Demo" 'Enter the name of the SQL instance
    SQLDatabase = "zzz_TrendAnalysis" 'Enter the name of the SQL database

    Conn.ConnectionString = "Provider=sqloledb;" _
    & "Data Source=" & SQLInstance & ";" _
    & "Initial Catalog=" & SQLDatabase & ";" _
    & "Integrated Security=SSPI;"

    If Conn.State = 1 Then 'To avoid errors where the connection was opened and not closed
        Conn.Close
    End If

    'Open the connection and assign the data to the table.

    Conn.Open

    With ActiveProject.ProjectSummaryTask

    Conn.Execute ("INSERT INTO " & SQLDatabase & ".dbo.ProjectStatusSnapshots([date], ProjectUID, ProjectName, " _
    & "ProjectStatusDate, ProjectACWP, ProjectBCWP, ProjectBCWS, ProjectEAC, ProjectCPI, " _
    & "ProjectSPI, ProjectTCPI, ProjectCost, ProjectActualCost, ProjectBaseline0Cost) " _
    & "VALUES(GetDate(), " _
    & Chr(39) & ActiveProject.GetServerProjectGuid & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.Name & Chr(39) _
    & Chr(44) & Chr(39) & ActiveProject.StatusDate & Chr(39) _
    & Chr(44) & .ACWP _
    & Chr(44) & .BCWP _
    & Chr(44) & .BCWS _
    & Chr(44) & .EAC _
    & Chr(44) & .CPI _
    & Chr(44) & .SPI _
    & Chr(44) & .TCPI _
    & Chr(44) & .Cost _
    & Chr(44) & .ActualCost _
    & Chr(44) & .BaselineCost & ")")

    End With

    Conn.Close

    MsgBox "Project snapshot has been saved.", vbOKOnly, "Confirmation"

End Sub

Run the VBA a couple of times to confirm it’s generating data.

Once you’ve validated the VBA, consider adding it as a button to a customized ribbon toolbar.  This will give your users the ability to trigger the macro directly from the main Microsoft Project interface.

Retrieving the Data

From there, it’s just a simple question of retrieving the data.  As I mentioned above, I fully expect the PM to potentially create multiple snapshots at each status period, I may only want to grab the “final” one that was posted each day.  To do that, we just need to modify a basic SQL query.

Here’s an example:

SELECT dbo.ProjectStatusSnapshots.Id, dbo.ProjectStatusSnapshots.Date, dbo.ProjectStatusSnapshots.ProjectName, dbo.ProjectStatusSnapshots.ProjectUID, dbo.ProjectStatusSnapshots.ProjectStatusDate, dbo.ProjectStatusSnapshots.ProjectACWP, dbo.ProjectStatusSnapshots.ProjectBCWP, dbo.ProjectStatusSnapshots.ProjectBCWS, dbo.ProjectStatusSnapshots.ProjectEAC, dbo.ProjectStatusSnapshots.ProjectCPI, dbo.ProjectStatusSnapshots.ProjectSPI, dbo.ProjectStatusSnapshots.ProjectTCPI, dbo.ProjectStatusSnapshots.ProjectCost, dbo.ProjectStatusSnapshots.ProjectActualCost, dbo.ProjectStatusSnapshots.ProjectBaseline0Cost, DailyRecord.LastRecord FROM dbo.ProjectStatusSnapshots RIGHT OUTER JOIN (SELECT MAX(Id) AS LastRecord FROM dbo.ProjectStatusSnapshots AS ProjectStatusSnapshots_1 GROUP BY ProjectUID, CAST(Date AS Date)) AS DailyRecord ON dbo.ProjectStatusSnapshots.Id = DailyRecord.LastRecord

Run this query to generate a table of the last entry posted per project on any given day.  You could then use the ProjectUID field to join this date to live data in the Project Server reporting database.

Filed under: BI, Project Desktop, Reporting, VBA Tagged: Trend Analysis

Lost. How To Choose Who To Vote For?

May 17th, 2012

I am lost. Or confused. Or at least something non-expert-like.

In September we have new elections in The Netherlands. Our current ruling parties cannot work together anymore.

I always vote. I think it’s important. As years go by I find it harder to choose which party to vote for. And we have a lot of them.

This time, I really have no clue.

This bothers me.

As I remember it, I used to have only a couple of choices. And the parties I could choose from represented a social group. Factory workers, small business owners, religious folks. If you’re a member of a certain social group, you’re choice is almost already made.

Both my grandparents were small business owners and I can trace my early voting behavior back to this.

Every party also had a single story for how things should be run: let the free market do it’s thing, tax the rich folks and redistribute wealth evenly, give priority to families. Stuff like that.

Easy choice. Easy solutions.

I love to map stuff. I like to know where I am in the political landscape.

Now I have many parties to choose from. Now I am not sure which social group they represent. I can only see a very small amount of flags.

Even within parties I am now confronted with internal elections broadcasted on television. Who will be the frontrunner for this party? So I get to see all the many faces from within a party. Increasing my options.

Media is providing me with information about every single candidate. Conflicting information of course.

There are no single stories any more. Or easy solutions that resonate with me.

Is this good or bad?

Yes.

Is this what Barry Schwarz meant with the Paradox of Choice? If you have too much options to choose from, you run into the risk of paralysis and being unhappy about your final choice, because you keep comparing them to the alternatives.

Perhaps.

It can also be a personal thing.

Perhaps when I started to vote there actually were many parties, but my vision of the landscape was limited.

Perhaps there are parties now with single stories, but I just don’t want to hear them. (blaming immigrants for example).

Perhaps I am not sure which social group I belong to.

I am glad I have the right to vote.

I am glad I have still 3 months to narrow my selection.

Lost. How To Choose Who To Vote For? is a post from: Project Shrink.

Project 2010: Problems since the February CU if you have a semi-colon (;) as your list separator

May 16th, 2012

In the February Cumulative Update for Project 2010 we fixed an issue described as:

• You create an .mpp file that was saved from a Project server by using the Save for Sharing command. When you try to resave the .mpp file back to the Project server in Project 2010, the save process fails, and you receive the following error message:
• Project Server was unable to find the specified resource. If the problem continues, contact your server administrator.
• This issue occurs when the list separator character that is contained in resource names within the .mpp file on the client differs from the list separator character on the server.

We are now finding that in fixing this, we broke a couple of other things that you may be running into if you are using a list separator that is a semi-colon (;).  This is most likely in Europe or Canada, but I’m sure there are plenty of other places that could see this too.  The issues are all related and we are working on a fix for them all, but just wanted to share some workarounds in case you are hitting these.  The different scenarios I have seen so far are:

 

• Using Task Information dialog to remove or add resource assignments to a task
• Using the Assign Resources dialog to add multiple resource assignments
• Using the Task Information Dialog to set predecessors or successor information

*** Update - For Spanish readers - http://blogs.technet.com/b/elfarodeprojectserver/archive/2012/05/17/project-2010-problemas-desde-el-cu-de-febrero-2012-si-tenemos-un-punto-y-coma-como-separador-de-lista.aspx ***

So here are some examples of what can go wrong – and these examples need certain settings to be in place before you would ever see them, so don’t feel left out if you don’t experience any of these issues.

This first example assumes you have the semi-colon as list separator and also this is used in your resource names as a separator between first and last names.  So you have a task that is already assigned to Smith; Brian and Jenkins; Adrian, and you want to add Fiessinger; Christophe.  So initially your Task Information dialog looks like this:

Then you add Fiessinger; Christophe – and click OK – then you will see this:

That doesn’t look quite right?  Opening up the Task Information dialog again I see:

It has split each name in two, and created 6 new local resources – and assigned them.  A couple of things here – it will not lose actual work – any assignments that already have work will be OK – and will not get un-assigned – but the extra local resource will still get created.  Undo will also put things right.  The same thing can occur even if you are removing a resource using this same dialog.  If I removed Jenkins; Adrian, it would create Smith and  Brian as two local resources.  The workaround here is to use the Resource Names column in one of the views such as the Gantt view – and select/deselect from the drop down.

The second issue is with the Assign Resources dialog (which is why it isn’t a good workaround for the first issues) and it has a couple of different scenarios depending on your use of the list separator in the resource names.  If you do have the separator – like the example above – then you cannot assign from the Assign Resources dialog – the Assign button is disabled – as I show here.

 

However, if I choose a resource with no list separator in the name the Assign button is active:

The further issue with the Assign Resources comes when you make multiple selections that do not contain the list separator, such as the following:

When I click Assign – I see an extra resource in my list, with a very cool name – “adrian jenkins;brian smith;christophe fiessinger” – and he/they has/have been assigned to the task.

If I look at the resource sheet I can see “adrian jenkins;brian smith;christophe fiessinger” has been added as a new local resource.  The workaround here, assuming you do not have the list separator in the name, is to assign one at a time, or of course the Resource Name column in the Gantt view can be used as for the previous example.

The last scenario is back to the Task Information dialog, but this time we are looking at the Predecessors tab.  Say we have 3 tasks, T1, T2, and, you guessed it, T3.  We open the Task Information dialog for T3, go to the Predecessors tab and enter either the IDs of the first two tasks, or select them in the drop down like this,

then press OK, we get the following error message.  There is a problem with the predecessor information.

The workaround for this one is to go to a view such as the Gantt view, and use the Predecessors column, and enter 1,2.

For each of these you could also work around them by setting your list separator to not be the semi-colon – but I appreciate that might give you some issues elsewhere – as it is a global setting on your PC.  If you wish to try this you can go to Control Panel - Clock, Language and Region - Change the date, time or number format, then select Additional Settings then change the List Separator from a semi-colon to a comma, for example. 

Sorry for any inconvenience this problem has caused you – and I will update this posting once I find out when a fix will be coming along – and potentially any other scenarios that I am made aware of where this bug rears its ugly head, and thanks to the customers that have quickly brought this to our attention.

 

If you didn’t get to Phoenix…

May 16th, 2012

Brian Ru just published a blog post over on the main Project blog announcing the release of all the recorded content from the Project Conference 2012 – on the Project Channel of Microsoft Showcase.  So if you didn’t get to Project Conference 2012 this is a great chance to catch up with all the great content.  For good support topics see PC319 and PC349 – as recently “leaked” on this very blog…

Enjoy!

Developing with UMT Project Essentials

May 16th, 2012

For those developer types working in the ever increasing number of organizations that have deployed our Project Essentials product – I wanted to make sure you saw Mircea’s post on developing custom applications against PE:

http://www.ro.umt.com/blog/2012/05/08/developing-project-essentials-applications-using-wcf-2/

Filed under: Project Essentials Tagged: Project Essentials, UMT, Workflow

Building advanced Project Server workflows with Nintex Workflow for Project Server

May 16th, 2012

Over the past week or two, Microsoft have been quietly uploading all the sessions from the recent Project Conference held in Phoenix to the Project channel on Microsoft Showcase. This morning the Project team officially announced the availability, so I am pleased to announce that the video of Mark McDermott’s and my session is now available for your viewing pleasure. I encourage you to watch it and let me know if you have any questions.

Finally, the session deck, including notes is available for download at slideshare.net.

Filed under: Demand Management, MVP, Project 2010, Project Conference Tagged: #mspc12, Nintex, Nintex Workflow, Project Conference, SharePoint

TechEd Europe–Amsterdam, 26-29 June.

May 14th, 2012

Just to say I’m going to be at the “ask the experts” stand at TechEd, answering questions on Project, Office and O365.  It would be good to put some faces to names, so please drop by to say hello.

http://europe.msteched.com/

Enjoy,  Ben.

Check #MSProject version number on remote machines using #PowerShell #ProjectServer #PS2010 #Office

May 14th, 2012

Below are two examples of PowerShell scripts to check the Project Professional 2010 version on remote machines. Ultimately the best solution would be to use Microsoft System Centre Configuration Manager to manage software updates. It is key that all Project Professional clients have the same version, especially if connecting to Project Server. Having all Project Professional clients at the same patch level will help with the stability of your EPM farm.

The two PowerShell scripts below give the same results but I wanted to include both as these are only examples of what is possible. Both scripts read the client machines from a text file. Add all of the client machines that have Project Profession installed into the text file as seen below:

The first simple version just checks the product version from the file system on the client machine using a UNC path location:

$pcs = Get-Content "C:\pcs.txt"
foreach($pc in $pcs)
{
$proj = (Get-Command "\\$pc\C$\Program Files (x86)\Microsoft Office\Office14\WINPROJ.exe").FileVersionInfo.Productversion
Write-host "$pc has the following version of Project Professional:" $Proj
}

The second option is slightly more complex and automates Project Professional and gets version from the build property:

$pcs = Get-Content "C:\pcs.txt"
foreach($pc in $pcs)
{
$session = New-PSSession -Name $pc1 -ComputerName $pc
Write-host $pc -NONEWLINE
Invoke-Command -Session $session -ScriptBlock {
$Proj = new-object -comobject msproject.application
Write-host " has the following version of Project Professional:" $Proj.Build
$Proj.quit | out-null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Proj) |out-null
Exit
remove-PSSession -Session $session}
}

The second option will require the remote machines to be configured for PowerShell remoting:

http://technet.microsoft.com/en-us/library/dd819498.aspx

Both scripts should be run with an account that has admin access (domain admin) on all client machines.

An example output for both scripts can be seen below. Please note, the scripts were only run against one machine, if you have multiple client machines each one will appear on a new line:

Project Server Brings Project Portfolio Management to Small Businesses

May 14th, 2012

No matter what size your business is, the decision of where to allocate your resources to meet your business goals is complicated.  A lot of factors come into play, and what may look like an obvious decision on the surface may not actually align with your goals in the long run.  This is where Microsoft Project Server 2010 can help.

If you have a leadership role in a small to medium sized organization, chances are you wear a lot of hats.  It’s natural to try to handle resource allocation in an ad-hoc manner.  Common wisdom says, only big firms can justify spending the money for portfolio planning and management processes and tools.  However, Project Server is a surprising resource for small companies.

With Project Server 2010, Microsoft has brought project portfolio management to the masses.  Before the 2010 release, portfolio prioritization was only available to highly structured, very mature organizations, but with Project Server 2010’s simplified web user interface, Microsoft walks any user through the process of capturing their business drivers, prioritizing those drivers against each other, establishing portfolios, etc. Once that is done the software applies a mature calculation process which evaluates the information you have already entered to produce charts, graphs, tables and other objective data to help you determine where you should focus your energy and resources.  There is no need for complex financials or other types of data.

Here’s an example of where this could be useful:

Suburban Plastics, Inc. – a fictitious company

Current business:  “Suburban, Inc.” produces sheet plastic for the local market.  The company is set in a suburban environment which provides the employees with a very comfortable area to exercise and a safe place to come to work.  “Suburban, Inc.” isn’t large enough to have a company fitness program.  The company would like to grow but not at the cost of its culture or history.

The business drivers, as decided upon by the leadership team, are as follows:

1. Grow Revenue
2. Diversify Market
3. Provide a healthy and happy work environment
4. Provide the highest quality product

After doing a pairwise comparison (shown below) of the drivers the following priority order is calculated and agreed upon by the leadership team.

After evaluating each driver against each other the following results were calculated by Project Server.

1. Provide the highest quality product                                          % 42
2. Grow Revenue                                                                         % 28
3. Provide a healthy and happy work environment                      % 19
4. Diversify Market                                                                       % 11

A proposal is presented to the leadership team to grow the business by entering into the manufacturing of plastic widgets.  This will most likely grow the annual revenue and it will certainly diversify their market.  On the surface this looks like a good way to invest in the future of the company.

However, after adding this initiative to the Portfolio it is easy to see that the initiative does not support the #1 business driver since this requires new machinery and new processes.  It scores high for #2, there is some risk so it only gets 80% credit for that driver.  To expand the business footprint, Suburban will have to relocate to an industrial neighborhood so it again does not support a business driver, this time #3.  For #4 the initiative is fully in alignment.  This means that the initiative gets full credit for supporting that driver.

Using Project Server the initiative scores a 33.4 % alignment.  This is very low and shows the leadership that while this sounded like a good idea, it is not very well aligned with their corporate priorities and therefore should not be undertaken.

This simple, structured method helps businesses eliminate preconceptions from the decision making process by providing objective data that is created around your company’s priorities, rather than an emotional response.  Essentially using Microsoft Project Server 2010’s portfolio management capability will help any organization stay true to its beliefs and goals.  You will not be driven by the passion of the moment to make your business decisions.

If you have questions about how this can work for your organization, feel free to contact me at davidd@adaquest.com .

Tagged: bpo, business resources, Project Portfolio Management, Project Server, Software as a Service

WPC 2012 Partner Awards Nomination–THANK YOU for your submissions!

May 14th, 2012

Partners – I would like to recognize your efforts in submitting your nominations! All judges and myself agreed that quality of submissions was the highest we have experienced so far! The diversity was huge - we have seen great case-studies and even greater solution you have built - and all this underscores the great success of Project 2010! Thank you!

To give you little more transparency into what’s next -  in June World-wide Partner Group will announce the results - for PPM competency you can expect one winner and 3 finalists. We will also publish 6 additional partner names who’s solutions and case-studies are “honorable to mention”.

We are working hard on the Microsoft Project presence at WPC 2012 – you can expect Project booth, Sessions (search for Project) led by Microsoft and showcasing partners, partner presentation in the partner theater and more. Stay tuned for updates in the near future!

Thanks and looking forward seeing you in Toronto!

Jan

Next Page »