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:

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:

The following are parameters that define the index:

Parameters for MSP_Epm_CreateCustomFieldIndexByName

The following parameters identify the custom field:

The following are parameters that define the index:

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:

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:

If CF1 gets deleted, then the table will look like this:

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:

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.