Reporting Database Diagrams

Reporting Database Diagrams

Rate This
  • Comments 8

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

Attachment: RDBSchema.zip
Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
  • If you are interested in writing reports over the Project Server 2007 reporting database, Chris has published

  • If you are interested in writing reports over the Project Server 2007 reporting database, Jim Corbin

  • Hi,

    First, thank you all for the help you do try to give us!

    Now I want to ask the question straight – is it possible to have two or more rows in the actuals for the same line (simply with same TS_Line_Uid), so different values for the hours and the comments for the day can be given. I’ve been trying that for some time now and so far with no success, though I got a bunch errors while executing the job.

    Thanks

    Hristiyan

    Note: the “TimeByDay” column is different in any second actual row (with same date but different time) in order to have the lineUid/timeDate columns unique relation.

  • 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.

  • 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!

  • Is there a way to write a report that returns the cost rates for each resource?

  • 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 peopl

  • 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.

Page 1 of 1 (8 items)