Using MS-Project Server for project management reporting and tracking integrated with SharePoint

Custom SQL Reporting in MS-Project Server 2013

Custom SQL Reporting in MS-Project Server 2013

It is easy to navigate the database schema in MS-Project Server to generate reports.  The SQL can be embedded in an ODC, or can be used within PowerPivot.  If joining Task and Project data, there’s a challenge of rollups.  The first challenge is avoiding double-counting from summary tasks.  The solution is to exclude them on the join, adding this condition:

where TaskIsSummary=0

The next source for double-counting are external tasks; those exposed through cross-linking tasks in separate projects. We can exclude both this way:

where TaskIsSummary=0 and TaskIsExternal = 0

The next problem is if merging task and project tables, project values would roll up incorrectly, however such numeric fields can be pro-rated to the project work, as long as we avoid divide-by-zero errors, here’s how, referencing a custom field called “Budgeted Costs”; note how its value is proportionate to the task work:

, case
when [MSP_EpmProject_UserView].[Budgeted Costs] = 0 THEN 0
when MSP_EpmTask_UserView.TaskRegularWork = 0 THEN 0
when MSP_EpmProject_UserView.ProjectWork = 0 THEN 0
else
[MSP_EpmProject_UserView].[Budgeted Costs] * ( MSP_EpmTask_UserView.TaskRegularWork/ MSP_EpmProject_UserView.ProjectWork )
END as [Budgeted Costs]
FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView
ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID
where TaskIsSummary=0 and TaskIsExternal = 0
ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName

One step further, we can do the same using task assignment data, here’s what that looks like using the assignment work:

, case
when [MSP_EpmProject_UserView].[Budgeted Costs] = 0 THEN 0
when MSP_EpmAssignment_UserView.AssignmentWork = 0 THEN 0
when MSP_EpmProject_UserView.ProjectWork = 0 THEN 0
else
[MSP_EpmProject_UserView].[Budgeted Costs] * ( MSP_EpmAssignment_UserView.AssignmentWork/ MSP_EpmProject_UserView.ProjectWork )
END as [Budgeted Costs]
,[MSP_EpmResource_UserView].[Cost Type]
,[MSP_EpmResource_UserView].[Resource Departments]
,[MSP_EpmResource_UserView].[RBS]
,[MSP_EpmResource_UserView].[Resource Title] FROM dbo.MSP_EpmProject_UserView INNER JOIN dbo.MSP_EpmTask_UserView ON MSP_EpmProject_UserView.ProjectUID = MSP_EpmTask_UserView.ProjectUID LEFT OUTER JOIN dbo.MSP_EpmAssignment_UserView ON MSP_EpmTask_UserView.TaskUID = MSP_EpmAssignment_UserView.TaskUID AND MSP_EpmTask_UserView.ProjectUID = MSP_EpmAssignment_UserView.ProjectUID LEFT OUTER JOIN dbo.MSP_EpmResource_UserView ON MSP_EpmAssignment_UserView.ResourceUID = MSP_EpmResource_UserView.ResourceUID
where TaskIsSummary=0 and TaskIsExternal = 0
ORDER BY MSP_EpmProject_UserView.ProjectName, MSP_EpmTask_UserView.TaskIndex, MSP_EpmTask_UserView.TaskName

Configuring MS-Project Server 2013 in CloudShare

Configuring MS-Project Server 2013 in CloudShare

I’m a huge fan of CloudShare. You get quick access to great template environments, and all the key development tools.

I recently loaded up MS-Project Server 2013 on SharePoint 2013 with SQL Server 2012, and found the PWA OLAP Cubes failed. Here’s the issues, and how to complete the configuration:

Queue Service not working
Needed to restart the queue service in services.msc including re-entering the credentials

If you are patching the server, you’ll find the Config Wizard fails. The admin password is not configured correctly. You can fix it by using this command; have your credentials ready:
Set-SPManagedAccount -UseExistingPassword -identity AD2012Administrator

OLAP Cube failure
The OLAP cube failed for quite a list of reasons:
1. OLAP Cube configuration is not configuring to point to the SQL Server Instance
2. SQL Client v10 (2008R2) is not installed. PWA depends on this older version, and it is needed on both Project Server as well as SQL Server.
3. Project Server also requires AMO for SQL Server 2008 R2 (SQLSERVER2008_ASAMO10)
4. The SQL Server SSAS account needs to have access to the Project Server database in order to extract source data to build the cube.

Performance
For performance I have disabled the following service on server, which seems to make it faster, as they do not relate to Project Server:
– Access Services
– Access Database Service 2010
– Machine Translation
– Foundation incoming email
– PowerPoint Conversion Service
– Visio Graphics service
– Word Automation Service

Patching
This CloudShare template is RTM, so I recommend applying the March 2013 CU, followed by the two MS-Project Server CUs. You will want to run the SharePoint Config Wizard after the March CU, as there’s a DB schema change; same after applying both Dec 2013 CUs.

AppFabric errors; ignored these for now.

MS-Project
Configure default MS-Project client behavior in: C:ProgramDataMicrosoftWindowsStart MenuProgramsMicrosoft Office 2013Office 2013 Tools, Project server accounts.

Excel Services
Note Excel Services is oddly not enabled, as that’s useful for a range of BI including PowerPivot. If you enable Excel Services, you’ll most likely want to also configure Secure Store, and also configure Excel Services trusted locations for the Excel files, and also trusted data connection libraries.

Migrating a full copy of MS-Project Server content

There’s a frequent need to refresh MS-Project Server test environments from Production.  The conventional wisdom holds that you need to delete and recreate the SharePoint Web Application (PWA).   However this requires you to:

  • Recreate Alternate Access Methods (AAM)
  • Set quotas
  • Refine Blocked File Types
  • Set User Policy
  • Set Service Application connections…

The faster and smoother better way is instead to drop the old PWA content DB, and and reconnect the new PWA Content database. Regardless, the very first step should actually be dropping the Project Server Web App.  This is done through the Project Server Service Application in Central Administration, Service Applications. Now let’s switch over to the replacement PWA Content DB.  This houses both the top level Site Collection, the PWA application Site Collection and all webs under it (mostly each are a project site):

Dismount-spcontentdatabase [the old content database we are replacing]
Mount-SPContentDatabase -name [the new content database ] -DatabaseServer [your DB server] -WebApplication "http ://pwa"  [change as needed]

However there is one big wrinkle.  Doing this seems to leave an orphaned explicit managed path definition in the Web Application that prevents creation of the PWA Site Collection.  This appears to be what leads people to simply delete and recreate the Web Application.  However the solution is quite simple; remove the orphaned site collection:

Remove-SPSite -Identity  "htt p://pwa/" [change as needed]

When recreating the Project Web Application note:

  • When removing the Project Server Web App, you may wish to uncheck the “Remove Content DB” checkbox
  • Halting the Timer jobs may be required for the steps above.  One advantage of using PowerShell is that it does not depend on the Web App Application Pool to be active
  •  “PWA” must be the name of the project web app
  • Get the database names right, to ensure you connect to the target databases migrated to this environment

If your replacement PWA DBs come from an environment with different security, you’ll need to adjust security manually at the database level.  I prefer to take screenshots in SQL Studio for comparison before starting.

OLAP Configuration

The OLAP cube inevitably needs to be reconfigured.  When refreshing the databases, the OLAP configuration will now mirror the source environment.  Make sure you know the name of the OLAP server and database to reset the OLAP configuration.

Delete the Data Connections OLAP folder, as well as the 13 assorted cubes and the folder in which they reside.  When rebuilding the cube, these get recreated with reference to the OLAP Server and database.

Lastly check the cube rebuild frequency and rebuild the cube.  You should see a successful OLAP cube build log, the new cubes recreated, and data in the cubes that is visible in the Excel PivotTables stored in the OLAP folder that is as current as the source PWA database.

Testing

Check main navigation links.  Any hard-coded navigation links in the source may not get repointed automatically in the newly refreshed environment, and could require hand-tuning.

Check that the new Excel OLAP pivots are in a SharePoint Excel Services Trusted Location

Other areas to test

  1. Add-ons
  2. Links
  3. Data (projects, resource pool and associated metadata)
  4. Sites
  5. Views
  6. Configurable fields and Lookup tables
  7. Enterprise Calendar
  8. Security
  9. Ability to access via MSPS client
  10. Scheduled backups
  11. Quick Launch settings in Server Settings
  12. Time/task management settings
  13. Project Detail pages
  14. Project site templates
  15. Ability to add a Risk, Issue to project sites
  16. OLAP
  17. Reports

When you’ve done a refresh once, you are golden!  I like to say “only in technology can you do something once, then be considered an ‘expert'”!