Customizing SQL Views to Provide Useful Datasets

In my last post, I decided to take a look at how to use External Content Types to surface Project Server Reporting database data on Risks and Issues.  In this post, I plan to talk about some quick and easy steps that are required to provide a useful dataset.

Now, before we get too far into this post, I should probably mention that working directly in the database is always a sensitive issue, but as far as I can tell, adding a custom view to the Reporting database will not cause supportability issues going forward.  Just make sure that everything is documented and backed up.  Now back to the narrative…

The challenge that I found when I first started playing with the risk tables in the Reporting database was that none of the risk tables seemed to include the Project name.  A couple include the Project ID field, but that won’t help when it’s surfaced in an External List.

…so I decided to create a custom SQL view.  To do that, I fired up SQL Management Studio and navigated to the Reporting database.  Once I found that, I right clicked on the Views option to create a new View.

From there, it’s a relatively simple matter to select the WSSRisk_Olapview and EPMProject_Userview tables to be included in my view.  I check the fields to be included, and then add a join at the ProjectUID field.  (If you’re not familiar with this interface, it works much like Access, you just grab the ProjectUID field from the table on the left and drag it to the one on the right to generate the join.)

Save the Custom View, and you can now use it for reporting purposes, whether that report is based on an External Content Type, an Office Data Connection file, or a direct connection back into SQL.

Centralized Risk Repositories with External Lists

I was plugging away at the Project Server newsgroup when one of the contributors asked an interesting question.  After trying to aggregate Issues from each of the project sites to a single centralized repository using Content Query Webparts, Bart E. asked, “So there is no OOTB solution for a sortable aggregation, I take it?”

….well, that got me to thinking.  Is there an easy out of the box solution to deliver centralized risks and issues from Project Server?  The obvious solution would seem to be through the use of External Content Types, which I have come to regard as the Swiss Army knife of the SharePoint 2010 world.  Using External Content Types and External Lists would allow the development of easily customizable lists of aggregated risks.

So I sat down with a virtual image to see if I could make it work.  To my surprise, 20 minutes later, I pretty much had it working – with some minor formatting issues that took me back to the drawing board.

Hence my next couple of posts will be about this topic, specifically how to use External Content Types to surface the risk information embedded in the Project Server reporting database.  This approach is only suitable in the following circumstances:

• Projects are published primarily from the Microsoft Project client – as that is the action which seems to refresh the database from the Project site data.
• Risks are managed without custom fields, or at least the aggregation does not require those custom fields to work.  The Reporting database only captures the default fields.
• Project schedules are updated regularly.  It is through the update process that the risks are populated within the Reporting database.

First, let’s start with a little review of the available literature.  What options are currently available?  Here are the ones I’ve come across so far.

1. SSRS Reports – as documented by Christophe Fiessinger here.
2. Third party tools such as this one from iPMO.
3. Utilizing the Content Query Webpart within SharePoint – which required a little more coding the last time I played with it than I was comfortable with.  Still, probably worth a blog post at some point in the future.  A variant of this approach would cross site collections and might include something like the Content Monster Webpart.

This series of posts will focus on a fourth method, using External Content Types and an External List to surface Reporting database data.  You could probably do the same with a Web Service pulling data from SharePoint content databases, but I would defer to the SharePoint folks on that.

Hence, over the next couple of days, watch out for the following posts:

1. Customizing SQL views to create the dataset
2. Creating an External Content Type from a customized view
3. Creating an External List with Risk data

(My plan is to come back and add hyperlinks to this post once each of those individual posts have been published.)

Back to the Basics: Calculating Duration in Fixed Duration Tasks

My colleague approached me with an interesting question the other day.  Under specific conditions, he could make seemingly identical tasks appear with different durations.

So the question is “why?”  Why do seemingly identical tasks have different durations.  First off, lets look at how duration is calculated…

Fixed Duration Activities Without Assignments

In the example below, I have three tasks.  Each task is a different type.

You’ll note that the duration is the same.  Next, I will introduce an exception of one day in the Project Calendar.  In theory, this will push out the finish date for each task.

The results appear as below:

We see that the duration has not changed, even though the tasks now finish up on 6/13 instead of 6/10.  This is as expected, because the duration is calculated as the total number of working days, and excludes any nonworking days such as weekends or holidays.

Fixed Duration Activities With Assignments

Now let’s assign a resource to each of those tasks.

Everything looks correct.  The resource is using the same Project Calendar, and therefore, we anticipate no changes.  Here’s where things get a bit trickier.  I am going to add a 1 day exception to my resource calendar.

…and now my schedule looks like this:

See how the duration shows differently, yet for all intents and purposes, the start and finish dates are identical?

The reason for that is that Fixed Duration activities ignore the resource calendar when calculating duration.  A Fixed Duration task calculates the difference between the start and end date using the Task or Project calendar.  In this case, since I haven’t applied a Task calendar, the calculation uses the Project calendar.

Fixed Unit and Fixed Work tasks on the other hand, calculate duration by counting how many days on work is actually assigned.

Project Center Ribbon Greyed Out After Solution Starter Uninstall

Here’s an issue that’s popped up a couple of times for me, and I figured was worth of note.

Upon navigating to the Project Center view in Microsoft Project Server 2010, I see that the ribbon is greyed out and inactive.  A small error notice appears in the bottom left of the screen stating.  When I click on the icon, it gives me the following message.

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; WOW64; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; InfoPath.2; MS-RTC LM 8; .NET4.0C; .NET4.0E; InfoPath.3)

Timestamp: Tue, 3 May 2011 00:06:53 UTC

Message: Object required

Line: 2

Char: 199616

Code: 0

URI: http://myserver/_layouts/cui.js?rev=wvoVpqlQb30nGo4DjDk8Kg%3D%3D

There may be a couple of reasons for this to occur.  The most common one that I’ve run into is that this will occur when a solution starter that appears on the ribbon is uninstalled from the server.  As far as I can tell, this leaves a cached version of the ribbon on your local client IE – which throws an error on load.

Luckily the fix is quite easy.  Simply delete your local IE cache, refresh the page, and everything is back to normal.

Know Thy Operational Integration Model

Sooner or later, every IT department implementation faces the issue of how to integrate their EPM tool with their operational service desk management tool.  This is not only an issue for IT, as I have seen the same challenges in pipeline and oilfield maintenance, but it’s an issue that seems to arise more in IT than in other domains. 

I was thinking about this a couple of weeks ago when I was working on a PMI presentation including items to consider when deploying an EPM tool.  During my preparation, I identified two potential models for Project-Operations integration that I think are helpful to take into consideration when developing a road map for an EPM tool deployment.

If you’re implementing an EPM tool right now, and you plan to integrate project and operational work management, it would behoove you to spend a couple minutes thinking about the following models – and how or when they may be implemented within your organization.

Model #1: Segregated Project & Break-Fix Work Force

This is the model most strongly espoused in all of the IT Service Management literature, i.e. that all of the people on staff doing break fix work are kept separate from the people building new applications.

In the ITIL vision then, each of the costs of the operational work items (read: “trouble tickets”) and the projects can be rolled up and mapped to a specific application to determine the total cost of ownership for that specific application.

What are the ramifications of this from a deployment perspective?  When developing the custom fields for slicing and dicing your project portfolio, ensure that projects are coded such that they may be rolled up to the model the TCO for your application portfolio (or to use non-IT terms, your asset portfolio), i.e. ensure your Project and Ops coding are compatible and may be combined.

Model #2: Integrated Project & Break-Fix Work Force

This is the model that I see more often in my deployments, specifically in low-maturity organizations where resources are often split between building new assets and keeping the existing ones working.  This, of course, is the configuration that makes Agile and TOC practitioners cringe.

In this environment, the resources are shared, and thus to develop an accurate profile of the work demand, a comprehensive picture of both operational and project work is required.  This sort of implementation is significantly more complicated than the first model, as it requires both the integration of the costs as well as the work demand profile – i.e. I need some way of modeling the resource commitment to breakfix work in addition to the project pipeline of proposed projects.

This kind of model also gets more complicated as actual costs are booked to both operational and project work – usually meaning either double booking resource time, using two different timesheet interfaces, or implementing a third party solution to provide a shared interface between operational and project tracking tools.

Moral of the Story

If you’re implementing an EPM tool, take the time up front during envisioning and certainly during the development of your road map to identify which operational integration model you will most likely be implementing – and to build into the deployment the specific features that will be required to support it.

Using the Bulk Import Tool to Edit Populated Lookup Fields

Well, I thought I had it all figured out with my review of the Bulk Import
Tool….specifically with regards to using the Bulk Import Tool to perform bulk edits of existing data. 

• Part 1
• Part 2

Looks like I may have missed one key element.

Recently when trying to edit a group of existing projects, the tool kept returning an error of customfieldmaxvaluesexceeded.  The weird thing was that the error wouldn’t occur on every project, but only some projects – and then not on the same projects in DEV and PROD.

After playing with a couple scenarios, I think that I’ve figured out the issue.

The Bulk Import Tool will return the customfieldmaxvaluesexceeded under the following circumstances:

1. The user is attempting to edit a field connected to a lookup table.
2. The field is already populated.
3. The new value is different than the original value.

This issue will not occur when the new value is the same as the old value, or if the lookup value is blank before the edit.

I am still trying to identify a valid workaround, but for now, I might make the following recommendations:

1. Manually edit those projects and clear out the lookup values.  Although, of course, by the time you do that, you may as well have just set the values correctly.
2. Export the data into Excel.  Edit in Excel.  Delete the old lookup field, create a new one, and then use the Bulk Import Tool to modify the new data set.
3. A variant of topic #2, but instead of deleting the field wholesale, simply delete the original value from the lookup table list.  This will blank the fields that contain that specific value.

All in all, I would say that #1 is probably the least work intensive approach to getting around this restriction.

What I Did For My Summer Vacation

(Taking a short break from the technical this week….Never fear, more Project fun is on its way.)

My family, like many mixed international families, represents a unique combination of cultures, both inherited and acquired.  As a result, every couple of years, we like to throw the kids on a plane and take them back to Mongolia to see their maternal grandparents. 

Since 1) there’s no direct flight from the US to Ulaanbaatar and 2) it’s probably a bit optimistic to expect two kids to travel for 24 hours, have a 12 hour jet lag, and still be on their best behavior in a high pressure family environment, we typically spend a couple of days in Beijing en route.  That’s where my wife and I lived off and on from 1994 through 2003.

Beijing

As usual, Beijing represents a combination of the new and the familiar old.  We now can zip around under the city in air conditioned comfort, but the subway station map still displays a collection of the same places that we knew and loved back in the day.  The latest musical trend is still straight outta’ Kashgar, only now it’s more experimental, and less flamenco-influenced.  The old Chinese taxis may have been replaced with domestically produced Hyundais, but Beijing cab drivers are still the same cab drivers at whose side thousands of foreign students have learned that impenetrable Beijing accent…

The Summer Palace still looks the same as it always has, although this was the first time I’ve ever actually rented a paddle boat and headed out into the lake behind it.

I was quite  pleased to have the opportunity to finally meet Liu Dashuang, Beijing-based Project MVP.  Good to see Project Server and project governance going strong in the Chinese market.

…and after a couple of days of old-fashioned Chinese hospitality and some polishing of my rusty Beijing accent, we hopped a Air China flight north to Ulaanbaatar, Mongolia.

Mongolia (UB & Kharkhorin)

Ulaanbaatar as well was a mix of the old and new.  There were still many of the landmarks I remember from the late 90s – mixed with a major construction boom and a flood of Korean cultural influence as thousands of Mongolian workers move back and forth between UB and South Korea.

The old Soviet jeeps appear to be a thing of the past, replaced by a wide mix of automobiles representing the wide reach of the Mongol diaspora across South Korea, Japan and Europe.  Luckily, I was still able to find some of the old buses I remember taking back and forth from UB to the steppes.

UB boasts a brand new privately funded monument to the great man himself…

…but Gandan Monastery was pretty much still as I remember it.

…and this time, we were even able to take the kids out to the countryside to show them a bit of my old life in Ovorkhangai.  We headed to Kharkhorin, the home of Erdene Zuu, Ogedei’s old capital city.

There, we stayed in a ger camp:

..where I kept waxing nostalgic for the small ger I lived in for two years in the late 90s.  Here’s the view from the back of the camp….

I was glad to see that my countryside skills hadn’t atrophied, namely the skill to make a fire and the stomach to drink copious amounts of airag, or fermented mare’s milk.

I was especially pleased to be able to introduce my kids to Narantsetseg, my old partner in crime, who not only took care of her three kids, extended family, and a small herd of animals – but also agreed to take on a fairly useless Peace Corps Volunteer with no real skills. 

Here, we stopped by her new English classroom.

Very glad to see that hundreds of Mongolian children are still being educated by her capable hands.

Passing Filter Parameters Through SharePoint URLs

Here’s a neat trick I figured out the other day.  It didn’t actually solve my problem, but I figured it was worth documenting in case it comes in handy in the future.  My goal was to add filter parameters to a URL so that it would automatically filter for specific elements.

This sort of technique could be used to point users or a page viewer webpart from a project workspace back to a centralized document repository, the likes of which I described in this post.

So first off, we need the URL of the list.  Since I have the Contoso demo environment open, I’ll just use the sample report library in the BI Center.

http://project.contoso.com/PWA/ProjectBICenter/Sample%20Reports/Forms/AllItems.aspx

Now let’s filter on all items where Modified By = Marc Soester.  We need to identify how the system is referring to the Modified By field.  To do this, click on the Library tab, and select the Library Settings option.  Click on the Modified By field in the middle of the page.

That will yield a page that looks like this:

Take a look at the URL:

http://project.contoso.com/PWA/ProjectBICenter/_layouts/FldEditEx.aspx?List=%7B0AE8F9C9%2D0277%2D40DE%2DAFAB%2D25B10127CC3A%7D&Field=Editor

That last bit is what we’re looking for.  Internally, SharePoint refers to the Modified By field as the Editor field.

So now we take the original URL and add the following string:

?&FilterField1=Editor&FilterValue1=Marc%20Soester …to yield the following URL:

http://project.contoso.com/PWA/ProjectBICenter/Sample%20Reports/Forms/AllItems.aspx?&FilterField1=Editor&FilterValue1=Marc%20Soester  

Click on that, and you get a filtered list:

Want to filter on two fields?  Use the following syntax:

http://project.contoso.com/PWA/ProjectBICenter/Sample%20Reports/Forms/AllItems.aspx?&FilterField1=Editor&FilterValue1=System%20Account&FilterField2=DocIcon&FilterValue2=vdw

…which will filter on all Visio Drawings (VDW) created by the System Account.

Identifying Resource Calendar Exceptions with Project Professional

I delivered a Microsoft Project Professional tips and tricks session a while back, and that may have gotten me thinking about other tricks that I’ve got buried deep in the recesses of presentation topics folder.

This post addresses a question that appears on the Project newsgroup every now and then, i.e. how does the project manager get an easy to use overview of all calendar exceptions for resources assigned to a project.  This technique is applicable to any version of Microsoft Project that I’ve ever worked with – and works equally well with standalone or enterprise files.

First, let’s take a look at how you find and set the calendar exceptions.  Navigate to the resource sheet….

…double click on a resource and select the option to Change Working Time.  This will display the exception screen.

That may be difficult to do for each of the resources in a project schedule – especially because in a Server environment, the Admin may be adding exceptions to the resource pool and neglecting to notify us.  We need a method to generate a single list of all exceptions.

The solution is quite easy.  Simply go to the Project tab and click on the Reports button.

Pick one of the Assignment reports and click on the Edit button.

In the Details dialog box, add the Resource calendar data.  Click OK and then select the report.

…and you have a handy report listing all calendar exceptions:

Applying Security to a PDP Redux

A couple of weeks ago, I thought I’d figured out how to apply security to a PDP page and documented it in a post here.  Well, it turns out I was wrong.  A reader pointed out that she’d tried the same thing – which appeared to work fine when logged in as an Admin, but not as a PM.

That solution may still work in some limited scenarios – specifically where the role with the diminished permissions has the ability to review a project, but not edit or save it.  If the role with the diminished permissions needs to edit the project, then that solution will throw a “Value does not fall within the expected range” error on check-in.

Melodie also pointed out a better method to accomplish the same thing.  This blog post is intended to document that solution.  Thank you very much to Melodie for contributing to the community.

Essentially, we’re going to leverage the SharePoint personalization functionality.  This feature is turned on by default and allows each user to create their own personal view of a page.  In this case, we will need to log in as the user and create personalized views of the required PDPs.

Follow these steps to personalize the page:

1) Log in as the user.  You’ll probably need them to log you in and sit next to you while you do this.

2) Select the option to Personalize this Page from the menu in the top right.

3) Add Webparts to the page and configure appropriately.  Click on Stop Editing to, well, stop editing.

And now this is what the page looks like for the administrator.

When logging in as someone else, this is what I see.

Still a bit hard to administer but not bad for specific scenarios.