Ottimizzazioni del servizio Dati report per campi personalizzati
Aggiornamento: dicembre 2009
Ultima modifica dell'argomento: 2015-02-27
In questo articolo viene descritto come ottimizzare soluzioni di segnalazione personalizzate create per il database delle relazioni di Microsoft Office Project Server 2007. Se si desidera creare visualizzazioni personalizzate o applicare indici personalizzati in tutte le visualizzazioni del database delle relazioni, leggere questo articolo per individuare alcune stored procedure di supporto utilizzabili insieme alle soluzioni.
Se i meccanismi generali del database delle relazioni non sono noti, vedere gli articoli di preparazione seguenti:
Segnalazione e database delle relazioni (informazioni in lingua inglese): (https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x410) (informazioni in lingua inglese)
Pacchetto report di Project Server (informazioni in lingua inglese): (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x410) (informazioni in lingua inglese)
Viene innanzitutto descritto il metodo utilizzato per memorizzare i dati del campo personalizzato nel database delle relazioni. Office Project Server 2007 dispone di diversi campi personalizzati predefiniti. Al crescere dell'istanza è possibile aggiungere nuovi campi personalizzati dell'organizzazione mentre quelli esistenti vengono eliminati nel corso della manutenzione ordinaria. Il meccanismo di archiviazione dei campi personalizzati nel database delle relazioni si basa sull'aggiunta dinamica di nuovi campi e la rimozione di quelli obsoleti. Inoltre è denormalizzato per ottimizzare le operazioni di creazione dei cubi e di segnalazione. I campi personalizzati sono memorizzati in tabelle di pool a più colonne MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask* e MSP_EpmCPAssn* per, rispettivamente, dati Progetti, Risorse, Attività e Assegnazioni. Quando vengono creati nuovi campi personalizzati, nuove colonne vengono aggiunte alle tabelle di pool del tipo di entità corrispondente e nuove tabelle vengono create quando le tabelle esistenti raggiungono un certo numero di colonne. Per una descrizione più dettagliata del metodo di memorizzazione dei campi personalizzati nel database delle relazioni, vedere Campi personalizzati locali e dell'organizzazione (informazioni in lingua inglese) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x410) (informazioni in lingua inglese) nella MSDN Library.
aggiornamento dell'infrastruttura per Microsoft Office Server contiene le visualizzazioni seguenti che raccolgono i dati campo personalizzato del database delle relazioni per le quattro entità di base:
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
Queste visualizzazioni utente sono gestite da Office Project Server e contengono tutti i campi personalizzati definiti per l'entità corrispondente. Ogni volta che si aggiunge un campo personalizzato, una nuova colonna viene automaticamente aggiunta alla visualizzazione corrispondente. Inoltre, ogni volta che si elimina un campo personalizzato, la colonna corrispondente viene rimossa dalla visualizzazione.
È anche possibile creare delle proprie visualizzazioni personalizzate per le esigenze dell'organizzazione. Ad esempio, se si dispone di un report che utilizza un piccolo sottoinsieme di campi, anziché utilizzare le visualizzazioni predefinite è possibile scegliere di creare delle proprie visualizzazioni personalizzate che includono solo dati pertinenti.
Creare visualizzazioni personalizzate
Per creare visualizzazioni personalizzate, è necessario innanzitutto trovare il percorso di memorizzazione dei valori di campo. Dopo aver identificato la tabella del pool di colonne e il numero di colonna per il campo di interesse, è possibile utilizzare un'istruzione Join per trascinare valori nella visualizzazione. In tutte le tabelle del pool di colonne esiste una colonna EntityUID contenente l'identificatore univoco dell'entità a cui fa riferimento una determinata riga di dati.
Funzione di supporto
La funzione seguente restituisce informazioni interessanti su tutti i campi personalizzati.
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
Valori restituiti
La funzione restituisce un set di dati contenente le informazioni sui campi personalizzati (una riga per ogni campo personalizzato). Se non viene trovato nessun campo personalizzato, la funzione restituisce un set di dati vuoto.
Il set di dati restituito dispone di una riga per ogni campo personalizzato con le colonne seguenti:
| Valore | Descrizione |
|---|---|
EntityTypeUID |
Identificatore univoco dell'entità padre per ogni campo personalizzato. Ad esempio: per campi personalizzati progetto, questa colonna visualizza un valore corrispondente a 'Progetti'. |
EntityName |
Nome dell'entità padre di ogni campo personalizzato. 'Progetti' nell'esempio precedente. |
CustomFieldTypeUID |
Identificatore univoco del campo personalizzato. |
CustomFieldName |
Nome del campo personalizzato. |
SecondaryCustomFieldTypeUID |
ID del campo personalizzato corrispondente. |
DataType |
Tipo di dati campo personalizzato. |
IsMultiValueEnabled |
La colonna indica 1 se sono possibili più valori per il campo personalizzato. |
IsRollDown |
La colonna indica 1 se i valori campo personalizzato sono riportati. |
LookupTableUID |
Se il campo personalizzato utilizza una tabella di ricerca, questa colonna visualizza il suo identificatore univoco. In caso contrario la colonna è null. |
LookupTableName |
Se il campo personalizzato utilizza una tabella di ricerca, questa colonna visualizza il suo nome. In caso contrario la colonna è null. |
LookupTableMembersViewName |
Project Server crea una visualizzazione per ogni tabella di ricerca definita. È disponibile una visualizzazione che consente di selezionare tutti i relativi membri. Questa colonna visualizza il nome della visualizzazione con i membri della tabella di ricerca utilizzati dal campo personalizzato. |
LookupTableHasMultipleLevels |
Questa colonna visualizza 1 se i valori della tabella di ricerca sono definiti su più livelli. |
ColumnPoolColumnName |
Nome della colonna in cui sono memorizzati i valori campo personalizzato. |
ColumnPoolTableName |
Tabella in cui sono memorizzati i valori campo personalizzato. |
EntityNonTimephasedTableName |
Tabella contenente i dati non rapportati alla scala cronologica per l'entità padre del campo personalizzato. Ad esempio: per un campo personalizzato progetto, la colonna visualizza 'MSP_EpmProject'. |
CreatedDate |
Data di creazione del campo personalizzato. |
ModificationDate |
Data ultima modifica al campo personalizzato. |
Esempio
Nell'esempio seguente viene illustrato come creare una semplice visualizzazione personalizzata contenente due valori campo personalizzato progetto.
Per questo esempio si presume di disporre di due campi personalizzati risorsa predefiniti (RBS e Cost Type) che si desidera mostrare nella visualizzazione insieme al nome della risorsa, all'ID risorsa, alla tariffa standard della risorsa, alla tariffa straordinario della risorsa e al nome account Windows NT della risorsa. Se si è certi che i nomi dei campi personalizzati sono univoci e che non verranno modificati, è possibile filtrare in base alla colonna CustomFieldName. Tuttavia, una soluzione migliore implica l'esecuzione di un'operazione SELECT come indicato di seguito.
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
Nei risultati, verificare di aver identificato i campi personalizzati desiderati, quindi prendere nota dei valori CustomFieldTypeUID (corrispondenti agli ID univoci).
Questo esempio si basa sull'ipotesi che i due identificatori univoci trovati siano:
{0000783FDE84434B9564284E5B7B3F49} per RBS
{000039B78BBE4CEB82C4FA8C0C400284} per Cost Type
Utilizzando i due identificatori univoci per RBS e Cost Type dell'esempio precedente, è possibile utilizzarli per scrivere lo script seguente:
--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
Creare indici di campo personalizzato
Individuare la colonna della tabella in cui sono salvati i valori di un campo personalizzato specifico può essere un'operazione complessa. A tale scopo, Project Server dispone di due stored procedure che creano un indice nella colonna appropriata, utilizzando come input il campo personalizzato e i parametri dell'indice.
Stored procedure di supporto
Se e quando un campo personalizzato richiede un indice per migliorare le prestazioni delle query utilizzate da alcuni report, utilizzare i metodi seguenti:
Metodo 1:
PROCEDURE MSP_CreateCustomFieldIndexByUID(@CustomFieldTypeUIDuniqueidentifier, @PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Metodo 2:
PROCEDURE MSP_CreateCustomFieldIndexByName(@customFieldName [NAME], @customFieldEntityName [NAME] = NULL,@PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Parametri per MSP_Epm_CreateCustomFieldIndexByUID
Il parametro seguente identifica il campo personalizzato:
| Parametro | Descrizione |
|---|---|
@CustomFieldTypeUID |
ID univoco del campo personalizzato in cui verrà creato l'indice |
Di seguito sono elencati i parametri che definiscono l'indice:
| Parametro | Descrizione |
|---|---|
@PadIndex |
Facoltativo. Specifica la quantità di spazio che occorre lasciare aperta su ogni pagina nei livelli intermedi dell'indice. |
@FillFactor |
Facoltativo. Specifica una percentuale che indica la quantità di riempimento del livello foglia per ogni pagina di indice durante la creazione dell'indice. Il valore di questo parametro deve essere compreso tra 1 e 100. |
@NoRecomputeStatistics |
Facoltativo. Se il valore è 1, le statistiche indice non aggiornate non vengono automaticamente ricalcolate. |
@SortInTempDB |
Facoltativo. Se il valore è 1, i risultati intermedi dell'ordinamento utilizzati per creare l'indice verranno memorizzati nel database tempdb. |
@FileGroup |
Facoltativo. L'indice verrà creato nel gruppo di file specificato. |
Parametri per MSP_Epm_CreateCustomFieldIndexByName
I seguenti parametri identificano il campo personalizzato:
| Parametro | Descrizione |
|---|---|
@CustomFieldName |
Nome del campo personalizzato in cui verrà creato l'indice. |
@CustomFieldEntityName |
Facoltativo. Nome dell'entità in cui è definito il campo personalizzato (ad esempio: Progetto per campi personalizzati progetto o Risorsa per campi personalizzati risorsa e così via). |
Di seguito sono riportati i parametri che definiscono l'indice:
| Parametro | Descrizione |
|---|---|
@PadIndex |
Facoltativo. Specifica la quantità di spazio che occorre lasciare aperta su ogni pagina nei livelli intermedi dell'indice. |
@FillFactor |
Facoltativo. Specifica una percentuale che indica la quantità di riempimento del livello foglia per ogni pagina di indice durante la creazione dell'indice. Il valore di questo parametro deve essere compreso tra 1 e 100. |
@NoRecomputeStatistics |
Facoltativo. Se il valore è 1, le statistiche dell'indice non aggiornate non verranno ricalcolate automaticamente. |
@SortInTempDB |
Facoltativo. Se il valore è 1, i risultati intermedi dell'ordinamento utilizzati per creare l'indice verranno memorizzati nel database tempdb. |
@FileGroup |
Facoltativo. L'indice verrà creato nel gruppo di file specificato. |
Per ulteriori informazioni sui parametri che definiscono la creazione dell'indice, è possibile leggere una descrizione del comando CREATE INDEX nella MSDN Library: CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x410).
Valori restituiti per entrambe le procedure
Di seguito sono riportati i valori restituiti per le procedure precedenti:
| Valore | Descrizione |
|---|---|
0 |
Esito positivo. L'indice è stato creato. |
-1 |
Indice non creato perché il campo personalizzato richiesto non è stato trovato. |
-2 |
L'indice esiste già. |
-3 |
Indice non creato. Impossibile eseguire l'istruzione CREATE INDEX. |
-4 |
Impossibile generare l'istruzione CREATE INDEX. Questa istruzione viene generata in una variabile di testo e quindi eseguita dinamicamente. Questo errore viene restituito quando non è possibile creare la stringa di comando. |
-5 |
Impossibile utilizzare questo metodo per indicizzare il campo personalizzato specificato. Alcuni tipi di campi personalizzati non possono essere indicizzati dalle stored procedure implementate (ad esempio, campi personalizzati multivalore). |
-6 |
Impossibile creare l'indice perché più campi personalizzati corrispondono ai criteri specificati. È possibile che esistano due o più campi personalizzati con lo stesso nome (su entità diverse) e che il metodo utilizzato per indicizzare il campo personalizzato per nome sia chiamato solo con il nome campo personalizzato senza fornire nessun nome di entità. |
Esempio
L'esempio seguente utilizza uno dei due campi personalizzati risorse predefiniti: Cost Type. Sono inoltre disponibili due metodi di identificazione dei campi personalizzati: in base all'ID o in base al nome. Di seguito sono riportati esempi di utilizzo di entrambi i metodi. Si consiglia, tuttavia, di utilizzare l'ID per identificare i campi personalizzati.
Per creare un indice per il campo personalizzato risorsa “Cost Type” in base al nome, chiamare:
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
Per creare un indice per questo campo personalizzato in base all'ID (vedere la sezione precedente sul recupero dell'UID del campo personalizzato utilizzando la funzione MFN_EpmGetAllCustomFieldsInformation):
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
Come "incollare" visualizzazioni e indici
L'uso dei metodi sopra specificati consente di ottimizzare la generazione dei rapporti applicando indici a campi personalizzati e creando visualizzazioni mirate/regolate come descritto nelle sezioni precedenti. Si noti, tuttavia, che durante un aggiornamento del database delle relazioni, indici e visualizzazioni personalizzate che utilizzano campi personalizzati potrebbero essere invalidate.
Questo si verifica perché durante un aggiornamento tutte le tabelle del pool di colonne campi personalizzati vengono cancellate e tutti i campi personalizzati vengono eliminati dal database delle relazioni. Durante il processo di sincronizzazione l'ordine di allocazione dei campi personalizzati potrebbe cambiare. Questo significa che i valori dei campi personalizzati possono essere salvati in una colonna diversa o persino in una tabella diversa.
Si supponga, ad esempio, che esistano due campi personalizzati testo CF1 e CF2 creati nell'ordine seguente: prima CF1, quindi CF2. CF1 riceve il valore CFVal0 nella tabella, mentre CF2 riceve il valore CFVal1. L'aspetto della tabella del pool di colonne è simile al seguente:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
Inferiore al preventivo |
Puntuale |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Nessuna previsione |
Nessuna previsione |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Superiore al preventivo |
Puntuale |
15 |
NULL |
Se CF1 viene eliminato, l'aspetto della tabella sarà simile al seguente:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
NULL |
Puntuale |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
NULL |
Nessuna previsione |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
NULL |
Puntuale |
15 |
NULL |
Tuttavia, dopo un aggiornamento le colonne nel pool di colonne vengono nuovamente ripopolate: CF1 non esisterà più e CF2 occuperà la colonna CFVal0. L'aspetto della tabella sarà simile al seguente:
| EntityUID | CFVal0 | CFVal1 | CFVal2 |
|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
Puntuale |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Puntuale |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Puntuale |
15 |
NULL |
Se in precedenza è stata creata una visualizzazione personalizzata o un indice che fa riferimento a CFVal1, dopo un aggiornamento del database delle relazioni, anziché a CF2 si farà riferimento a un campo personalizzato diverso. Il risultato finale è che l'indice termina nella colonna errata. Per risolvere il problema, se si creano visualizzazioni personalizzate o indici per migliorare le prestazioni di segnalazione, è necessario anche valutare la creazione di una stored procedure:
PROCEDURE MSP_OnRefreshCompleted();
Se questa stored procedure esiste, viene chiamata automaticamente dopo un aggiornamento del database delle relazioni. Gli indici dei campi personalizzati e/o le visualizzazioni personalizzate verranno ricreati automaticamente.
Esempio
Se si desidera che le modifiche dei due esempi precedenti rimangano valide dopo un aggiornamento del database delle relazioni, è necessario convertire i due script in una stored procedure e chiamarla MSP_OnRefreshCompleted. Inoltre, è necessario rendere questa stored procedure rientrante, ovvero che può essere chiamata più volte di seguito senza che si verifichino problemi di esecuzione.
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
La visualizzazione personalizzata "MySampleView" e l'indice campo personalizzato "Cost Type" verranno automaticamente riapplicati dopo un aggiornamento del database delle relazioni.