Using PSI to update dates and publish resulting in frequent reporting db discrepancies that require delete and re-publish... RRS feed

  • Question

  • Hello!

    I've been dealing with an issue in our Project Server 2013 server for a while that I'd love to know if anyone else has encountered.  We use PowerShell and .Net PSI to process date updates to a key field on a SQL Server database.  When the script detects this date has changed, it then initiates the .Net application that will loop through all schedules for the updated project dates and basically (via PSI) just open the schedule, update the project start date, save, and publish the schedule.  It does this for anywhere between 1 and a few hundred schedules each time. 

    As time has gone forward, I started getting reports of issues where the reports were showing incorrect dates. I tracked that back to the reporting database, as the published and draft databases showed the correct dates. I initially tried just re-publishing the projects with the bad dates, but that didn't work. I'm thinking it's because the system wouldn't detect a change, so it wouldn't actually wipe/re-load the reporting DB for the project. SO, I looked for a way to delete the reporting DB objects for the bad schedules.  I found the dbo.MSP_Epm_DeleteProject SP that takes a ProjectUID and clears out the reporting DB for the ProjectUID.  I then wrote a script that runs a bit after the first script.  This one queries the reporting DB for schedules that have tasks where either the start or finish doesn't match the start/finish dates from the same schedule in the publish DB.  For each of these schedules, I use a .Net app to first run the SP to delete the reporting DB objects, then PSI to publish the schedule.  This does result in all projects having matching dates. 

    The PROBLEM now, aside from why these are happening, is that the SP that deletes the reporting DB objects takes about 2-3 minutes to run.  If we publish a few hundred schedules, more than half will result in reporting DB discrepancies, so let's say 175 are bad.  It ends up taking between 6 and 8 hours to fix them all.  Interesting best I can tell, this only affects projects that were updated/saved/published via the PSI application. Manually updated/published projects have never shown up on the discrepancy list.

    SO... my first question is... does anyone know anything about this bug? 

    My second question is... does anyone know a better way to fix the reporting db discrepancies? 

    We're currently on the Aug 2016 CU, so I'm working on upgrading to Dec 2017 cu now.  I haven't read anything that would lead me to believe this would help, but it couldn't hurt.  I'm asking here before contacting MS support, as we've got a lot of customizations to our system for various reasons (nothing that should affect the internal function of the publish/reporting database process) and I'm fairly sure their first response will be that we either need to undo many things (not possible) or that we've violated some rules and are not supportable. 

    Thanks for any advice!

    • Edited by RockyCherry Thursday, January 25, 2018 3:14 PM added project server 2013
    Thursday, January 25, 2018 3:13 PM