Report Data Service optimizations for custom fields
This Office product will reach end of support on October 10, 2017. To stay supported, you will need to upgrade. For more information, see , Resources to help you upgrade your Office 2007 servers and clients.
Topic Last Modified: 2016-11-14
This article describes how to optimize custom reporting solutions built for the Reporting Database (RDB) of Microsoft Office Project Server 2007. If you are interested in building custom views or applying custom indexes on any views in the RDB, read this article for some helper stored procedures that can be used in conjunction with your solutions.
If you are not already familiar with the general mechanics of the RDB, see these background articles:
Reporting and the RDS: (https://go.microsoft.com/fwlink/?LinkId=123365)
Project Server Report Pack: (https://go.microsoft.com/fwlink/?LinkId=123367)
First, let us take a look at how custom field data is stored in the RDB. Office Project Server 2007 has several pre-defined custom fields. As the instance grows, new enterprise custom fields may be added and existing ones may be deleted during regular maintenance. The custom field storage mechanism in the RDB is designed to dynamically handle addition of new and removal of old fields, and it is de-normalized to optimize for more efficient cube-building and reporting operations. Custom fields are stored in multiple column pool tables MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask*, and MSP_EpmCPAssn*for Projects, Resources, Tasks, and Assignments data, respectively. As new custom fields are created, new columns are added to column pool tables of the corresponding entity type, and new tables are created when existing tables reach a certain number of columns. For a more detailed description of how custom fields are stored in RDB, see Local and Enterprise Custom Fields (https://go.microsoft.com/fwlink/?LinkId=123368) in the MSDN Library Online.
The Infrastructure Update for Microsoft Office Servers contains the following views that aggregate RDB custom field data for each of the four core entities:
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
These user views are maintained by Office Project Server and contain all custom fields defined for the corresponding entity. Whenever a custom field is added, a new column is automatically added to the corresponding view. Also, whenever a custom field is deleted, its corresponding column is removed from the view.
You may also build your own views customized for your organization’s needs. For example, if you have one report that uses a small subset of fields, instead of using the default views, you may choose to create your own custom views that include only relevant data.
Create custom views
To create your own custom views, you first need to find out where the field values are stored. Once you know which column pool table and column number points to the field of interest, you can then use a Join statement to pull values into your view. All column pool tables have an EntityUID column containing the unique identifier of the entity that a given data-row references.
Helper function
The following function returns interesting information about all custom fields.
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
Return values
The function returns a dataset with the custom field information (one row for each custom field). If no custom field is found, the function returns an empty dataset.
The returned dataset has one row for each custom field with the following columns:
| Value | Description |
|---|---|
EntityTypeUID |
The unique identifier of the parent entity for each custom field. (For example: for project custom fields, this column displays a value corresponding to 'Projects'.) |
EntityName |
The name of the parent entity of each custom field (in the example above, that would be 'Projects'). |
CustomFieldTypeUID |
The unique identifier of the custom field. |
CustomFieldName |
The name of the custom field. |
SecondaryCustomFieldTypeUID |
The id of the corresponding custom field. |
DataType |
Custom field data type. |
IsMultiValueEnabled |
The column displays 1 if the custom field can have multiple values. |
IsRollDown |
The column displays 1 if the custom field values are rolled down. |
LookupTableUID |
If the custom field uses a lookup table, this column displays its unique identifier. Otherwise the column is null. |
LookupTableName |
If the custom field uses a lookup table, this column displays its name. Otherwise the column is null. |
LookupTableMembersViewName |
Project Server creates a view for each lookup table defined. There is a view that selects all its members. This column displays the name of the view with the members of the lookup table used by the custom field. |
LookupTableHasMultipleLevels |
This column displays 1 if the lookup table has its values defined on more than one level. |
ColumnPoolColumnName |
The name of the column that stores custom field values. |
ColumnPoolTableName |
The table that stores the custom field values. |
EntityNonTimephasedTableName |
The table that stores non-time-phased data for the parent entity of the custom field. (For example: for a project custom field, the column displays 'MSP_EpmProject'.) |
CreatedDate |
The date when the custom field was created. |
ModificationDate |
The date when the custom field was last modified. |
Example
Here is an example to illustrate how to create a simple custom view showing two project custom field values.
For this example, assume that we have two predefined resource custom fields (RBS and Cost Type) that we want to see in the view, along with the resource name, resource id, resource standard rate, resource overtime rate, and the resource Windows NT account name. If you are sure that the custom field names are unique and they won't change, you can use the CustomFieldName column to filter by. However, a better idea would be to first do a SELECT operation like the following.
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
In the results, make sure that you have identified the custom fields you want, and then make note of their CustomFieldTypeUID values. (These are their unique ids.)
Let's assume for this example that the two unique identifiers you find are:
{0000783FDE84434B9564284E5B7B3F49} for RBS
{000039B78BBE4CEB82C4FA8C0C400284} for Cost Type
Using the two unique identifiers for the RBS and Cost Type from the example above, you can use them to write the following script:
--Declare the variables used
DECLARE @CommandTextnvarchar(4000)-- This is the buffer where
-- the command will be created
-- Declare the variables used
DECLARE
-- This is the information necessary about each custom field:
DECLARE @TableNameForCF1 nvarchar(100)
DECLARE @ColumnNameForCF1 nvarchar(100)
DECLARE @TableNameForCF2 nvarchar(100)
DECLARE @ColumnNameForCF2 nvarchar(100)
-- Get the information about RBS custom field:
SELECT
@TableNameForCF1 = ColumnPoolTableName,
@ColumnNameForCF1 = ColumnPoolColumnName
FROMMFN_Epm_GetAllCustomFieldsInformation()
WHERE
CustomFieldTypeUID = '{0000783F-DE84-434B-9564-284E5B7B3F49}'--RBS ID
-- Get the information about Cost Type custom field:
SELECT
@TableNameForCF2 = ColumnPoolTableName,
@ColumnNameForCF2 = ColumnPoolColumnName
FROMMFN_Epm_GetAllCustomFieldsInformation()
WHERE
CustomFieldTypeUID = '{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'-- Cost Type ID
--Now we can build the SELECT command that will get the data in the view
SET @CommandText = 'SELECT ResourceUID, ResourceName, ResourceNTAccount, ' +
'ResourceStandardRate, ResourceOvertimeRate,'
--If both custom fields are allocated in the same column pool table,
-- we just need to join with it once
IF @TableNameForCF1 = @TableNameForCF2
SET @CommandText = @CommandText + ' RCFV.' + @ColumnNameForCF1 + ', ' +
'RCFV.'+ @ColumnNameForCF2 + '' +
'FROM MSP_EpmResource' +
'INNER JOIN ' + @TableNameForCF1 + ' AS RCFV' +
' ON MSP_EpmResource.ResourceUID = RCFV.EntityUID'
ELSE
SET @CommandText = @CommandText + ' RCF1V.' + @ColumnNameForCF1 + ', ' +
'RCF2V.'+ @ColumnNameForCF2 + '' +
'FROM MSP_EpmResource' +
'INNER JOIN ' + @TableNameForCF1 + ' AS RCFV1' +
' ON MSP_EpmResource.ResourceUID = RCFV1.EntityUID' +
'INNER JOIN ' + @TableNameForCF2 + ' AS RCFV2' +
'ON MSP_EpmResource.ResourceUID = RCFV2.EntityUID'
--Now we have the command, we can execute it
SET @CommandText = 'CREATE VIEW MySampleView AS ' + @CommandText
EXECsp_executesql @CommandText
Create custom field indexes
It can be complicated to find out which column in which table a specific custom field’s values are saved in. Therefore, Project Server has two stored procedures that create an index on the appropriate column, taking as input the custom field and the index parameters.
Helper stored procedures
If and when you that a custom field needs an index to improve the performance of the queries used by some reports, you can use the following methods:
Method 1:
PROCEDURE MSP_CreateCustomFieldIndexByUID(@CustomFieldTypeUIDuniqueidentifier, @PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Method 2:
PROCEDURE MSP_CreateCustomFieldIndexByName(@customFieldName [NAME], @customFieldEntityName [NAME] = NULL,@PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Parameters for MSP_Epm_CreateCustomFieldIndexByUID
The following parameter identifies the custom field:
| Parameter | Description |
|---|---|
@CustomFieldTypeUID |
The unique id of the custom field on which the index will be created |
The following are parameters that define the index:
| Parameter | Description |
|---|---|
@PadIndex |
Optional. Specifies how much space to leave open on each page in the intermediate levels of the index. |
@FillFactor |
Optional. Specifies a percentage that indicates how full Microsoft SQL Server should make the leaf level of each index page during index creation. This parameter must have a value between 1 and 100. |
@NoRecomputeStatistics |
Optional. If the value is 1, then out-of-date index statistics are not automatically recomputed. |
@SortInTempDB |
Optional. If the value is 1, then the intermediate sort results used to build the index will be stored in the tempdb database. |
@FileGroup |
Optional. The index will be created on the specified file group. |
Parameters for MSP_Epm_CreateCustomFieldIndexByName
The following parameters identify the custom field:
| Parameter | Description |
|---|---|
@CustomFieldName |
The name of the custom field on which the index will be created. |
@CustomFieldEntityName |
Optional. The name of the entity on which the custom field is defined (for example: Project for project custom fields or Resource for the resource custom fields, etc.). |
The following are parameters that define the index:
| Parameter | Description |
|---|---|
@PadIndex |
Optional. Specifies how much space to leave open on each page in the intermediate levels of the index. |
@FillFactor |
Optional. Specifies a percentage that indicates how full SQL Server should make the leaf level of each index page during index creation. This parameter must have a value between 1 and 100. |
@NoRecomputeStatistics |
Optional. If the value is 1, then out-of-date index statistics are not automatically recomputed. |
@SortInTempDB |
Optional. If the value is 1, then the intermediate sort results used to build the index are stored in the tempdb database. |
@FileGroup |
Optional. The index will be created on the specified file group. |
For more information about the parameters that define the index creation, you can read a description of the CREATE INDEX command in the MSDN Library: CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?LinkID=94749).
Return values for both procedures
The following are return values for the previous procedures:
| Value | Description |
|---|---|
0 |
Success. The index has been successfully created. |
-1 |
Index not created because the requested custom field was not found. |
-2 |
The index already exists. |
-3 |
Index is not created; CREATE INDEX statement execution failed. |
-4 |
Failed to generate the CREATE INDEX statement. This statement is generated in a text variable, and then executed dynamically. This error is returned when the building of the command string fails. |
-5 |
The specified custom field could not be indexed with this method. There are some types of custom fields that can't be indexed by the provided stored procedures (such as multivalue custom fields). |
-6 |
The index could not be created because more than one custom field matched the specified criteria. This can happen if there are two or more custom fields with the same name (on different entities) and the method for indexing custom field by name is called with just the custom field name, without providing any entity name. |
Example
The following example uses one of the two predefined resource custom fields: Cost Type. There are also two methods of identifying the custom fields: by ID or by name. Below are usage examples for both methods, but the recommended way is to use ID to identify custom fields.
To create an index for the resource custom field “Cost Type” by name, call:
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
To create an index for this custom field by ID (see the previous section on fetching custom field UID by using the MFN_EpmGetAllCustomFieldsInformation function):
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
Making views and indexes "stick"
You can optimize report-generation using the above methods by applying indexes on custom fields and creating targeted/trimmed views as described in the previous sections. However, note that during an RDB refresh, indexes and customized views that use custom fields can be invalidated.
This happens because during a refresh, all custom-field column-pool tables are cleared and all custom fields are deleted from the RDB. During the resynchronization process the custom field allocation order might change. This means that the custom field values may be saved in a different column or even in a different table.
For example, imagine that there were two custom fields created in the following order: first CF1, then CF2, where CF1 and CF2 are both text custom fields. CF1 will get CFVal0 column in the table, and CF2 will get CFVal1. The column pool table will look something like this:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
Under budget |
On schedule |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
No baseline |
No baseline |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Over budget |
On schedule |
15 |
NULL |
If CF1 gets deleted, then the table will look like this:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
NULL |
On schedule |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
NULL |
No baseline |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
NULL |
On schedule |
15 |
NULL |
However, after a refresh the columns in the column pool are repopulated again (from scratch, and CF1 will not exist anymore and CF2 will now occupy the CFVal0 column). The table will look like this:
| EntityUID | CFVal0 | CFVal1 | CFVal2 |
|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
On schedule |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
No baseline |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
On schedule |
15 |
NULL |
If you have previously created a customized view or index pointing to CFVal1, after an RDB refresh, instead of pointing to CF2, it is now pointing to a different custom field. The bottom line is that in such cases the index ends up on the wrong column, which is not desirable. To solve this problem, if you are creating customized views or indexes to improve reporting performance, you should also consider creating a stored procedure:
PROCEDURE MSP_OnRefreshCompleted();
If this stored procedure exists, it is called automatically after an RDB refresh has completed successfully. It will re-create the custom field indexes and/or customized views.
Example
If you want the changes from the two examples above to remain valid after an RDB refresh, then you must convert the two scripts to a stored procedure and call it MSP_OnRefreshCompleted. You must also make this stored procedure reentrant (meaning that it will execute correctly if you call it multiple times in a row).
CREATE PROCEDUREMSP_OnRefreshCompleted
AS
BEGIN
-- Declare the variables used
DECLARE @CommandTextnvarchar(4000)-- This is the buffer where the commandwill be created
-- This is the information necessary about each custom field:
DECLARE @TableNameForCF1 nvarchar(100)
DECLARE @ColumnNameForCF1 nvarchar(100)
DECLARE @TableNameForCF2 nvarchar(100)
DECLARE @ColumnNameForCF2 nvarchar(100)
DECLARE@ViewNamenvarchar(100)SET @ViewName ='MySampleView'
--Drop the old view, if one exists
IFEXISTS(SELECT*FROMdbo.sysobjects WHEREid =OBJECT_ID('[dbo].['+@ViewName +']') AND
OBJECTPROPERTY(id,'IsView')= 1)
BEGIN
SET@CommandText ='DROP VIEW [dbo].['+ @ViewName +']'
EXECsp_executesql@CommandText
END
-- Get the information about RBS custom field:
SELECT
@TableNameForCF1 = ColumnPoolTableName,
@ColumnNameForCF1 = ColumnPoolColumnName
FROMMFN_Epm_GetAllCustomFieldsInformation()
WHERE
CustomFieldTypeUID = '{0000783F-DE84-434B-9564-284E5B7B3F49}'--RBS ID
-- Get the information about Cost Type custom field:
SELECT
@TableNameForCF2 = ColumnPoolTableNam
@ColumnNameForCF2 = ColumnPoolColumnName e,
FROMMFN_Epm_GetAllCustomFieldsInformation()
WHERE
CustomFieldTypeUID = '{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'-- Cost Type ID
--Now we can build the SELECT command that will get the data in the view
SET @CommandText = 'SELECT ResourceUID, ResourceName, ResourceNTAccount, ' +
'ResourceStandardRate, ResourceOvertimeRate,'
--If both custom fields are allocated in the same column pool table, we just need to join with it once
IF @TableNameForCF1 = @TableNameForCF2
SET @CommandText = @CommandText + ' RCFV.' + @ColumnNameForCF1 + ', ' +
'RCFV.'+ @ColumnNameForCF2 + '' +
'FROM MSP_EpmResource' +
'INNER JOIN ' + @TableNameForCF1 + ' AS RCFV' +
' ON MSP_EpmResource.ResourceUID = RCFV.EntityUID'
ELSE
SET @CommandText = @CommandText + ' RCF1V.' + @ColumnNameForCF1 + ', ' +
'RCF2V.'+ @ColumnNameForCF2 + '' +
'FROM MSP_EpmResource' +
'INNER JOIN ' + @TableNameForCF1 + ' AS RCFV1' +
' ON MSP_EpmResource.ResourceUID = RCFV1.EntityUID' +
'INNER JOIN ' + @TableNameForCF2 + ' AS RCFV2' +
'ON MSP_EpmResource.ResourceUID = RCFV2.EntityUID'
--Now we have the command, we can execute it
SET @CommandText = 'CREATE VIEW MySampleView AS ' + @CommandText
EXECsp_executesql @CommandText
-- Clear all the custom field indexes
EXECMSP_Epm_ClearAllCustomFieldIndexes
-- Re-Create all the indexes
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
END
GO
GRANTEXECONdbo.MSP_OnRefreshCompleted_TestTOProjectServerRole
GO
Now the customized view "MySampleView" and the custom field index on "Cost Type" will automatically be reapplied after an RDB refresh.