Problem!
Project resource assignments are tracked in an Excel Spreadsheet, the following data as outlined in the table below is tracked.
| Person Name | Title | Role | Status | Allocation | Project Name | Project Number | Start Date | End Date | Client | Project Type | Department | Employee Number | Product Skill | Skillset |
| Person A | Business Analyst | Business Analyst | Full Time | 100% | Web Implementation | P002 | 2/15/2010 | 4/2/2010 | Client A | Client | Business Services | 00001 | ||
| Person B | Developer | Developer | Contractor | 20% | New Corporate Web Site | P004 | 4/5/2010 | 8/6/2010 | Marketing | Internal | Technical Delivery | 00002 | Java, Jboss | C, Java |
Utilizing the pivotal table feature of Excel, utilization can be tracked. To show the utilization an excel formula is used to display the utilization percentage, 100% meaningfully utilized during that period. See below the pivotal view and on the right side the allocation by week.
| Total Utilization for Weeks Viewed | Week Starting On | ||||||||||||
| Person Name | Title | Role | Project Name | Project Number | Project Type | Client | Allocation Start Date | Allocation End Date | 2/15/2010 | 2/22/2010 | 3/1/2010 | ||
| Person A | Business Analyst | Business Analyst | Web Implementation | P002 | Client A | Client A | 2/15/2010 | 4/2/2010 | 100% | 100% | 100% | 100% | |
| Person B | Developer | Developer | New Corporate Web Site | P004 | Internal | Marketing | 4/5/2010 | 8/6/2010 | 0% | 0% | 0% | 0% |
This is a simple way of doing project and portfolio management, some simple reporting can be done using Excel, more sophisticated reports is better done using Crystal Reports or any open source report designer. To really use the power of Crystal Reports the data as shown above, needs to be moved to a database.
The Solution
Mysql was chosen as the db, a db table was setup named host_sheet, the table structure followed the columns of the excel spreadsheet, as shown below.
Table host_sheet
—————-
host_sheet_id INT
host_sheet_person VARCHAR
host_sheet_title VARCHAR
host_sheet_role VARCHAR
host_sheet_start_date DATETIME
host_sheet_end_date DATETIME
host_sheet_allocation DOUBLE
host_sheet_project_name VARCHAR
host_sheet_project_number VARCHAR
host_sheet_status VARCHAR
host_sheet_client VARCHAR
host_sheet_project_type VARCHAR
host_sheet_department VARCHAR
host_sheet_employee VARCHAR
host_sheet_product_skill VARCHAR
host_sheet_request_skillset VARCHAR
To transfer the data from Excel, the Pentaho Data Integration – ETL (extract, transform, load) tool was used, a transformation was created and scheduling on automatic updates.
This was the first step to move data from Excel to a MySQL database, the next step was to take the records in the database and break them into a record by a day. As you can see at the top, showing the Excel spreadsheet data, only the start and end date of an assignment are known. For the flexibility of reporting, the data had to be broken down to a record by a day.
To achieve a record by day, an MYSQL stored procedure was created, it reads the start date loops around and add record tills it reaches the end date. Below shows the store procedure
delimiter //
create procedure tph ()
begin
declare begin_date datetime;
declare end_date datetime;
declare start_date datetime;
declare total_days int;
declare allocation decimal(3,2);
declare hours decimal(25,6);
declare cur_date datetime;
declare role nvarchar(256);
declare title nvarchar(256);
declare project_number nvarchar(30);
declare status nvarchar(256);
declare client nvarchar(256);
declare project_type nvarchar(256);
declare department nvarchar(256);
declare employee nvarchar(256);
declare product_skill nvarchar(256);
declare skillset nvarchar(256);
declare project_name nvarchar(256);
declare person nvarchar(256);
declare daysinweek int default 5;
declare hoursinweek int default 7.5;
declare no_more_records int default 0;
declare td cursor for
select
host_sheet_person,
host_sheet_title,
host_sheet_role,
host_sheet_start_date,
host_sheet_end_date,
host_sheet_allocation,
host_sheet_project_name,
host_sheet_project_number,
host_sheet_status,
host_sheet_client,
host_sheet_project_type,
host_sheet_department,
host_sheet_employee,
host_sheet_product_skill,
host_sheet_request_skillset,
datediff(host_sheet_end_date, host_sheet_start_date)+1
from
host_sheet
where ( 2010-01-01 <= host_sheet_start_date or 2011-12-31 >= host_sheet_end_date) and host_sheet_project_type = Client
order by host_sheet_start_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_records = 1;
create table tp_data (
td_date datetime,
td_hours decimal(25,9),
td_person nvarchar(256),
td_title nvarchar(256),
td_role nvarchar(256),
td_start_date datetime,
td_end_date datetime,
td_allocation decimal(3,2),
td_project_name nvarchar(256),
td_project_number nvarchar(30),
td_status nvarchar(256),
td_client nvarchar(256),
td_project_type nvarchar(256),
td_department nvarchar(256),
td_employee nvarchar(256),
td_product_skill nvarchar(256),
td_skillset nvarchar(256));
open td;
FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
set allocation = allocation/daysinweek;
REPEAT
set cur_date = start_date;
WHILE total_days > 0 DO
set hours = allocation * hoursinweek;
if DAYNAME(cur_date) in ( Monday , Tuesday , Wednesday , Thursday , Friday ) then
INSERT INTO tp_data (td_date, td_hours, td_person, td_title, td_role, td_start_date, td_end_date, td_allocation, td_project_name, td_project_number, td_status, td_client, td_project_type, td_department, td_employee, td_product_skill, td_skillset) VALUES
(cur_date, hours, person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset);
end if;
set cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY);
set total_days = total_days – 1;
END WHILE;
FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
set allocation = allocation/daysinweek;
UNTIL no_more_records = 1
END REPEAT;
close td;
END
//
delimiter ;
To create the store procedure in your database, copy the following into MySQL Workbench or any other MySQL tool and execute.
use staffingtool;
drop database if exists tp_data;
drop procedure if exists tph;
delimiter //
create procedure tph ()
begin
declare begin_date datetime;
declare end_date datetime;
declare start_date datetime;
declare total_days int;
declare allocation decimal(3,2);
declare hours decimal(25,6);
declare cur_date datetime;
declare role nvarchar(256);
declare title nvarchar(256);
declare project_number nvarchar(30);
declare status nvarchar(256);
declare client nvarchar(256);
declare project_type nvarchar(256);
declare department nvarchar(256);
declare employee nvarchar(256);
declare product_skill nvarchar(256);
declare skillset nvarchar(256);
declare project_name nvarchar(256);
declare person nvarchar(256);
declare daysinweek int default 5;
declare hoursinweek int default 7.5;
declare no_more_records int default 0;
declare td cursor for
select
host_sheet_person,
host_sheet_title,
host_sheet_role,
host_sheet_start_date,
host_sheet_end_date,
host_sheet_allocation,
host_sheet_project_name,
host_sheet_project_number,
host_sheet_status,
host_sheet_client,
host_sheet_project_type,
host_sheet_department,
host_sheet_employee,
host_sheet_product_skill,
host_sheet_request_skillset,
datediff(host_sheet_end_date, host_sheet_start_date)+1
from
host_sheet
where ( 2010-01-01 <= host_sheet_start_date or 2011-12-31 >= host_sheet_end_date) and host_sheet_project_type = Client
order by host_sheet_start_date;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_records = 1;
create table tp_data (
td_date datetime,
td_hours decimal(25,9),
td_person nvarchar(256),
td_title nvarchar(256),
td_role nvarchar(256),
td_start_date datetime,
td_end_date datetime,
td_allocation decimal(3,2),
td_project_name nvarchar(256),
td_project_number nvarchar(30),
td_status nvarchar(256),
td_client nvarchar(256),
td_project_type nvarchar(256),
td_department nvarchar(256),
td_employee nvarchar(256),
td_product_skill nvarchar(256),
td_skillset nvarchar(256));
open td;
FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
set allocation = allocation/daysinweek;
REPEAT
set cur_date = start_date;
WHILE total_days > 0 DO
set hours = allocation * hoursinweek;
if DAYNAME(cur_date) in ( Monday , Tuesday , Wednesday , Thursday , Friday ) then
INSERT INTO tp_data (td_date, td_hours, td_person, td_title, td_role, td_start_date, td_end_date, td_allocation, td_project_name, td_project_number, td_status, td_client, td_project_type, td_department, td_employee, td_product_skill, td_skillset) VALUES
(cur_date, hours, person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset);
end if;
set cur_date = DATE_ADD(cur_date, INTERVAL 1 DAY);
set total_days = total_days – 1;
END WHILE;
FETCH from td INTO person, title, role, start_date, end_date, allocation, project_name, project_number, status, client, project_type, department, employee, product_skill, skillset, total_days;
set allocation = allocation/daysinweek;
UNTIL no_more_records = 1
END REPEAT;
close td;
END
//
delimiter ;
When it is loaded – the store procedure can be called by issue this command.
call tph();
As the output of the stored procedure, a new database table tp_data is created – the structure is as shown below.
td_date DATETIME
td_hours DECIMAL
td_person VARCHAR
td_title VARCHAR
td_role VARCHAR
td_start_date DATETIME
td_end_date DATETIME
td_allocation DECIMAL
td_project_name VARCHAR
td_project_number VARCHAR
td_status VARCHAR
td_client VARCHAR
td_project_type VARCHAR
td_department VARCHAR
td_employee VARCHAR
td_product_skill VARCHAR
td_skillset VARCHAR
There is one record by working day, weekends are skipped. The new data set allows accurate reporting e.g. utilization by role, utilization by project, resource availability by day or week or month.
There is an endless list of reports that can be created.
Leave a Reply