Database Diagrams–Project Server Reporting Database

by Welcome to Marquee Insights

image

These high level entity relationship diagrams were first published in the deck for my Project Conference Hands On Lab deck. I’ve had a number of requests for this information so here it is.

These diagrams are based on the 2010 RDB but the 2013 RDB should not be materially different. For 2007 users, many of these same entities also exist in the 2007 RDB as well. The UID fields are the keys used to join these entities together.

The name of the entity also contains the recommended table or view name to be used in the Reporting database. Any entity ending with _UserView will automatically include custom fields for that entity. Multi-value custom fields are not included and require special querying to retrieve (more on that later).

If you are tying different data entities together, you should also consider using the lowest level of detail for a given data element. For example, if you are querying Project – Task – Assignments – Resources, I would use AssignmentWork rather than TaskWork or ProjectWork as AssignmentWork will aggregate correctly in PivotTables. Otherwise, you will get a multiple of ProjectWork or TaskWork, depending on the number of records retrieved.

Click on the graphic below to make it bigger.

Project Server Reporting Database Entity Relationship Diagram

PSERD1

Project Server Reporting Database Timesheet Entity Relationship Diagram

PSERD2

Happy querying!

2 Responses to “Database Diagrams–Project Server Reporting Database”

May 27, 2013 at 8:02 am, MS Project Server said:

Great article! 🙂

Sincerely,
Martin from MS Project Server

Reply

February 05, 2014 at 7:29 am, Mahesh said:

very helpful. Thank you.
Mahesh.

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.