WaterStorageAggregate table
Important
Some or all of this functionality is available as part of a preview release. The content and the functionality are subject to change.
The WaterStorageAggregate aggregate table stores "water stored" aggregated by facility and reporting period.
The CreateAggregateForWaterStorageMetrics_INTB notebook contains the computation logic.
Inputs
Be sure to store the measurement values of water stored in storage containers for a measurement date in the Storage container product actual storage ESG data model table.
This table shows the details of the required fields to populate in the ESG data model table.
| Column name | Explanation |
|---|---|
| StorageContainerId | StorageContainer.StorageContainerId Ingest the details of each storage container in the Storage container table. Note: Ensure that for each Storage container Facility Id is mandatorily populated. |
| Actual Storage Units | Store the actual stored water quantity of the container. |
| Period Start Timestamp and Period End Timestamp | Map to the date when stored water of the container was measured. |
| Actual Storage UOM ID | Map the unit of measurement for the stored water measurement value. Note: Should map to a valid UnitOfMeasureId in the UnitOfMeasure reference table that has UnitofMeasureType.UnitOfMeasureTypeName = Volume. |
To view other properties of the ESG data model table, refer to the ESGSchema.json file in the ConfigAndDemoData lakehouse.
For details on ingesting, transforming, and loading data into ESG data model tables, go to ESG data estate overview.
This table shows the WaterStorageAggregate table column descriptions and their mapping to ESG data model tables.
| Column name | Is nullable | Description | Mapping to ESG data model table |
|---|---|---|---|
| ReportingPeriod | Required | Specifies the period for the stored water value aggregated. Currently, only calendar years are supported. 1. Consider all the StorageContainerActualProductStorage records for each facility's storage containers and find the minimum year from the PeriodEndTimestamp and PeriodStartTimestamp of the records. 2. From the minimum year until the current reporting year, calculate the aggregate table records. |
Year of PeriodEndTimestamp or PeriodStartTimestamp |
| FacilityName | Required | Facility in which the storage container is physically located. | StorageContainerProductActualStorage.StorageContainerId > StorageContainer.FacilityId > Facility.FacilityName |
| FacilityId | Required | Unique identifier for the facility where storage containers are located. | StorageContainerProductActualStorage.StorageContainerId > StorageContainer.FacilityId |
| CountryOrRegion | Optional | Specifies the country/region in which the Facility is located. | StorageContainerProductActualStorage.StorageContainerId > StorageContainer.FacilityId > Facility.LocationId > Location.CountryId > Country.ISOCountryCode |
| WaterRiskIndexName | Optional | Specifies the water risk associated with the Facility. | StorageContainerProductActualStorage.StorageContainerId > StorageContainer.FacilityId > Facility.PartyId > WaterRisk.WaterRiskIndexId for WaterRiskType = Water stress > WaterRiskIndex.WaterRiskIndexName |
| StoredWaterBeginningOfThePeriod | Required | Aggregated stored water at the beginning of the reporting period for a facility. - To find the storage units at the beginning of the period, use the Storage container Product Actual storage record with the measurement date (PeriodStartTimestamp if PeriodEndTimestamp is null or PeriodEndTimestamp) closest (<=) to the reporting year start date. |
Sum the actual storage units at the beginning of the reporting year for each storage container in the facility |
| StoredWaterEndOfThePeriod | Required | Aggregated stored water at the end of the reporting period for a facility. - To find the storage units at the end of the period, use the Storage container Product Actual storage record with the measurement date (PeriodStartTimestamp if PeriodEndTimestamp is null or PeriodEndTimestamp) closest (<=) to the reporting year end date. |
Sum the actual storage units at the end of the reporting year for each storage container in the facility |
| ChangeInStoredVolume | Calculated value | Stores the difference between stored water at the end and beginning of the reporting period. | Calculated as StoredWaterEndOfThePeriod minus StoredWaterBeginningOfThePeriod at the facility level for each reporting period. |
| UnitOfMeasureName | Required | Unit of measurement for StoredWaterBeginningOfThePeriod, StoredWaterEndOfThePeriod, and ChangeInStoredVolume. The default unit of measure for this aggregate table is Cubic meters. Actual Storage Units are converted to Cubic meters using the UnitOfMeasureConversion table. For other units (such as Litres), update UnitOfMeasureNames key in WaterStorageAggregate in aggregate_tables_config.json in ESG metrics/Config folder. |
UnitOfMeasureId > UnitOfMeasure.UnitOfMeasureName |
Note
If there are records in the measurement tables, but the attributes mapped to required fields in the aggregate table are missing, then aggregate table generation fails. You need to ensure that data for required fields is available for all the records in the ESG data model measurement tables.
