Missing traditional SQL Reporting views in Project Server 2016 Project Web App Database?
Below are the possible reasons for those missing views and recommendations to resolve them.
Did you host multiple PWA site collections in a single Content DB? If yes then Project Server 2016 drops these views if there are multiple PWA site collections in a single Content DB. Microsoft designed Project Server 2016 Database SQL views in such a way that they will expose only the first PWA instance data through these views and that is why they are dropping them when you host multiple PWA instances in a single Content DB. So, it is always recommended to host each PWA site collection in a separate Content DB in case if you are going to use SSRS for your reporting otherwise you need to create your own views or you need to access pjrep schema Table Valued functions that are prefixed like pjrep.MSP_TVF to pull the data for reporting for different PWA instances.
Did you host your Project Web App instance as a root site in your Web Application? If yes then we have seen cases where the Reporting views are missing if you host your PWA as a root site. It is always recommended to create new PWA site colleciton on an explicit managed path (<%WebAppURL%>/pwa) or under a wildcard path (<%WebAppURL%>/sites/pwa) and do not create a new PWA site colleciton on a root of a Web application with host header as ‘/’ (<%WebAppURL%>/).
Here is the Powershell script to re-create those missing views in case if you feel none of the above reasons are causing the issue in your environment.
Repair-SPProjectWebInstance <%PWA URL%> -RepairRule 7
I hope you find this blog post helpful. For more tips and tricks on Project Management, please visit www.epmainc.com