When a metric is configured with multiple partitions and using SQL as it's data source (with a query that returns the MetricValueDateTime), the metric values will not get updated when the Calculate Metrics job runs and there happens to already be an existing value for the metric with the same date and partition values that are not returned by the query,
Actual Behavior
In the scenario above, the update is actually getting a silent exception when it tries to add new metric values (see this issue: https://github.com/SparkDevNetwork/Rock/issues/5904), so the job will not add any new values or update any existing values for the metric on the specified date.
Expected Behavior
When query returns values for a metric for specific date and partition combination, any existing values with that same date and partition values should be updated with new values. If existing entries do not exist with same date and partition values, a new metric value should be added. This is the behavior specified in the help text of the Metric source:
NOTE: If a [MetricValueDateTime] is specified and there is already a metric value, the value will get updated. This is handy if you have a weekly metric, but schedule it to calculate every day.
Steps to Reproduce
First make sure more than one campus exists.
Go to General Settings > Defined Types > Connection Statuses
Add a new 'Test' Connection Status
Go to Tools > Metrics
Add a new metric with following options
Title: People by Campus and Connection Status
Source Type: SQL
Source SQL:
DECLARE @CurrentDate date = ( SELECT SYSDATETIMEOFFSET() AT TIME ZONE 'Mountain Standard Time' )
SELECT
COUNT(*),
dbo.[ufnUtility_GetSundayDate]( @CurrentDate ) AS [MetricValueDateTime],
[PrimaryCampusId],
[ConnectionStatusValueId]
FROM [Person]
WHERE [PrimaryCampusId] IS NOT NULL
GROUP BY [PrimaryCampusId],[ConnectionStatusValueId]
Schedule: Pick a schedule that starts more than a week ago, and recurs daily.
Partitions: Campus and Defined Value (Connection Status)
Manually add a new value for the new metric for the following Sunday's date, use any campus, and pick the new 'Test' connection status.
Go to System Settings and run the Calculate Metrics job
Return to metrics and notice that there are not any new values (The job actually got an exception trying to add the new values, but did not report it).
Delete the manual value you had entered
Go back to System Settings and run the Calculate Metrics job
Return back to metrics and notice that it added all the correct values for following Sunday.
Issue Confirmation
[X] Perform a search on the Github Issues to see if your bug or enhancement is already reported.
[X] Try to reproduce the problem on a fresh install or on the demo site.
Description
When a metric is configured with multiple partitions and using SQL as it's data source (with a query that returns the MetricValueDateTime), the metric values will not get updated when the Calculate Metrics job runs and there happens to already be an existing value for the metric with the same date and partition values that are not returned by the query,
Actual Behavior
In the scenario above, the update is actually getting a silent exception when it tries to add new metric values (see this issue: https://github.com/SparkDevNetwork/Rock/issues/5904), so the job will not add any new values or update any existing values for the metric on the specified date.
Expected Behavior
When query returns values for a metric for specific date and partition combination, any existing values with that same date and partition values should be updated with new values. If existing entries do not exist with same date and partition values, a new metric value should be added. This is the behavior specified in the help text of the Metric source:
NOTE: If a [MetricValueDateTime] is specified and there is already a metric value, the value will get updated. This is handy if you have a weekly metric, but schedule it to calculate every day.
Steps to Reproduce
First make sure more than one campus exists.
Go to General Settings > Defined Types > Connection Statuses
Add a new 'Test' Connection Status
Go to Tools > Metrics
Add a new metric with following options Title: People by Campus and Connection Status Source Type: SQL Source SQL:
Schedule: Pick a schedule that starts more than a week ago, and recurs daily. Partitions: Campus and Defined Value (Connection Status)
Manually add a new value for the new metric for the following Sunday's date, use any campus, and pick the new 'Test' connection status.
Go to System Settings and run the Calculate Metrics job
Return to metrics and notice that there are not any new values (The job actually got an exception trying to add the new values, but did not report it).
Delete the manual value you had entered
Go back to System Settings and run the Calculate Metrics job
Return back to metrics and notice that it added all the correct values for following Sunday.
Issue Confirmation
Rock Version
v15.4, v16.2
Client Culture Setting
en-US