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