自定义域的报告数据服务优化

更新时间: 2009年12月

上一次修改主题： 2015-03-09

本文介绍如何优化为 Microsoft Office Project Server 2007 的报告数据库 (RDB) 构建的自定义报告解决方案。如果您对生成自定义视图或在 RDB 中的任何视图中应用自定义索引感兴趣，请参阅本文以了解可以与您的解决方案结合使用的某些 Helper 存储过程。

如果您对 RDB 的通用机制不熟悉，请参阅以下背景文章：

• 报告数据库和报告数据服务（该链接可能指向英文页面）：(https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x804)（该链接可能指向英文页面）

• Project Server 报告包（该链接可能指向英文页面）：(https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x804)（该链接可能指向英文页面）

首先，让我们了解一下自定义域数据是如何存储在 RDB 中的。 Office Project Server 2007 具有若干个预定义的自定义域。随着该实例的不断增大，在进行常规维护时，可能会添加新的企业自定义域，并删除现有的域。RDB 中的自定义域存储机制专为动态处理新域的添加和旧域的删除而设计，它取消了规范化，为的是优化该存储机制以实现更有效的多维数据集生成和报告操作。自定义域将存储在多个列库表 MSP_EpmCPPrj*（包含项目数据）、MSP_EpmCPRes*（包含资源数据）、MSP_EpmCPTask*（包含任务数据）和 MSP_EpmCPAssn*（包含工作分配数据）中。在创建新的自定义域时，新列也会添加到相应的实体类型的列库表中。当现有表的数量达到了一定的列数量时，将会创建新表。有关自定义域如何存储在 RDB 中的更多详细说明，请参阅 MSDN Library Online 中的本地和企业自定义域（该链接可能指向英文页面） (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x804)（该链接可能指向英文页面）。

Microsoft Office Server 的基础结构更新包含了如下视图，这些视图汇集了每个核心实体（共四个）的 RDB 自定义域数据：

• MSP_EpmProject_UserView

• MSP_EpmTask_UserView

• MSP_EpmAssignment_UserView

• MSP_EpmResource_UserView

这些用户视图是通过 Office Project Server 来维护的，它们包含了为相应实体定义的所有自定义域，无论何时添加自定义域时，新的列都会自动添加到相应的视图中。并且在删除自定义域时，其相应的列也会从视图中删除。

您也可以根据组织的需要生成自定义的视图。例如，如果您具有一个使用一小部分域的报告，则无需使用默认视图，您就可以选择创建只包含相关数据的自定义视图。

创建自定义视图

若要创建自定义的视图，首先需要找到存储域值的位置。当知道了哪个列库表和列号指向所需的域时，就可以使用 Join 语句将值放入您的视图中。所有列库表都具有一个 EntityUID 列，它包含了指定的数据行引用的实体的唯一标识符。

Helper 函数

以下函数将返回所有自定义域的相关信息。

FUNCTION MFN_Epm_GetAllCustomFieldsInformation();

返回值

该函数将返回带有自定义域信息（每个自定义域代表一行）的数据集。如果未找到自定义域，该函数将返回空的数据集。

在返回的数据集中，每个自定义域都对应一行，其中自定义域包含以下列：

示例

以下示例将说明如何创建一个简单的自定义视图来显示两个项目自定义域值。

在本示例中，假设我们具有两个要在视图中查看的预定义资源自定义域（RBS 和成本类型）以及资源名称、资源 ID、资源标准费率、资源加班费率和资源 Windows NT 帐户名称。如果您确信自定义域名称是唯一的并且不会更改，则可以使用 CustomFieldName 列进行筛选。但是，最好还是首先执行 SELECT 操作，如下所示。

SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'

在结果中，请确保您已标识了所需的自定义域，然后记录这些域的 CustomFieldTypeUID 值。（这些是其唯一的 ID。）

让我们假设在本示例中，您可以找到以下两个唯一标识符：

• {0000783FDE84434B9564284E5B7B3F49} 用于 RBS

• {000039B78BBE4CEB82C4FA8C0C400284} 用于成本类型

使用上例中的 RBS 和成本类型的两个唯一标识符，您可以编写以下脚本：

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

创建自定义域索引

找到保存特定自定义域值所在的表和列是一件很复杂的事情。因此，Project Server 具有两个存储过程，用于在相应的列上创建索引，这些过程将作为对自定义域和索引参数的输入。

Helper 存储过程

当自定义域需要索引来提高某些报告使用的查询的性能时，您可以使用以下方法：

方法 1：

PROCEDURE MSP_CreateCustomFieldIndexByUID(@CustomFieldTypeUIDuniqueidentifier, @PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);

方法 2：

PROCEDURE MSP_CreateCustomFieldIndexByName(@customFieldName [NAME], @customFieldEntityName [NAME] = NULL,@PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);

MSP_Epm_CreateCustomFieldIndexByUID 的参数

以下参数用于标识自定义域：

下面是用于定义索引的参数：

MSP_Epm_CreateCustomFieldIndexByName 的参数

以下参数用于标识自定义域：

下面是用于定义索引的参数：

有关定义索引创建的参数的详细信息，可以参阅 MSDN 库中的 CREATE INDEX 命令说明：CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x804)。

两个过程的返回值

下面是前一个过程的返回值：

示例

以下示例使用两个预定义的资源自定义域中的一个：成本类型。还提供了两种标识自定义域的方法：按 ID 或按名称。下面是这两种方法的使用示例，但是建议使用 ID 来标识自定义域。

若要按名称创建资源自定义域“成本类型”的索引，请调用：

EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'

按 ID 创建此自定义域的索引（请参阅有关使用 MFN_EpmGetAllCustomFieldsInformation 函数获取自定义域 UID 的前一部分内容）：

EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'

使视图和索引“继续有效”

您可以使用上述方法来优化报告生成操作，方法是：按前面部分所述在自定义域上应用索引并创建目标/剪切视图。但是，请注意，在 RDB 刷新期间，使用自定义域的索引和自定义视图可能会失效。

这是因为在刷新期间，所有自定义域的列库表都被清除，并且所有自定义域都从 RDB 中被删除。在重新同步的过程中，自定义域的分配顺序可能会发生改变。这就意味着自定义域值可能会保存在其他列中，甚至是其他表中。

例如，假设按以下顺序创建了两个自定义域：第一个是 CF1，另一个是 CF2，其中 CF1 和 CF2 都是文本自定义域。CF1 将获取表中的 CFVal0，而 CF2 将获取 CFVal1。列库表将显示如下：

如果删除了 CF1，则该表将显示如下：

但是，在刷新后，将从头开始重新填充列库中的列（CF1 将不再存在，而 CF2 将会立即占据 CFVal0 列）。此表将如下所示：

如果先前创建了指向 CFVal1 的自定义视图或索引，在经过 RDB 刷新后，此内容将会立即指向不同的自定义域，而不是指向 CF2。结果就是，在这些情况下，索引最终出现在错误的列上，这也是不希望看到的结果。为了解决此问题，在您创建自定义视图或索引以提高报告性能的同时，还应该考虑创建一个存储过程：

PROCEDURE MSP_OnRefreshCompleted();

如果具有存储过程，则会在成功完成 RDB 刷新后自动调用该过程。该过程将重新创建自定义域索引和/或自定义的视图。

示例

如果希望在 RDB 刷新之后仍然使上述两个示例的更改保持有效，就必须将两个脚本转换到存储过程中，然后调用 MSP_OnRefreshCompleted。另外，还必须使存储过程成为可重入 过程（也就是说，在某行中多次调用该过程时，该过程仍能正常执行）。

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

在 RDB 刷新后，将立即自动重新应用自定义的视图“MySampleView”和“成本类型”的自定义域索引。