Welcome to MSDN Blogs Sign in | Join | Help

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

Published Tuesday, December 05, 2006 3:34 AM by Chris Boyd

Attachment(s): RDBSchema.zip

Comments

Monday, December 04, 2006 7:47 PM by Microsoft Office Project 2007

# Reporting Database Documentation Published

If you are interested in writing reports over the Project Server 2007 reporting database, Chris has published

Monday, December 04, 2006 9:36 PM by Office News

# Reporting Database Documentation Published

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

Tuesday, December 12, 2006 7:46 AM by Hristiyan

# re: Reporting Database Diagrams

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.

Tuesday, January 09, 2007 11:02 AM by Carles Prat

# re: Reporting Database Diagrams

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.

Wednesday, December 12, 2007 3:52 PM by Julia_Reese

# re: Reporting Database Diagrams

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!

Wednesday, May 14, 2008 8:45 PM by fddavis69

# re: Reporting Database Diagrams

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

Friday, June 06, 2008 6:55 AM by Weddings

# Project Programmability : 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 peopl

Anonymous comments are disabled
 
Page view tracker