Export (0) Print
Expand All

Report Data Service optimizations for custom fields

Updated: December 10, 2009

 

Topic Last Modified: 2009-12-03

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:

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 (http://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.

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.

The following function returns interesting information about all custom fields.

FUNCTION MFN_Epm_GetAllCustomFieldsInformation();

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.

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:

  • {0000783F‑DE84‑434B‑9564‑284E5B7B3F49} for RBS

  • {000039B7‑8BBE‑4CEB‑82C4‑FA8C0C400284} 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

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.

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);

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.

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) (http://go.microsoft.com/fwlink/?LinkID=94749).

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.

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}'

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.

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.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft