Aggregating data - monthly to annual

[Colin Chalmers]

Hi all,

We recently did a PoC for a client and ran into a couple of issues which we found limiting in functionality.

Maybe this is meant to be, or not and there is a fix/enhancement coming.

We set up manual metric definition to get monthly inputs of data for energy bills. We then wanted to setup a calculated metric definition to aggregate data on an annual roll-up frequency but that is not possible.

While it’s possible to aggregate multiple metrics into a single (calculated) metric, they all must have the same frequency. So, monthly metrics roll-up into a monthly metric. We couldn’t collect monthly metrics, calculate the emissions with an emission factor, and then roll them up into a single annual CO2e metric for the annual report.

Is this the expected behaviour and if so, why? Maybe we are missing something.

Thanks in advance!

[Amit Gujarathi]

Hi @Colin Chalmers ,
I trust you are doing great.

Currently, ServiceNow's functionality restricts the ability to set up a calculated metric definition with different roll-up frequencies. It is designed to aggregate multiple metrics into a single metric, but all the metrics must have the same frequency. This means that monthly metrics can only roll up into a monthly metric, preventing you from calculating emissions using an emission factor and then rolling them up into a single annual CO2e metric for the annual report.

Regarding whether this behavior is expected, it aligns with the current functionality of ServiceNow. However, it's important to note that there might be future fixes or enhancements planned to address this limitation.

To work around this limitation, I recommend the following approach:

1. Create separate metrics for each frequency: Instead of trying to combine monthly and annual metrics into a single calculated metric, create separate metrics for each frequency. For example, create a monthly metric for monthly data collection and an annual metric for annual roll-up.

2. Configure metric formulas: Define the formula for each metric to calculate the desired values. For monthly metrics, calculate the emissions using the emission factor. For the annual metric, sum up the monthly emissions to obtain the annual CO2e value.

3. Automate data collection: Set up automated processes or integrations to collect monthly energy bill data and populate the respective monthly metric. This ensures that the data is regularly updated.

4. Schedule calculations and roll-ups: Configure scheduled jobs or scripts to perform the calculations and roll-ups. These processes can sum up the monthly metrics into the annual metric, providing the desired annual CO2e value for the annual report.

[Vaisakh Krishna]

Hi Colin,

Yes, you can only include those metric definitions in a calculated metric definition whose frequency is the same as the calculated metric definition's frequency. That is the expected bevhiour.

Coming to your use case, below is what I propose (make sure you are on the latest version of ESG management):

1. Set up a manual metric definition (frequency=monthly) to collect the data for energy bills (same as what you have already done)

2. Set up a calculated metric definition (calculation level=Entity, frequency=monthly) and include the manual metric definition in it, create a formula with the manual metric definition and emission factor, and calculate the emissions.

3. Once you complete Step 2, you will have metrics created on the Calculated metric definition with the computed value.

4. As part of our last release, we have shipped 2 new tables, called Metric data by entity (sn_grc_metric_data_by_entity) and Time dimension (sn_grc_metric_time_dimension). Read more about these tables here (see the section towards the end called 'Metric data by entity'). You can use these to aggregate the data for the monthly metrics that you have on the calculated metric definition. That way you are still collecting, and computing data on a monthly basis, and using these new tables for reporting on the frequency you wish, which in your case is annually.

Please reach out to me in case you have any further queries.

Thanks,

Vaisakh

[Colin Chalmers]

Hi Vaisakh,

Thanks for your reply. On face value I'd say that bullet 4 is something we should be able to use.

I looked on our instance and found the tables, I also looked at the link you sent and read the information.

What I don't yet understand is how we fill / use these tables?

1. I didn't see a link from the menu to access these tables forms, is that correct?

2. Should we use a scheduled Job or an action to fill the tables?

3. Should I presume that if we setup an annual metric definition we can then access these tables/metrics for reporting?

Pardon my naivety here, struggling to see how this fits together and appreciate your help.

[Vaisakh Krishna]

Hi Colin,

My responses below:

1. Yes, that is correct. You can access by typing the table names.list on the app navigator (e.g. sn_grc_metric_data_by_entity.list)

2. No action required from your side. There are scheduled jobs OOTB that fill these tables. The scheduled jobs are "Generate metric data by entity" and "Generate time dimensions", you should see them in your instance.

3. You can use these tables for reporting irrespective of the frequency of the metric definitions you are using for data collection. So, yes, you can setup an annual metric definition and use these tables for reporting.

Thanks,

Vaisakh