"Reporting (Project Sync)" queue job when linking tasks to risks.
A customer pain point brought to my attention this week is related to linking tasks to risks from PWA vs linking risks to tasks via the project site’s Risks list.
The customer noticed that, in Project Server 2010, linking a task to risk from PWA’s drill down view of the project schedule does not make that link show up in the Reporting database so he could report on it using SSRS (SQL Server Reporting Services). However, he noticed that if he links the risk to the task from the Risks list, he was able to report on the link immediately.
I dusted off my Project Server 2010 test server (just kidding, it gets such frequent and heavy testing that I never turn the VM off) and created a project plan with a project site.
I opened the project site, and then clicked on the Risks list. I created a new risk and added a link to a task from the plan. The queue presented this Reporting (Project Sync) job:
Next, I opened the schedule in PWA and drilled into the plan, then linked the risk there, but no reporting job materialized in the queue. Nothing queue related happened at all, in fact, unless I saved the plan, but still there was no Reporting job (notice that I did not publish here, which definitely would have fired a reporting job). Then, I published the plan and the Reporting (Project Sync) job fired.
So, when the user creates the link between task and risk from PWA, the user must publish the plan in order to make the Reporting (Project Sync) job run. After that job runs, the information related to the link will be in the Reporting database. When the user creates the link between risk and task from the Risks list, the user doesn’t need to do anything else because the Reporting (Project Sync) job runs immediately.
This is because changes to Project Server Risks list on the site writes directly to published db so the reporting database has be to be updated. I ran a profiler trace and found a stored procedure that writes to the published DB when a risk to task link is made. When you edit a plan in PWA, your changes do not impact the published db until you publish, so absent any changes, the published and reporting data still match. Another way of looking at this behavior is to see that project managers may not want changes made to the plan published until they are ready for the changes to be publicly available. Changes to the site are publicly available without a project publish so the sync job fires when a task/risk is linked.
There real problem to my mind is that there is no way for users to know there is a difference in the behavior based on where you link the task/risk. That’s because there is no warning/dialog pop-up saying if you edit the plan in PWA, you won’t be able to report on the new link until you publish. The result is that a user links a task/risk via PWA and doesn’t know he can’t report on that link until he publishes while at the same time, the user can create the risk/task link from the Risk list and report right away.
From a database and code perspective, linking a risk to a task and linking a task to a risk are actually very different. Strictly speaking, linking a risk to a task from the Risks list writes to the Published DB, which then requires a corresponding update to the Reporting DB. Linking a task to a risk from the Project Center drill down view writes to the Draft database – and remember, you can make changes all day to the Draft and never see those changes until you publish the plan. While the actions the user takes appear to be roughly the same whether you link from a task to a risk or vice versa, the command being sent to the databases differ behind the veil of the user interface. The behavior editing a plan and not seeing those changes until a publish occurs is perfectly in line with a project’s not being made public until a project manager wants them to be by publishing.
Because the project site is already public, linking a risk to a task causes an update to occur against the reporting database. This behavior is unlikely to change in Project Server 2010, but the fact of the behavior should be relayed to project managers so they can determine the best choice for linking tasks and risks.
Naturally, this line of investigation led to testing on Project Server 2013. Does the same thing happen there?
For testing, I created a plan called 0000 Linking Risks and published it with a project site on an out of the box instance of PWA.
In PWA, I drill into the project plan schedule and you see the URL below:
I selected a task, then clicked on the Options tab, and then on the Related Items button. At this point, the URL changes to the project site URL, where I can create the association.
This means I’m in the project site when I make the association, so the Reporting job fires when I save. This DID NOT HAPPEN in Project Server 2010.
The fact I’m in the project site when I create the association means the behavior is different in Project Server 2013 and the Reporting (Project Sync) job fires because there is only one way to link a task to a risk. I thought this was worth pointing out.