Signaler les optimisations de service de données relatives aux champs personnalisés
Mis à jour: décembre 2009
Dernière rubrique modifiée : 2015-02-27
Cet article explique comment optimiser les solutions personnalisées de création de rapports conçues pour la base de données de création de rapports de Microsoft Office Project Server 2007. Si vous souhaitez créer des affichages personnalisés ou appliquer des index personnalisés à des affichages de la base de données de création de rapports, consultez cet article pour découvrir quelles procédures stockées d’application auxiliaire peuvent être utilisées avec vos solutions.
Si vous ne maîtrisez pas encore les grands principes de fonctionnement de la base de données de création de rapports, consultez les articles de référence suivants :
Rapports et service de données de rapport (en anglais) : (https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x40C)
Project Server Report Pack (en anglais) : (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x40C, éventuellement an anglais)
Commençons par examiner comment les données de champ personnalisé sont stockées dans la base de données de création de rapports. Office Project Server 2007 dispose de plusieurs champs personnalisés prédéfinis. À mesure que l’instance se développe, de nouveaux champs personnalisés d’entreprise peuvent être ajoutés. Les champs existants peuvent quant à eux être supprimés au cours des opérations de maintenance. Le mécanisme de stockage des champs personnalisés dans la base de données de création de rapports est conçu pour permettre l’ajout de nouveaux champs et la suppression de champs existants. Il a été dénormalisé pour optimiser les opérations de création de cubes et de rapports. Les champs personnalisés sont stockés dans plusieurs tables de liste de colonnes : MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask* et MSP_EpmCPAssn* (pour les données de projet, de ressource, de tâche et d’affectation, respectivement). À mesure que de nouveaux champs personnalisés sont créés, de nouvelles colonnes sont ajoutées aux tables de liste de colonnes du type d’entité correspondant. De nouvelles tables sont créées lorsque le nombre de colonnes des tables existantes atteint une certaine limite. Pour découvrir une description plus détaillée du mode de stockage des champs personnalisés dans la base de données de création de rapports, consultez l’article intitulé Champs personnalisés locaux et champs personnalisés d’entreprise (en anglais) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x40C) (en anglais) dans la bibliothèque en ligne MSDN.
La Mise à jour d’infrastructure pour les produits serveur de Microsoft Office contient les affichages suivants, qui regroupent les données de champ personnalisé de la base de données de création de rapports, pour chacune des quatre entités principales :
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
Ces affichages utilisateur sont gérés par Office Project Server et contiennent tous les champs personnalisés définis pour l’entité correspondante. Chaque fois qu’un champ personnalisé est ajouté, une nouvelle colonne est ajoutée automatiquement à l’affichage correspondant. En outre, chaque fois qu’un champ personnalisé est supprimé, la colonne correspondante est supprimée de l’affichage.
Vous pouvez également créer vos propres affichages personnalisés, en fonction des besoins de votre organisation. Par exemple, si l’un de vos rapports utilise un sous-ensemble limité de champs, vous pouvez créer des affichages personnalisés contenant uniquement les données souhaitées, au lieu d’utiliser les affichages par défaut.
Créer des affichages personnalisés
Pour créer des affichages personnalisés, vous devez savoir où sont stockées les valeurs de champ. Une fois que vous savez quelle table de liste de colonnes et quel numéro de colonne pointent vers le champ souhaité, vous pouvez utiliser une instruction Join pour récupérer les valeurs dans votre affichage. Toutes les tables de liste de colonnes contiennent une colonne EntityUID dans laquelle se trouve l’identificateur unique de l’entité à laquelle une ligne de données particulière fait référence.
Fonction d’application auxiliaire
La fonction suivante renvoie des informations utiles sur les champs personnalisés.
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
Valeurs renvoyées
La fonction renvoie un DataSet contenant les informations des champs personnalisés (une ligne pour chaque champ personnalisé). Si aucun champ personnalisé n’est détecté, un DataSet vide est renvoyé.
Le DataSet renvoyé contient une ligne pour chaque champ personnalisé, ainsi que les colonnes suivantes :
| Valeur | Description |
|---|---|
EntityTypeUID |
Identificateur unique de l’entité parent de chaque champ personnalisé. Par exemple, pour les champs personnalisés de projet, cette colonne contient la valeur Projects. |
EntityName |
Nom de l’entité parent de chaque champ personnalisé. Dans l’exemple ci-dessus, il s’agit de Projects. |
CustomFieldTypeUID |
Identificateur unique du champ personnalisé. |
CustomFieldName |
Nom du champ personnalisé. |
SecondaryCustomFieldTypeUID |
ID du champ personnalisé correspondant. |
DataType |
Type de données de champ personnalisé. |
IsMultiValueEnabled |
La colonne contient la valeur 1 si le champ personnalisé peut prendre plusieurs valeurs. |
IsRollDown |
La colonne contient la valeur 1 si les valeurs du champ personnalisé sont généralisées. |
LookupTableUID |
Si le champ personnalisé utilise une table de choix, cette colonne affiche son identificateur unique. Sinon, la valeur de la colonne est Null. |
LookupTableName |
Si le champ personnalisé utilise une table de choix, cette colonne affiche son nom. Sinon, la valeur de la colonne est Null. |
LookupTableMembersViewName |
Project Server crée un affichage pour chaque table de choix définie. Un des affichages sélectionne tous ses membres. Cette colonne affiche le nom de l’affichage ainsi que les membres de la table de choix utilisée par le champ personnalisé. |
LookupTableHasMultipleLevels |
Cette colonne contient la valeur 1 si les valeurs de la table de choix sont définies sur plusieurs niveaux. |
ColumnPoolColumnName |
Nom de la colonne stockant les valeurs de champ personnalisé. |
ColumnPoolTableName |
Table stockant les valeurs de champ personnalisé. |
EntityNonTimephasedTableName |
Table stockant les données non chronologiques de l’entité parent du champ personnalisé. Par exemple, pour un champ personnalisé de projet, la colonne contient la valeur MSP_EpmProject. |
CreatedDate |
Date de création du champ personnalisé. |
ModificationDate |
Date de la dernière modification du champ personnalisé. |
Exemple
Voici un exemple qui montre comment créer un affichage personnalisé simple affichant deux valeurs de champ personnalisé de projet.
Dans cet exemple, supposons que nous disposons de deux champs personnalisés de ressource prédéfinis (RBS et Cost Type), que nous souhaitons afficher aux côtés du nom de la ressource, de son ID, de son tarif standard, de son tarif d’heures supplémentaires et de son nom de compte Windows NT. Si vous êtes certain que les noms des champs personnalisés sont uniques et qu’ils ne seront pas modifiés, vous pouvez utiliser la colonne CustomFieldName pour filtrer les données. Cependant, il est préférable de commencer par exécuter une opération SELECT similaire à l’opération ci-dessous.
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
Dans les résultats, assurez-vous que vous avez identifié les champs personnalisés souhaités, puis notez leurs valeurs CustomFieldTypeUID. Il s’agit de leur ID unique.
Imaginons que dans cet exemple, les deux identificateurs uniques détectés sont :
{0000783FDE84434B9564284E5B7B3F49} pour RBS
{000039B78BBE4CEB82C4FA8C0C400284} pour Cost Type
Maintenant que vous disposez des deux identificateurs uniques RBS et Cost Type ci-dessus, vous pouvez écrire le script suivant :
--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
Créer des index de champ personnalisé
Il est parfois difficile de déterminer dans quelle colonne et quelle table sont stockées les valeurs d’un champ personnalisé. Par conséquent, Project Server offre deux procédures stockées permettant de créer un index dans la colonne appropriée, à partir du champ personnalisé et des paramètres d’index.
Procédures stockées d’application auxiliaire
Lorsque vous pensez qu’un champ personnalisé nécessite un index pour améliorer les performances des requêtes utilisées par certains rapports, utilisez les méthodes suivantes :
Méthode 1 :
PROCEDURE MSP_CreateCustomFieldIndexByUID(@CustomFieldTypeUIDuniqueidentifier, @PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Méthode 2 :
PROCEDURE MSP_CreateCustomFieldIndexByName(@customFieldName [NAME], @customFieldEntityName [NAME] = NULL,@PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Paramètres de MSP_Epm_CreateCustomFieldIndexByUID
Le paramètre suivant identifie le champ personnalisé :
| Paramètre | Description |
|---|---|
@CustomFieldTypeUID |
ID unique du champ personnalisé sur lequel l’index sera créé. |
Les paramètres suivants définissent l’index :
| Paramètre | Description |
|---|---|
@PadIndex |
Facultatif. Indique la quantité d’espace à laisser ouvert sur chacune des pages des niveaux intermédiaires de l’index. |
@FillFactor |
Facultatif. Spécifie un pourcentage indiquant le degré de remplissage du niveau feuille devant être respecté par Microsoft SQL Server pour chaque page d’index lors de la création de l’index. La valeur de ce paramètre doit être comprise entre 1 et 100. |
@NoRecomputeStatistics |
Facultatif. Si la valeur est 1, les statistiques d’index obsolètes ne sont pas recalculées automatiquement. |
@SortInTempDB |
Facultatif. Si la valeur est 1, les résultats de tri intermédiaire utilisés pour créer l’index sont stockés dans la base de données tempdb. |
@FileGroup |
Facultatif. L’index sera créé dans le groupe de fichiers spécifié. |
Paramètres de MSP_Epm_CreateCustomFieldIndexByName
Les paramètres suivants identifient le champ personnalisé :
| Paramètre | Description |
|---|---|
@CustomFieldName |
Nom du champ personnalisé sur lequel l’index sera créé. |
@CustomFieldEntityName |
Facultatif. Nom de l’entité sur laquelle le champ personnalisé est défini. Par exemple, Project pour les champs personnalisés de projet ou Resource pour les champs personnalisés de ressource. |
Les paramètres suivants définissent l’index :
| Paramètre | Description |
|---|---|
@PadIndex |
Facultatif. Indique la quantité d’espace à laisser ouvert sur chacune des pages des niveaux intermédiaires de l’index. |
@FillFactor |
Facultatif. Spécifie un pourcentage indiquant le degré de remplissage du niveau feuille devant être respecté par SQL Server pour chaque page d’index lors de la création de l’index. La valeur de ce paramètre doit être comprise entre 1 et 100. |
@NoRecomputeStatistics |
Facultatif. Si la valeur est 1, les statistiques d’index obsolètes ne sont pas recalculées automatiquement. |
@SortInTempDB |
Facultatif. Si la valeur est 1, les résultats de tri intermédiaire utilisés pour créer l’index sont stockés dans la base de données tempdb. |
@FileGroup |
Facultatif. L’index sera créé dans le groupe de fichiers spécifié. |
Pour plus d’informations sur les paramètres définissant la création d’index, consultez la description de la commande CREATE INDEX dans la bibliothèque MSDN : CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x40C).
Valeurs renvoyées par les deux procédures
Voici les valeurs renvoyées par les procédures décrites ci-dessus :
| Valeur | Description |
|---|---|
0 |
Opération réussie. L’index a été correctement créé. |
-1 |
L’index n’a pas été créé, car le champ personnalisé demandé est introuvable. |
-2 |
L’index existe déjà. |
-3 |
L’index n’a pas été créé. L’exécution de l’instruction CREATE INDEX a échoué. |
-4 |
La génération de l’instruction CREATE INDEX a échoué. Cette instruction est générée dans une variable textuelle et exécutée de façon dynamique. Cette erreur est renvoyée lorsque la création de la chaîne de commande échoue. |
-5 |
Le champ personnalisé spécifié n’a pas pu être indexé à l’aide de cette méthode. Certains types de champs personnalisés ne peuvent pas être indexés à l’aide des procédures stockées fournies (c’est notamment le cas des champs personnalisés à valeurs multiples). |
-6 |
La création de l’index a échoué, car plusieurs champs personnalisés correspondent aux critères spécifiés. Cette situation peut se produire lorsqu’au moins deux champs personnalisés portent le même nom (dans différentes entités) et que la méthode d’indexation du champ personnalisé à partir du nom est appelée en utilisant uniquement le nom du champ personnalisé, sans fournir de nom d’entité. |
Exemple
L’exemple suivant utilise l’un des deux champs personnalisés de ressource prédéfinis, Cost Type. Deux méthodes permettent d’identifier les champs personnalisés : par ID ou par nom. Voici des exemples utilisant chacune de ces méthodes. Sachez cependant qu’il est recommandé d’utiliser l’ID pour identifier les champs personnalisés.
Pour créer un index pour le champ personnalisé de ressource Cost Type à partir du nom, appelez la commande suivante :
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
Pour créer un index pour ce champ personnalisé à partir de son ID (consultez la section ci-dessus consacrée à la récupération de l’UID des champs personnalisés à l’aide de la fonction MFN_EpmGetAllCustomFieldsInformation) :
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
Cohérence des affichages et des index
Vous pouvez optimiser les processus de création de rapports à l’aide des méthodes ci-dessus en appliquant des index sur les champs personnalisés et en créant des affichages ciblés ou limités, comme expliqué dans les sections précédentes. Sachez cependant que pendant l’actualisation d’une base de données de création de rapports, il peut arriver que les index et les affichages personnalisés soient invalidés.
En effet, pendant l’actualisation, toutes les tables de liste de colonnes de champ personnalisé sont effacées et tous les champs personnalisés sont supprimés de la base de données de création de rapports. Pendant le processus de resynchronisation, l’ordre d’affectation des champs personnalisés peut être modifié. Par conséquent, les valeurs de champ personnalisé sont susceptibles d’être enregistrées dans une autre colonne, voire dans une autre table.
Supposons par exemple que deux champs personnalisés ont été créés dans l’ordre suivant : CF1 en premier, puis CF2. Ces deux champs personnalisés sont de type texte. CF1 récupère la colonne CFVal0 dans la table et CF2 récupère la colonne CFVal1. La table de la liste de colonnes est similaire à l’exemple suivant :
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
En dessous du budget |
Sans retard |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Pas de planification initiale |
Pas de planification initiale |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Dépasse le budget |
Sans retard |
15 |
NULL |
Si CF1 est supprimé, la table se présente désormais comme ceci :
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
NULL |
Sans retard |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
NULL |
Pas de planification initiale |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
NULL |
Sans retard |
15 |
NULL |
Cependant, après actualisation, les colonnes de la liste de colonnes sont à nouveau remplies, en recommençant à zéro. CF1 n’existe plus et CF2 occupe désormais la colonne CFVal0. Le tableau se présente alors comme ceci :
| EntityUID | CFVal0 | CFVal1 | CFVal2 |
|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
Sans retard |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Sans retard |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Sans retard |
15 |
NULL |
Si vous avez déjà créé un affichage personnalisé ou un index pointant vers CFVal1, après l’actualisation de la base de données de création de rapports, au lieu de pointer vers CF2, il pointe maintenant vers un autre champ personnalisé. Au final, l’index se retrouve dans la mauvaise colonne et ce problème doit être résolu. Pour ce faire, si vous créez des affichages personnalisés ou des index pour améliorer les performances de création de rapports, pensez à créer également une procédure stockée :
PROCEDURE MSP_OnRefreshCompleted();
Si cette procédure stockée existe, elle est appelée automatiquement après une actualisation de la base de données de création de rapports. Elle recrée les index de champ personnalisé et/ou les affichages personnalisés.
Exemple
Si vous souhaitez que les modifications décrites dans les deux exemples ci-dessus restent valides après une actualisation de la base de données de création de rapports, vous devez convertir les deux scripts en procédure stockée et l’appeler MSP_OnRefreshCompleted. Vous devez également la rendre réentrante, de sorte qu’elle soit correctement exécutée si vous l’appelez plusieurs fois de suite.
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
L’affichage personnalisé « MySampleView » et l’index de champ personnalisé « Cost Type » sont alors automatiquement réappliqués après une actualisation de la base de données de génération de rapports.