Dimensional Model for Project management software

[fabio.salles]

Hello all!


I am tasked in building a data mart for project management software (namely the IBM Application Lifecycle Management software Jazz Server.) I have never designed any DM on this kind of data and I'm a bit at loss with dimensions. Like:

- Is project a dimension? Project gathers info like customer, leader, type, an assortment of dates etc. It sounds like a dimension, but it changes all too often to be so.
- There is a sleigh of people working on it, most of them as an footman - system analysts, programmers, documentation etc. Each one logs work into the project through an workitem, which in its turn has some dates, a couple of descriptions, a text log and worked hours log. Again, workitem feels like a dimension and do not change as much as the project.
- There are several business process in the evolution of the project: the project is created, evolves and then closes (lifecycle); inside the project workitens are created, stay open for a while and then are concluded; while an workiten is active the employee logs time to it, maybe changing previous time logged (as when he/she forgets about an office leave and log a full day instead of half a day or when it leaves all the logging to be done at the end of the week.)
- My first star is worked hours by employee by date by workitem by project which press even more for making workitem and project dimensions (but it looks plain wrong.) Worst of all, workitem lives inside projects so a full blown dimension would be workitem with an hiearchy from project down to workitem wih all their dates!

Have any of you ever met such need, such dimensional model? I did a little research into this subject but it proved awkward - most of google results are about management of a DW project and not about a DW on project management solutions.

Thanks a lot for you attention and best regards,


Fábio de Salles
Serpro/Brazil

[sachij3u]

A typical set of dimensions could be
--Party Dim (You can have various role types as Employees, manager, etc)
--Calendar Dim (Juts the date)
--Time Dim (Hr to minutes , you may not want seconds)
--TaskItems Dim (Different types of task/workitems)
--Status Dim (It can have different project statuses like created, initiated, scheduled, In progress closed,
--Version Dim (Project version - Draft, Initial, Approved, Closed)

Scheduled_ACtual_Effort_Fact (In your case its the project) - You can choose to have it accumulating snapshot and update the % complete. You can also choose to have this as periodic snapshot.snapshot
Party Dim
TaskItems Dim
Scheduled Start Time Calendar Dim
Scheduled End Time Calendar Dim
Time Dim
Status Dim
Version Dim
Scheduled Effort (Hrs)
Actual Effort
Complete %

You can create more fact tables depending on what you would like to measure. Hope this helps.