Workiva Carbon (旧Sustain.Life)により、企業の排出量と環境への影響を測定し、カーボンフットプリントの計算と削減に役立てることができます。
WorkivaのESGソリューションでは、Workiva Carbon Refresh テンプレートを使用して、Workiva Carbonからワークスペースにデータを自動的にダウンロードするチェーンを作成することができます:
- ワーキバ・カーボン
- Workivaカーボン・マッピング・データ更新
- Wdata|プライマリ|にデータをロードする
- Wdataにデータをロードする|データセットを置き換える
- Wdataにデータをロードする|新しいデータセットを追加する
- 更新状況
ヒント: テンプレートは複数のチェーンを作成しますが、Workiva Carbon からワークスペースにデータをダウン ロードするには、 チェーンを 1 つだけ実行します。
これらのチェーン店である:
-
Chain Builder のWorkiva Carbon コネクタを使用すると、Workiva Carbon から以下のような企業データを簡単にダウンロードできます:
- 毎月の入力値
- 算定された二酸化炭素換算排出量 (CO2e)
- 選択した方法論の詳細
- ダウンロードしたデータを、カスタムWorkiva Carbon lead sheet Spreadsheetにインポートする:
- 場所
- グループ
- 各スコープカテゴリーにおける温室効果ガス(GHG)活動
その後、Workiva Carbon lead sheet Spreadsheetを環境・社会・ガバナンス(ESG)報告のソースとして使用することができます。
注: Workiva カーボン・リードシート スプレッドシートとそれに関連するクエリは Workiva が提供している。このスプレッドシートをお持ちでない方で、ご興味のある方は、カスタマー・サクセス・マネージャー(CSM)にお問い合わせください。
前提条件
チェーンを作成する前に、まず チェーンビルダー で、これらのコネクタ との接続を設定します:
ヒント すべてのチェーンコマンドはCloudRunnerを使用します。GroundRunnerは必要ありません。
- Workiva カーボン・リードシート スプレッドシート
- 元帳テーブル、グループ階層テーブル、グループマッピングテーブル。
Workiva Carbon Refresh テンプレートからチェーンを作成
Workiva Carbon Refresh テンプレートからチェーンを作成するには、Chain Builder から:
-
Chains で、Create をクリックし、Create chain from template を選択する。
-
1.0 Workiva Carbon refresh テンプレートを選択し、Create from template をクリックします。
- チェーンを作成する環境を選択し、Next をクリックする。
- テンプレートで作成された変数を設定し、Nextをクリックする。
変数 可変タイプ 新しい変数 スプレッドシート ID チェーン スプレッドシート ID 開始月 チェーン・ダイナミック 開始月 月末 チェーン・ダイナミック 月末 wsv-WorkivaCarbon-Ledger-TableID ワークスペース wsv-WorkivaCarbon-Ledger-TableID wsv-WorkivaCarbon-Group Hierarchy-TableID ワークスペース wsv-WorkivaCarbon-Group Hierarchy-TableID wsv-WorkivaCarbon-グループエントリマッピング-テーブルID ワークスペース wsv-WorkivaCarbon-グループエントリマッピング-テーブルID wsv-WorkivaCarbonSchemaName ワークスペース wsv-WorkivaCarbonSchemaName - テンプレートによって作成された各チェーンについて、そのコマンドの接続とCloudRunnerを選択し、 Next chain をクリックします。
ヒント: データが正しく流れるようにするには、テンプレートのチェーン全体で同じ 接続を使用します。チェーンごとに異なるコネクションを使わないこと。
-
Load data to Wdata チェーンで、使用するワークスペース変数を選択し、Next をクリックする:
- Load to Wdata テンプレート から作成したチェーンが既にある場合は、New variable で既存の wsv-WdataLoadWarningThreshold ワークスペース変数を選択する。
- このテンプレートに変数を作成させるには、新しい変数 を空白のままにする。
ヒント: チェーン テンプレートで作成されたLoad to Wdata チェーンで使用される変数については、デフォルトの選択を維持します。
- すべて の接続をマップしたら、Submit をクリックしてチェーンを作成する。
- チェーンが作成されたら、View your new chain をクリックして、新しいWorkiva Carbon refresh チェーンを開きます。
-
チェーン設定 をクリックし、変数の値を入力し、保存 をクリックする。
変数 値 スプレッドシート ID Workiva Carbon lead sheet SpreadsheetのIDを入力してください。 元帳テーブルID スプレッドシートの受信接続で使用されるファクト・テーブルの ID を入力します。 グループ階層テーブルID スプレッドシートの受信接続で使用される階層ディメンジョン・テーブルの ID を入力します。 グループ・マッピング・テーブルID スプレッドシートの受信接続で使用されるマッピング・ディメンジョン・テーブルの ID を入力します。 - Publish をクリックし、チェーンの発行に関するメモを入力し、Publish をクリックする。
Build the chain to log data downloads
To start, build a chain to update the Sustain.Life Spreadsheet to track when emission data downloads to Workiva.
<h3 id="h_01HW875P172RFEJ36N9J8XZ9JD">Step 1. Create the chain</h3>
-
From Chains, click Create, and select
Create chain.
- In Setup, enter a name of <code>3. Sustain.Life log</code> and a description to help explain that the chain logs when data downloads from Sustain.Life.
-
Click Save.
- From Chain events, move Runtime inputs to Start.
- Select the Runtime inputs event, and click Edit.
-
In Basic info, enter a name and description to help identify
the event.
-
In Variables, add TextField inputs with these display names:
- <code>Range</code>
- <code>Spreadsheet ID</code>
- <code>Control sheet ID</code>
- <code>Chain run log ID</code>
- <code>Refresh month</code>
- <code>Refresh year</code>
Tip: Leave the variables' default values blank; they'll be generated when you run the 1. Sustain.Life refresh chain created later.
-
Select Required for all variables, and click
Save.
To identify the time period of the emissions data to download from Sutain.Life:
- From Available BizApps, select File utilities, and move Create file to the canvas.
- Drag a link from Runtime inputs to Create file.
- Select the Create file command, and click Edit.
-
In Basic info, enter a name and description to identify
the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the File utilities connector to use. Text Enter the string to update in Control sheet: - Enter <code>,,,Emissions data last updated on </code>.
- Select the System.DateTime variable from Runtime.
- From Available BizApps, select Workiva, and move Write sheet data to the canvas.
- Drag a link from Create file to Write sheet data.
- Select the Write sheet data command, and click Edit.
- In Basic info, enter a name and description to help identify the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the Workiva connector to use. Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger. Sheet ID/name Select the Control sheet ID runtime input from Trigger. Data file Select the Created file output from Create file. Delimiter Enter a comma (<code>,</code>). Range Select the Range runtime input from Trigger. Use Platform API Select this checkbox.
To update the Chain run log sheet when data downloads from Sustain.Life:
- From Available BizApps, select Workiva, and move Get sheet data to the canvas.
- Drag a link from Runtime inputs to Get sheet data.
- Select the Get sheet data command, and click Edit.
- In Basic info, enter a name and description to help identify the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Workiva connector as earlier. Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger. Sheet ID/name Select the Chain run log ID runtime input from Trigger. Region Enter <code>A1:</code>. Value style Select Calculated. Revision Enter <code>-1</code>. - From Available BizApps, select File utilities, and move Create file to the canvas.
- Drag a link from Get sheet data to Create file.
- Select the Create file command, and click Edit.
-
In Basic info, enter a name and description to help identify
that the command creates the log entry.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same File utilities connector as earlier. Text Enter the string to update the columns of the Chain run log sheet: - Enter the column headers: <code>Date,Details</code>, and press Enter.
- Select the System.DateTime variable from Runtime.
-
Click the System.DateTime variable,
and add a Parse Date/Time transformation:
- Select ISO Extended (Platform Standard).
- In Output date format, enter <code>%Y-%m-%d</code>.
- Select the input and output date timezones.
- Enter <code>,Emission data for </code>.
- Select the Refresh year runtime input from Trigger.
- Enter <code>-</code>.
- Select the Refresh month runtime input from Trigger.
- Enter <code> was successfully refreshed</code>.
- From Available BizApps, select Tabular transformation, and move Advanced query to the canvas.
- Drag a link from Create file to Advanced query.
- Select the Advanced query command, and click Edit.
- In Basic info, enter a name and description to identify the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the Tabular transformation connector to use. Tables Add two tables to use in the query: -
For the first table:
- In File, select the Data output from Get sheet data.
- In Table name, enter <code>a</code>.
-
For the second table:
- In File, select the Created file output from the preceding Create file.
- In Table name, enter <code>b</code>.
Query Enter the query to run: <pre>select * from a union all select * from b</pre> Input delimiter Select Comma. Output delimiter Select Comma. Preview results Select this checkbox. -
For the first table:
- From Available BizApps, select Workiva, and move Overwrite sheet data to the canvas.
- Drag a link from Advanced query to Overwrite sheet data.
- Select the Overwrite sheet data command, and click Edit.
- In Basic info, enter a name and description to identify the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Workiva connector as earlier. Spreadsheet ID Select the Spreadsheet ID runtime input from Trigger. Sheet ID/name Select the Chain run log ID runtime input from Trigger. Data file Select the Result output from Advanced query. Delimiter Enter a comma (<code>,</code>). Start cell Enter <code>A1</code>. Use async Select this checkbox. - Click Publish, enter a note about its publication, and click Publish.
Build chains from the Load Data to Wdata template
Next, use the Load Data to Wdata template to build chains to update the Sustain.Life Spreadsheet's connected table with the downloaded emission data. The Load Data to Wdata template creates chains that work together to:
- Determine whether the dataset already exists in the table
- Update the table with the latest dataset
To create the chains from Templates, open the Load Data to Wdata | Primary chain template, and click New chain.
Build a chain to download data from Sustain.Life
Finally, build a chain to download emission data from Sustain.Life and import it into your workspace.
<h3 id="h_01HZ7GR4GXCAME78MJRQQED0JH">Step 1. Create the chain</h3>
- From Chains, click Create, and select Create chain.
-
In Setup, enter a name of
<code>1. Sustain.Life refresh</code> and a description to help explain that
the chain downloads data from Sustain.Life and uploads it to the table.
-
Add the variables, and click Save:
Name Value Spreadsheet ID Enter the ID for the Sustain.Life Spreadsheet. Control sheet ID Enter the ID for the Control sheet section of the Spreadsheet. Chain run log ID Enter the ID for the Chain run log section of the Spreadsheet. Ledger table ID Enter the ID for the Ledger fact table used by the Spreadsheet's connected queries. Group Hierarchy table ID Enter the ID for the Group Hierarchy dimension table used by the Spreadsheet's connected queries. Group Mapping table ID Enter the ID for the Group Mapping dimension table used by the Spreadsheet's connected queries.
To determine the time period of the data to download, based on the Control sheet section of the Sustain.Life Spreadsheet:
- From Available BizApps, select Workiva, and move Get sheet data to Start.
- Select the Get sheet data command, and click Edit.
- In Basic info, enter a name and description to help identify the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Workiva connector as earlier. Spreadsheet ID Select the Spreadsheet ID variable from Chain. Sheet ID/name Select the Control sheet ID variable from Chain. Region Enter <code>A4:</code>. Value style Select Calculated. Revision Enter <code>-1</code>. - From Available BizApps, select Tabular transformation, and move Advanced query to the canvas.
- Drag a link from Start to Advanced query.
- Select the Advanced query command, and click Edit.
- In Basic info, enter a name and description to identify the command.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Tables Add the table to use in the query: - In File, select the Data output from Get sheet data.
- In Table name, enter <code>a</code>.
Query Enter the query to run: <pre>select `Transaction Year` as "yr",
case when `Transaction Month` = 'January' then '01-01'
when `Transaction Month` = 'February' then '02-01'
when `Transaction Month` = 'March' then '03-01'
when `Transaction Month` = 'April' then '04-01'
when `Transaction Month` = 'May' then '05-01'
when `Transaction Month` = 'June' then '06-01'
when `Transaction Month` = 'July' then '07-01'
when `Transaction Month` = 'August' then '08-01'
when `Transaction Month` = 'September' then '09-01'
when `Transaction Month` = 'October' then '10-01'
when `Transaction Month` = 'November' then '11-01'
when `Transaction Month` = 'December' then '12-01'
end as "tmth"
from a </pre>Input delimiter Select Comma. Output delimiter Select Comma. Preview results Select this checkbox. - From Available BizApps, select Tabular transformation, and move Extract value to the canvas.
- Drag a link from Advanced query to Extract value.
- Select the Extract value command, and click Edit.
-
In Basic info, enter a name and description to identify
that the command determines the year of the data to download.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Input file Select the Result output from Advanced query. Column index Enter <code>1</code>. Delimiter Select Comma. Row index Enter <code>2</code>. - From Available BizApps, select Tabular transformation, and move another Extract value to the canvas.
- Drag another link from Advanced query to the second Extract value.
- Select the Extract value command, and click Edit.
-
In Basic info, enter a name and description to identify
that the command determines the month to download.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Tabular transformation connector as earlier. Input file Select the Result output from Advanced query. Column index Enter <code>2</code>. Delimiter Select Comma. Row index Enter <code>2</code>.
To download emissions data from Sustain.Life into the Ledger fact table:
- From Available BizApps, select Sustain.Life, and move Execute SQL to the canvas.
- Drag links from both Extract value commands to the Execute SQL command.
- Select the Execute SQL command, and click Edit.
- In Basic info, enter a name and description to identify that the command downloads emission data.
-
Enter the command's properties, and click Save:
Property Value Connector Select the Sustain.Life connector to use. SQL text/file - Enter the query to run: <pre style="width: 600px;"> select a.CUSTOMER_NAME, a.CUSTOMER_ID, a.SCOPE_ID, a.ID, a.ENTRY_ID, a.LOCATION_NAME, a.INTERVAL_START_DATE_MONTH AS START_DATE, a.DATABANK_CATEGORY_NAME, a.GHGP_CATEGORY_NAME, a.CATEGORYL2_DISPLAY_NAME, a.CATEGORYL3_DISPLAY_NAME, a.CATEGORYL4_DISPLAY_NAME, a.AMOUNT_USER_INPUT, a.AMOUNT_USER_UNIT_OF_MEASURE, a.REPORTING_AMOUNT, a.REPORTING_AMOUNT_UNIT_OF_MEASURE, a.REPORTING_SPEND, a.REPORTING_SPEND_UNIT_OF_MEASURE, a.REPORTING_EMISSION as EMISSIONS_MTCO2E, case when a.GHGP_CATEGORY_NAME = 'Purchased electricity'then e.value/1000000 when a.GHGP_CATEGORY_NAME = 'Purchased steam, heat, and cooling' then a.REPORTING_EMISSION end as EMISSIONS_MTCO2E_LOCATION, a.REPORTING_EMISSION_UNIT_OF_MEASURE, b.EMISSION_FACTORS:otherGasFactors[0].gas::string as gas1, b.EMISSION_FACTORS:otherGasFactors[0].formula::string as formula1, b.EMISSION_FACTORS:otherGasFactors[0].gwp::string as gwp1, b.EMISSION_FACTORS:otherGasFactors[0].initialFactorValue::double as gas1_original_factor, b.EMISSION_FACTORS:otherGasFactors[0].initialFactorUnitOfMeasure::string as gas1_original_factor_uom, b.EMISSION_FACTORS:otherGasFactors[1].gas::string as gas2, b.EMISSION_FACTORS:otherGasFactors[1].formula::string as formula2, b.EMISSION_FACTORS:otherGasFactors[1].gwp::string as gwp2, b.EMISSION_FACTORS:otherGasFactors[1].initialFactorValue::double as gas2_original_factor, b.EMISSION_FACTORS:otherGasFactors[1].initialFactorUnitOfMeasure::string as gas2_original_factor_uom, b.EMISSION_FACTORS:otherGasFactors[2].gas::string as gas3, b.EMISSION_FACTORS:otherGasFactors[2].formula::string as formula3, b.EMISSION_FACTORS:otherGasFactors[2].gwp::string as gwp3, b.EMISSION_FACTORS:otherGasFactors[2].initialFactorValue::double as gas3_original_factor, b.EMISSION_FACTORS:otherGasFactors[2].initialFactorUnitOfMeasure::string as gas3_original_factor_uom, b.emission_factors:usedFactor:factorSource:sourceName::string as source_name, b.emission_factors:usedFactor:factorSource:sourceUrl::string as source_url, YEAR(a.INTERVAL_START_DATE_MONTH) as "year", MONTHNAME(a.INTERVAL_START_DATE_MONTH) as "month" from CDB_WORKIVA.APP_EMISSIONS.databank_entry_ledger_cdb a left join CDB_WORKIVA.APP_EMISSIONS.CALCULATION_AUDIT_CDB as b on a.AUDIT_REFERENCE_ID = b.id left join CDB_WORKIVA.APP_EMISSIONS.SECONDARY_METRIC_CDB e on a.entry_id = e.entry_id and a.ghgp_category_name = 'Purchased electricity' and e.kind = 'location-based-amount' and a.row_id in ('row.buildings.electricity.withcontract.grid','row.buildings.electricity.grid') where a.interval_start_date_month=' </pre>
- Select the Value output of the Extract value command for the year.
- Enter a dash (<code>-</code>).
- Select the Value output of the Extract value command for the month.
- Enter a closing quote (<code>'</code>).
- From Chain events, move Run chain to the canvas.
- Drag a link from Execute SQL to Run chain.
- Select the Run chain event, and click Edit.
- In Basic info, enter a name and description to help identify that the chain imports emissions data into the workspace.
-
In Chain to run, select the Primary chain created from the
Load Data to Wdata template.
-
Enter the chain's runtime inputs, and click Save.
Runtime input Value Table ID Select the Ledger table ID variable from Chain. File name Build a unique filename for each dataset created: - Enter <code>Emission_data_</code>.
- Select the Value output of the Extract value command for the year.
- Enter a hyphen (<code>-</code>).
- Select the Value output of the Extract value command for the month.
- Enter <code>.csv</code>.
Data file Select the Result set (CSV) output of the Execute SQL command for the emission data. Load method Select Replace dataset. - From Chain events, move another Run chain to the canvas.
- Drag a link to connect the two Run chain events.
- Select the new Run chain event, and click Edit.
- In Basic info, enter a name and description to help identify that the chain updates the Chain run log sheet of the Sustain.Life Spreadsheet.
-
In Chain to run, select the
3. Sustain.Life log chain created earlier.
-
Enter the chain's runtime inputs, and click Save.
Runtime input Value Range Enter <code>B5:D5</code>. Spreadsheet ID Select the Spreadsheet ID variable from Chain. Control sheet ID Select the Control sheet ID variable from Chain. Chain run log ID Select the Chain run log ID variable from Chain. Refresh month Select the Value output of the Extract value command for the month. Refresh year Select the Value output of the Extract value command for the year.
To download group entry mapping from Sustain.Life into the group mapping dimension table:
- From Available BizApps, select Sustain.Life, and move another Execute SQL to the canvas.
- Drag links from the first Execute command command to the new Execute SQL command.
- Select the new Execute SQL command, and click Edit.
-
In Basic info, enter a name and description to identify
that the command downloads group entry mappings.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Sustain.Life connector as earlier. SQL text/file Enter the query to run: <pre>select * from CDB_WORKIVA.app_emissions.group_entry_mapping_cdb;</pre> - From Chain events, move another Run chain to the canvas.
- Drag a link from the new Execute SQL to the new Run chain.
- Select the new Run chain event, and click Edit.
- In Basic info, enter a name and description to help identify that the chain imports group mappings into the workspace.
-
In Chain to run, select the Primary chain created from the
Load Data to Wdata template.
-
Enter the chain's runtime inputs, and click Save.
Runtime input Value Table ID Select the Group mapping table ID variable from Chain. File name Enter <code>group_mapping.csv</code>. Data file Select the Result set (CSV) output of the Execute SQL command for the group mappings. Load method Select Replace dataset.
To download group hierarchies from Sustain.Life into the group hierarchy dimension table:
- From Available BizApps, select Sustain.Life, and move another Execute SQL to the canvas.
- Drag links from the first Execute command command to the new Execute SQL command.
- Select the new Execute SQL command, and click Edit.
-
In Basic info, enter a name and description to identify
that the command downloads group hierarchies.
-
Enter the command's properties, and click Save:
Property Value Connector Select the same Sustain.Life connector as earlier. SQL text/file Enter the query to run: <pre>select * from CDB_WORKIVA.app_emissions.group_hierarchy_cdb;</pre> - From Chain events, move another Run chain to the canvas.
- Drag a link from the new Execute SQL to the new Run chain.
- Select the new Run chain event, and click Edit.
-
In Basic info, enter a name and description to help identify
that the chain imports group hierarchies into the workspace.
- In Chain to run, select the Primary chain created from the Load Data to Wdata template.
-
Enter the chain's runtime inputs, and click Save.
Runtime input Value Table ID Select the Group hierarchy table ID variable from Chain. File name Enter <code>group_hierarchy.csv</code>. Data file Select the Result set (CSV) output of the Execute SQL command for the group hierarchies. Load method Select Replace dataset. - Click Publish, enter a note about the chain's publication, and click Publish.
Workiva Carbonからデータをダウンロード
Workiva Carbonの炭素会計データでワークスペースを更新する:
-
Chain Builder から、Workiva Carbon refresh チェーンを選択し、Execute をクリックし、Run chain をクリックします。
-
Workiva Carbon リードシート スプレッドシートから、Workiva Carbon マッピングドロップダウン と希望する集計(場所、グループ、GHG カテゴリー/アクティビティ)の入力接続をリフレッシュします。
接続をリフレッシュするには、Connections パネルで接続を選択し、Parameters でリフレッシュするデータの期間を選択し、Apply & refresh をクリックします。
Workiva Carbonのリードシート SpreadsheetのEmissions セクションには、Workiva Carbonからダウンロードしたデータが含まれており、月次データの集計ごとにセクションが分かれている。ESGレポーティングのアウトプットにこのデータを使用する: