Extracting Project Actuals information from Microsoft Project Server (MSPS)

MSPS has four databases that it stores data from Enterprise Project Server – Draft, Published, Reporting, and Archive databases.   It is recommended not to query Draft and Published databases directly.   Reporting database as name indicates is the right database to query directly to extract MSPS data for further reporting of consumption in other applications.    I had a requirements to extract Project Actuals data and push it to one of custom app.   Following are the views that I touched to extract data from -

  1. MSP_EpmProject_UserView – Project Information View (High level Project definition data)
  2. MSP_EpmTask_UserView – Task Information View
  3. MSP_EpmResource_UserView – Resource Information View – All the Resource related information (name, windows NT account, CostCenter, etc.)
  4. MSP_EpmAssignment – Assignment Information Table
  5. MSP_EpmAssignmentByDay – Assignment Information Table  -- this is to extract Assignment Actual Work (TimeinHours) and Assignment Actual Cost (Invoiced Cost or any similar non-Labor cost)