Export (0) Print
Expand All

Performance and capacity planning best practices

Project Server 2007

Updated: January 10, 2011

Updated: 2011-01-10

This article contains two white papers:

  • Performance testing white paper

  • Performance and capacity planning best practices white paper

Performance testing white paper

This white paper contains a description of an actual performance testing lab for Microsoft Office Project Server 2007.

The first chapter (“Running a Project Server 2007 Performance Test Lab”), describes how to run performance tests against a Office Project Server 2007 deployment by using Visual Studio Team System 2008 and a set of community-based tools built for the purpose.

The second chapter (“Test Environment”), documents the specifics of the lab environment that was used for the tests we ran for collecting data for this white paper.

The third chapter (“Test Results”), describes in detail the test scenarios that were conducted, and it summarizes the data collected for every scenario.

The paper is available from the Microsoft Download Center.

Download size: 4 MB

Performance and capacity planning best practices white paper

The purpose of this guide is to extend the Microsoft Office Project Server 2007 Performance Testing Lab white paper by providing best practices and recommendations.

This paper examines a wide array of system objects, including:

  • Platform-related objects — including the farm, shared service providers, application pools, web applications, databases, disks, network, memory, CPU, logs, and performance counters

  • Data-related objects — including projects, tasks, assignments, resources, custom fields, and security

  • User-related objects — including localization, workloads, queue job processor threads, and interface feeds

For each system object, the following information is provided:

  • Definition — The definition of the object

  • Guidelines for acceptable performance — The best practices and supporting statements

  • Calculation factors — The performance and scalability calculations to support the best practices

  • Scope of impact — A list of objects affected in the system when best practices are not implemented

The paper is available from the Microsoft Download Center.

Download size: 1.0 MB

Project system Health Report script

The following script from the "best practices" white paper is provided here to makes it easy for you to copy and paste this query into SQL Server query analyzer for running a report on system health and to determine scalar depth across the data profile.

/*
-- **************************************************** SUMMARY NOTES ****************************************************** --
PROJECT SYSTEM HEALTH REPORT V2.0
    Extracts a series of data points from Draft & Reporting databases to qualify the data in a Project system. 
    Run the report against your Draft database and save the results to file, send the resulting .CSV file with 
    Column Headers to Microsoft (We are working on making     the data analysis public as a series of reports to 
    remove the need to send data to Microsoft)

CUSTOMIZATION
    1. Set local variable @DRAFT_DB_NAME with your DRAFT database name.
    1. Set local variable @PUBLISHED_DB_NAME with your PUBLISHED database name.
    3. Set local variable @REPORTING_DB_NAME with your REPORTING database name (and SERVER if on a separate server).
    4. Set local variable @ERADICATE_PROJ_NAME to 1 if require masking project name for privacy information

PRIVACY/INFORMATION PROTECTION NOTES
    1. Report contains data that can be used to quantify the amount of project work in the org, 
    please review this for sensitivity prior to sending the data to Microsoft
CHANGELOG
    01/05/2009 pmc: Prepared for Customer Release, some p
    01/05/2009 pmc: Changes made for Project Server 14 Alpha (Project Server 2010)
    05/11/2009 EPMGP: Added values to incorporate Quality Assurance data points for the Health Check Tool
*/
-- ************************************************ BEGIN DECLARE VARIABLES ************************************************ --

DECLARE @SQL_SELECT nvarchar(max);        -- Variable to contain SELECT statements
DECLARE @SQL_JOINS nvarchar(max);        -- Variable to contain JOIN statements
DECLARE @PS_TASK_START varchar(20);        -- Variable to swith column name between PS2007 and PS2010 implementation
DECLARE @PS_TASK_FINISH varchar(20);        -- Variable to swith column name between PS2007 and PS2010 implementation
DECLARE @DRAFT_DB_NAME varchar(100);        -- Variable to set the DRAFT database name
DECLARE @PUBLISHED_DB_NAME varchar(100);    -- Variable to set the PUBLISHED database name
DECLARE @REPORTING_DB_NAME varchar(100);    -- Variable to set the REPORTING database name
DECLARE @ERADICATE_PROJ_NAME CHAR(1);        -- Variable to switch between displaying project name or masking the value

----------------------------------------------------  END DECLARE VARIABLES  --------------------------------------------------

-- *********************************************** BEGIN SET VARIABLE VALUES *********************************************** --

SET @DRAFT_DB_NAME = 'BLANK_Draft';        -- Set local variable @DRAFT_DB_NAME with your DRAFT database name
SET @PUBLISHED_DB_NAME = 'BLANK_Published';    -- Set local variable @PUBLISHED_DB_NAME with your PUBLISHED database name
SET @REPORTING_DB_NAME = 'BLANK_Reporting';    -- Set local variable @REPORTING_DB_NAME with your REPORTING database name
SET @ERADICATE_PROJ_NAME = 0;            -- Set local variable to 1 to mask project name for privacy or default to 0
SET @PS_TASK_START = 'TASK_START_DATE';        -- Set local variable to TASK_START_DATE for PS2007 and REM PS2010
SET @PS_TASK_FINISH = 'TASK_FINISH_DATE';    -- Set local variable to TASK_FINISH_DATE for PS2007 and REM PS2010
--SET @PS_TASK_START = 'TASK_SCHED_START';    -- Set local variable to TASK_SCHED_START for PS2010 and REM PS2007
--SET @PS_TASK_FINISH = 'TASK_SCHED_FINISH';    -- Set local variable to TASK_SCHED_FINISH for PS2010 and REM PS2007

--------------------------------------------------  END SET VARIABLE VALUES  ---------------------------------------------------

SET @SQL_SELECT = N'use [' + @DRAFT_DB_NAME + N']

SET NOCOUNT ON;

WITH ProjHierarchy
AS (SELECT Proj.PARENT_PROJ_UID, 1 AS DEPTH
    FROM dbo.MSP_PROJ_HIERARCHIES AS Proj
    UNION ALL
    SELECT Proj.PARENT_PROJ_UID, Hier.DEPTH+1
    FROM dbo.MSP_PROJ_HIERARCHIES AS Proj
    INNER JOIN ProjHierarchy AS Hier
    ON Proj.CHILD_PROJ_UID = Hier.PARENT_PROJ_UID)
SELECT 
        Proj.PROJ_UID AS [PRJ: PROJ ID]
       ,CASE ' + @ERADICATE_PROJ_NAME + N' WHEN 0 THEN Proj.PROJ_NAME ELSE N''*** NAME ERADICATED ***''END AS [PRJ: NAME]
       ,CASE(Proj.PROJ_TYPE) 
            WHEN 0 THEN N''Project'' 
            WHEN 1 THEN N''Template'' 
            WHEN 2 THEN N''Global''
            WHEN 3 THEN N''Resource Global''
            WHEN 4 THEN N''LightWeightProject''
            WHEN 5 THEN N''Inserted Project''
            WHEN 6 THEN N''Master Project''
            WHEN 100 THEN N''New Project''
            WHEN 101 THEN N''New Template''
            WHEN 102 THEN N''New Global''
            WHEN 103 THEN N''New Resource Global''
            WHEN 1000 THEN N''Inactive Project''
            WHEN 1001 THEN N''Inactive Template''
            WHEN 1002 THEN N''Inactive Global''
            WHEN -1 THEN N''Void''
            ELSE ''Unknown'' 
        END AS [PRJ: TYPE]
       ,ISNULL(CollabRes.RES_NAME, Proj.PROJ_PROP_AUTHOR) AS [PRJ: AUTHOR]
       ,ISNULL(CONVERT(VARCHAR(10), Proj.CREATED_DATE,111), ''0000/00/00'') AS [PRJ: CREATED]
       ,ISNULL(CONVERT(VARCHAR(10), Proj.PROJ_INFO_START_DATE,111), ''0000/00/00'') AS [PRJ: START_DATE]
       ,ISNULL(CONVERT(VARCHAR(10), Proj.PROJ_INFO_FINISH_DATE,111), ''0000/00/00'') AS [PRJ: FINISH_DATE]
       ,DATEDIFF(mm, Proj.PROJ_INFO_START_DATE,Proj.PROJ_INFO_FINISH_DATE) AS [PRJ: DURATION (Mnth)]
       ,ISNULL(CONVERT(VARCHAR(10), Proj.MOD_DATE,111), ''0000/00/00'') AS [Project Modified]
       ,ISNULL(CONVERT(VARCHAR(10), Proj.PROJ_INFO_STATUS_DATE,111), ''0000/00/00'') AS [PRJ: STATUSED]
       ,ISNULL(Task.TASK_PCT_COMP, 0) AS [PRJ: %COMPLETE]
       ,CASE 
            ISNULL(Proj.PROJ_SESSION_UID,N''00000000-0000-0000-0000-000000000000'') 
            WHEN N''00000000-0000-0000-0000-000000000000'' 
            THEN 0 ELSE DATEDIFF(d,Proj.PROJ_CHECKOUTDATE,getdate()) 
        END AS [PRJ: CHECKED_OUT(Days)]
       ,CAST(Task.TASK_ACT_WORK / 60000 AS DECIMAL(10,2)) AS [PRJ: TOTAL_ACTUAL_WORK(Hrs)]
       ,CAST(Task.TASK_REM_WORK / 60000 AS DECIMAL(10,2)) AS [PRJ: TOTAL_REMAINING_WORK(Hrs)]
       ,ISNULL(LinkData.[Cross Project Links],0) AS [PRJ: #_CROSS_PROJECT_LINKS]
       ,ISNULL(HierSummaryData.[Number of SubProjects],0) AS [PRJ: #_SUB_PROJECTS]
       ,ISNULL(HierSummaryData.[SubProject Depth],0) AS [PRJ: SUB_PROJECT_DEPTH]
       ,ISNULL(AllTaskData.[Task Count],0) AS [TSK: TOTAL_TASK_COUNT]
       ,ISNULL(RecentTasks.[Recent Changes],0) AS [TSK: TOTAL_RECENT_CHANGE]
       ,ISNULL(CONVERT(DECIMAL(18,0), CONVERT(FLOAT, RecentTasks.[Recent Changes])/CONVERT(float, AllTaskData.[Task Count])*100), 0) AS [TSK: %_CHANGED]
       ,ISNULL(SRA.[SRA Count], 0) AS [TSK: SRA_COUNT]
       ,ISNULL(LeafTaskData.[Leaf Task Count],0) AS [TSK: LEAF_COUNT]
       ,ISNULL(LeafTaskData.[Max Leaf Calendar Duration (Days)], 0) AS [TSK: MAX_LEAF_TASK_CALENDAR_DURATION(Days)]
       ,ISNULL(LeafTaskData.[Average Leaf Calendar Duration (Days)], 0) AS [TSK: AVERAGE_LEAF_TASK_CALENDAR_DURATION(Days)]
       ,ISNULL(LeafTaskData.[Total Leaf Task Ignoring ResCalendar], 0) AS [TSK: TOTAL_LEAF_TASK_IGNORING_RES_CALENDAR]
       ,ISNULL(LinkData.[Total Number of Links],0) AS [TSK: TOTAL_NUMBER_OF_LINKS]
       ,ISNULL(AssnDataAggregated.[Average Resources on Assigned Tasks],0) AS [ASN: AVG_RESOURCES_ON_ASSIGNMENTS]
       ,ISNULL(AssnDataAggregated.[Total Number of Assignments],0) AS [ASN: #_TOTAL_ASSIGNMENTS]
       ,ISNULL(AssnDataAggregated.[Number of Tasks with Assignments],0) AS [ASN: TOTAL_TASK_WITH_ASSIGNMENTS]
       ,ISNULL(RealData.[Actual Count],0) AS [ASN: TOTAL_ASNBYDAY_COUNT]
       ,ISNULL(ProjCF.[Number of Project Custom Field Values],0) AS [CF: PRJ_CF_VALUES]
       ,ISNULL(ProjLCF.[Number of Local Custom Field Definitions],0) AS [CF: PRJ_LOCAL_CF_DEFINITIONS]
       ,ISNULL(TaskCFAggregated.[Total Task Custom Fields],0) AS [CF: TOTAL_TASK_CFs]
       ,ISNULL(TaskCFAggregated.[Max Task Custom Fields],0) AS [CF: MAX_TASK_CFs]
       ,ISNULL(TaskCFAggregated.[Average Task Custom Fields],0) AS [CF: AVG_TASK_CFs]
       ,ISNULL(TaskCFAggregated.[Number of Tasks with Custom Fields],0) AS [CF: #_TASK_WITH_ CFs]
       ,ISNULL(AssnCFAggregated.[Total Assn Custom Fields],0) AS [CF: TOTAL_ASN_CFs]
       ,ISNULL(AssnCFAggregated.[Max Assn Custom Fields],0) AS [CF: MAX_ASN_CFs]
       ,ISNULL(AssnCFAggregated.[Average Assn Custom Fields],0) AS [CF: AVG_ASN_CFs]
       ,ISNULL(AssnCFAggregated.[Number of Assn with Custom Fields],0) AS [ASN: #_ASN_WITH_CFs]
       ,ISNULL(TaskBaseAggregated.[Number of Baselines],0) AS [BSL: #_BASELINES]
       ,ISNULL(TaskBaseAggregated.[Total Task Baseline Rows],0) AS [BSL: TOTAL_TSK_BASELINE]
       ,ISNULL(AssnBaseAggregated.[Total Assn Baseline Rows],0) AS [BSL: TOTAL_ASN_BASELINE]  
       ,ISNULL(ResBaseAggregated.[Total Resource Baseline Rows],0) AS [BSL: TOTAL_RES_BASELINE]
       ,ISNULL(Resources.[Total Resources],0) AS [RES: TOTAL_TEAM]
       ,ISNULL(Resources.[Enterprise Resources],0) AS [RES: TOTAL_ENT_TEAM]
       ,ISNULL(ActiveResAggregated.[Active Resources],0) AS [RES: TOTAL_ACTIVE]
       ,ISNULL(CONVERT(VARCHAR(10), Proj.WPROJ_LAST_PUB,111), ''0000/00/00'') AS [PWS: PUBLISHED]
       ,ISNULL(Collab.WPROJ_STS_SUBWEB_NAME, 0) AS [PWS: NAME] 
       ,ISNULL(Collab.PROJ_TOTAL_DOC_COUNT, 0) AS [PWS: DOC_COUNT]
       ,ISNULL(Collab.PROJ_ACTIVE_ISSUE_COUNT, 0) AS [PWS: ISSUE_COUNT]
       ,ISNULL(Collab.PROJ_ACTIVE_RISK_COUNT, 0) AS [PWS: RISK_COUNT]
       ,ISNULL(CONVERT(DECIMAL(18,2),(CAST(AssnDataAggregated.[Total Number of Assignments] AS REAL) / AllTaskData.[Task Count])),0) AS [RATIO: ASN:TSK]
       ,ISNULL(CONVERT(DECIMAL(18,2),(CAST(AssnCFAggregated.[Total Assn Custom Fields] AS REAL) / AssnDataAggregated.[Total Number of Assignments])),0) AS [RATIO: ASNCF:ASN]
       ,ISNULL(CONVERT(DECIMAL(18,2),(CAST(TaskCFAggregated.[Total Task Custom Fields] AS REAL) / AllTaskData.[Task Count])),0) AS [RATIO: TSKCF:TSK]
       ,CONVERT(VARCHAR(10), GetDate(),111) AS [RECORDED_DATE]'

SET @SQL_JOINS = N'

    FROM dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)

    -- ************  Project Summary Task
    INNER JOIN dbo.MSP_TASKS AS Task WITH(NOLOCK)
    ON (Proj.PROJ_UID = Task.PROJ_UID AND Task.TASK_OPTINDX = 1) -- Use the Project Summary Task for rollup values

    -- ************  Count the number of resource assignments on summary tasks
    LEFT OUTER JOIN    (Select    MP.PROJ_UID AS PROJ_UID, COUNT(DISTINCT MA.Task_name) AS [SRA Count]
                    from msp_assignments as MA inner join msp_projects as MP on MA.Proj_uid=MP.proj_uid 
                    where MA.task_uid in (select task_uid from msp_tasks where task_is_summary=1)
                    GROUP BY MP.PROJ_UID) AS SRA
    ON (PROJ.PROJ_UID = SRA.PROJ_UID)

    -- ************  Get the Project Manager Name 
    LEFT OUTER JOIN ' + @PUBLISHED_DB_NAME + N'.dbo.MSP_RESOURCES As CollabRes
    ON Proj.PROJ_PROP_AUTHOR = CollabRes.WRES_ACCOUNT

    -- ************  Get the rowcount from our (usually) largest table 
    LEFT OUTER JOIN (SELECT Assn.ProjectUID as PROJ_UID,COUNT(*) + 1 AS [Actual Count]
                    FROM ' + @REPORTING_DB_NAME + N'.dbo.MSP_EpmAssignmentByDay AS Assn
                    GROUP BY Assn.ProjectUID) AS RealData
    ON (Proj.PROJ_UID = RealData.PROJ_UID)

    -- ************  Project Workspace data from PublishedDB 
    LEFT OUTER JOIN ' + @PUBLISHED_DB_NAME + N'.dbo.MSP_PROJECTS AS Collab
    ON     (Proj.PROJ_UID = Collab.PROJ_UID)

    -- ************  Project Custom Field Data
    LEFT OUTER JOIN (SELECT pcf.PROJ_UID,COUNT(*) AS [Number of Project Custom Field Values]
                    FROM dbo.MSP_PROJ_CUSTOM_FIELD_VALUES AS pcf WITH(NOLOCK)
                    GROUP BY pcf.PROJ_UID) AS ProjCF
    ON (Proj.PROJ_UID = ProjCF.PROJ_UID)

    -- ************  Project Local Custom Fields
    LEFT OUTER JOIN (SELECT plcf.PROJ_UID
                           ,COUNT(*) AS [Number of Local Custom Field Definitions]
                     FROM dbo.MSP_PROJECT_CUSTOM_FIELDS AS plcf WITH(NOLOCK)
                     GROUP BY plcf.PROJ_UID) AS ProjLCF
    ON (Proj.PROJ_UID = ProjLCF.PROJ_UID) 
                
    -- ************  Project Hierarchy Below each Project
    LEFT OUTER JOIN (SELECT HierData.PROJ_UID
                           ,COUNT(HierData.DEPTH) AS [Number of SubProjects]
                           ,MAX(HierData.DEPTH) AS [SubProject Depth]
                     FROM (SELECT Proj.PROJ_UID
                                 ,Hier.DEPTH 
                           FROM ProjHierarchy as Hier
                           INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
                           ON Hier.PARENT_PROJ_UID = Proj.PROJ_UID) AS HierData
                     GROUP BY HierData.PROJ_UID) AS HierSummaryData
    ON (Proj.PROJ_UID = HierSummaryData.PROJ_UID)

    -- ************  Project resource data
    LEFT OUTER JOIN (SELECT Pres.PROJ_UID
                           ,COUNT(*) AS [Total Resources]
                           ,SUM(CAST(Pres.RES_IS_ENTERPRISE_RESOURCE AS INT)) AS [Enterprise Resources]
                           ,SUM(CAST(Pres.RES_BOOKING_TYPE AS INT)/2) AS [Proposed Resources]
                     FROM dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)
                     WHERE Pres.RES_ID > 0 -- Ignore internal resources
                     GROUP BY Pres.PROJ_UID) AS Resources
    ON (Proj.PROJ_UID = Resources.PROJ_UID)

     -- ************  Resources with Assignments
     LEFT OUTER JOIN (SELECT ActiveRes.PROJ_UID
                            ,COUNT(*) AS [Active Resources]
                      FROM (SELECT Assn.PROJ_UID
                                  ,Assn.RES_UID
                            FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)
                            INNER JOIN dbo.MSP_PROJECT_RESOURCES AS Pres WITH(NOLOCK)
                            ON (Assn.PROJ_UID = Pres.PROJ_UID AND Assn.RES_UID = Pres.RES_UID)
                            WHERE Pres.RES_ID > 0 -- Ignore internal resources
                            GROUP BY Assn.PROJ_UID, Assn.RES_UID) AS ActiveRes
                      GROUP BY ActiveRes.PROJ_UID) AS ActiveResAggregated
     ON (Proj.PROJ_UID = ActiveResAggregated.PROJ_UID) 

    -- ************  Leaf task data            
    LEFT OUTER JOIN (SELECT Task.PROJ_UID
                       ,COUNT(*) AS [Leaf Task Count]
                       -- IMPLEMENTATION NOTE - Use _SCHED_ below for PS2010, Ignores User Scheduled dates 
                        ,MAX(DATEDIFF(d,Task.' + @PS_TASK_START + N', Task.' + @PS_TASK_FINISH + N')) AS [Max Leaf Calendar Duration (Days)]
                        ,AVG(DATEDIFF(d,Task.' + @PS_TASK_START + N', Task.' + @PS_TASK_FINISH + N')) AS [Average Leaf Calendar Duration (Days)]
                       ,SUM(CAST(TASK_IGNORES_RES_CAL AS INT)) AS [Total Leaf Task Ignoring ResCalendar]
                     FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
                     WHERE Task.TASK_IS_SUMMARY = 0x0 -- Ignore summary tasks
                     AND Task.TASK_IS_MILESTONE = 0x0 -- Ignore milestones
                     AND Task.TASK_IS_SUBPROJ = 0x0 -- Ignore subprojects
                     GROUP BY Task.PROJ_UID) AS LeafTaskData
    ON (Proj.PROJ_UID = LeafTaskData.PROJ_UID)

    -- ************  All visible tasks in the project
    LEFT OUTER JOIN (SELECT Task.PROJ_UID
                       ,COUNT(*) AS [Task Count]
                     FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
                     WHERE Task.TASK_OPTINDX > 1
                     GROUP BY Task.PROJ_UID) AS AllTaskData
    ON (Proj.PROJ_UID = AllTaskData.PROJ_UID)

    -- ************  Task data changed on Last Project saved
    LEFT OUTER JOIN (SELECT Task.PROJ_UID
                           ,COUNT(*) AS [Recent Changes]
                     FROM dbo.MSP_TASKS AS Task WITH(NOLOCK)
                     INNER JOIN dbo.MSP_PROJECTS AS Proj WITH(NOLOCK)
                     ON (Task.PROJ_UID = Proj.PROJ_UID AND Task.MOD_REV_COUNTER = Proj.MOD_REV_COUNTER)
                     WHERE Task.TASK_OPTINDX > 1
                     GROUP BY Task.PROJ_UID) AS RecentTasks
    ON (Proj.PROJ_UID = RecentTasks.PROJ_UID)

    -- ************  Task Custom Field Data
    LEFT OUTER JOIN (SELECT TaskCF.PROJ_UID
                           ,MAX(TaskCF.[Task CF Count]) AS [Max Task Custom Fields]
                           ,AVG(TaskCF.[Task CF Count]) AS [Average Task Custom Fields]
                           ,SUM(TaskCF.[Task CF Count]) AS [Total Task Custom Fields]
                           ,COUNT(*) AS [Number of Tasks with Custom Fields]
                     FROM (SELECT TCF.PROJ_UID
                                 ,TCF.TASK_UID
                                 ,COUNT(*) AS [Task CF Count]
                           FROM dbo.MSP_TASK_CUSTOM_FIELD_VALUES AS TCF WITH(NOLOCK)
                           GROUP BY TCF.PROJ_UID, TCF.TASK_UID) AS TaskCF
                     GROUP BY TaskCF.PROJ_UID) AS TaskCFAggregated
    ON (Proj.PROJ_UID = TaskCFAggregated.PROJ_UID)

    -- ************   Link Data
    LEFT OUTER JOIN (SELECT Links.PROJ_UID
                           ,COUNT(*) AS [Total Number of Links]
                           ,SUM(CAST(LINK_IS_CROSS_PROJ AS INT)) AS [Cross Project Links]
                     FROM dbo.MSP_LINKS AS Links
                     GROUP BY Links.PROJ_UID) AS LinkData
    ON (Proj.PROJ_UID = LinkData.PROJ_UID)

    -- ************  Task Baseline Data
    LEFT OUTER JOIN (SELECT TaskBaseData.PROJ_UID
                           ,COUNT(*) AS [Number of Baselines]
                           ,SUM(TaskbaseData.[Task Baseline Rows]) AS [Total Task Baseline Rows]
                     FROM (SELECT TaskBase.PROJ_UID
                                 ,TaskBase.TB_BASE_NUM AS [Baseline]
                                 ,COUNT(*) AS [Task Baseline Rows]
                           FROM dbo.MSP_TASK_BASELINES AS TaskBase WITH(NOLOCK)
                           GROUP BY TaskBase.PROJ_UID, TaskBase.TB_BASE_NUM) AS TaskBaseData
                     GROUP BY TaskBaseData.PROJ_UID) AS TaskBaseAggregated
    ON (Proj.PROJ_UID = TaskBaseAggregated.PROJ_UID)

    -- ************  Assignment Baseline Data
    LEFT OUTER JOIN (SELECT AssnBaseData.PROJ_UID
                           ,COUNT(*) AS [Total Assn Baseline Rows]
                     FROM dbo.MSP_ASSIGNMENT_BASELINES AS AssnBaseData WITH(NOLOCK)
                     GROUP BY AssnBaseData.PROJ_UID) AS AssnBaseAggregated
    ON (Proj.PROJ_UID = AssnBaseAggregated.PROJ_UID)

    -- ************  Resource Baseline Data
    LEFT OUTER JOIN (SELECT ResBaseData.PROJ_UID
                           ,COUNT(*) AS [Total Resource Baseline Rows]
                     FROM dbo.MSP_PROJECT_RESOURCE_BASELINES AS ResBaseData WITH(NOLOCK)
                     GROUP BY ResBaseData.PROJ_UID) AS ResBaseAggregated
    ON (Proj.PROJ_UID = ResBaseAggregated.PROJ_UID) 

    -- ************  Assignment Data Summary
    LEFT OUTER JOIN (SELECT AssnTaskData.PROJ_UID
                           ,AVG(AssnTaskData.[Assignment Count]) AS [Average Resources on Assigned Tasks]
                           ,SUM(AssnTaskData.[Assignment Count]) AS [Total Number of Assignments]
                           ,COUNT(*) AS [Number of Tasks with Assignments]
                     FROM (SELECT Assn.PROJ_UID
                                 ,Assn.TASK_UID
                                 ,COUNT(*) AS [Assignment Count]
                           FROM dbo.MSP_ASSIGNMENTS AS Assn WITH(NOLOCK)
                           GROUP BY Assn.PROJ_UID, Assn.TASK_UID) AS AssnTaskData
                     GROUP BY AssnTaskData.PROJ_UID) AS AssnDataAggregated    
     ON (Proj.PROJ_UID = AssnDataAggregated.PROJ_UID)

    -- ************  Assignment Custom Field Data
    LEFT OUTER JOIN (SELECT AssnCF.PROJ_UID
                           ,MAX(AssnCF.[Assn CF Count]) AS [Max Assn Custom Fields]
                           ,AVG(AssnCF.[Assn CF Count]) AS [Average Assn Custom Fields]
                           ,SUM(AssnCF.[Assn CF Count]) AS [Total Assn Custom Fields]
                           ,COUNT(*) AS [Number of Assn with Custom Fields]
                     FROM (SELECT ACF.PROJ_UID
                                 ,ACF.ASSN_UID
                                 ,COUNT(*) AS [Assn CF Count]
                           FROM dbo.MSP_ASSN_CUSTOM_FIELD_VALUES AS ACF WITH(NOLOCK)
                           GROUP BY ACF.PROJ_UID, ACF.ASSN_UID) AS AssnCF
                     GROUP BY AssnCF.PROJ_UID) AS AssnCFAggregated
    ON (Proj.PROJ_UID = AssnCFAggregated.PROJ_UID)
    ORDER BY 
        [CF: TOTAL_ASN_CFs]
        ,[CF: TOTAL_TASK_CFs]
        ,[PRJ: DURATION (Mnth)]
'
EXEC (@SQL_SELECT + @SQL_JOINS)

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft