How to track EPM Resource field changes in your Reporting Database

Leveraging the EPM 2007 Eventing service as well as the Reporting database here is a way of tracking all changes made to a Resource's fields. A common request I heard from customers is the ability to track when a Resource changes from one node to another node in the RBS.

Steps to do it (code download below!!!):

• Created a custom table in the Reporting database called: MCS_EpmResource_UserView_Tracking
• Created a custom stored procedure in the Reporting database called: MCS_EPM_InsertResourceChange that takes the RES_UID as a parameter

DECLARE @ResourceTimesheetManagerUID uniqueidentifier
DECLARE @ResourceModifiedDate datetime 

SELECT    @ResourceModifiedDate = ResourceModifiedDate,
        @ResourceTimesheetManagerUID = ResourceTimesheetManagerUID
FROM MSP_EpmResource
WHERE ResourceUID = @ResourceUID 

IF NOT EXISTS(SELECT * FROM dbo.MCS_EpmResource_UserView_Tracking WHERE ResourceUID = @ResourceUID AND ResourceModifiedDate = @ResourceModifiedDate) 

Note that I only INSERT a new row in my history table if the Modified Date has changed.

• Created a custom event handler that gets called every time a Resource is changed (OnResourceChanged)

        public override void OnResourceChanged(PSContextInfo contextInfo, ReportingPostResourceChangedEventArgs e)
        {
            // Standard processing
            base.OnResourceChanged(contextInfo, e);

            // Load settings from Microsoft.Office.Project.Server.Eventing.exe.config
            LoadSettings();

            // Build T-SQL command
            string cmdText = string.Format("EXEC MCS_EPM_InsertResourceChange '{0}'",e.ResourceUid.ToString());

            // Store Resource field update
            ConnectAndExecuteNonQuery(connectionString, cmdText);
        }

• Deploy event handler in GAC and define an entry in the PWA configuration (refer to SDK for more details)
• and voila!

From now on, every time a Resource's field is changed (for instance it's name, RBS tec ...) and entry will be inserted in the tracking table mentioned above.

Eventually you will want to build a report that leverages that historical data to show you a view before and after the change was made for instance.