NetRevenueAggregate 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 NetRevenueAggregate table stores net revenue data in specific currency from the environmental, social, and governance (ESG) data model table as reporting period and party dimensions. The CreateAggregateForNetRevenueMetrics_INTB notebook contains the computation logic that aggregates the data and stores the aggregated data in the NetRevenueAggregate table in the ComputedESGMetrics_LH lakehouse.
Inputs
For generating the NetRevenueAggregate table, you should store the precalculated net revenue data for different nodes (such as Party in the ESG data model schema) in your organization hierarchy in the Party Business Metric table of the ESG data model.
Mapping source data to the required Party Business Metric table:
| Columns | Description |
|---|---|
| BusinessMetricId | The foreign key to the BusinessMetric reference table. This table stores the name of precalculated metrics. It has a record where BusinessMetricName = Net revenue (Used to calculate GHG intensity). Use this business metric for storing revenue data in PartyBusinessMetric table. You can create more business metrics and map revenue data to the custom business metric. You need to accordingly modify the aggregate table computation logic and metric computation logic. |
| PartyBusinessMetricValue | Map the revenue value to this field. |
| IsoCurrencyCode | Map the currency code of the revenue value in this field. Note – All the revenue data should be in the same currency code. Provide a valid currency code as per the IsoCurrencyCode field of the Currency reference table. |
| PeriodStartDate | Start time of the period for which the revenue value is being stored. |
| PeriodEndDate | End time of the period for which the revenue value is being stored. |
To learn more about the ESG data model tables and the ingestion mechanism, go to Overview of ESG data estate.
This table shows the NetRevenueAggregate table’s column descriptions and mapping with ESG data model table fields.
| Column | Is nullable | Description | Mapping to ESG data model table |
|---|---|---|---|
| ReportingPeriod | Required | Specifies the period for which the NetRevenue value is aggregated. Note: Currently only calendar years are supported. |
Year of PeriodEndDate |
| PartyId | Required | Unique identifier of the Party Note: Based on the scenarios explained in Configuration parameters, the PartyId here can be measurement table’s PartyId or RelatedParty.RelatedPartyId. |
PartyId |
| PartyName | Required | Name of the party. | PartyId > Party.PartName Note: Based on the scenarios explained in Configuration parameters, the PartyName here can be measurement table’s PartyName or RelatedParty.RelatedPartyId>Party.PartyName. |
| PartyTypeName | Required | The party type for which data is aggregated and stored in the aggregate tables. | PartyId > Party.PartyTypeId > PartyTypeName Note: Based on the scenarios explained in Configuration parameters, the PartyTypeName here can be the measurement table’s PartyId > Party.PartyTypeName > PartyTypeName or RelatedParty.RelatedPartyId > Party.Party.PartyTypeId > PartyType.PartyTypeName. |
| NetRevenue | Required | Stores the revenue value for the specified RevenueType | PartyBusinessMetricValue |
| IsoCurrencyCode | Required | Specifies the currency of the Net Revenue value | IsoCurrencyCode |
| RevenueType | Required | Specifies the type of revenue | BusinessMetricId > BusinessMetric.BusinessMetricName |
| IsRolledUp | Optional | IsRolledUp flag indicates whether a row is a rolled-up record or not. The computation logic always generates the aggregate table with this flag as false. Capability provides a utility function to roll up records in the aggregate table along the organization’s hierarchy (party or secondary party dimension). The rolled-up records are also stored in the same aggregate table. |
Calculated column. Value not mapped from ESG data model tables. |
Configuration parameters
You can specify the parameters to compute NetRevenueAggregate table for the applicable scenarios.
Scenario 1
Net revenue data stored in the Party business metric measurement table at a Party level where PartyType for all parties is the same. For example, you store revenue data at party level where all parties are of Party type = Organization unit. Specify the parameter as follows:
PartyTypeName: The party type for which data is aggregated and stored in the aggregate tables. Specify the PartyType of the party for which revenue data is stored in the measurement table. For this example, specify the Party Type as Organization unit.
Note
In this scenario, all records in the PartyBusinessMetric table should have PartyId so that the party’s PartyType is the same.
RelationshipTypeName: The relationship type between the party at which the measurement data is stored in the measurement tables and the party type for aggregate table. In this scenario, because the PartyType of the party in the measurement table is same as the PartyType for which data needs to be aggregated, this parameter isn't required. Keep this parameter blank.
BusinessMetricNames: Specify the list of the names of BusinessMetricNames used for storing the revenue data in the PartyBusinessMetric. By default, the value of this parameter is set to Net revenue (used to calculate GHG intensity). If you use a custom business metric to store revenue data, include that business metric’s name in this parameter.
Scenario 2
The organization has a parent-child hierarchy. Net revenue data is stored at specific nodes in the hierarchy.
Example: Revenue data stored for Facility 1, Contoso EUR, Contoso USA, Facility 3.
In this case, revenue data is stored in the party business metric as Party with different party types. All parties are part of the organization hierarchy that is mapped in the RelatedParty table, so you can set these configuration parameters:
Party type: The party type for which data is aggregated and stored in the aggregate tables. Party type specified should be such that all the data in the measurement table can be rolled up to the parties of specified party type. For this example, specify Organization unit because:
- Revenue stored at Facility 1 can be rolled up to Contoso EUR party (party type = Organization unit).
- Revenue stored at Contoso EUR party is already of party type = organization unit, so it matches with the party type of aggregation and doesn't require roll-up.
- Revenue stored for Facility 3 party can be rolled up to the Contoso USA party (party type = organization unit).
RelationshipTypeName: The relationship type between the party for which the measurement data is stored in the measurement tables and the Party type for aggregate table. Specify as the PartyRelationshipTypeName that is used to store organizational hierarchy in the RelatedParty table.
BusinessMetricNames: Specify the list of the names of the BusinessMetricNames used for storing the revenue data in the PartyBusinessMetric. By default, the value of this parameter is set to Net revenue (used to calculate GHG intensity). If you used a custom business metric to store revenue data, include its name in this parameter.
