Optimización del servicio de datos de informes para campos personalizados
Actualizado: diciembre de 2009
Última modificación del tema: 2015-02-27
En este artículo se describe cómo optimizar soluciones personalizadas de generación de informes creadas para la base de datos de informes (RDB) de Microsoft Office Project Server 2007. Si está interesado en crear vistas personalizadas o aplicar índices personalizados a cualquier vista de la RDB, lea este artículo para conocer algunos procedimientos auxiliares almacenados que puede usarse junto con sus soluciones.
Si no está ya familiarizado con la mecánica general de la RDB, vea estos artículos de contexto:
Creación de informes y RDS (en inglés): (https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0xC0A) (en inglés)
Paquete de informes de Project Server (en inglés): (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0xC0A) (en inglés)
En primer lugar, veamos cómo se almacenan los campos personalizados en la base de datos de informes. Office Project Server 2007 tiene varios campos personalizados predefinidos. A medida que la instancia crece, se pueden agregar nuevos campos personalizados de empresa y eliminar los existentes durante el mantenimiento regular. El mecanismo de almacenamiento de campos personalizados en la base de datos de informes está diseñado para controlar de forma dinámica la adición de campos nuevos y la eliminación de campos antiguos, y está desnormalizado para optimizar operaciones de generación de informes y creación de cubos más eficaces. Los campos personalizados se almacenan en varias tablas de grupos de columnas MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask* y MSP_EpmCPAssn* para datos de proyectos, recursos, tareas y asignaciones respectivamente. A medida que se crean nuevos campos personalizados, se agregan nuevas columnas a las tablas de grupos de columnas del tipo de entidad correspondiente, así como nuevas tablas cuando las existentes alcanzan un número determinado de columnas. Para obtener una descripción más detallada acerca del almacenamiento de campos personalizados en la base de datos de informes, vea la información sobre campos personalizados de empresa y locales (en inglés) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0xC0A) (en inglés) en MSDN Library Online.
La Actualización de infraestructura para servidores de Microsoft Office contiene las siguientes vistas que agregan datos de campos personalizados de la RDB para cada una de las cuatro entidades principales:
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
Office Project Server mantiene estas vistas de usuario, que contienen todos los campos personalizados definidos para la entidad correspondiente. Si se agrega un campo personalizado, se crea una nueva columna de forma automática para la vista correspondiente. Además, si se elimina un campo personalizado, la columna correspondiente se elimina de la vista.
También se pueden crear vistas que se ajusten a las necesidades de la organización. Por ejemplo, si tiene un informe que usa un subconjunto pequeño de campos, en lugar de usar las vistas predeterminadas, puede crear vistas personalizadas que sólo incluyan datos relevantes.
Creación de vistas personalizadas
Para crear vistas personalizadas, primero es necesario buscar la ubicación de almacenamiento de los valores de campo. Una vez localizada la tabla de grupo de columnas y el número de columna que señalan al campo en cuestión, se puede usar una instrucción Join para obtener valores en la vista. Todas las tablas de grupos de columnas tienen una columna EntityUID que contiene el identificador único de la entidad a la que hacen referencia una fila o datos determinados.
Función auxiliar
La siguiente función devuelve información interesante acerca de todos los campos personalizados.
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
Valores devueltos
La función devuelve un conjunto de datos con la información de campo personalizado (una fila para cada campo personalizado). Si no se encuentra ningún campo personalizado, la función devuelve un conjunto de datos vacío.
El conjunto de datos devuelto tiene una fila para cada campo personalizado con las siguientes columnas:
| Valor | Descripción |
|---|---|
EntityTypeUID |
Identificador único de la entidad principal de cada campo personalizado. (Por ejemplo: para campos personalizados de proyecto, esta columna muestra un valor correspondiente a 'Proyectos'.) |
EntityName |
Nombre de la entidad principal de cada campo personalizado (en el ejemplo anterior sería 'Proyectos'). |
CustomFieldTypeUID |
Identificador único del campo personalizado. |
CustomFieldName |
Nombre del campo personalizado. |
SecondaryCustomFieldTypeUID |
Identificador del campo personalizado correspondiente. |
DataType |
Tipo de datos de campo personalizado. |
IsMultiValueEnabled |
La columna muestra 1 si el campo personalizado puede tener varios valores. |
IsRollDown |
La columna muestra 1 si se aplican los valores de campo personalizado. |
LookupTableUID |
Si el campo personalizado usa una tabla de consulta, esta columna muestra su identificador único. En caso contrario, la columna será nula. |
LookupTableName |
Si el campo personalizado usa una tabla de consulta, esta columna muestra su nombre. En caso contrario, la columna será nula. |
LookupTableMembersViewName |
Project Server crea una vista para cada tabla de consulta definida. Hay una vista que selecciona todos sus miembros. Esta columna muestra el nombre de la vista con los miembros de la tabla de consulta que usa el campo personalizado. |
LookupTableHasMultipleLevels |
Esta columna muestra 1 si la tabla de consulta tiene los valores definidos en más de un nivel. |
ColumnPoolColumnName |
Nombre de la columna que almacena los valores de campo personalizado. |
ColumnPoolTableName |
Tabla que almacena los valores de campo personalizado. |
EntityNonTimephasedTableName |
Tabla que almacena los datos que no son de fase temporal para la entidad principal del campo personalizado. (Por ejemplo: para un campo personalizado de proyecto, la columna muestra "MSP_EpmProject"). |
CreatedDate |
Fecha de creación del campo personalizado. |
ModificationDate |
Fecha de la última modificación del campo personalizado. |
Ejemplo
A continuación se muestra un ejemplo que ilustra cómo crear una vista personalizada sencilla que muestre dos valores de campo personalizado de proyecto.
Para este ejemplo, se supone que tenemos dos campos personalizados de recurso predefinido (EDR y Tipo de costo) que queremos ver en la vista, junto con el nombre del recurso, el identificador del recurso, la tasa estándar del recurso, la tasa de horas extra del recurso y el nombre de cuenta de Windows NT del recurso. Si está seguro de que los nombres de campo de recurso son únicos y no van a cambiar, puede usar la columna CustomFieldName para filtrar. Sin embargo, se recomienda realizar primero una operación SELECT como la siguiente.
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
En los resultados, asegúrese de haber identificado los campos personalizados que desee y, a continuación, anote los valores de CustomFieldTypeUID. (Estos son los identificadores únicos).
Supongamos para este ejemplo que los dos identificadores únicos son:
{0000783FDE84434B9564284E5B7B3F49} para EDR
{000039B78BBE4CEB82C4FA8C0C400284} para Tipo de costo
Puede usar los dos identificadores únicos para EDR y Tipo de costo del ejemplo anterior para escribir el siguiente 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
Creación de índices de campo personalizado
Puede resultar complicado averiguar en qué columna de qué tabla se guardan los valores de un campo personalizado específico. Por ello, Project Server tiene dos procedimientos almacenados que crean un índice en la columna correspondiente tomando como entrada el campo personalizado y los parámetros de índice.
Procedimientos auxiliares almacenados
Si un campo personalizado necesita un índice para mejorar el rendimiento de las consultas usadas por algunos informes, puede usar los métodos siguientes:
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
El siguiente parámetro identifica el campo personalizado:
| Parámetro | Descripción |
|---|---|
@CustomFieldTypeUID |
Identificador único del campo personalizado sobre el que se creará el índice |
A continuación se muestran los parámetros que definen el índice:
| Parámetro | Descripción |
|---|---|
@PadIndex |
Opcional. Especifica el espacio que hay que dejar libre en cada página en los niveles intermedios del índice. |
@FillFactor |
Opcional. Especifica un porcentaje que indica hasta dónde debe llenar Microsoft SQL Server el nivel hoja de cada página de índice durante la creación del índice. Este parámetro debe tener un valor entre 1 y 100. |
@NoRecomputeStatistics |
Opcional. Si el valor es 1, las estadísticas de índices obsoletas no se volverán a calcular de forma automática. |
@SortInTempDB |
Es opcional. Si el valor es 1, los resultados de ordenación intermedios usados para generar el índice se almacenarán en la base de datos tempdb. |
@FileGroup |
Opcional. El índice se creará en el grupo de archivos especificado. |
Parámetros para MSP_Epm_CreateCustomFieldIndexByName
Los siguientes parámetros identifican el campo personalizado:
| Parámetro | Descripción |
|---|---|
@CustomFieldName |
Nombre del campo personalizado sobre el que se creará el índice. |
@CustomFieldEntityName |
Opcional. El nombre de la entidad en la que se define el campo personalizado (por ejemplo: Proyecto para campos personalizados de proyecto o Recurso para los campos personalizados de recursos, etc.). |
A continuación se muestran los parámetros que definen el índice:
| Parámetro | Descripción |
|---|---|
@PadIndex |
Opcional. Especifica el espacio que hay que dejar libre en cada página en los niveles intermedios del índice. |
@FillFactor |
Opcional. Especifica un porcentaje que indica hasta dónde debe llenar SQL Server el nivel hoja de cada página de índice durante la creación del índice. Este parámetro debe tener un valor entre 1 y 100. |
@NoRecomputeStatistics |
Opcional. Si el valor es 1, las estadísticas de índices obsoletas no se volverán a calcular de forma automática. |
@SortInTempDB |
Opcional. Si el valor es 1, los resultados de ordenación intermedios usados para generar el índice se almacenarán en la base de datos tempdb. |
@FileGroup |
Opcional. El índice se creará en el grupo de archivos especificado. |
Para obtener más información sobre los parámetros que definen la creación del índice, puede leer una descripción del comando CREATE INDEX en la biblioteca de MSDN: CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0xC0A).
Valores devueltos para ambos procedimientos.
A continuación se muestran los valores devueltos para los procedimientos anteriores:
| Valor | Descripción |
|---|---|
0 |
El índice se ha creado correctamente. |
-1 |
El índice no se ha creado porque no se encontró el campo personalizado solicitado. |
-2 |
El índice ya existe. |
-3 |
No se ha creado el índice. Error al ejecutar la instrucción CREATE INDEX. |
-4 |
Error al generar la instrucción CREATE INDEX. Esta instrucción se genera en una variable de texto y, a continuación, se ejecuta de forma dinámica. Este error se devuelve cuando se produce un error al crear la cadena de comando. |
-5 |
No se ha podido indizar el campo personalizado especificado con este método. Existen algunos tipos de campos personalizados que no pueden indizarse mediante los procedimientos almacenados proporcionados (como los campos personalizados con varios valores). |
-6 |
No se ha podido crear el índice porque más de un campo personalizado coincide con los criterios especificados. Esto puede suceder si hay dos o más campos personalizados con el mismo nombre (en diferentes entidades) y se llama al método para indizar campos personalizados por su nombre únicamente con el nombre de campo personalizado, sin proporcionar ningún nombre de entidad. |
Ejemplo
En el ejemplo siguiente se usa uno de los dos campos personalizados de recursos predefinidos: Tipo de costo. También hay dos métodos para identificar los campos personalizados: por identificador o por nombre. A continuación se muestran ejemplos de uso para ambos métodos, aunque se recomienda usar identificadores para identificar los campos personalizados.
Para crear un índice para el campo personalizado de recurso “Tipo de costo” por nombre, llame a:
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
Para crear un índice para este campo personalizado por identificador (vea la sección anterior acerca de la obtención del UID de campo personalizado mediante la función MFN_EpmGetAllCustomFieldsInformation):
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
Mantener "unidos" vistas e índices
Se puede optimizar la generación de informes con los métodos anteriores si se aplican índices a campos personalizados y se crean vistas dirigidas o recortadas, tal y como se describe en las secciones anteriores. Sin embargo, tenga en cuenta que durante una actualización de la RDB, pueden invalidarse los índices y las vistas personalizadas que usan campos personalizados.
Esto sucede porque, durante una actualización, se borran todas las tablas de grupos de columnas de campos personalizados y se eliminan todos los campos personalizados de la RDB. Durante el proceso de resincronización, es posible que cambie el orden de asignación de campos personalizados. Esto significa que los valores de campo personalizado pueden guardarse en una columna diferente o incluso en una tabla diferente.
Por ejemplo, imagine que hay dos campos personalizados creados en el siguiente orden: primero CF1 y después CF2, donde CF1 y CF2 son campos personalizados de texto. CF1 obtendrá la columna CFVal0 en la tabla y CF2 obtendrá la columna CFVal1. La tabla de grupo de columnas será similar a la siguiente:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
Infrapresupuestado |
Según lo programado |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Sin línea de base |
Sin línea de base |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Presupuesto sobrepasado |
Según lo programado |
15 |
NULL |
Si CF1 se elimina, la tabla tendrá el siguiente aspecto:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
NULL |
Según lo programado |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
NULL |
Sin línea de base |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
NULL |
Según lo programado |
15 |
NULL |
Sin embargo, tras realizar una actualización, las columnas del grupo de columnas se vuelven a rellenar (se empieza desde cero, CF1 ya no existirá y CF2 ocupará ahora la columna CFVal0). La tabla tendrá este aspecto:
| EntityUID | CFVal0 | CFVal1 | CFVal2 |
|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
Según lo programado |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Según lo programado |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Según lo programado |
15 |
NULL |
Si ha creado previamente un índice personalizado o una vista personalizada que señalan a CFVal1, después de actualizar la RDB, en lugar de señalar a CF2, señalarán a un campo personalizado diferente. Básicamente, en estos casos el índice acaba en la columna errónea, algo que se debe evitar. Para solucionar este problema, cuando cree índices o vistas personalizados para mejorar el rendimiento de los informes, considere la posibilidad de crear también un proceso almacenado:
PROCEDURE MSP_OnRefreshCompleted();
Si existe este procedimiento almacenado, se le llama automáticamente una vez se ha completado correctamente la actualización de RDB. Se volverán a crear los índices de campos personalizados y las vistas personalizadas.
Ejemplo
Si desea que los cambios de los dos ejemplos anteriores sigan siendo válidos tras la actualización de RDB, deberá convertir los dos scripts en un procedimiento almacenado y llamarlo MSP_OnRefreshCompleted. También debe hacer que este procedimiento almacenado sea reentrante (lo que significa que se ejecutará de forma correcta si se le llama varias veces 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
Ahora la vista personalizada "MySampleView" y el índice de campo personalizado de "Tipo de costo" se volverán a aplicar inmediatamente tras realizar una actualización de RDB.