In this article, we will take an overview of the Project Server Database changes to know Where Project data stored in SQL Server to query Enterprise Custom Fields from Project Server database.
Applies To
- Project Server 2019.
- Project Server 2016.
- Project Server 2013.
- Project Server 2010.
PWA Data Structure in Project Server
Before we getting started to query the custom fields from Project Server Database, let’s first have a quick journey to briefly explore the PWA structure in Project Server.
What’s PWA Instance?
The Project Web App Instance is a SharePoint site collection with a PWA site template that requires an independent license to be able to enable the PWA features on it.
In PWA instance, we have
- A project center that holds project info.
- Each project consists of related tasks and each task can be assigned to one or more resources!
- Each project has its project site as a sub site under the PWA instance.
- This project site by default has risk and issue lists and document library to upload documents and files for its related project.
Therefore, we can initially categorize the data stored in Project Server to main two categories:
- PWA info
- Projects.
- Tasks.
- Resources.
- Configuration settings.
- Other data.
- Project Sites.
- Lists.
- Document Library.
- Custom Pages.
You might also like to read Install and Configure Project Server 2016 step by step.
In this article, we will mainly concentrate on how to query enterprise custom fields from Project Server Database in Project Server versions, so please, let us first explore what’s the custom fields and how to manage and create it in Project Server.
Custom Fields in Project Server
In Project server as well as Project online, besides the default fields like Project Name and Start, End ..etc, you can easily manage Enterprise Custom Fields and Lookup Tables to match your business requirements in a flexible manner.
Enterprise Custom Fields
In PWA Settings, below “Enterprise Data“, you have an option to manage, create, and view a summary of the custom fields and lookup tables defined for Project Web App.
From this page, you can also drill in to create a new custom field or lookup table, or modify the definition of an existing custom field or lookup table.
You might also like to read Export Enterprise Custom Fields Details in Project Server
Create a new custom field in Project Server
You can easily click on “New field” to create a new custom field or on “Copy field” to create a new custom field from an existing custom field.
When you create a new field, you should specify the following:
- Field Name is mandatory and it must be unique and not used before.
- Field Description is optional to describe your field name in more detail.
- Entity: select which entity the new custom field will belong to.
- Project.
- Task.
- Resource.
- Type: select the data type of the new custom field.
- Custom Attributes: Choose whether the field has
- Single line of text,
- Multiple lines of text,
- Lookup table, or
- Calculated formula.
Note: Fields with multiple lines of text will not be available in the Project client.
- Department: is a default lookup table field that you can fill it with your own values to classify Each Custom Field can belong to a specific Department.
Note: If a Department is not selected then the Custom Field will be assumed to be available across the server.
Values to Display: Choose whether you want just the data or graphical indicators to be displayed.
Note: Graphical indicators are not displayed in all areas of Project Web App.
Behavior: These settings affect the behavior of this custom field. If the behavior is controlled by workflow, this custom field can be made required or read-only at the workflow stage level.
Lookup Tables
As you can see, when you create a new custom filed one of the attributes options is “Lookup Table” to create a field from a predefined list.
The predefined list can be configured by creating a new lookup table.
Again, to create a new Lookup table, you should specify its unique name and type. Also, you should Specify the “Code mask” for the lookup table. The code mask defines for each level the number and sequence of characters that are allowed.
- For Length, you can type Any or a number from 1 through 255.
- For the separator, type from one through three characters.
Moreover, you can specify the lookup tables values and build its hierarchy and control the order items as you prefer.
Where Project Data stored in SQL Server?
Now, Let’s go back to our main question that is Where Project Server data is stored in SQL Server?
As we earlier mentioned, we have categorized the Project Server data into two main categories Project Info and Project Sites.
Of course, these data stored in specific databases hosted on SQL Server. but each Project Server version has its own structure to save it. So, in the next section, we will take an overview of the database changes for each Project Server version from Project Server 2010 to Project Server 2019.
You might also like to read What’s new and deprecated in Project Server 2016?
Project Server Databases
Actually, there are continuous enhancements for the Project Server databases to facilitate task operations, such as backup and restore, migration, etc. however, many tables and views still do the same job in all Project Server versions with some changes like the database name and schema …etc.
In this section, we will take a quick overview of the database structure changes. Also, we will show how to retrieve all custom field information details. Moreover, we will learn how to query Custom Fields from Project Server database.
You might also like to read PWA Content Database Consideration In Project Server.
Project Server 2010 Databases
In Project Server 2010, the data is partitioned into five databases:
- Four databases for the PWA data, and
- One content database that holds the PWA Site Collection and its project sites content.
PWA info is stored in the following 4 databases:
- Archive Database.
- The archive database is used to store online backups of data from the rest of the site.
- Draft Database.
- The Draft database contains tables for editing unpublished projects from Microsoft Project Professional 2010 and the Project Server Interface Web services. Project data in the Draft database cannot be accessed by using Microsoft Project Web App. This database also hosts the tables that are used by the Project Queue.
- Published Database.
- The Published database contains all of the published projects. Published projects are visible in Project Web App. The Published database also contains tables that are specific to Project Web App (timesheets, models, views, and so on), and global data tables (which contain resources, custom fields, security definitions, and other metadata). This database also hosts the tables that are used by the Timesheet Queue.
- Reporting Database.
- The Reporting database contains the same data as the Published database but optimized for generating reports and online analytical processing (OLAP) cubes. Data in the Reporting database is updated almost in real-time, is comprehensive, and is optimized for read-only report generation.
Project Site is stored in a content database:
- Content Database.
- The Content database contains project site content such as custom pages, workflows, management settings, documents, and lists of issues, risks, …etc
Query Custom Fields from Project Server Database in Project Server 2010
In Project Server 2010, you should use the “Reporting” database to query custom fields based on its related entity (Project, Task, Resource) from the following database Views:
EPM Project User View
In”MSP_EpmProject_UserView“, you can find the built-in project fields, and the enterprise custom fields for project entity that you have created.
SELECT * FROM [MSP_EpmProject_UserView]EPM Task User View
In”MSP_EpmTask_UserView“, you can find the built-in Task fields, and the enterprise custom fields for task entity that you have created.
SELECT * FROM [MSP_EpmTask_UserView]EPM Resource User View
In”MSP_EpmResource_UserView“, you can find the built-in Resource fields, and the enterprise custom fields for resource entity that you have created.
SELECT * FROM [MSP_EpmResource_UserView]Project Server 2013 Database
In Project Server 2013, the 4 Project databases (Draft, Archive, Publishing, and Reporting) have been consolidated to one ProjectWebApp Database, plus one content database that holds the PWA Site Collection and its project sites content.
Query Custom Fields from Project Server Database in Project Server 2013
In Project Server 2013, you should use the “ProjectWebApp” database to query custom fields based on its related entity (Project, Task, Resource) from the following database Views:
EPM Project User View
In”MSP_EpmProject_UserView“, you can find the built-in project fields, and the enterprise custom fields for project entity that you have created.
SELECT * FROM [ProjectWebApp].[dbo].[MSP_EpmProject_UserView]EPM Task User View
In”MSP_EpmTask_UserView“, you can find the built-in Task fields, and the enterprise custom fields for task entity that you have created.
SELECT * FROM [ProjectWebApp].[dbo].[MSP_EpmTask_UserView]EPM Resource User View
In”MSP_EpmResource_UserView“, you can find the built-in Resource fields, and the enterprise custom fields for resource entity that you have created.
SELECT * FROM [ProjectWebApp].[dbo].[MSP_EpmResource_UserView]Project Server 2016 Database
In Project Server 2016, the Project PWA database and the content database have been consolidated to a single SharePoint Content Database that holds the PWA Site Collection with its project site content as well as project data.
Query Custom Fields from Project Server Database in Project Server 2016
In Project Server 2016, you should use the “pjrep.” schema to query custom fields based on its related entity (Project, Task, Resource) from the following database Views:
EPM Project User View
In”pjrep.MSP_EpmProject_UserView“, you can find the built-in project fields, and the enterprise custom fields for project entity that you have created.
SELECT * FROM pjrep.MSP_EpmProject_UserViewEPM Task User View
In”pjrep.MSP_EpmTask_UserView“, you can find the built-in Task fields, and the enterprise custom fields for task entity that you have created.
SELECT * FROM pjrep.MSP_EpmTask_UserViewEPM Resource User View
In”MSP_EpmResource_UserView“, you can find the built-in Resource fields, and the enterprise custom fields for resource entity that you have created.
SELECT * FROM pjrep.MSP_EpmResource_UserViewProject Server 2019 Database
In Project Server 2019, it has the same structure and schema as Project Server 2016.
Note: in all Project Server versions, the Enterprise custom fields with a lookup table single value will be listed in “MSP_EpmProject_UserView“. but the fields with a lookup table multiple values enabled will not be listed directly in “MSP_EpmProject_UserView”. it will be stored in an association table as mentioned at Query Multiple Values Lookup Table Field in Project Server.
Conclusion
In conclusion, we have explored the PWA data structure to know Where Project data stored in SQL Server to be able to query Enterprise Custom Fields from Project Server database.
See Also
- Project Server 2013 Migration from Staging to Production Farm.
- Migrate to Project Server 2016 Step By Step.
- SQL Server Best Practices for SharePoint 2019.
- GitHub Repository for Project Server Queries.
Have a Question?
If you have any related questions, please don’t hesitate to ask it at deBUG.to Community.