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:

Valor	Descrição
EntityTypeUID	O identificador exclusivo da entidade pai para cada campo personalizado (por exemplo: para campos personalizados do projeto, essa coluna exibe um valor correspondente a 'Projetos').
EntityName	O nome da entidade pai de cada campo personalizado (no exemplo acima, seria 'Projetos').
CustomFieldTypeUID	O identificador exclusivo do campo personalizado.
CustomFieldName	O nome do campo personalizado.
SecondaryCustomFieldTypeUID	A identificação do campo personalizado correspondente.
DataType	Tipo de dados de campo personalizado.
IsMultiValueEnabled	A coluna exibirá 1 se o campo personalizado puder ter vários valores.
IsRollDown	A coluna exibirá 1 se os valores dos campos personalizados forem rolados para baixo.
LookupTableUID	Se o campo personalizado usar uma tabela de pesquisa, essa coluna exibirá seu identificador exclusivo. Caso contrário, a coluna será nula.
LookupTableName	Se o campo personalizado usar uma tabela de pesquisa, essa coluna exibirá seu nome. Caso contrário, a coluna será nula.
LookupTableMembersViewName	O Project Server cria uma exibição para cada tabela de pesquisa definida. Há uma exibição que seleciona todos os seus membros. Essa coluna exibe o nome da exibição com os membros da tabela de pesquisa usada pelo campo personalizado.
LookupTableHasMultipleLevels	Essa coluna exibirá 1 se a tabela de pesquisa tiver seus valores definidos em mais de um nível.
ColumnPoolColumnName	O nome da coluna que armazena valores de campo personalizado.
ColumnPoolTableName	A tabela que armazena os valores do campo personalizado.
EntityNonTimephasedTableName	A tabela que armazena dados não temporais para a entidade pai do campo personalizado (por exemplo: para um campo personalizado do projeto, a coluna exibe 'MSP_EpmProject').
CreatedDate	A data em que o campo personalizado foi criado.
ModificationDate	A data em que o campo personalizado foi modificado pela última vez.

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:

Parâmetro	Descrição
@CustomFieldTypeUID	A identificação exclusiva do campo personalizado no qual o índice será criado

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

Parâmetro	Descrição
@PadIndex	Opcional. Especifica o espaço a ser deixado em aberto em cada página nos níveis intermediários do índice.
@FillFactor	Opcional. Especifica uma porcentagem que indica como o Microsoft SQL Server deve completar o nível de folha de cada página de índice durante a criação do índice. Esse parâmetro deve ter um valor entre 1 e 100.
@NoRecomputeStatistics	Opcional. Se o valor for 1, as estatísticas de índice desatualizadas não serão automaticamente recalculadas.
@SortInTempDB	Opcional. Se o valor for 1, os resultados intermediários de classificação usados para criar o índice serão armazenados no banco de dados tempdb.
@FileGroup	Opcional. O índice será criado no grupo de arquivos especificado.

Parâmetros para MSP_Epm_CreateCustomFieldIndexByName

Os seguintes parâmetros identificam o campo personalizado:

Parâmetro	Descrição
@CustomFieldName	O nome do campo personalizado no qual o índice será criado.
@CustomFieldEntityName	Opcional. O nome da entidade na qual o campo personalizado está definido (por exemplo: Projeto para campos personalizados do projeto ou Recurso para os campos personalizados do recurso etc.).

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

Parâmetro	Descrição
@PadIndex	Opcional. Especifica o espaço a ser deixado em aberto em cada página nos níveis intermediários do índice.
@FillFactor	Opcional. Especifica uma porcentagem que indica como o SQL Server deve completar o nível de folha de cada página de índice durante a criação do índice. Esse parâmetro deve ter um valor entre 1 e 100.
@NoRecomputeStatistics	Opcional. Se o valor for 1, as estatísticas de índice desatualizadas não serão automaticamente recalculadas.
@SortInTempDB	Opcional. Se o valor for 1, os resultados intermediários de classificação usados para criar o índice serão armazenados no banco de dados tempdb.
@FileGroup	Opcional. O índice será criado no grupo de arquivos especificado.

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:

Valor	Descrição
0	Sucesso. O índice foi criado com êxito.
-1	O índice não foi criado porque o campo personalizado solicitado não foi encontrado.
-2	O índice já existe.
-3	O índice não foi criado; falha na execução da instrução CREATE INDEX.
-4	Falha ao gerar a instrução CREATE INDEX. Essa instrução é gerada em uma variável de texto e executada dinamicamente. Esse erro é retornado quando há uma falha na criação da sequência de caracteres do comando.
-5	O campo personalizado especificado não pode ser indexado com esse método. Há alguns tipos de campos personalizados que não podem ser indexados por procedimentos armazenados fornecidos (como campos personalizados de vários valores).
-6	O índice não pode ser criado porque mais de um campo personalizado corresponde aos critérios especificados. Isso pode acontecer se houver dois ou mais campos personalizados com o mesmo nome (em entidades diferentes) e se o método para indexação de campo personalizado por nome for chamado com apenas o nome do campo personalizado, sem fornecer qualquer nome de entidade.

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:

EntityUID	CFVal0	CFVal1	CFVal2	CFVal3 …
AF129A8C-DCB5-4FB0-9E30-406458614A31	Abaixo do orçamento	No prazo	15	NULL
4D607B14-E40C-4549-8E92-45A3A96D6892	Sem linha de base	Sem linha de base	NULL	NULL
8496EA23-4B25-4DBE-B68A-755A27246842	Acima do orçamento	No prazo	15	NULL

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

EntityUID	CFVal0	CFVal1	CFVal2	CFVal3 …
AF129A8C-DCB5-4FB0-9E30-406458614A31	NULL	No prazo	15	NULL
4D607B14-E40C-4549-8E92-45A3A96D6892	NULL	Sem linha de base	NULL	NULL
8496EA23-4B25-4DBE-B68A-755A27246842	NULL	No prazo	15	NULL

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:

EntityUID	CFVal0	CFVal1	CFVal2
AF129A8C-DCB5-4FB0-9E30-406458614A31	No prazo	15	NULL
4D607B14-E40C-4549-8E92-45A3A96D6892	No prazo	NULL	NULL
8496EA23-4B25-4DBE-B68A-755A27246842	No prazo	15	NULL

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.