Оптимизации службы данных отчета для настраиваемых полей
Обновлено: Декабрь 2009 г.
Последнее изменение раздела: 2015-02-27
В данной статье описан процесс оптимизации решений пользовательской отчетности, созданных для базы данных отчетности Microsoft Office Project Server 2007. Данная статья предоставляет вспомогательные хранимые процедуры, которые можно использовать вместе с решениями в построении представлений или применении настраиваемых индексов для представлений в базе данных отчетности.
Если общие механизмы базы данных отчетности еще не известны, см. связанные статьи:
Отчетность и база данных отчетности (на английском языке): (https://go.microsoft.com/fwlink/?linkid=123365\&clcid=0x419) (на английском языке)
Пакет отчетности по Project Server (на английском языке): (https://go.microsoft.com/fwlink/?linkid=123367\&clcid=0x419) (на английском языке)
Сначала давайте обратим внимание на процесс хранения данных настраиваемых полей в базе данных отчетности. В Office Project Server 2007 имеется несколько предопределенных настраиваемых полей. По мере возрастания экземпляра возможно добавление новых корпоративных настраиваемых полей и удаление существующих во время регулярного обслуживания. Механизм хранения настраиваемых полей в базе данных отчетности разработан для динамической обработки добавления новых и удаления старых полей, и он денормализован для оптимизации более эффективных операций построения куба и отчетности. Настраиваемые поля хранятся в нескольких таблицах пула столбцов MSP_EpmCPPrj*, MSP_EpmCPRes*, MSP_EpmCPTask* и MSP_EpmCPAssn* для данных проектов, ресурсов, задач и назначений соответственно. По мере создания новых настраиваемых полей новые столбцы добавляются в таблицы пула столбцов соответствующего типа сущности, и создаются новые таблицы, когда в существующих таблицах достигается определенное количество столбцов. Более подробное описание хранения настраиваемых полей в базе данных отчетности см. в разделе Локальные и корпоративные настраиваемые поля (на английском языке) (https://go.microsoft.com/fwlink/?linkid=123368\&clcid=0x419) (на английском языке) в библиотеке MSDN в сети.
Обновление инфраструктуры Microsoft Office Servers содержит следующие представления для сбора данных настраиваемых полей базы данных отчетности для каждого из четырех основных сущностей:
MSP_EpmProject_UserView
MSP_EpmTask_UserView
MSP_EpmAssignment_UserView
MSP_EpmResource_UserView
Эти представления пользователей обслуживаются сервером Office Project Server и содержат все настраиваемые поля, определенные для соответствующей сущности. При добавлении настраиваемого поля новый столбец автоматически добавляется к соответствующему представлению. Также при удалении настраиваемого поля его соответствующий столбец удаляется из представления.
Также можно построить свои собственные представления, настроенные для потребностей организации. Например, при наличии одного отчета, использующего маленькое подмножество полей, вместо использования представлений по умолчанию можно создать свои собственные настраиваемые представления, в которых будут включены только необходимые данные.
Создание представлений
Чтобы создать свои собственные настраиваемые представления, в первую очередь необходимо выяснить, где хранятся значения полей. Если известно, какая таблица пула столбцов и номер столбца указывает на необходимое поле, можно использовать инструкцию Join для извлечения значений в представление. Все таблицы пула столбцов имеют столбец EntityUID, содержащий уникальный идентификатор сущности, на которую ссылается данная строка данных.
Вспомогательная функция
Следующая функция возвращает интересные сведения обо всех настраиваемых полях.
FUNCTION MFN_Epm_GetAllCustomFieldsInformation();
Возвращаемые значения
Функция возвращает набор данных со сведениями о настраиваемых полях (одна строка для каждого настраиваемого поля). При отсутствии настраиваемых полей функция возвращает пустой набор данных.
В возвращенном наборе данных содержится одна строка для каждого настраиваемого поля со следующими столбцами:
| Значение | Описание |
|---|---|
EntityTypeUID |
Уникальный идентификатор родительской сущности для каждого настраиваемого поля. (Например, для настраиваемых полей проекта этот столбец отображает значение, соответствующие 'Projects'.) |
EntityName |
Имя родительской сущности каждого настраиваемого поля (в предыдущем примере именем будет 'Projects'). |
CustomFieldTypeUID |
Уникальный идентификатор настраиваемого поля. |
CustomFieldName |
Имя настраиваемого поля. |
SecondaryCustomFieldTypeUID |
Идентификатор соответствующего настраиваемого поля. |
DataType |
Тип данных настраиваемого поля. |
IsMultiValueEnabled |
Столбец отображает значение 1, если настраиваемое поле может содержать несколько значений. |
IsRollDown |
Столбец отображает значение 1, если выполняется развертывание значений настраиваемого поля. |
LookupTableUID |
Если настраиваемое поле использует таблицу подстановки, этот столбец отображает ее уникальный идентификатор. В противном случае значение столбца равно нулю. |
LookupTableName |
Если настраиваемое поле использует таблицу подстановки, этот столбец отображает ее имя. В противном случае значение столбца равно нулю. |
LookupTableMembersViewName |
Сервер Project Server создает представление для каждой определенной таблицы подстановки. Существует значение, выбирающее всех участников. Этот столбец отображает имя представления с участниками таблицы подстановки, используемой настраиваемым полем. |
LookupTableHasMultipleLevels |
Этот столбец отображает 1, если в таблице подстановки содержатся значения, определенные более чем на одном уровне. |
ColumnPoolColumnName |
Имя столбца, в котором хранятся значения настраиваемого поля. |
ColumnPoolTableName |
Таблица, в которой хранятся значения настраиваемого поля. |
EntityNonTimephasedTableName |
Таблица, в которой хранятся неповременные данные для родительской сущности настраиваемого поля. (Например, для настраиваемого поля проекта этот столбец отображает значение 'MSP_EpmProject'.) |
CreatedDate |
Дата создания настраиваемого поля. |
ModificationDate |
Дата последнего изменения настраиваемого поля. |
Пример
Далее приводится пример, иллюстрирующий создание простого настраиваемого представления, показывающего два значения настраиваемых полей проекта.
Для этого примера необходимо предположить, что в наличии имеются два предопределенных настраиваемых поля ресурса (СДРес и Тип затрат), которые необходимо увидеть в представлении наряду с именем, идентификатором, стандартной ставкой, ставкой сверхурочных работ и именем учетной записи Windows NT ресурса. Если точно известно, что имена настраиваемых полей являются уникальными и не будут изменены, можно использовать столбец CustomFieldName для фильтрации. Однако лучше было бы сначала выполнить операцию "ВЫДЕЛИТЬ" следующим образом.
SELECT * FROM MFN_EpmGetAllCustomFieldsInformation() WHERE EntityName='Resource'
В результате необходимо убедиться, что определены необходимые настраиваемые поля, а затем зарегистрировать их значения CustomFieldTypeUID. (Это уникальные идентификаторы.)
Давайте предположим, что в этом примере существует два следующих уникальных идентификатора:
{0000783FDE84434B9564284E5B7B3F49} для СДРес
{000039B78BBE4CEB82C4FA8C0C400284} для типа затрат
Можно использовать два уникальных идентификатора для СДРес и типа затрат из вышеуказанного примера для создания следующего сценария:
--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
Создание индексов настраиваемых полей
Может быть непросто обнаружить, в каком столбце в какой таблице сохраняются конкретные значения настраиваемого поля. Поэтому в Project Server имеются в наличии две хранимых процедуры, создающие индекс в подходящем столбце, принимая в качестве ввода настраиваемое поле и параметры индекса.
Вспомогательные хранимые процедуры
Когда для настраиваемого поля требуется индекс для улучшения производительности запросов, используемых некоторыми отчетами, можно воспользоваться следующими методами:
Метод 1:
PROCEDURE MSP_CreateCustomFieldIndexByUID(@CustomFieldTypeUIDuniqueidentifier, @PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Метод 2:
PROCEDURE MSP_CreateCustomFieldIndexByName(@customFieldName [NAME], @customFieldEntityName [NAME] = NULL,@PadIndex bit= NULL,@FillFactorsmallint= NULL,@NoRecomputeStatistics bit= NULL,@SortInTempDB bit= NULL,@FileGroupnvarchar(400)= NULL);
Параметры для MSP_Epm_CreateCustomFieldIndexByUID
Следующий параметр определяет настраиваемое поле:
| Параметр | Описание |
|---|---|
@CustomFieldTypeUID |
Уникальный идентификатор настраиваемого поля, в котором будет создан индекс |
Далее приводятся параметры, определяющие индекс:
| Параметр | Описание |
|---|---|
@PadIndex |
Необязательный атрибут. Указывает, сколько пространства необходимо оставить открытым на каждой странице в средних уровнях индекса. |
@FillFactor |
Необязательный атрибут. Указывает процентное отношение, указывающее, насколько сервер Microsoft SQL Server должен выполнить конечный уровень каждой страницы индексов во время создания индексов. Этот параметр должен иметь значение между 1 и 100. |
@NoRecomputeStatistics |
Необязательный атрибут. Если значение равно 1, устаревшая статистика индексов не пересчитывается автоматически. |
@SortInTempDB |
Необязательный атрибут. Если значение равно 1, средние результаты сортировки, используемые для построения индекса, будут отсортированы в базе данных tempdb. |
@FileGroup |
Необязательный атрибут. Индекс будет создан в указанной группе файлов. |
Параметры для MSP_Epm_CreateCustomFieldIndexByName
Следующие параметры определяют настраиваемое поле:
| Параметр | Описание |
|---|---|
@CustomFieldName |
Имя настраиваемого поля, в котором будет создан индекс. |
@CustomFieldEntityName |
Необязательный атрибут. Имя сущности, в которой определяется настраиваемое поле (например, "Project" для настраиваемых полей проекта или "Resource" для настраиваемых полей ресурса, и т. д.). |
Далее приводятся параметры, определяющие индекс:
| Параметр | Описание |
|---|---|
@PadIndex |
Необязательный атрибут. Указывает, сколько пространства оставить открытым на каждой странице в средних уровнях индекса. |
@FillFactor |
Необязательный атрибут. Указывает процентное отношение, указывающее, насколько сервер SQL Server должен выполнить конечный уровень каждой страницы индексов во время создания индексов. Этот параметр должен иметь значение между 1 и 100. |
@NoRecomputeStatistics |
Необязательный атрибут. Если значение равно 1, устаревшая статистика индексов не пересчитывается автоматически. |
@SortInTempDB |
Необязательный атрибут. Если значение равно 1, средние результаты сортировки, используемые для построения индекса, будут отсортированы в базе данных tempdb. |
@FileGroup |
Необязательный атрибут. Индекс будет создан в указанной группе файлов. |
Дополнительные сведения о параметрах, определяющих создание индексов, см. в описании команды CREATE INDEX в библиотеке MSDN: CREATE INDEX (Transact-SQL) (https://go.microsoft.com/fwlink/?linkid=94749\&clcid=0x419).
Возвращаемые значения для обеих процедур
Далее приведены возвращаемые значения для предыдущих процедур:
| Значение | Описание |
|---|---|
0 |
Успешно завершено. Индекс был успешно создан. |
-1 |
Индекс не был создан, поскольку не было найдено запрашиваемое настраиваемое поле. |
-2 |
Индекс уже существует. |
-3 |
Индекс не был создан; сбой при выполнении инструкции CREATE INDEX. |
-4 |
Сбой при создании инструкции CREATE INDEX. Эта инструкция создается в текстовой переменной и затем выполняется динамически. Эта ошибка возвращается, когда происходит сбой построения строки команды. |
-5 |
Индексирование указанного настраиваемого поля с этим методом невозможно. Существуют некоторые типы настраиваемых полей, индексирование которых невозможно с помощью предоставленных хранимых процедур (например, настраиваемые поля с несколькими значениями). |
-6 |
Создание индекса невозможно, поскольку указанным критериям соответствует более одного настраиваемого поля. Это может произойти, если существует два и более настраиваемых полей с одним именем (в разных сущностях) и метод для индексирования настраиваемого поля по имени называется именем настраиваемого поля, а имя сущности отсутствует. |
Пример
В следующем примере используется один из двух предопределенных настраиваемых полей ресурса: тип затрат. Также существует два метода определения настраиваемых полей: по идентификатору или по имени. Далее приводятся примеры использования для обоих методов, но рекомендуется использовать идентификатор для определения настраиваемых полей.
Чтобы создать индекс для настраиваемого поля ресурса "Тип затрат" по имени, вызовите:
EXECMSP_Epm_CreateCustomFieldIndexByName'Cost Type', 'Resource'
Чтобы создать индекс для настраиваемого поля по идентификатору (см. предыдущий раздел о получении уникального идентификатора UID настраиваемого поля с помощью функции MFN_EpmGetAllCustomFieldsInformation):
EXECMSP_Epm_CreateCustomFieldIndexByUID'{000039B7-8BBE-4CEB-82C4-FA8C0C400284}'
"Присоединение" представлений и индексов
Можно оптимизировать создание отчетов с помощью вышеупомянутых методов путем применения индексов к настраиваемым полям и создания целевых/сокращенных представлений, как описано в предыдущих разделах. Однако следует отметить, что во время обновления базы данных отчетности индексы и настраиваемые представления, использующие настраиваемые поля, станут недействительными.
Это происходит, поскольку во время обновления все таблицы пула столбцов настраиваемых полей очищаются и все настраиваемые поля удаляются из базы данных отчетности. Во время процесса повторной синхронизации порядок распределения настраиваемых полей может измениться. Это значит, что значения настраиваемых полей могут быть сохранены в другом столбце или даже в другой таблице.
Например, можно представить, что существует два настраиваемых поля, созданных в следующем порядке: сначала CF1, а затем CF2, где CF1 и CF2 являются текстовыми настраиваемыми полями. CF1 получит столбец CFVal0 в таблице, а CF2 получит CFVal1. Таблица пула столбцов будет выглядеть следующим образом:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
В бюджете |
По графику |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
Отсутствует опорная линия |
Отсутствует опорная линия |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
Превышение бюджета |
По графику |
15 |
NULL |
При удалении CF1 таблица будет выглядеть следующим образом:
| EntityUID | CFVal0 | CFVal1 | CFVal2 | CFVal3 … |
|---|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
NULL |
По графику |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
NULL |
Отсутствует опорная линия |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
NULL |
По графику |
15 |
NULL |
Однако после обновления столбцы в пуле столбцов будут вновь наполнены ("с нуля", и CF1 больше не будет существовать, а CF2 теперь займет столбец CFVal0). Таблица будет выглядеть следующим образом:
| EntityUID | CFVal0 | CFVal1 | CFVal2 |
|---|---|---|---|
AF129A8C-DCB5-4FB0- 9E30-406458614A31 |
По графику |
15 |
NULL |
4D607B14-E40C-4549- 8E92-45A3A96D6892 |
По графику |
NULL |
NULL |
8496EA23-4B25-4DBE- B68A-755A27246842 |
По графику |
15 |
NULL |
Если ранее было создано настраиваемое представление или индекс, указывающий на CFVal1, после обновления базы данных отчетности вместо указателя на CF2 теперь он будет указывать на другое настраиваемое поле. В итоге в таких случаях индекс будет не в том столбце, в котором необходимо. Для решения этой проблемы при создании настраиваемых представлений или индексов для улучшения производительности отчетности также рекомендуется создание хранимой процедуры:
PROCEDURE MSP_OnRefreshCompleted();
При наличии данной хранимой процедуры она вызывается автоматически после успешного завершения обновления базы данных отчетности. Она повторно создаст индексы настраиваемых полей и/или настраиваемых представлений.
Пример
Если необходимо, чтобы изменения из двух вышеописанных примеров оставались действительными после обновления базы данных отчетности, нужно преобразовать два сценария в хранимую процедуру и назвать ее MSP_OnRefreshCompleted. Также необходимо сделать эту хранимую процедуру реентерабельной (это означает, что она будет правильно выполняться при многократном ее вызове в строке).
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
Теперь настраиваемое представление "MySampleView" и индекс настраиваемого поля в "Типе затрат" будут автоматически повторно применены после обновления базы данных отчетности.