自訂欄位的報告資料服務最佳化
更新日期: 2009年12月
上次修改主題的時間： 2015-02-27
本文將告訴您，如何最佳化為 Microsoft Office Project Server 2007 報表資料庫 (RDB) 所建立的自訂報表解決方案。如果您想要建立自訂檢視或對 RDB 的任何檢視套用自訂索引，請閱讀本文，以瞭解一些可以與您的解決方案搭配使用的協助程式預存程序。
如果您還不熟悉 RDB 的一般機制，請參閱下列背景文章：
報表資料庫和報表資料服務 (英文) ：(https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x404)
Project Server 報表套件 (英文) ：(https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x404)
首先，讓我們看看如何在 RDB 中儲存自訂欄位資料。 Office Project Server 2007 具有數種預先定義的自訂欄位。隨著執行個體的成長，可能會新增企業自訂欄位，而且可能會在一般維護期間刪除現有的自訂欄位。RDB 中的自訂欄位儲存機制係設計成動態處理新欄位的新增作業及舊欄位的移除作業，而且會予以反正規化來進行最佳化，以更有效率地進行 Cube 建立和報告作業。專案、資源、任務及工作分派資料的自訂欄位會分別儲存在下列多個資料行集區資料表中：MSP_EpmCPPrj*、MSP_EpmCPRes*、MSP_EpmCPTask* 及 MSP_EpmCPAssn*。建立新的自訂欄位時，會將新資料行新增至對應實體類型的資料行集區資料表，而且會在現有的資料表達到特定資料行數時建立新的資料表。如需如何在 RDB 中儲存自訂欄位的詳細說明，請參閱 MSDN Library Online 的本機和企業自訂欄位 (英文) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x404) 。
Microsoft Office Servers 基礎結構更新 包含下列檢視，分別為四個核心實體彙總 RDB 自訂欄位資料：
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
這些使用者檢視是由 Office Project Server 所維護，且包含所有為對應實體所定義的自訂欄位。只要新增自訂欄位，即會在對應檢視中新增資料行。此外，只要刪除自訂欄位，也會一併從檢視中移除其對應資料行。
您也可以建立針對組織需求自訂的專屬檢視。例如，如果您有某份報表使用少數欄位，而不是使用預設檢視，則可以選擇建立僅包含相關資料的專屬自訂檢視。
建立自訂檢視
若要建立專屬的自訂檢視，則需要先瞭解欄位值的儲存位置。知道是哪個資料行集區資料表且資料行編號指向所需的欄位之後，即可使用 Join 陳述式將值提取至您的檢視。所有資料行集區資料表都會有 [EntityUID] 資料行，內含指定資料列所參照之實體的唯一識別碼。
協助程式函式
下列函式會傳回所有自訂欄位的相關所需資訊。
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
傳回值
該函式會傳回含有自訂欄位資訊的資料集 (代表每個自訂欄位的一個資料列)。如果找不到自訂欄位，則函式會傳回空的資料集。
傳回的資料集具有代表每個自訂欄位的一個資料列及下列資料行：
| 值 | 描述 |
|---|---|
EntityTypeUID |
每個自訂欄位之上層實體的唯一識別碼 (例如：若是專案自訂欄位，則此資料行會顯示與 'Projects' 對應的值)。 |
EntityName |
每個自訂欄位之上層實體的名稱 (在上例中，為 'Projects')。 |
CustomFieldTypeUID |
自訂欄位的唯一識別碼。 |
CustomFieldName |
自訂欄位的名稱。 |
SecondaryCustomFieldTypeUID |
對應自訂欄位的識別碼。 |
DataType |
自訂欄位資料類型。 |
IsMultiValueEnabled |
如果自訂欄位可以有多個值，則此資料行會顯示 1。 |
IsRollDown |
如果展開自訂欄位值，則此資料行會顯示 1。 |
LookupTableUID |
如果自訂欄位使用查閱資料表，此資料行會顯示其唯一識別碼。否則此資料行會是 Null。 |
LookupTableName |
如果自訂欄位使用查閱資料表，此資料行會顯示其名稱。否則此資料行會是 Null。 |
LookupTableMembersViewName |
Project Server 會為每一個定義的查閱資料表建立檢視。其中一個檢視會選取其所有成員。此資料行會顯示具有自訂欄位所用之查閱資料表成員的檢視名稱。 |
LookupTableHasMultipleLevels |
如果在多個層級定義查閱資料表的值，則此資料行會顯示 1。 |
ColumnPoolColumnName |
儲存自訂欄位值的資料行名稱。 |
ColumnPoolTableName |
儲存自訂欄位值的資料表。 |
EntityNonTimephasedTableName |
針對自訂欄位的上層實體儲存非時段資料的資料表 (例如：若是專案自訂欄位，則此資料行會顯示 'MSP_EpmProject')。 |
CreatedDate |
自訂欄位的建立日期。 |
ModificationDate |
自訂欄位的上次修改日期。 |
範例
以下範例說明如何建立簡單的自訂檢視，以顯示兩個專案自訂欄位值。
在此範例中，假設具有兩個想要顯示在檢視中的預先定義資源自訂欄位 (RBS 和「成本類型」)，以及資源名稱、資源識別碼、資源標準工資率、資源加班工資率和資源 Windows NT 帳戶名稱。如果您確定自訂欄位名稱是唯一的，而且不會變更，則可以使用 [CustomFieldName] 資料行作為篩選依據。不過，最好是先執行 SELECT 作業 (如下所示)。
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
在結果中，確定找到想要的自訂欄位，然後記下其 [CustomFieldTypeUID] 值 (這些是其唯一識別碼)。
假設您在此範例中找到的兩個唯一識別碼為：
{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 提供兩個預存程序以建立適當資料行的索引，並使用自訂欄位和索引參數作為輸入。
協助程式預存程序
如果自訂欄位需要索引來改善部分報表所使用查詢的效能，則可以使用下列方法：
方法 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 的參數
下列參數可識別自訂欄位：
| 參數 | 描述 |
|---|---|
@CustomFieldTypeUID |
要在其上建立索引之自訂欄位的唯一識別碼。 |
以下是定義索引的參數：
| 參數 | 描述 |
|---|---|
@PadIndex |
選擇性。指定索引中繼層級中每一頁面上保留供使用的空間。 |
@FillFactor |
選擇性。指定百分比，以指出完整 Microsoft SQL Server 應該如何在建立索引期間建立每個索引頁面的分葉層級。此參數的值必須介於 1 與 100 之間。 |
@NoRecomputeStatistics |
選擇性。若此值為 1，將不會自動重新計算過期的索引統計資料。 |
@SortInTempDB |
選擇性。若此值為 1，則用來建立索引的中繼排序結果會儲存於 tempdb 資料庫。 |
@FileGroup |
選擇性。將會針對指定的檔案群組建立索引。 |
MSP_Epm_CreateCustomFieldIndexByName 的參數
下列參數可識別自訂欄位：
| 參數 | 描述 |
|---|---|
@CustomFieldName |
要在其上建立索引之自訂欄位的名稱。 |
@CustomFieldEntityName |
選擇性。在其上定義自訂欄位的實體名稱 (例如：Project 代表專案自訂欄位，或 Resource 代表資源自訂欄位，以此類推)。 |
以下是定義索引的參數：
| 參數 | 描述 |
|---|---|
@PadIndex |
選擇性。指定索引中繼層級中每一頁面上保留供使用的空間。 |
@FillFactor |
選擇性。指定百分比，以指出完整 SQL Server 應該如何在建立索引期間建立每個索引頁面的分葉層級。此參數的值必須介於 1 與 100 之間。 |
@NoRecomputeStatistics |
選擇性。若此值為 1，將不會自動重新計算過期的索引統計資料。 |
@SortInTempDB |
選擇性。若此值為 1，則用來建立索引的中繼排序結果會儲存於 tempdb 資料庫。 |
@FileGroup |
選擇性。將會針對指定的檔案群組建立索引。 |
如需定義索引建立之參數的詳細資訊，請閱讀 MSDN Library 中的 CREATE INDEX 命令描述：CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x404)。
兩個程序的傳回值
下列是先前程序的傳回值：
| 值 | 描述 |
|---|---|
0 |
成功。已成功建立索引。 |
-1 |
因為找不到要求的自訂欄位，所以未建立索引。 |
-2 |
索引已存在。 |
-3 |
未建立索引；CREATE INDEX 陳述式執行失敗。 |
-4 |
無法產生 CREATE INDEX 陳述式。此陳述式是使用文字變數所產生，然後動態地執行。建立命令字串失敗時，會傳回此錯誤。 |
-5 |
指定的自訂欄位無法使用這種方法編製索引。某些類型的自訂欄位無法透過提供的預存程序編製索引 (例如多值自訂欄位)。 |
-6 |
因為多個自訂欄位符合指定的準則，所以無法建立索引。如果有兩個以上的自訂欄位同名 (在不同的實體上)，而且呼叫依名稱編製自訂欄位索引的方法只提供自訂欄位名稱，而未提供任何實體名稱，就會發生這種情況。 |
範例
下列範例使用其中一個預先定義的資源自訂欄位 (共兩個)：「成本類型」。識別自訂欄位的方法有兩種：依識別碼或依名稱。以下是這兩種方法的使用範例，但建議使用識別碼來識別自訂欄位。
若要依名稱建立資源自訂欄位「成本類型」的索引，請呼叫：
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
若要依識別碼建立此自訂欄位的索引 (請參閱上一節有關使用 MFN_EpmGetAllCustomFieldsInformation 函式取得自訂欄位 UID 的內容)：
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
「固定」檢視和索引
您可以如前面章節所述，套用自訂欄位上的索引及建立目標/調整的檢視，以使用上述方法最佳化報表的產生。不過請注意，在 RDB 重新整理期間，使用自訂欄位的索引和自訂檢視可能會失效。
在重新整理期間，會清除所有自訂欄位的資料行集區資料表，並從 RDB 中刪除所有自訂欄位，因此會發生這種情況。而在重新同步處理程序期間，自訂欄位配置順序可能會變更。這表示自訂欄位值可能會儲存在不同的資料行中，甚至不同的資料表中。
例如，假設已依下列順序建立兩個自訂欄位：先建立 CF1，再建立 CF2，其中 CF1 和 CF2 都是文字自訂欄位。CF1 會取得資料表中的 CFVal0 資料行，而 CF2 會取得 CFVal1。資料行集區資料表會類似：
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
低於預算 |
按預定時程 |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
無比較基準 |
無比較基準 |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
超出預算 |
按預定時程 |
15 |
NULL |
如果刪除 CF1，則資料表會類似：
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
NULL |
按預定時程 |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
NULL |
無比較基準 |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
NULL |
按預定時程 |
15 |
NULL |
不過，重新整理之後，會重新填入資料行集區中的資料行 (重頭開始，而 CF1 不再存在，CF2 現在則會佔用 CFVal0 資料行)。資料表會類似：
| EntityUID | CFVal0 | CFVal1 | CFVal2 |
|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
按預定時程 |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
按預定時程 |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
按預定時程 |
15 |
NULL |
如果您先前已經建立指向 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
現在，自訂檢視 "MySampleView" 和「成本類型」的自訂欄位索引會在 RDB 重新整理之後自動重新套用。