Reporting Database Diagrams
A number of people have asked for entity-relationship diagrams of the Reporting database (RDB) for Project Server 2007. The attached RDBSchema.zip file contains RDB Schema.vsd,a Microsoft Office Visio file with the main tables and views that most people use to make reports. The diagram pages are formatted for Tabloid (B)-size paper, 11 x 17 inches.
The pages are:
· EPM User Views: schemas of the Project, Resource, Task, Assignment, and related ByDay user views.
· EPM Relationships: a simplified E-R diagram of the Project, Resource, Task, Assignment, and related ByDay tables.
· Timesheet Tables
· SharePoint Data: tables and user views for issues, risks, deliverables, and list item associations.
The diagrams were created using Visio Professional 2007. Visio Standard doesn’t include the database capabilities. You can use Visio Professional 2003 SP2 or Visio Professional 2007 to reformat the pages for a larger printer or rearrange and create additional E-R diagrams. The first page, in particular, could use a larger page size to expand the views (MSP_EpmTask_UserView has a 4.8 point font size to fit on an 11 x 17 page). To see the list of all 108 tables and views in the RDB, click the Visio Database menu, click View, and then click Tables and Views. Create a new page and drag items from the Tables and Views pane to the page.
If you don’t have one of the required versions of Visio, you can use the free Visio 2007 Viewer to see the diagram pages in Internet Explorer, although printouts of the large pages with the Visio Viewer are not as good as printing with Visio.
The Project 2007 SDK download includes the HTML Help version of the RDB Schema reference, pj12ReportingDB.chm. In the Visio diagram, the Notes fields for the tables, views, and columns include the same comments that are in the pj12ReportingDB.chm (with an update for the MSP_WssListItemAssociation table). To see the notes in Visio, right-click a table or view, and then click Database Properties. Click Notes in the Categories list to see the table or view notes; or click Columns to see the notes and other properties of all the columns in that table. You can modify the RDB to add your own tables, views, and columns for custom reports, and then add notes and custom content in the RDB Schema diagram with Visio.
--Jim Corbin
Comments
Anonymous
December 04, 2006
If you are interested in writing reports over the Project Server 2007 reporting database, Chris has publishedAnonymous
December 04, 2006
If you are interested in writing reports over the Project Server 2007 reporting database, Jim CorbinAnonymous
December 11, 2006
The comment has been removedAnonymous
January 09, 2007
In diagram called "EPM User View Pseudo-Relationships", is correct the specification of table Msp_EmpAssignmentByDay? There are a column named ResourceUID that I don't see in the "Database reference - ProjectServer_Reporting". Exists this column in the table? Thank you.Anonymous
December 12, 2007
Hello! The Reporting Database Diagrams are very helpful, but where can I find the field definitions/ descriptions? (For example, under Timesheet Tables -> MSP_TimesheetProject, does LatestDate=Submitted date? Thank you!Anonymous
May 14, 2008
Is there a way to write a report that returns the cost rates for each resource?Anonymous
June 05, 2008
A number of people have asked for entity-relationship diagrams of the Reporting database (RDB) for Project Server 2007. The attached RDBSchema.zip file contains RDB Schema.vsd ,a Microsoft Office Visio file with the main tables and views that most peoplAnonymous
April 29, 2010
Can It be possible to create custom table in reporting database by any mean.If yes than how can we do that. Also can we create custom table in Publish database through PSI.