Christophe Fiessinger's Blog Updates on Microsoft's Enterprise Social Networking (ESN) and Project Portfolio Management (PPM) offerings
I’m thrilled to announce the release of the following two white papers which provide step by step guidance on surfacing key Project Portfolio Management data into insightful reports and dashboards:
For additional information on Microsoft Project Server 2010 business intelligence & reporting please check out Business Intelligence in Project Server 2010.
Microsoft Project Server 2010 Reporting with Excel Services, 71 pages
Table Of Content
The purpose of this document is to provide enough detail to be able to create and deploy reports which will support the deployment of Microsoft Enterprise Project Management (EPM) Solution. Microsoft EPM Solution relies on Microsoft SharePoint Server 2010, Microsoft SQL Server 2008 or 2008 R2, Microsoft Project Server 2010 and Microsoft Project Professional 2010. Although each individual component of the solution is well documented on TechNet and MSDN (for the Business Intelligence), little documentation exists, as of today, which describes a step-by-step approach to building reports specific to Project Server 2010 data with Excel and Excel Services.
This document is not intended to provide an in-depth description of all the features available in Excel 2007 or 2010 and Excel Services 2010 when building a report but rather provide the building blocks required to ‘be up and running’ quickly.
Creating Dashboards for Microsoft Project Server 2010, 115 pages
The purpose of this document is to provide sufficient detail to fully design and implement Business Intelligence Dashboards that will support an Enterprise Project Management Solution (EPM), which consists primarily of SharePoint 2010 and Project Server 2010. It also includes detailed steps on how to replicate reports and dashboards included in the Project Server 2010 demonstration and evaluation pack available on the Microsoft Download Center. The purpose is to help you easily create reports and dashboards for your environment with similar characteristics.
The target audience for this document is the business owner and report developers who are looking for a quick way to develop Dashboards that will support an Enterprise Project Management Solution for their organization.
In MS Project you have the ability to add notes to each tasks in a project plan. This data is stored as an Image Data Type in the SQL database called TASK_RTF_NOTES (note that the field TASK_NOTES is a nvarchar of size 255, thus you’ll only get the first 255 characters displayed). So how can you render the entire note field using SQL Server Reporting Services? Before giving you the answer a special thank you to Sam Brooks from Microsoft for passing along this solution:
(SQL Reporting Services code sample attached at the bottom of this post)
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P
INNER JOIN PWA_Published.dbo.MSP_TASKS AS TRTF
AND P.ProjectName='Task RTF' --- For debugging purposes REMOVE!!!!
ORDER BY P.ProjectName, T.TaskName
Note that the TASK_RTF_NOTES is not in the Project Server Reporting database, thus we need to get it from the Published database.
‘Instantiate a rich text box control in memory
Public rtfRTB As new System.Windows.Forms.RichTextBox
‘Instantiate a stringbuilder object
Public s As New System.Text.StringBuilder()
Public Function byteArrayToString(ByVal b() As Byte) As String
Dim i As Integer
dim mystr as string
on error goto errortrap
s.length = 0
For i = 0 To b.Length - 1
If i <> b.Length - 1 Then
mystr = left(s.ToString, len(s.ToString)-1)
rtfRTB.rtf = mystr
s.length = 0
Another way to do the same thing is to use custom assemblies, check this blog post from Bryant Likes for more information: http://blogs.sqlxml.org/bryantlikes/pages/824.aspx
I just came accross this great tool VhdResizer that will achieve the following:
VhdResize will resize Microsoft's VHD files and will also convert between Fixed and Dynamic file types. This is a sector by sector copy operation from one size/type to the other and the source file remains unaltered.
FYI I have just completed the install of Project Server 2007 + Service Pack 1 (and Office Server 2007 + SP1 as well) using Windows Server 2008 Release Candidate 1 (RC1) from MSDN.
I used this two great posts from Bill Baer to complete the install:
Two screen captures to prove it (check bottom right corner :)
Expect more posts in the future about the advantages of running PS 2007 on W2K8 (IIS7, hypervisor-based virtualization ...).
In the meantime for more information about Windows Server 2008, please check the official Microsoft site here: http://www.microsoft.com/windowsserver2008/default.mspx
Last week we released Microsoft Dynamics AX 2009, and with it comes an out of the box integration with Project Server 2007.
Both products address a common scenario: Line Of Business Integration (LOBI) between an ERP system and Project Server; in this case no need for custom code (check out the LOBI session code sample Brian Smith and I did at TechEd Developer last week in Orlando)!
Following this post (New SQL Reporting Services Sample Reports for Project Server), please find below the list of Excel Services (samples attached) reports included in the newly released demo VPC.
If you want to learn more about Excel Services (requires SharePoint Server) two new great books have been published on the subject:
Prepping for my TechEd presentations in less than two weeks (Microsoft Project Presence at Tech.Ed North America 2010), I worked on a presentation to demo how easy it is to upgrade from Microsoft Office Project Server 2007 to Microsoft Project Server 2010 (come to Rolly and I’s session entitled Deploying and Upgrading to Microsoft Project Server 2010. I used the five sample databases (yes five: one SharePoint content, and four PWA instance databases) that we built for our core EPM 2007 demo virtual machine (VM) (about 550MB of bak files). I backed up all five dbs from my 2007 farm/demo VM and created an ISO with the five databases and two magic scripts (one to restore the dbs in T-SQL and one PowerShell script to upgrade the databases (basically two commands: Mount-SPContentDatabase and New-SPProjectWebInstance), ready to mount to my new 2010 demo VM.
And guess what… on my demo laptop (yes with 8GB RAM, 4 cores, and dual SSD), I had the Project Server 2010 and SharePoint Server 2010 demo virtual machine up and running; in 10min my PWA 2007 instance got upgraded to 2010!!! Yes you heard me, barely enough time for a coffee. Want a live demo come to New Orleans and see it yourself!
More seriously used the RESTORE DATABASE T-SQL command which took 30s; then ran the following two PowerShell commands (content db upgrade took about 2min, and rest to upgrade Project Server 2007 four dbs and provision PWA):
Mount-SPContentDatabase -Name Litware_Content -WebApplication Project New-SPProjectWebInstance -Url http://project.contoso.com/litware -AdminAccount "CONTOSO\Administrator" -PrimaryDbserver "DEMO2010A" -PublishedDbname "Litware_Published" -ArchiveDbname "Litware_Archive" -DraftDbname "Litware_Draft" -ReportingDbserver "DEMO2010A" -ReportingDbname "Litware_Reporting"
Mount-SPContentDatabase -Name Litware_Content -WebApplication Project
New-SPProjectWebInstance -Url http://project.contoso.com/litware -AdminAccount "CONTOSO\Administrator" -PrimaryDbserver "DEMO2010A" -PublishedDbname "Litware_Published" -ArchiveDbname "Litware_Archive" -DraftDbname "Litware_Draft" -ReportingDbserver "DEMO2010A" -ReportingDbname "Litware_Reporting"
As usual a lot more detailed information on TechNet to conduct an upgrade or migration: Upgrade and Migration for Project Server 2010 (this article in particular: Database-attach full upgrade to Project Server 2010). Yes there is a lot more process/training/customization etc… to upgrade to 2010 but at least on the data front its super easy!
See the upgrade site below (notice the dashboard quick links from the EPM 2007 PPM demo VM). Happy upgrade!
Following this SharePoint 2010 announcement today: Announcing SharePoint Server 2010 Preliminary System Requirements please note the following requirements for Project Server 2010
Please find below two recently published article on TechNet to help you migrate your existing Project Server 2007 farm to 64-bit:
Finally please encourage your customers and partners to attend our upcoming Microsoft Project Conference 2009 in September to learn about Project 2010.
Thanks to Sharry please find below some useful steps when troubleshooting EPM 2007 queue issues:
1. Use Manage Queue page (Sever Settings -> Queue -> Manage Queue) to look at correlations (use the CorrelationUID column for help here) to see why a certain correlation is blocked. Usually if your queue is still working for other jobs/entities, then restarting the queue is not necessary. If you cannot see any problems and your queue is still working, then your filters on the Manage Queue page are not right – check them. Restarting the Queue is only necessary when nothing is processing. Using the “By Project” filter works nicely for looking at the queue job history of projects. For other correlations, use CorrelationUID.
2. Look first for Failed and Blocking states – those are the jobs that are “blocking” others on the same correlation (again, use the correlation UID here to see what jobs are affected). You can either retry these jobs if the error looks like something having to do with something recoverable (like loss of network or database connection), or you can cancel. Canceling with the default settings will cancel the entire correlation, so make sure you know what data you could be losing by doing so.
3. Are jobs stuck in the “Getting Enqueued” state? If so, WinProj needs to be opened again on that user’s machine who submitted the job to see if WinProj will continue sending the project. If that doesn’t work, then you will need to cancel the jobs in this “getting enqueued” state. Note that this effectively means that the save from WinProj never happened, and that data will need to be resaved again. This is the same thing that happens when you just blindly restart the queue. But at least doing it this way means that you know what is being lost.
4. Look at the error (click the link in the Error column) to get an idea about why the failure occurred. Sometimes you can correct the problem and re-save/re-submit your job.
5. Start comparing Event Logs to what you’ve found on the Manage Queue page. Look for errors around the same time as failed jobs in the queue.
6. SharePoint Logs (usually located here: C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\LOGS ) . Same technique as #5 – look for errors around the same time as failed jobs in the queue.
Once you clear the blocking job(s), the queue should immediately resume processing on that correlation again, and pick up from where it last left off.
With the recent release of Microsoft Project Server 2010 (PS) last May and its rapid adoption by customers, a recurring question I have seen and been asked is how can I integrate Project Server 2010 with application X? Where application X can be a combination of the following:
Import financial information, and WBS
The following Microsoft Project Server data type/entities are typically synched:
And there are more I have seen or heard but basically they revolve around the above application types. So how do I interface PS with these other mission critical applications (also referred to as Line Of Business Integration or LOBI) within my organization? This is obviously a very broad topic and this blog post is by no means exhaustive but let me try to get you started in answering this question.
Well as you can guess IT DEPENDS! The biggest challenge and the most complex part of such integration is clearly defining the scenario, documenting what data will be transferred between the two systems, what’s the “master” and what’s the “slave”, exception handling (if an update is rejected what happens for instance? If a task is deleted what happens? etc.)
The second phase is implementing the bridge and for that as mentioned in this recent post: Microsoft Project Server 2010 Integration with SAP you have two options: either use an existing partner solution that is publically available (search here for instance: http://www.microsoft.com/project/en/us/partners.aspx or BING it) or you can build your own connector and in that case look at the Microsoft Project 2010 Software Development Kit.
Points to consider when integrating PS with LOB (by no mean exhaustive):
What’s the specific use case?
Interface scalability and performance – was on a recent call with a partner who realized their interface to another system did not took too long to process updates…
What PS data entities (Project, Task, Resource, Lookup Table) needs to be updated and transferred? How is the custom data mapping defined?
Interface logging and monitoring - would expect any solution whether its of the shelve or custom made to provide visibility of data updates so that it can be easily monitored and maintained
What are the additional Custom Fields required in PS to enable the transfer?