Where in the SDK are the Visio diagrams for the RDB?

  • Article

The Project 2007 SDK download includes E-R diagrams of the major tables and views in the Project Server Reporting database. The Project 2010 SDK download does not include the Visio diagrams, although it does have the new RDB_Field_Selector.xls tool, along with the Project2010_ReportingDB.chm schema reference. You can easily create database diagrams by using either Visio Professional or Microsoft SQL Server Management Studio.

For example, with a Visio diagram, you can see the relationships between the timesheet tables:

TimesheetTables_84

If you have Visio Professional 2010 and access to a Project Server 2010 Reporting database, you can reverse engineer the RDB to new database diagrams. Use the Visio diagrams in the Project 2007 SDK download as an example.

With the Reverse Engineer Wizard in Visio, select the Microsoft SQL Server driver, and then create a new data source, for example:

VisioReverseEngineerWizard

… and then follow through the wizard to select the tables and views you want for each diagram. In the Project 2007 SDK, there are four E-R diagrams:

  • EPM User Views:  this is a very crowded page, suitable mainly for printing on a large sheet of paper.
  • EPM Relationships:  shows the main relationships between the views in the previous page.
  • Timesheet Tables
  • SharePoint Data:  shows the links from tasks and projects to the Issues and Risks views.

To create diagrams using SQL Server Management Studio, expand a Project Server Reporting database, right-click the Database Diagrams folder, and then click New Database Diagram. Select the tables you want, and SQL Server arranges them in a useful manner so that relationship lines don’t cross. Following are the timesheet tables in the Project Server 2010 Reporting database:

SQLDiagram_Timesheets_84

Hover the mouse pointer over a relationship to show the relationship details.Database diagrams can help you derive T-SQL queries to build reports.

Note: When you create enterprise custom fields with lookup tables, Project Server creates additional views in the RDB, and can also create more column pool tables as required, for that instance of Project Web App.

For more information, see Custom Fields and the Reporting Database in the Project 2010 SDK.