Создание инструментов проектного офиса на базе Microsoft Project Server
Привет!
Сегодня мы расскажем о своем опыте использования Project Server для планирования и учета трудозатрат по проектам, о том, как мы его оперативно настроили под свои задачи и добились в итоге четкой картины: менеджеры видят, как работает компания, насколько успешно сдаются проекты, какова эффективность каждого отдельно взятого сотрудника за запрашиваемый период времени и т.д.
История и статистика использования Project Server в EastBanc Technologies
Мы используем Project Server c 2005 года для учета рабочего времени и планирования работ в рамках группы компаний, состоящей из двух офисов в разных часовых поясах — в России и США. Также учитываем в системе временно привлекаемых подрядчиков.
Примерная статистика:
Всего проектов в системе — 603,
Сотрудников — 216,
Табелей учета рабочего времени (они же time sheets, они же таймщиты) на проверку еженедельно — 140,
Задач в неделю 260.
Workflow выглядит так: каждый проект мы заводим в Project, включаем туда всех членов проектной команды, создаем план проекта (задачи, планируемые сроки и трудозатраты). Сотрудники регулярно заносят информацию о том, сколько рабочего времени было потрачено на задачи по проектам за каждый рабочий день — заполняют так называемый time sheet, табель учета рабочего времени.
Для сотрудника это выглядит просто как проставление цифр в таблице с назначенными на них задачами по дням. При необходимости, задачи в проекте они могут заводить самостоятельно. А поскольку речь идет о «бюрократической», рутинной для сотрудника процедуре, о которой несложно забыть, настроены автоматические email-напоминания.
На основе данных, отправленных сотрудниками, строится OLAP-куб, который позволяет менеджерам парой кликов конструировать отчеты различных форматов – по сути, отчет можно собрать под любые нужды, которые приходят в голову менеджерам, и анализировать информацию в удобном виде в любом разрезе.
Инструменты для реализации данного решения на Project Server
Что мы сделали, чтобы настроить Project Server под свои нужды, описанные выше?
Для построения отчетности в интересующих нас разрезах существует несколько технических возможностей, которые предоставляет Project Server 2010:
1. Использование одной из баз данных Project Server (о том, как конфигурировать здесь).
В конечном счете делается довольно простой запрос.
SELECT DISTINCT EpmResource.ResourceTimesheetManagerUID,
MSP_EpmResource.ResourceName
FROM MSP_EpmResource
INNER JOIN MSP_EpmResource AS EpmResource
ON MSP_EpmResource.ResourceUID
= EpmResource.ResourceTimesheetManagerUID
AND EpmResource.ResourceUID
<> EpmResource.ResourceTimesheetManagerUID
ORDER BY
Результат с помощью Pivot таблиц публикуется в Excel Services:
2. Использование OLAP-кубов, встроенных в Project Server (как конфигурировать тут).
В Excel выглядит так:
В нашем случае мы столкнулись с тем, что оба способа имеют серьезные недостатки.
С точки зрения структуры данных:
- Необходимо уметь фильтровать сотрудников по признакам «уволен» или «работает», т.к. за 10 лет истории компании база пользователей Project Server накопила довольно большой архив.
- В измерении времени нужно иметь возможность строить отчеты по реальным месяцам, а не по фискальным периодам, т.к. они ложатся в основу табелей учета рабочего времени для бухгалтерии и впоследствии загружаются 1С.
- В измерении сотрудника нужно понимать следующие вещи: a. Принадлежность к структурному подразделению компании: Россия, Америка или внешние организации-контрактеры; b. Табельный номер сотрудника в 1С; c. Адрес электронной почты для рассылки уведомлений о незаполненном вовремя отчете.
- Задачи проекта.
В публикации в Excel Services есть один, но важный недостаток: при большом объеме данных (см. количество проектов, сотрудников, задач в нашей системе выше) любое применение фильтра приводит к выполнению запроса на БД, а это — время на ожидание результатов и построение самого отчета.
У OLAP-кубов тоже есть нюанс: они разбиты на серию различных кубов с разбросанными по ним данными, например, задачи в одном, а time sheets в другом. В целом, кубы больше заточены на анализ портфеля, чем на Ad Hoc-работу.
Что мы сделали для обеспечения своих нужд:
За основу построения OLAP-куба мы взяли стандартный sql-запрос от MicroSoft’a к Project Server, немного доработав его под наши нужды. В частотности внесли изменения во временные периоды, т.к. нам важно иметь два измерения — по реальным неделям и по рабочим неделям, добавили электронный ящик сотрудника и признак «уволен».
SELECT R.StartDate, R.EndDate, R.PeriodName, R.TimeByDay, R.TimeByDay_DayOfWeek, R.ActualWorkBillable, R.ProjectName,
R.TS_LINE_CACHED_ASSIGN_NAME + ' (' + R.ProjectName + ')' AS TaskName, R.Status, R.ProjectAccount, R.Location, R.ResourceCompany, R.EmployeeID, R.TS_LINE_CLASS_NAME, R.Type, R.ProjectOwner,
R.Firstname, R.LastName, R.ResourceName, R.ModifiedDate, R.ResourceNameUID, DATEPART(yyyy, R.EndDate) AS PeriodYear, DATEPART(mm, R.EndDate)
AS PeriodMonth, DATEPART(ww, R.EndDate) AS PeriodWeek, DATEPART(yyyy, R.TimeByDay) AS RealPeriodYear, DATEPART(mm, R.TimeByDay) AS RealPeriodMonth, DATEPART(ww, R.TimeByDay) AS RealPeriodWeek, R.ProjectStatus,
CASE WHEN ISNULL(R.RES_TERMINATION_DATE, GETDATE()) >= GETDATE() THEN 0 ELSE 1 END AS IsFire, R.WRES_EMAIL AS Email
FROM (SELECT R_1.RES_HIRE_DATE, R_1.RES_TERMINATION_DATE, R_1.WRES_EMAIL, R_1.TODAY, R_1.IsActive, R_1.StartDate, R_1.EndDate, R_1.PeriodName, R_1.TimeByDay,
R_1.TimeByDay_DayOfWeek, R_1.ActualWorkBillable, R_1.ProjectName, R_1.[Task Name], R_1.Status, R_1.ProjectAccount, R_1.Location, R_1.ResourceCompany,
R_1.EmployeeID, R_1.TS_LINE_CACHED_ASSIGN_NAME, R_1.TS_LINE_CLASS_NAME, R_1.Type, R_1.TimesheetClass, R_1.ProjectOwner, R_1.Firstname,
R_1.LastName, R_1.ResourceName, R_1.ModifiedDate, R_1.ResourceNameUID, R_1.ResourceCC, R_1.CostCenter, R_1.ProjectType, CASE WHEN R_1.ProjectStatus IS NULL THEN 'Undefined' ELSE R_1.ProjectStatus END AS ProjectStatus, R_1.TS_LINE_UID,
DATEADD(day, - MIN(DATEDIFF(day, T.EFFECTIVE_DATE, R_1.TimeByDay)), R_1.TimeByDay) AS EFFECTIVE_DATE
FROM (SELECT res.RES_HIRE_DATE, res.RES_TERMINATION_DATE, res.WRES_EMAIL, GETDATE() AS TODAY, CASE WHEN (res.RES_TERMINATION_DATE > GETDATE() OR
res.RES_TERMINATION_DATE IS NULL) THEN 'Active' ELSE 'Inactive' END AS IsActive, tpr.WPRD_START_DATE AS StartDate,
tpr.WPRD_FINISH_DATE AS EndDate, tpr.WPRD_NAME AS PeriodName, ISNULL(tla.TS_ACT_START_DATE, tpr.WPRD_START_DATE)
AS TimeByDay, DATEPART(weekday, tla.TS_ACT_START_DATE) AS TimeByDay_DayOfWeek, tla.TS_ACT_VALUE / 60000 AS ActualWorkBillable,
CASE tcl.TS_LINE_CLASS_NAME WHEN 'Standard' THEN tp.PROJ_NAME ELSE tcl.TS_LINE_CLASS_NAME END AS ProjectName,
tsk.TASK_NAME AS [Task Name],
CASE t .TS_STATUS_ENUM WHEN 0 THEN 'InProgress' WHEN 1 THEN 'Submitted' WHEN 2 THEN 'Acceptable' WHEN 3 THEN 'Approved' WHEN
4 THEN 'Rejected' WHEN 5 THEN 'Pending' ELSE 'Missing' END AS Status,
CASE tcl.TS_LINE_CLASS_NAME WHEN 'Standard' THEN PP.ProjectAccount WHEN 'Administrative & General' THEN '0700-000' WHEN 'Bench time'
THEN '0702-000' WHEN 'Holidays' THEN '0500-000' WHEN 'Internal Projects' THEN '0701-000' WHEN 'Pre-sales & Overhead' THEN '0600-000' WHEN
'Recruitment (interview)' THEN '0703-000' WHEN 'Sales activity' THEN '0704-000' WHEN 'Vacation' THEN '0209-000' ELSE PP.ProjectAccount END
AS ProjectAccount, C.Location, RC.ResourceCompany, E.EmployeeID, tl.TS_LINE_CACHED_ASSIGN_NAME, tcl.TS_LINE_CLASS_NAME,
tcl.TS_LINE_CLASS_TYPE AS Type, tcltop.TS_LINE_CLASS_NAME AS TimesheetClass, pr_owner.RES_NAME AS ProjectOwner,
SUBSTRING(tr.RES_NAME, 0, CHARINDEX(' ', tr.RES_NAME)) AS Firstname, SUBSTRING(tr.RES_NAME, CHARINDEX(' ', tr.RES_NAME) + 1,
LEN(tr.RES_NAME)) AS LastName, SUBSTRING(tr.RES_NAME, CHARINDEX(' ', tr.RES_NAME) + 1, LEN(tr.RES_NAME))
+ ' ' + SUBSTRING(tr.RES_NAME, 0, CHARINDEX(' ', tr.RES_NAME)) AS ResourceName, t.MOD_DATE AS ModifiedDate,
tr.RES_UID AS ResourceNameUID, tr.ResourceCC, PCC.CostCenter, PT.ProjectType, PPS.ProjectStatus as ProjectStatus, tla.TS_LINE_UID
FROM pub.MSP_WEB_TIME_PERIODS AS tpr CROSS JOIN
(SELECT RES_UID, RES_NAME, CASE WHEN tr.ResourceCC = 4 OR
tr.ResourceCC = 6 THEN 'Only DC' ELSE CASE WHEN tr.ResourceCC = 1 THEN 'Only NSK' ELSE 'DC & NSK' END END AS ResourceCC
FROM (SELECT RES_UID, RES_NAME, SUM(CASE WHEN tr.CostCenter IS NULL
THEN 4 ELSE CASE WHEN tr.CostCenter = 'DC' THEN 2 ELSE 1 END END) AS ResourceCC
FROM (SELECT DISTINCT tr.RES_UID, tr.RES_NAME, PCC.CostCenter
FROM pub.MSP_RESOURCES AS tr INNER JOIN
pub.MSP_PROJECT_RESOURCES AS pr ON pr.RES_UID = tr.RES_UID INNER JOIN
pub.MSP_PROJECTS AS p ON p.PROJ_UID = pr.PROJ_UID LEFT OUTER JOIN
(SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS CostCenter
FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS pspCF ON
pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN
pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON
psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE
WHERE (pspCF.MD_PROP_NAME = 'Cost_Center')) AS PCC ON PCC.PROJ_UID = p.PROJ_UID
WHERE (tr.RES_TYPE = 2 OR tr.RES_TYPE = 102)) AS tr
GROUP BY RES_UID, RES_NAME) AS tr) AS tr INNER JOIN
pub.MSP_RESOURCES AS res ON res.RES_UID = tr.RES_UID LEFT OUTER JOIN
pub.MSP_TIMESHEETS AS t ON t.WPRD_UID = tpr.WPRD_UID AND t.RES_UID = tr.RES_UID LEFT OUTER JOIN
pub.MSP_TIMESHEET_LINES AS tl ON tl.TS_UID = t.TS_UID AND tl.TS_LINE_ACT_SUM_VALUE > 0 LEFT OUTER JOIN
pub.MSP_TIMESHEET_ACTUALS AS tla ON tla.TS_LINE_UID = tl.TS_LINE_UID LEFT OUTER JOIN
pub.MSP_TIMESHEET_CLASSES AS tcl ON tcl.TS_LINE_CLASS_UID = tl.TS_LINE_CLASS_UID LEFT OUTER JOIN
(SELECT TS_LINE_CLASS_UID, TS_LINE_CLASS_IS_EDITABLE, TS_LINE_CLASS_NAME, TS_LINE_CLASS_TYPE,
TS_LINE_CLASS_NEED_APPROVAL, TS_LINE_CLASS_ORGANIZATION, TS_LINE_CLASS_DESC, TS_LINE_CLASS_IS_DISABLED,
TS_LINE_CLASS_ALWAYS_DISPLAY, CREATED_DATE, MOD_DATE, CREATED_REV_COUNTER, MOD_REV_COUNTER
FROM pub.MSP_TIMESHEET_CLASSES
WHERE (TS_LINE_CLASS_TYPE = 0)) AS tcltop ON tcltop.TS_LINE_CLASS_UID = tl.TS_LINE_CLASS_UID LEFT OUTER JOIN
(SELECT PROJ_UID, PROJ_NAME, WRES_UID
FROM pub.MSP_PROJECTS
UNION
SELECT 'E38038FA-F8CA-47D1-BFD4-6B45B8462972' AS Expr1, 'Administrative' AS Expr2, NULL AS Expr3) AS tp ON
tp.PROJ_UID = tl.PROJ_UID LEFT OUTER JOIN
pub.MSP_TASKS AS tsk ON tsk.TASK_UID = tl.TASK_UID LEFT OUTER JOIN
pub.MSP_RESOURCES AS pr_owner ON pr_owner.RES_UID = tp.WRES_UID LEFT OUTER JOIN
(SELECT ppResCFV.RES_UID, ppResCFV.TEXT_VALUE AS EmployeeID
FROM pub.MSP_RES_CUSTOM_FIELD_VALUES AS ppResCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS ppCF ON ppResCFV.MD_PROP_UID = ppCF.MD_PROP_UID
WHERE (ppCF.MD_PROP_NAME = 'employeeID')) AS E ON tr.RES_UID = E.RES_UID LEFT OUTER JOIN
(SELECT ppResCFV.RES_UID, ppResCFV.TEXT_VALUE AS Location
FROM pub.MSP_RES_CUSTOM_FIELD_VALUES AS ppResCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS ppCF ON ppResCFV.MD_PROP_UID = ppCF.MD_PROP_UID
WHERE (ppCF.MD_PROP_NAME = 'co')) AS C ON tr.RES_UID = C.RES_UID LEFT OUTER JOIN
(SELECT pspPrjCFV.PROJ_UID, pspPrjCFV.TEXT_VALUE AS ProjectAccount
FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID
WHERE (pspCF.MD_PROP_NAME = 'Project Account')) AS PP ON PP.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN
(SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS CostCenter
FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN
pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE
WHERE (pspCF.MD_PROP_NAME = 'Cost_Center')) AS PCC ON PCC.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN
(SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS ProjectStatus
FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN
pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE
WHERE (pspCF.MD_PROP_NAME = 'Project Status')) AS PPS ON PPS.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN
(SELECT pspPrjCFV.PROJ_UID, psLV.LT_VALUE_TEXT AS ProjectType
FROM pub.MSP_PROJ_CUSTOM_FIELD_VALUES AS pspPrjCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS pspCF ON pspPrjCFV.MD_PROP_UID = pspCF.MD_PROP_UID LEFT OUTER JOIN
pub.MSP_LOOKUP_TABLE_VALUES AS psLV ON psLV.LT_STRUCT_UID = pspPrjCFV.CODE_VALUE
WHERE (pspCF.MD_PROP_NAME = 'ProjectType')) AS PT ON PT.PROJ_UID = tp.PROJ_UID LEFT OUTER JOIN
(SELECT ppResCFV.RES_UID, ppResCFV.TEXT_VALUE AS ResourceCompany
FROM pub.MSP_RES_CUSTOM_FIELD_VALUES AS ppResCFV INNER JOIN
pub.MSP_CUSTOM_FIELDS AS ppCF ON ppResCFV.MD_PROP_UID = ppCF.MD_PROP_UID
WHERE (ppCF.MD_PROP_NAME = 'Resource Company')) AS RC ON tr.RES_UID = RC.RES_UID
WHERE (tpr.WPRD_START_DATE < GETDATE()) AND (tpr.WPRD_START_DATE >= '12.01.2008')) AS R_1 LEFT OUTER JOIN
CUSTOM_RES_PROJ_ASSIGNMENTS AS T ON T.RES_NAME = R_1.ResourceName AND T.PROJ_NAME = R_1.ProjectName AND
(T.EFFECTIVE_DATE IS NULL OR
DATEDIFF(day, T.EFFECTIVE_DATE, R_1.TimeByDay) >= 0)
GROUP BY R_1.RES_HIRE_DATE, R_1.RES_TERMINATION_DATE, R_1.WRES_EMAIL, R_1.TODAY, R_1.IsActive, R_1.StartDate, R_1.EndDate, R_1.PeriodName, R_1.TimeByDay,
R_1.TimeByDay_DayOfWeek, R_1.ActualWorkBillable, R_1.ProjectName, R_1.[Task Name], R_1.Status, R_1.ProjectAccount, R_1.Location, R_1.ResourceCompany,
R_1.EmployeeID, R_1.TS_LINE_CLASS_NAME, R_1.Type, R_1.TimesheetClass, R_1.ProjectOwner, R_1.Firstname, R_1.LastName, R_1.ResourceName,
R_1.ModifiedDate, R_1.ResourceNameUID, R_1.ResourceCC, R_1.CostCenter, R_1.ProjectStatus, R_1.ProjectType, R_1.TS_LINE_UID,
R_1.TS_LINE_CACHED_ASSIGN_NAME) AS R LEFT OUTER JOIN
CUSTOM_RES_PROJ_ASSIGNMENTS AS T ON T.RES_NAME = R.ResourceName AND T.PROJ_NAME = R.ProjectName AND (R.EFFECTIVE_DATE IS NOT NULL AND
T.EFFECTIVE_DATE = R.EFFECTIVE_DATE OR
R.EFFECTIVE_DATE IS NULL AND T.EFFECTIVE_DATE IS NULL)
Из-за удалённости сервера нам пришлось сделать небольшой SSIS пакет для «перекачки» данных во временную таблицу. После этого мы сделали полученную таблицу источником данных для нашего куба, добавили необходимые измерения и меру.
Для обеспечения актуальности создали sql-джоб для получения данных, а затем и процессинга куба.
Практика показала — сотрудникам свойственно забывать, что отчеты о потраченном времени нужно заполнять еженедельно. Для это мы написали небольшой SSIS-пакет, который выполняет MDX-запрос к кубу, определяет «забывчивых» сотрудников и отправляет письмо с просьбой заполнить таймщит. При этом если сегодня пятница, то проверяется текущая неделя, а если понедельник, вторник или среда, то прошлая.
Отдельно остановимся на возникшей с данным пакетом проблемой. Она заключалась в том, что фактически sql-джоб, выполняющий данный пакет «живет» по часовому поясу GMT+6. Нашим американским коллегам необходимо отправлять напоминание в их пятницу в 17:00, а в Новосибирске это уже суббота 5:00 (либо 4:00 в зависимости от перевода часов в США), и так как рабочая неделя в кубе начинается с субботы, всем коллегам из США приходило письмо, что отчет не заполнен. Решение данной проблемы лежит на поверхности и заключается в добавлении дополнительного условия проверке текущего дня недели.
Результат
Вот что у нас получилось, примеры некоторых отчетов:
1. Отчет за период по всем сотрудникам. Еженедельно менеджеры просматривают табели всех сотрудников – контролируют сам факт заполнения и правильность разноски трудозатрат по проектам.
2. Отчет по проекту. После окончания проекта менеджеры могут проанализировать, как прошла реализация: какие задачи фактически были сделаны, сколько ресурсов на них потрачено, насколько это соответствует нашим изначальным планам на проект. Это же можно контролировать и по ходу проекта.
3. Отчет по сотруднику. В любой момент можно проанализировать деятельность отдельно взятого сотрудника по проектам за интересующий период.
В результате:
- Все сотрудники EastBanc заполняют минимум по 40 часов, так как у нас 40-часовая неделя. Заполняют вовремя!
- Сотрудники заполняют таймщиты правильно: отпуска, больничные, праздники, проекты, на которых они работают, в том числе и внутренние, — всё разносится по нужным графам.
- Задачи на проектах соответствуют плану, т.к. они достаточно детализированы. Нет задач больше 16 часов, чтобы вовремя спохватиться, когда что-то идет не так.
- По завершению каждого проекта менеджеры получают подробнейший анализ.
- Каждый менеджер следит за своими проектами. За картиной в целом следит менеджер проектного офиса.
Сейчас мы имеем очень удобный enterprise-инструмент, которым вся компания пользуется каждый день. Сделать все удалось с помощью улучшения существующих стандартных средств Project Server за очень ограниченное время, т.к. стояла задача, не изощряясь и не изобретая велосипед, быстро решить проблему учета трудозатрат.
Автор: eastbanctech