ユーザー設定フィールドのレポート データ サービスの最適化
更新日: 2009年12月
トピックの最終更新日: 2015-03-09
この記事では、Microsoft Office Project Server 2007 のレポート データベース (RDB) 用に構築したカスタム レポート ソリューションを最適化する方法について説明します。カスタム ビューの構築または RDB の任意のビューへのカスタム インデックスの適用に関心がある場合は、ソリューションと併用できるヘルパー ストアド プロシージャに関するこの記事が役立ちます。
RDB の一般的なしくみに精通していない場合は、以下の背景記事を参照してください。
レポート データベースとレポート データ サービス: (https://msdn.microsoft.com/ja-jp/library/aa568342.aspx)
Project Server Report Pack: (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x411)
最初に、ユーザー設定フィールド データが RDB に格納される方法を確認します。 Office Project Server 2007 には、いくつかの定義済みユーザー設定フィールドがあります。インスタンスが追加されると、定期的なメンテナンスの際に新しいエンタープライズ ユーザー設定フィールドが追加され、既存のユーザー設定フィールドが削除される場合があります。RDB のユーザー設定フィールドの保存メカニズムは、新しいフィールドの追加および古いフィールドの削除を動的に処理するように設計され、より効率的なキューブ作成およびレポート操作のため最適化するように非正規化されています。ユーザー設定フィールドであるプロジェクト、リソース、タスク、および割り当てデータは、それぞれカラム プール テーブル MSP_EpmCPPrj*、MSP_EpmCPRes*、MSP_EpmCPTask*、および MSP_EpmCPAssn* に格納されます。新しいユーザー設定フィールドが作成されると、対応するエンティティの種類のカラム プール テーブルに新しい列が追加され、既存のテーブルが特定の列数に達すると新しいテーブルが作成されます。ユーザー設定フィールドを RDB に格納する方法の詳細については、MSDN オンライン ライブラリの「ローカル ユーザー設定フィールドとエンタープライズ ユーザー設定フィールド」(https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x411) を参照してください。
Microsoft Office サーバー製品インフラストラクチャ更新プログラム には、4 つのコア エンティティそれぞれに対して RDB ユーザー設定フィールド データを集計する次のビューが用意されています。
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
これらのユーザー ビューは Office Project Server によって管理され、対応するエンティティに対して定義されたすべてのユーザー設定フィールドが含まれます。ユーザー設定フィールドが追加されるたびに、新しい列が対応するビューに自動的に追加されます。また、ユーザー設定フィールドが削除されるたびに、対応する列がビューから削除されます。
組織のニーズに応じてカスタマイズされた独自のビューを構築することもできます。たとえば、フィールドの小さなサブセットを使用するレポートがある場合、既定のビューを使用するのではなく、関連データのみを含む独自のカスタム ビューを作成できます。
カスタム ビューを作成する
独自のカスタム ビューを作成するには、まず、フィールドの値が格納される場所を検出する必要があります。該当するフィールドをポイントするカラム プール テーブルと列番号を確認し、Join ステートメントを使用して値をカスタム ビューにプルします。すべてのカラム プール テーブルには、特定のデータ行が参照するエンティティの一意の識別子を含む [EntityUID] 列があります。
ヘルパー関数
次の関数は、すべてのユーザー設定フィールドに関する該当する情報を返します。
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
戻り値
この関数はユーザー設定フィールド情報 (ユーザー設定フィールドあたり 1 行) を含むデータセットを返します。ユーザー設定フィールドが見つからない場合、この関数は空のデータセットを返します。
返されたデータセットは、ユーザー設定フィールドあたり 1 行ずつを持ち、以下の列を含みます。
値 | 説明 |
---|---|
EntityTypeUID |
各ユーザー設定フィールドの親エンティティの一意の識別子です (たとえば、プロジェクト ユーザー設定フィールドの場合、この列には 'Projects' に対応する値が表示されます)。 |
EntityName |
各ユーザー設定フィールドの親エンティティの名前です (上記の例では、'Projects' になります)。 |
CustomFieldTypeUID |
ユーザー設定フィールドの一意の識別子です。 |
CustomFieldName |
ユーザー設定フィールドの名前です。 |
SecondaryCustomFieldTypeUID |
対応するユーザー設定フィールドの ID です。 |
DataType |
ユーザー設定フィールドのデータの種類です。 |
IsMultiValueEnabled |
ユーザー設定フィールドに複数の値を指定できる場合、この列には 1 が表示されます。 |
IsRollDown |
ユーザー設定フィールドの値が細分化される場合、この列には 1 が表示されます。 |
LookupTableUID |
ユーザー設定フィールドが参照テーブルを使用する場合、この列には一意の識別子が表示されます。それ以外の場合は、null です。 |
LookupTableName |
ユーザー設定フィールドが参照テーブルを使用する場合、この列には参照テーブルの名前が表示されます。それ以外の場合は、null です。 |
LookupTableMembersViewName |
Project Server では定義された参照テーブルごとにビューが作成されます。参照テーブルのすべてのメンバを選択するビューがあります。この列には、ユーザー設定フィールドによって使用される参照テーブルのビューの名前がメンバと共に表示されます。 |
LookupTableHasMultipleLevels |
参照テーブルに 2 つ以上のレベルで定義された値が含まれる場合、この列には 1 が表示されます。 |
ColumnPoolColumnName |
ユーザー設定フィールドの値を格納する列の名前です。 |
ColumnPoolTableName |
ユーザー設定フィールドの値を格納するテーブルの名前です。 |
EntityNonTimephasedTableName |
ユーザー設定フィールドの親エンティティの時間単位以外のデータを格納するテーブルです (たとえば、プロジェクト ユーザー設定フィールドの場合、この列には 'MSP_EpmProject' が表示されます)。 |
CreatedDate |
ユーザー設定フィールドが作成された日付です。 |
ModificationDate |
ユーザー設定フィールドが最後に変更された日付です。 |
例
次に 2 つのプロジェクト ユーザー設定フィールドの値を表示する単純なカスタム ビューを作成する方法を例を挙げて説明します。
この例では、2 つの定義済みリソース ユーザー設定フィールド (RBS とコストの種類) に加えて、リソース名、リソース ID、リソースの標準単価、リソースの超過単価、およびリソースの Windows NT アカウント名をビューに表示すると仮定します。ユーザー設定フィールド名が一意であり、変更されることがない場合、[CustomFieldName] 列を使用してフィルタ処理を行うことができます。ただし、まず次に示す SELECT 操作を実行することをお勧めします。
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
この結果から、対象のユーザー設定フィールドが識別されていることを確認して、それぞれの CustomFieldTypeUID 値を記録します (この値は、それぞれの一意の識別子です)。
この例では、次の 2 つの一意の識別子が検出されると仮定します。
RBS 用の {0000783FDE84434B9564284E5B7B3F49}
コストの種類用の {000039B78BBE4CEB82C4FA8C0C400284}
上記の例より RBS およびコストの種類用の 2 つの一意の識別子を使用して、次のスクリプトを記述できます。
--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 には、ユーザー設定フィールドとインデックス パラメータを入力として使用して、該当する列にインデックスを作成する 2 つのストアド プロシージャが用意されています。
ヘルパー ストアド プロシージャ
いくつかのレポートで使用されるクエリのパフォーマンスを改善するために、ユーザー設定フィールドにインデックスが必要な場合は、次のメソッドを使用できます。
メソッド 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 |
インデックスが作成されるユーザー設定フィールドの一意の ID です。 |
インデックスを定義するパラメータを以下に示します。
パラメータ | 説明 |
---|---|
@PadIndex |
省略可能。インデックスの中間レベルの各ページにどの程度の領域を残すかを指定します。 |
@FillFactor |
省略可能。Microsoft SQL Server が、インデックス作成時に、各インデックス ページをリーフ レベルで、どれだけ満たすかをパーセント (%) で指定します。このパラメータは 1 ~ 100 の間の値を指定する必要があります。 |
@NoRecomputeStatistics |
省略可能。値が 1 の場合、古いインデックス統計が自動的に再計算されます。 |
@SortInTempDB |
省略可能。値が 1 の場合、インデックスの構築に使用される並べ替えの途中結果が tempdb データベースに格納されます。 |
@FileGroup |
省略可能。指定したファイル グループでインデックスが作成されます。 |
MSP_Epm_CreateCustomFieldIndexByName のパラメータ
次のパラメータによってユーザー設定フィールドが識別されます。
パラメータ | 説明 |
---|---|
@CustomFieldName |
インデックスが作成されるユーザー設定フィールドの名前です。 |
@CustomFieldEntityName |
省略可能。ユーザー設定フィールドが定義されるエンティティの名前です (例 : プロジェクトユーザー設定フィールドの場合は Project、リソース ユーザー設定フィールドの場合は Resource など)。 |
インデックスを定義するパラメータを以下に示します。
パラメータ | 説明 |
---|---|
@PadIndex |
省略可能。インデックスの中間レベルの各ページにどの程度の領域を残すかを指定します。 |
@FillFactor |
省略可能。Microsoft SQL Server が、インデックス作成時に、各インデックス ページをリーフ レベルで、どれだけ満たすかをパーセント (%) で指定します。このパラメータは 1 ~ 100 の間の値を指定する必要があります。 |
@NoRecomputeStatistics |
省略可能。値が 1 の場合、古いインデックス統計が自動的に再計算されます。 |
@SortInTempDB |
省略可能。値が 1 の場合、インデックスの構築に使用される並べ替えの途中結果が tempdb データベースに格納されます。 |
@FileGroup |
省略可能。指定したファイル グループでインデックスが作成されます。 |
インデックスの作成を定義するパラメータの詳細については、MSDN ライブラリ「CREATE INDEX (Transact-SQL)」(https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x411) の CREATE INDEX コマンドに関する説明を参照してください。
両方のプロシージャの戻り値
前述のプロシージャの戻り値は次のとおりです。
値 | 説明 |
---|---|
0 |
成功。インデックスは正常に作成されました。 |
-1 |
要求されたユーザー設定フィールドが見つからなかったため、インデックスは作成されませんでした。 |
-2 |
インデックスは既に存在します。 |
-3 |
インデックスは作成されませんでした。CREATE INDEX ステートメントの実行が失敗しました。 |
-4 |
CREATE INDEX ステートメントの生成に失敗しました。このステートメントはテキスト変数で生成され、動的に実行されます。このエラーは、コマンド文字列のビルドに失敗したときに返されます。 |
-5 |
このメソッドでは、指定されたユーザー設定フィールドはインデックスを作成できませんでした。指定されたストアド プロシージャではインデックス付けできない種類のユーザー設定フィールド (複数の値を持つユーザー設定フィールドなど) が一部あります。 |
-6 |
複数のユーザー設定フィールドが指定された条件に一致したため、インデックスを作成できませんでした。これは、異なるエンティティに定義された同じ名前を持つ 2 つ以上のユーザー設定フィールドが存在し、名前によってユーザー設定フィールドのインデックス付けを行うメソッドが、エンティティ名を指定せずにユーザー設定フィールド名のみで呼び出された場合に発生します。 |
例
次の例は、2 つの定義済みリソース ユーザー設定フィールドの 1 つ "Cost Type" を使用します。また、ID または名前によってユーザー設定フィールドを識別する 2 つのメソッドも存在します。以下に両方のメソッドの使用例を示します。ただし、ID を使用してユーザー設定フィールドを識別する方法をお勧めします。
名前によってリソース ユーザー設定フィールド "Cost Type" のインデックスを作成するには、次のメソッドを呼び出します。
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
ID によって ユーザー設定フィールドのインデックスを作成するには、次のメソッドを呼び出します (MFN_EpmGetAllCustomFieldsInformation
関数を使用して、ユーザー設定フィールドの UID を取得する方法に関する前述のセクションを参照してください)。
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
ビューおよびインデックスを "固定" する
上記のメソッドを使用して、(前述のセクションの説明に従って) ユーザー設定フィールドにインデックスを適用し、対象設定済みのビューおよびトリミングされたビューを作成することにより、レポートの生成を最適化できます。ただし、RDB 更新時に、ユーザー設定フィールドを使用する、インデックス付きビューおよびカスタマイズされたビューが無効になる場合があります。
これは、更新時にすべてのユーザー設定フィールドのカラム プール テーブルが消去され、すべてのユーザー設定フィールドが RDB から削除されるために発生します。再同期プロセス中に、ユーザー設定フィールドの割り当て順が変わる可能性があります。つまり、ユーザー設定フィールドの値が別の列または別のテーブルに保存される可能性があります。
たとえば、最初に CF1、次に CF2 の順序で作成され、CF1 と CF2 が両方とも テキスト ユーザー設定フィールドである 2 つのユーザー設定フィールドが存在すると仮定します。 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 更新が正常に完了した後、自動的に呼び出されます。ストアド プロシージャは、ユーザー設定フィールドのインデックスおよびカスタマイズされたビューを再作成します。
例
上記の 2 つの例で、RDB を更新後、変更を引き続き有効にする場合は、2 つのスクリプトをストアド プロシージャに変換し、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"、およびユーザー設定フィールド "Cost Type" のインデックスが自動的に再適用されるようになりました。