A customer asked me today if he could make changes to the Reporting database in his Project Server 2010 environment. The short answer is, yes, you can create entities in the Reporting database. Below is a scrubbed version of the conversation.
Customer: I have a request from the business to create one or more custom tables in the Project Server Reporting Database and run reports against those tables using <Big Reporting Application> from <Big Application> Company. Do you have any information about best practices for making changes or what is allowed to be changed in the Project Server 2010 Reporting database?
Me: Add, but don’t modify or delete any entities already present and thoroughly test your additions in a test environment. Beyond that, the only best practices I would forward would be related to designing SQL tables and other entities and I would reach out to a SQL PFE for those.
Customer: Will Microsoft still support us if we create a custom SQL Table in the Project Server Reporting Database?
Me: Yes. Ideally, we are talking about only a few tables or less. If we are talking about using <Big Reporting Application> with many, many tables, I would recommend you consider a separate database (with all of its attendant overhead), but less exposure to upgrade risks.
Customer: Can Cumulative Updates or SP2 Updates impact the custom tables in the database?
Me: The risk of an impact on custom tables always exists and can be mitigated by thorough testing of any CUs or SPs in a test environment with the same configuration as the production tables. Also, good backups prior to an upgrade can alleviate concerns. Please see this link for information on what you can add tables/entities/etc. to the Reporting database: http://technet.microsoft.com/en-us/library/ff686786.aspx#section5. I also found this link: http://msdn.microsoft.com/en-US/library/ms504195(v=office.14).aspx#pj14_Programmability_Databases.