Optimierungen des Berichtsdatendiensts für benutzerdefinierte Felder

Letzte Aktualisierung: Dezember 2009

Letztes Änderungsdatum des Themas: 2015-02-27

In diesem Artikel wird beschrieben, wie benutzerdefinierte Berichterstellungslösungen für die Berichtsdatenbank von Microsoft Office Project Server 2007 optimiert werden. Wenn Sie benutzerdefinierte Sichten erstellen oder benutzerdefinierte Indizes auf beliebige Sichten in der Berichtsdatenbank anwenden möchten, finden Sie hier Informationen zu gespeicherten Hilfsprozeduren, die in Verbindung mit Ihren Lösungen verwendet werden können.

Wenn Sie noch nicht mit der allgemeinen Funktionsweise der Berichtsdatenbank vertraut sind, finden Sie Informationen in folgenden Hintergrundartikeln:

• Die Berichtsdatenbank und der Berichtsdatendienst (in englischer Sprache) (https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x407)

• Project Server Report Pack (in englischer Sprache) (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x407)

Zunächst wird dargestellt, wie Daten für benutzerdefinierte Felder in der Berichtsdatenbank gespeichert werden. Office Project Server 2007 verfügt über mehrere vordefinierte benutzerdefinierte Felder. Mit zunehmendem Umfang der Instanz werden neue benutzerdefinierte Enterprise-Felder hinzugefügt, und vorhandene werden während der regelmäßigen Wartung gelöscht. Der Mechanismus zur Speicherung benutzerdefinierter Felder in der Berichtsdatenbank wurde so entwickelt, dass die Hinzufügung und Entfernung von Feldern dynamisch behandelt wird. Er wurde zur Optimierung von Cubeerstellungs- und Berichtsvorgängen denormalisiert. Benutzerdefinierte Felder werden in folgenden Spaltenpooltabellen mit mehreren Spalten gespeichert: MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask* und MSP_EpmCPAssn* für Projekt-, Ressourcen-, Vorgangs- bzw. Zuordnungsdaten. Wenn neue benutzerdefinierte Felder erstellt werden, werden Spaltenpooltabellen neue Spalten vom entsprechenden Entitätstyp hinzugefügt. Wenn die vorhandenen Tabellen eine bestimmte Anzahl von Spalten erreichen, werden neue Tabellen erstellt. Eine detailliertere Beschreibung der Speicherung von benutzerdefinierten Feldern in der Berichtsdatenbank finden Sie unter Benutzerdefinierte lokale und Enterprise-Felder (in englischer Sprache) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x407) in der MSDN Library.

Das Infrastrukturupdate für Microsoft Office Server enthält die folgenden Sichten, in denen Daten benutzerdefinierter Felder in der Berichtsdatenbank für jede der vier Kernentitäten zusammengefasst sind:

• MSP_EpmProject_UserView

• MSP_EpmTask_UserView

• MSP_EpmAssignment_UserView

• MSP_EpmResource_UserView

Diese Benutzersichten werden von Office Project Server verwaltet und enthalten alle für die entsprechende Entität definierten benutzerdefinierten Felder. Wenn ein benutzerdefiniertes Feld hinzugefügt wird, wird der entsprechenden Sicht automatisch eine neue Spalte hinzugefügt. Wenn ein benutzerdefiniertes Feld gelöscht wird, wird die entsprechende Spalte aus der Sicht entfernt.

Sie können auch eigene Sichten entsprechend den Anforderungen Ihrer Organisation erstellen. Wird z. B. im einem Bericht nur eine kleine Teilmenge der Felder verwendet, können Sie eigene benutzerdefinierte Sichten mit ausschließlich den relevanten Daten erstellen, statt die Standardsichten zu verwenden.

Erstellen benutzerdefinierter Sichten

Zum Erstellen eigener benutzerdefinierter Sichten müssen Sie zunächst ermitteln, wo die Feldwerte gespeichert werden. Wenn Sie die Spaltenpooltabelle und die Spaltennummer kennen, die auf das betreffende Feld verweisen, können Sie mit einer Join-Anweisung Werte für die Sicht abrufen. Alle Spaltenpooltabellen weisen eine EntityUID-Spalte auf, die den eindeutigen Bezeichner der Entität enthält, auf die eine bestimmten Datenzeile verweist.

Hilfsfunktion

Die folgende Funktion gibt interessante Informationen zu allen benutzerdefinierten Feldern zurück.

FUNCTION MFN_Epm_GetAllCustomFieldsInformation();

Rückgabewerte

Die Funktion gibt ein Dataset mit Informationen zu benutzerdefinierten Feldern zurück (eine Zeile für jedes benutzerdefinierte Feld). Wird kein benutzerdefiniertes Feld gefunden, wird ein leeres Dataset zurückgegeben.

Das zurückgegebene Dataset enthält für jedes benutzerdefinierte Feld eine Zeile mit den folgenden Spalten:

Beispiel

Im Folgenden finden Sie ein Beispiel dafür, wie eine einfache benutzerdefinierte Sicht mit zwei benutzerdefinierten Projektfeldern erstellt wird.

In diesem Beispiel wird angenommen, dass zwei vordefinierte benutzerdefinierte Ressourcenfelder (RBS und Cost Type) in der Sicht zusammen mit dem Ressourcennamen, der Ressourcennummer, dem Standardsatz der Ressource, dem Überstundensatz der Ressource sowie dem Windows NT-Kontonamen angezeigt werden sollen. Wenn Sie sicher sind, dass die benutzerdefinierten Feldnamen eindeutig sind und nicht geändert werden, können Sie mit der CustomFieldName-Spalte filtern. Allerdings wäre es günstiger, zunächst einen SELECT-Vorgang wie den folgenden auszuführen.

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

Identifizieren Sie in den Ergebnissen die gewünschten benutzerdefinierten Felder, und notieren Sie dann deren CustomFieldTypeUID-Werte. (Dies sind die eindeutigen Bezeichner.)

Für dieses Beispiel sei angenommen, Sie finden die beiden folgenden eindeutigen Bezeichner:

• {0000783FDE84434B9564284E5B7B3F49} für RBS

• {000039B78BBE4CEB82C4FA8C0C400284} für Cost Type

Mit den beiden eindeutigen Bezeichnern für RBS und Cost Type aus dem obigen Beispiel können Sie das folgende Skript erstellen:

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

Erstellen von Indizes für benutzerdefinierte Felder

Es kann kompliziert sein, herauszufinden, in welcher Spalte welcher Tabelle die Werte eines bestimmten benutzerdefinierten Felds gespeichert werden. Daher verfügt Project Server über zwei gespeicherte Prozeduren, mit denen ein Index für die entsprechende Spalte erstellt wird. Als Eingabe der Prozeduren werden das benutzerdefinierte Feld und die Indexparameter übergeben.

Gespeicherte Hilfsprozeduren

Wenn Sie feststellen, dass für ein benutzerdefiniertes Feld ein Index erforderlich ist, um die Leistung der von einigen Berichten verwendeten Abfragen zu verbessern, können Sie die folgenden Methoden verwenden:

Methode 1:

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

Methode 2:

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

Parameter für "MSP_Epm_CreateCustomFieldIndexByUID"

Mit dem folgenden Parameter wird das benutzerdefinierte Feld identifiziert:

Die folgenden Parameter definieren den Index:

Parameter für "MSP_Epm_CreateCustomFieldIndexByName"

Mit den folgenden Parametern wird das benutzerdefinierte Feld identifiziert:

Die folgenden Parameter definieren den Index:

Weitere Informationen zu den Parametern, die die Indexerstellung definieren, finden Sie in einer Beschreibung des CREATE INDEX-Befehls in der MSDN Library: CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x407).

Rückgabewerte für beide Verfahren

Im Folgenden sind die Rückgabewerte für die vorherigen Verfahren aufgeführt:

Beispiel

Im folgenden Beispiel wird eines der beiden vordefinierten benutzerdefinierten Ressourcenfelder verwendet: Cost Type. Es gibt zwei Methoden zum Identifizieren der benutzerdefinierten Felder: nach Nummer oder nach Namen. Im Folgenden sind Verwendungsbeispiele für beide Methoden angegeben. Die empfohlene Vorgehensweise ist jedoch die Verwendung der Nummer, um benutzerdefinierte Felder zu identifizieren.

Verwenden Sie folgenden Aufruf, um einen Index für das benutzerdefinierte Ressourcenfeld Cost Type nach Namen zu erstellen:

EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'

Verwenden Sie folgenden Aufruf, um einen Index für dieses benutzerdefinierte Feld nach Nummer zu erstellen (Informationen zum Abrufen der UID des benutzerdefinierten Felds mithilfe der MFN_EpmGetAllCustomFieldsInformation-Funktion finden Sie vorherigen Abschnitt):

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

Aufrechterhalten der Gültigkeit von Sichten und Indizes

Sie können die Berichterstellung mithilfe der oben genannten Methoden optimieren, indem Sie Indizes für benutzerdefinierte Felder anwenden und auf die Benutzer zugeschnittene Sichten erstellen, wie in den vorherigen Abschnitten beschrieben. Beachten Sie jedoch, dass bei einer Aktualisierung der Berichtsdatenbank Indizes und benutzerdefinierte Sichten, die benutzerdefinierte Felder verwenden, ungültig werden können.

Der Grund ist, dass bei einer Aktualisierung alle Spaltenpooltabellen für benutzerdefinierte Felder gelöscht werden und alle benutzerdefinierten Felder aus der Berichtsdatenbank gelöscht werden. Bei der Neusynchronisierung kann sich die Zuordnungsreihenfolge der benutzerdefinierten Felder ändern. Dies bedeutet, dass die Werte für ein benutzerdefiniertes Feld möglicherweise in einer anderen Spalte oder sogar in einer anderen Tabelle gespeichert werden.

Angenommen, zwei benutzerdefinierte Felder wurden in der folgenden Reihenfolge erstellt: zuerst CF1, dann CF2. Bei CF1 und CF2 handelt es sich um benutzerdefinierte Textfelder. CF1 erhält in der Tabelle die CFVal0-Spalte, CF2 die CFVal1-Spalte. Die Spaltenpooltabelle sieht etwa folgendermaßen aus:

Wenn CF1 gelöscht wird, sieht die Tabelle folgendermaßen aus:

Nach einer Aktualisierung werden die Spalten in der Spaltenpooltabelle jedoch neu aufgefüllt (CF1 ist nicht mehr vorhanden, und CF2 belegt jetzt die CFVal0-Spalte). Die Tabelle sieht folgendermaßen aus:

Wenn Sie zuvor eine benutzerdefinierte Sicht oder einen Index erstellt haben, die bzw. der auf CFVal1 verweist, wird nach einer Aktualisierung der Berichtsdatenbank anstatt auf CF2 auf ein anderes benutzerdefiniertes Feld verwiesen. Das Fazit ist, dass in solchen Fällen der Index auf die falsche Spalte verweist, was nicht wünschenswert ist. Zur Lösung des Problems haben Sie folgende Möglichkeit: Wenn Sie zur Leistungsoptimierung der Berichterstellung benutzerdefinierte Sichten oder Indizes erstellen, sollten Sie erwägen, auch folgende gespeicherte Prozedur zu erstellen:

PROCEDURE MSP_OnRefreshCompleted();

Wenn diese gespeicherte Prozedur vorhanden ist, wird sie nach einer erfolgreichen Aktualisierung der Berichtsdatenbank automatisch aufgerufen. Dadurch werden die Indizes und/oder benutzerdefinierten Sichten für benutzerdefinierte Felder neu erstellt.

Beispiel

Sollen die Änderungen aus den beiden obigen Beispielen nach einer Aktualisierung der Berichtsdatenbank gültig bleiben, müssen Sie die beiden Skripts in eine gespeicherte Prozedur konvertieren und mit MSP_OnRefreshCompleted benennen. Außerdem müssen Sie sicherstellen, dass die gespeicherte Prozedur eintrittsinvariant ist (das bedeutet, dass sie ordnungsgemäß ausgeführt wird, wenn sie mehrmals hintereinander aufgerufen wird).

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

Jetzt werden die benutzerdefinierte Sicht MySampleView und der Index für das benutzerdefinierte Feld Cost Type nach einer Aktualisierung der Berichtsdatenbank automatisch erneut angewendet.