Can Metric Definitions report on historical data?

[BradDavis]

I want to calculate the lead/cycle times of projects in 2021 by creating a metric definition that shows a project's calculated time in a specific state field. Then I will add up the amount of the time the project began in work in progress until it was placed into completed. This part is already done. I do not know or cannot find a way to use this metric definition to report on historical data. Please assist. Thank you.

[Uncle Rob · Accepted solution]

These records watch for events in order to do their math, so there's no *good* way to do this retroactively (that I know of).   

[Brian Dailey1]

Hi Brad,

Robert is correct, things like metric definitions really need to catch an event in the act to be able to run their calculations.  That said, I like to think that (almost) nothing is really impossible. If you can identify the changes that would in effect trigger the metric (i.e., state changes to WIP) and later complete it (state changes to Complete), then you may be able to run a fix script to do the calculation yourself and backfill that historical data.

Doing so would involve querying the [sys_audit] data for changes to the desired field (state) on Project records, so when Robert said "no *good* way to do this", again he was correct. The Audit table can be huge, and running a script against it can definitely impact performance of your instance while running. Having addressed that however, there are at least a couple of ways to mitigate that risk.

1. Most straightforward: Work to develop the fix script in subprod instances, then schedule a maintenance window on Prod to run it and collect your data.
2. More steps, but less risk: Clone down Prod to a subprod instance (including all necessary data), develop the script, and then run it in subprod to populate your metric data.  Then export/import those metric records into Prod.

To do the actual work in script, you're going to need to figure out the following steps:

1. How to calculate the duration for your metric from two audit entries for each Project (change TO Work in Progress, change FROM Work in Progress to Complete).
2. How to insert a Metric Instance record via script that relate back to your desired metric definition.

It's possible, and depending on how badly you need the data, doable.

Good luck,

-Brian

[Uncle Rob]

"Catch it in the act" - Consider this STOLEN

[Brian Dailey1]

Stolen?