Create, configure, and harmonize reference values
Reference values are standardized, predefined values used to categorize, classify, or represent specific information in data systems. These values serve as common reference points or codes to ensure consistency and accuracy when storing or exchanging data. In Sustainability data solutions in Microsoft Fabric, reference values range from common values like countries, currencies, or unit of measure, to environmental, social, and governance (ESG) specific values like greenhouse gases, water types, waste types, and more.
To view the JSON files that store the reference data tables, and the reference values used in Sustainability data solutions in Fabric, navigate to ConfigAndDemoData_LH > Files > Reference data > Data.
The reference data tables in Sustainability data solutions in Fabric are divided into three folders:
Common: The reference values that aren't specific to any industry. For example: country, currency, unit of measure.
Sustainability: The reference values that are specific to sustainability. For example: greenhouse gases, water types, waste types.
Customer: The reference values that are added by and specific to Sustainability data solutions in Fabric users.
To add your own Sustainability data solutions in Fabric reference values for any reference table, follow these steps:
Create a copy of the JSON file present in the Common or Sustainability folder under the reference data location that contains the reference values for the reference table that you want to modify.
Add this file to the Customer folder in the same reference data location. Make sure you don't modify the name of the file, and that it matches with the reference table name.
Remove the reference values present in the JSON file and add the new reference values that you want to use. Make sure the ID values that you provide for the reference data are within the permissible range mentioned in the Contributors.json files present in ConfigAndDemoData_LH > Files > Reference data.
To load the reference values, run the LoadReferenceTables_INTB notebook from your Sustainability data solutions in Fabric workspace.
On successful completion, the reference values are populated in the reference tables present in the lakehouse ProcessedESGData_LH.
Each source system employs its own set of reference values for the same data entities, which can lead to data inconsistency. Reference value harmonization is the process of standardizing and aligning reference values used in different data systems or sources to ensure consistent and accurate data integration and analysis. Reference value harmonization ensures that data elements referring to the same concepts are consistently represented across various systems, promoting data accuracy and making it possible to perform reliable cross-source analysis, reporting, and other data transformations.
You can perform reference value harmonization in Sustainability data solutions in Fabric with these methods:
- DTT’s Reference Data Manager (preview)
- Cross referential tables
A cross referential table stores the mappings between the source and target key values and is used to resolve the reference value lookups. These tables can be created and populated using dataflows or notebook code. This structure shows an example of how the table appears:
- Table: Name of the reference data table
- Source Value: Reference value defined in the source system
- Target Value: Reference value defined in the JSON file
- Contributor: Type of reference value
This table provides definitions of reference tables in Sustainability data solutions in Fabric.
| Reference table name | Type | Definition |
|---|---|---|
| Asset Type | Common | A classification of Asset based upon common characteristics, attributes, or functions. |
| Business Metric | Common | A financial or performance metric or measurement that provides information about the performance or financial status of the organization, corporation, or business unit. |
| Calculation Algorithm | Common | A calculation that is used for calculating a metric. |
| Calculation Algorithm Basis | Common | The basis used by the calculation algorithm. |
| Country | Common | The Country definition is based upon ISO 3166 Country Codes. The term "country" is perhaps a little misleading: not all the "countries" in ISO 3166 are independent. Many of them are geographically separate territories that are legally portions or dependencies of other countries. Nonetheless the term "country" is used throughout. Where the code for a country includes territory also included in another code, a code for the exclusive part can be formed by replacing the second and third letters by X. |
| Currency | Common | The definition of Currency is based upon the ISO 4217 standard. ISO 4217 is the international standard describing three letter codes (also known as the currency code) to define the names of currencies established by the International Organization for Standardization (ISO). |
| Employee Party Relationship Type | Common | The nature of the relationship between the employee and party. |
| Event Type | Common | A categorization of events that can occur with a party to create a history of predefined events. |
| Facility Type | Common | A categorization of facilities based upon common characteristics or functionality. |
| Gender | Common | The social roles based on the sex of the person (culturally learned) or personal identification of one's own gender based on an internal awareness (gender identity). |
| Greenhouse Gas | Sustainability | A greenhouse gas is a gas that absorbs and emits radiant energy within the thermal infrared range and contributes to the greenhouse effect. |
| Greenhouse Gas Emissions Category | Sustainability | Greenhouse Gas Emission Category is a means of classifying emissions within a given Emission Scope. |
| Greenhouse Gas Emissions Purpose Type | Sustainability | A categorization of purposes for aggregating greenhouse gas emissions. |
| Greenhouse Gas Emissions Scope | Sustainability | Scope is the basis for mandatory Greenhouse Gas reporting. Currently, there are three scopes used to define emissions. |
| Greenhouse Gas Source Type | Sustainability | A type of greenhouse gas source. |
| Incident Type | Common | The type of incident. |
| Item Type | Common | A type of Item based on common characteristics, function, or similar criteria. |
| Location Type | Common | A means of categorizing locations based on common characteristics or functions. |
| Metric Purpose | Common | The purpose of a Metric. |
| Party Organization Type | Common | A categorization of Organizations based on organization responsibility, reporting structure, function, or others. |
| Party Relationship Type | Common | The nature of the relationship between the two parties. |
| Party Type | Common | A categorization of parties based upon common characteristics or similar criteria. |
| Process Type | Common | The type of process. |
| Purchased Energy Type | Sustainability | A type of purchased energy. |
| Sample Test Category | Sustainability | A category of testing that is used to classify sample test types. |
| Sample Test Result Type | Common | The result of the sample test. |
| Sample Test Type | Common | A type of test. |
| Storage Container Type | Common | The type of storage container. |
| Sustainable Content Type | Sustainability | Circularity is a practice or a framework that focuses on reducing waste, pollution, and consumption of finite resources by keeping products' value intact for a longer period of time and reusing, remaking, and recycling them. To do so, it's necessary to determine sustainable content types for every item. |
| Unit Of Measure | Common | A standard of measurement. |
| Unit Of Measure Type | Common | A categorization of units of measure based on common characteristics, performed functions, or similar criteria. |
| Waste Category | Sustainability | A categorization of waste types based upon common characteristics or similar criteria. |
| Waste Diversion Method | Sustainability | The type of waste diversion. |
| Waste Material Type | Common | A categorization of waste based upon common characteristics or similar criteria. |
| Waste Stream | Sustainability | A Waste Stream is the complete flow of waste from its domestic or industrial source through to recovery, recycling, or final disposal. Waste streams can be divided into two broad types: streams made of materials (such as metals or plastics) or streams made of certain products (such as electronic waste or end-of-life vehicles) which require specific treatment and ultimately feed into materials-related streams. |
| Water Risk Index | Sustainability | The Water Risk Index. |
| Water Risk Type | Sustainability | The type of water risk. |
| Water Source Type | Sustainability | The source of water. |
| Water Type | Sustainability | A type of water based on use or purpose. |
| Water Utilization Type | Sustainability | A categorization of water utilization based upon common characteristics or similar criteria. |
