Relatar as otimizações do Serviço de Dados para campos personalizados

Atualizado: dezembro de 2009

Tópico modificado em: 2015-02-27

Este artigo descreve como otimizar soluções personalizadas de relatórios criadas para o Banco de Dados de Relatório (RDB) do Microsoft Office Project Server 2007. Se estiver interessado na criação de modos de exibição personalizados ou na aplicação de índices personalizados em qualquer modo de exibição do RDB, leia este artigo para obter alguns procedimentos armazenados auxiliares que poderão ser usados em conjunto com as soluções.

Se você ainda não conhece a mecânica geral do RDB, consulte estes artigos de suporte:

• Relatórios e o RDS (em inglês): (https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x416) (em inglês)

• Project Server Report Pack (em inglês): (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x416) (em inglês)

Primeiro, vamos dar uma olhada nos dados de campo personalizado armazenados no RDB. O Office Project Server 2007 possui vários campos personalizados predefinidos. À medida que a instância aumenta, novos campos personalizados da empresa podem ser adicionados e os existentes podem ser excluídos durante a manutenção regular. O mecanismo de armazenamento do campo personalizado no RDB foi criado para lidar dinamicamente com a adição de novos campos e com a remoção de antigos, e é desnormalizado para otimizar as operações de criação de cubo e de relatórios mais eficientes. Os campos personalizados são armazenados em várias tabelas de pool de colunas MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask* e MSP_EpmCPAssn* para dados de projetos, recursos, tarefas e atribuições, respectivamente. À medida que novos campos personalizados são criados, novas colunas são adicionadas a tabelas de pool de colunas do tipo de entidade correspondente, e novas tabelas são criadas quando tabelas existentes atingem um certo número de colunas. Para obter uma descrição mais detalhada de como os campos personalizados são armazenados no RDB, consulte Campos personalizados locais e da empresa (em inglês) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x416) (em inglês) na Biblioteca MSDN Online.

O Atualização de Infraestrutura para os Microsoft Office Servers contém as seguintes exibições que agregam dados de campo personalizado do RDB para cada uma das quatro entidades principais:

• MSP_EpmProject_UserView

• MSP_EpmTask_UserView

• MSP_EpmAssignment_UserView

• MSP_EpmResource_UserView

Essas exibições de usuário são mantidas pelo Office Project Server e contêm todos os campos personalizados definidos para a entidade correspondente. Sempre que um campo personalizado é adicionado, uma nova coluna é adicionada automaticamente para a exibição correspondente. Além disso, sempre que um campo personalizado for excluído, sua coluna correspondente será removida da exibição.

Também é possível criar exibições próprias personalizadas para as necessidades da sua organização. Por exemplo, se você tiver um relatório que use um pequeno subconjunto de campos, em vez de usar as exibições padrão, poderá optar por criar suas próprias exibições personalizadas que incluam somente os dados relevantes.

Criar exibições personalizadas

Para criar suas próprias exibições personalizadas, primeiro você precisará descobrir onde os valores de campo são armazenados. Depois que souber que tabela de pool de colunas e número de colunas aponta para o campo de interesse, você poderá usar uma instrução do Join para obter valores em sua exibição. Todas as tabelas de pool de coluna têm uma coluna EntityUID que contém o identificador exclusivo da entidade que faz referência a uma determinada linha de dados.

Função auxiliar

A função a seguir retorna informações interessantes sobre todos os campos personalizados.

FUNCTION MFN_Epm_GetAllCustomFieldsInformation();

Valores de retorno

A função retorna um conjunto de dados com as informações do campo personalizado (uma linha para cada campo personalizado). Se nenhum campo personalizado for encontrado, a função retornará um conjunto de dados vazio.

O conjunto de dados retornado tem uma linha para cada campo personalizado com as seguintes colunas:

Exemplo

Este é um exemplo para ilustrar a criação de uma exibição personalizada simples mostrando dois valores de campo personalizado do projeto.

Neste exemplo, suponha que temos dois campos personalizados de recurso predefinido (EDR e Tipo de Custo) que desejamos consultar na exibição, junto com o nome do recurso, a identificação do recurso, a taxa padrão do recurso, a taxa de horas extras do recurso e o nome de conta do Windows NT do recurso. Se tiver certeza de que os nomes de campos personalizados são exclusivos e de que eles não mudam, você poderá usar a coluna CustomFieldName na filtragem. No entanto, uma ideia melhor seria primeiro fazer uma operação SELECT, como a seguir.

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

Nos resultados, verifique se você identificou os campos personalizados desejados e anote seus valores de CustomFieldTypeUID (eles são suas identificações exclusivas).

Vamos supor que, neste exemplo, os dois identificadores exclusivos encontrados são:

• {0000783FDE84434B9564284E5B7B3F49} para EDR

• {000039B78BBE4CEB82C4FA8C0C400284} para Tipo de Custo

Usando os dois identificadores exclusivos para o EDR e o Tipo de custo do exemplo acima, você poderá usá-los para escrever o seguinte script:

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

Criar índices de campo personalizado

Pode ser complicado descobrir em qual coluna de qual tabela foram salvos os valores de um campo personalizado específico. Portanto, o Project Server tem dois procedimentos armazenados que criam um índice na coluna apropriada, usando como entrada o campo personalizado e os parâmetros de índice.

Procedimentos armazenados auxiliares

Se e quando souber que um campo personalizado precisa de um índice para melhorar o desempenho das consultas usadas por alguns relatórios, você poderá usar os seguintes métodos:

Método 1:

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

Método 2:

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

Parâmetros para MSP_Epm_CreateCustomFieldIndexByUID

O parâmetro a seguir identifica o campo personalizado:

Estes são os parâmetros que definem o índice:

Parâmetros para MSP_Epm_CreateCustomFieldIndexByName

Os seguintes parâmetros identificam o campo personalizado:

Estes são os parâmetros que definem o índice:

Para obter mais informações sobre os parâmetros que definem a criação do índice, leia uma descrição do comando CREATE INDEX na Biblioteca MSDN: CREATE INDEX (Transact-SQL) (em inglês) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x416) (em inglês).

Retornar valores para ambos os procedimentos

Estes são valores de retorno para os procedimentos anteriores:

Exemplo

O exemplo a seguir usa um dos campos personalizados de dois recursos predefinidos: Tipo de Custo. Também há dois métodos para identificar os campos personalizados: por ID ou pelo nome. A seguir, mostraremos exemplos de uso dos dois métodos, mas a maneira recomendada é usar ID para identificar os campos personalizados.

Para criar um índice para o campo personalizado de recurso "Tipo de Custo" por nome, chame:

EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'

Para criar um índice para este campo personalizado por ID (consulte a seção anterior sobre como buscar UID de campo personalizado usando a função MFN_EpmGetAllCustomFieldsInformation):

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

Fazendo exibições e índices permanecerem

Você pode otimizar a geração de relatórios usando os métodos acima aplicando índices em campos personalizados e criando exibições destinadas/montadas conforme descrito nas seções anteriores. No entanto, observe que durante uma atualização do RDB, índices e exibições personalizadas que usam campos personalizados poderão ser invalidadas.

Isso ocorre porque, durante uma atualização, todas as tabelas de pool de colunas do campo personalizado serão desmarcadas e todos os campos personalizados serão excluídos do RDB. Durante o processo de ressincronização, a ordem de alocação de campo personalizado poderá mudar. Isso significa que os valores dos campos personalizados poderão ser salvos em uma coluna diferente ou mesmo em uma tabela diferente.

Por exemplo, imagine que havia dois campos personalizados criados na seguinte ordem: primeiro CF1, depois CF2, onde CF1 e CF2 são campos personalizados de texto. CF1 obterá a coluna CFVal0 da tabela e CF2 obterá CFVal1. A tabela de pool de colunas será assim:

Se CF1 for excluído, a tabela será assim:

No entanto, após uma atualização as colunas no pool de coluna serão preenchidas novamente (do zero, CF1 não existirá mais e CF2 agora ocupará a coluna CFVal0). A tabela será assim:

Se você tiver criado anteriormente uma exibição personalizada ou um índice apontando para CFVal1, após uma atualização, em vez de apontar para CF2, o RDB agora estará apontando para um campo personalizado diferente. O resultado final é que, em tais casos, o índice terminará na coluna errada, o que não é desejável. Para resolver esse problema, se você estiver criando exibições personalizadas ou índices para melhorar o desempenho do relatório, também deverá considerar a criação de um procedimento armazenado:

PROCEDURE MSP_OnRefreshCompleted();

Se esse procedimento existir, será chamado automaticamente depois que uma atualização RDB for concluída com êxito. Ele recriará os índices de campo personalizado e/ou as exibições personalizadas.

Exemplo

Se desejar que as alterações dos dois exemplos acima permaneçam válidas após uma atualização do RDB, converta os dois scripts em um procedimento armazenado e chame-o deMSP_OnRefreshCompleted. Você também deverá tornar esse procedimento armazenado reentrante (ou seja, ele será executado corretamente se você chamá-lo várias vezes seguidas).

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

Agora a exibição personalizada "MySampleView" e o índice de campo personalizado em "Tipo de custo" serão automaticamente reaplicados após uma atualização do RDB.