Friday, April 20, 2007

Microsoft Project Server and Analytics Service

Microsoft Project Server provides the built-in integration with their Microsoft SQL analytics services. In Microsoft Project Server 2003, they call their solution Portfolio Analyzer.

Here are some of my findings:
  1. Microsoft Portfolio Analyzer provides the built-in ETL process to push their project data from transactional schema to multi-dimensional schema. The process first put the project data into dimension and fact tables. Then the 2nd process pushes the star schema data into their OLAP cubes.
  2. Their ETL process can be extended so you can have additional attributes in the dimension and the fact tables. No sure how this is accomplished. I guess some kind of user hooks are provided.
  3. The MSP_ tables are the same as those in the MSP Project database so a Microsoft Project Server license is not required. However, the MSP_VIEW tables are also used to create the MSP_CUBE tables, which are their staging tables.
  4. The ETL process can take hours to run.
  5. The Microsoft Project client is expected to be used to enter the task and progress.
  6. Project and Resource categorization are supported. They do not use the text1..text10 attribute columns but use "Enterprise Outline code". Outline code is a very powerful feature provided by Microsoft Project. "It allows you to show a hierarchy of tasks that is different from WBS codes or outline numbers. Outline codes can be assigned to either tasks or resources.", according to MSP Help.
The metrics supported in Microsoft Project Portfolio Analyzer:
  • Baseline and Actual Work
  • Actual Overtime Work
  • Baseline and Actual Material Work
  • Baseline and Actual Cost
  • Availability
Resource Availability and Capacity is captured down to the day level with the total time (hour) available for that day. A very typical warehouse design. Under this design, you do not know whether the resource is available in the morning or the afternoon. You will know how many hours the resource is available. The resource does not have to be a single person. Just as resources in the Microsoft Project, you can assign the max. unit as 200% to indicate the 2 headcount. I think that they inherit the concept in Portfolio Analyzer.

Resource Booking and Assignment is also captured down to the day level. You can have hard booking or soft booking. This is knid of standard in all resource management solution now.
Post a Comment