Sustainability Item_Values CO2factor

by

Overview

sustainability.item_values_co2factor provides a structured, item-value representation of greenhouse gas (GHG) conversion factors used for carbon reporting.

The view standardises UK Government conversion factor data into a consistent CDM-aligned format, enabling emissions calculations to be performed in a repeatable and governed way across reporting and analytics.

It forms a foundational dataset for sustainability, emissions tracking, and environmental reporting use cases.

Business Purpose

This view enables the business to convert activity data (e.g. fuel usage, energy consumption) into carbon emissions using recognised and auditable conversion factors.

Specifically, it supports:

  • carbon footprint calculations (CO₂e)
  • sustainability and ESG reporting
  • regulatory and compliance reporting
  • internal environmental performance tracking
  • consistent use of UK Government GHG conversion standards

Without this, emissions reporting becomes inconsistent very quickly — different teams using different factors is exactly how credibility gets lost.

View Pattern

This is an item values view.

That means:

  • numeric values (conversion factors) are stored as rows
  • contextual meaning is provided via metadata
  • the structure supports aggregation and reuse across domains

This is the correct pattern — conversion factors are values, not codes.

Grain

The grain of the view is:

one row per
OBJECT_SEQ + ITEM_KEY + ATTRIBUTE

Each row represents a single conversion factor for a specific emissions category.

Object and Item Identity

OBJECT_SEQ

{"ASSET":"CO2|<Level_2>","ASSET_ID":"<Level_3>"}

This defines the sustainability asset being measured.

  • ASSET groups the factor under a category (e.g. CO2|Liquid fuels)
  • ASSET_ID identifies the specific emissions source within that category

This is a good design choice — you’ve effectively created a reusable emissions classification structure.

ITEM_KEY

{"SEQ_NO_KEY":"<ID split by |>"}

Derived from the source ID, this preserves the original hierarchical key.

It allows traceability back to the source dataset without exposing raw structure everywhere.

ITEM_TYPE

GHG

Represents greenhouse gas conversion factors.

Source

  • mis.CO2_conversion_factors

Captured in:

{"Source":"mis.CO2_conversion_factors"}

This aligns with UK Government published conversion factors for company reporting.

Key Features

1. Standardised GHG conversion factors

Uses recognised UK Government factors, ensuring consistency and credibility in emissions reporting.

2. Numeric precision control

Conversion factors are:

  • cast to FLOAT
  • then standardised to DECIMAL(18,3)
  • then stored as VARCHAR

This ensures consistent rounding and avoids floating-point inconsistencies downstream.

3. Metadata-driven classification

Each value is enriched with:

  • Level 1, 2, 3 hierarchy
  • Units of measure (UOM)
  • GHG unit (e.g. kg CO₂e)

This is critical — the value alone is meaningless without context.

4. Filtered to relevant domain scope

Currently constrained to:

Level_2 = 'Liquid fuels'

This gives a controlled MVP scope while leaving room for expansion.

5. Zero-value exclusion

Records where the conversion factor is zero are excluded:

TRY_PARSE(v.CODE_VALUE as FLOAT) <> 0

This avoids polluting calculations with invalid or placeholder values.

Attribute

Conversion_Factor

This is the core value.

CODE_VALUE

  • The numeric conversion factor
  • Stored as a string representation of a decimal (3dp precision)

Example:

2.676

VALUE_METADATA

{
  "Level_1": "...",
  "Level_2": "...",
  "Level_3": "...",
  "UOM": "...",
  "GHG_Unit": "..."
}

This provides full classification and usage context:

  • Level_1 / Level_2 / Level_3 → hierarchical categorisation
  • UOM → unit of the input activity (e.g. litres)
  • GHG_Unit → output emissions unit (e.g. kg CO₂e)

Output Fields

  • OBJECT_SEQ
  • ITEM_KEY
  • ITEM_TYPE
  • ATTRIBUTE
  • CODE_VALUE
  • VALUE_METADATA
  • CREATE_DATE
  • EXPIRY_DATE
  • SOURCE_SYSTEM
  • ACTIVE_FLAG
  • CHECKSUM

Audit and History Behaviour

Audit

  • CREATE_DATE = current execution date
  • EXPIRY_DATE = 2099-12-31
  • ACTIVE_FLAG = 1

Checksum

Generated from:

  • OBJECT_SEQ
  • ITEM_KEY
  • ATTRIBUTE
  • CODE_VALUE

Supports:

  • change detection
  • future historical tracking (RPV-compatible)

Design Notes

1. This is a reference dataset — treat it as authoritative

These factors should not be overridden locally. If they change, they should change centrally and propagate.

2. Object model is deliberately generic

Using:

ASSET / ASSET_ID

instead of something overly specific (like fuel type) gives you flexibility to extend into:

  • electricity
  • travel
  • waste
  • supply chain emissions

3. Metadata carries the real meaning

The numeric factor is useless without:

  • unit of measure
  • emissions unit
  • classification hierarchy

Consumers must always use metadata alongside the value.

4. Stored as VARCHAR — by design

You’ve standardised precision before storage, which is good.

But downstream:

  • cast back to numeric when performing calculations

Do not perform maths directly on the string.

5. Current scope is intentionally narrow

Only:

Liquid fuels

That’s the right move — prove the pattern before scaling.

Reporting Guidance

This view is designed to be joined with activity or consumption data.

Typical pattern:

Activity Data (e.g. litres of fuel)
        ×
Conversion Factor
        =
Emissions (kg CO₂e)

Example Use Cases

Example 1: Fuel emissions calculation

  • Join fuel usage (litres) to conversion factors
  • Multiply by Conversion_Factor
  • Output emissions in kg CO₂e

Example 2: Sustainability dashboard

  • Aggregate emissions by:
    • asset type
    • business unit
    • region
  • Use metadata to group results

Example 3: ESG reporting

  • Use standardised factors to:
    • ensure auditability
    • align with UK Government methodology

Usage Considerations

Always join on the correct classification

Use OBJECT_SEQ and/or metadata levels to ensure you are applying the correct factor.

Never ignore units

Mismatch between:

  • activity unit (e.g. litres)
  • factor unit (UOM)

will invalidate results.

Cast before calculation

CAST(CODE_VALUE AS DECIMAL(18,3))

Treat this as a controlled dataset

Changes should be:

  • versioned
  • traceable
  • communicated

Known Limitations

  • Currently limited to Liquid fuels
  • No temporal versioning of factors yet (annual changes not modelled)
  • Assumes single current factor set (no historical factor comparison)
  • Relies on correct upstream mapping of activity data to factor categories

Summary

sustainability.item_values_co2factor establishes a clean, governed foundation for emissions calculation within the data platform.

It brings:

  • standardisation (UK GHG factors)
  • structure (CDM item-values pattern)
  • scalability (asset-based modelling)

Most importantly — it turns sustainability from a narrative into something measurable and repeatable.

That’s the difference between “reporting on sustainability” and actually managing it.

Leave a Comment