This post has been republished via RSS; it originally appeared at: Project Support Blog articles.
First published on TECHNET on Aug 17, 2016In Project Server 2016 you can still make direct access to the database for reporting as long as each PWA site is in its own Content Database – but you may have noticed that the PSDataAccess role has an explicit Deny on Select for all tables in the database. As an example lets take a look at pjrep.MSP_EpmEnterpriseProjectType – a useful table to find the different EPT information. Looking at the PSDataAccess role permissions I can see the Deny is set.
This is not a bug or miss-configuration – but by design. All access to table information is available through Table Valued Functions – or TVFs. (Look under Programmability, Functions, Table Valued Functions if you are using SQL Server Management Studio). These are basically functions that return a table as the result – and they are called with a a parameter of the SiteID of the PWA you are accessing. The SiteID field can be found in each table usually as column 1 – and is the identifier used to ensure segregation of the multiple PWA sites that could exist in a single Content DB. I say ‘could’ as we do support multiple PWA in the same database but we do NOT support direct database access to reporting if there is more than one (we don’t generate the views). OLAP and ODATA is still supported in these situations *** (9/21/2017 correction - OLAP is NOT supported when there is more than one PWA) *** – and these methods also use the SiteID behind the scenes to ensure the correct data is returned.
The TVF to get to the pjrep.MSP_EpmEnterpriseProjectType table is called pjrep.MSP_TVF_EpmEnterpriseProjectType. To retrieve data simple select and add the parameter – so for my site this happens to be
Select * from pjrep.MSP_TVF_EpmEnterpriseProjectType('8F2455DA-2745-40AE-B9E0-F1E6DD573AFB')
I’m aware this may break some of your reports written against earlier Project Server data stores – but hopefully the correction is pretty straightforward.