ポートフォリオ集

目次

    【システム連携ポートフォリオ】 TROCCO × Snowflakeで高速に構築するデータ分析基盤のご紹介 〜TROCCOのHTTPコネクタで実現するBoardからSnowflakeへのノーコードでのデータ連携〜

    2024.09.18

    目次

      今回は、最新のサービスを組み合わせることで、ノーコードで迅速にシステムを構築できる方法をご紹介いたします。特に、TROCCOとSnowflakeの組み合わせに焦点を当て、データ連携システムと分析アプリケーションを効率的に構築する方法を、具体的な設定例を交えてお伝えします。

      システム連携と分析ダッシュボード：全体像

      まず、今回構築するシステムの全体像をご紹介いたします。下図のように、SalesforceとBoardのデータをTROCCOを使用してSnowflakeに連携し、Snowflake内で集約したデータをTableauダッシュボードとして可視化し、収益性の分析を行います。

      特筆すべき点は、TROCCOが現時点でBoardへの標準コネクタを提供していないにもかかわらず、ノーコードでシステム全体を構築できることです。HTTPコネクタという拡張機能を活用することで、標準コネクタが存在しないSaaSとのデータ連携も可能となり、結果として異なるシステムのデータを容易に統合し分析できています。

      本記事では、この柔軟なデータ統合と分析の実現方法に焦点を当てて解説いたします。

      【ユーザーのお悩み】分析基盤は作りたいけれども、標準で対応するコネクタが無く、サイロ化された環境で効率良く分析ができない

      近年、多くの企業がそれぞれの業務に最適なSaaSを導入し、業務効率化を図っています。営業管理にSalesforce、プロジェクト管理にBacklog、経理管理にfreeeなど、各部門が最適なツールを選択し導入することで、個々の業務プロセスは大幅に改善されてきました。

      　

      しかし、その一方で新たな課題が浮上しています。それは、データのサイロ化です。各SaaSに散らばったデータを統合し、全体像を把握することが困難になってきているのです。

      例えば、弊社では以下のようなシステム構成を採用しています：

      　

      • Salesforce:
        • 案件管理
        • 受注後、プロジェクトを作成しTeamSpirit系のオブジェクトに連携
        • 案件全体の金額を管理
        • 月ごとの売上（請求）金額は管理対象外
      • TeamSpirit:
        • プロジェクトの稼働管理
        • 各メンバーは Salesforce 内の TeamSpirit を使用してプロジェクトの稼働を管理
      • Board:
        • 請求管理
        • お客様への請求書送付
        • 請求金額を freee 会計に連携
        • 正確な請求金額の管理

      　

      これらのサービスは、それぞれ単独では素晴らしい機能を提供しています。Salesforceは顧客管理と営業プロセスの可視化に優れ、TeamSpiritは従業員の勤怠管理とプロジェクトごとの工数管理を効率化し、Boardは請求書の発行からfreee会計への連携までのオペレーションを効率化しています。

      　

      しかし、データが分断されていることで、新たな課題が生まれています。プロジェクトの収益性管理における不都合です。具体的に説明しましょう。Boardで正確な請求金額（売上金額）が管理され、TeamSpiritでプロジェクト稼働コストが管理されていますが、この2つのデータが繋がっていません。そのため、プロジェクトの実際の収益性をリアルタイムで把握することが困難になっています。

      保有しているデータから考えれば、以下のような構成で管理ができるはずです：

      月々の請求金額 vs (プロジェクトメンバーの時間単価 × 稼働時間)の合計金額
      → 利益率が想定の閾値になっているかどうか

      この計算を行うことで、各プロジェクトの収益性をタイムリーに把握し、問題があれば迅速に対応することができるはずです。例えば、あるプロジェクトの利益率が想定を下回っている場合、以下のような対応が可能となります：

      　

      1. プロジェクトマネージャーに状況を確認
      2. 必要に応じて追加の請求交渉を行う
      3. 今後の工数見積もりの精度を向上させる
      4. 似たようなプロジェクトの見積もり時に参考にする

      　

      しかし、現実にはどうでしょうか。プロジェクト数が多くなると、データが繋がっていないため、タイムリーな集計ができず、日々の業務の中で活用することが難しくなってしまいます。分析を行う際にはスプレッドシートで大量のデータを手作業で突合せて計算する…そんな非効率な作業を強いられているケースも少なくありません。

      　

      この問題を解決するための一つの方法として、APIを活用したデータ連携が考えられます。例えば、BoardもSalesforceもAPIを提供しているため、Heroku等の中継サーバーを構築してプログラムを書けば連携は可能です。しかし、ここで新たな課題が浮上します。

      それは、保守性の問題です。APIを使ってカスタムの連携プログラムを書いたとしても、ビジネスの変化に伴いデータ構造や連携ロジックを変更する必要が出てくる可能性が高いです。その都度、プログラムを修正し、テストし、デプロイする…この作業を繰り返すことは、開発リソースの観点からも望ましくありません。

      変化の多いビジネスにおいて、なるべくコードを書かずに保守性を上げたいというのは、開発会社である弊社でも同じ悩みです。ビジネスロジックの変更に柔軟に対応でき、かつ安定性も担保できるシステムを構築することは、長期的に見れば非常に重要な観点です。

      これらの課題を解決するためには、以下のような要件を満たすソリューションが必要となります：

      1. 複数のSaaSのデータを簡単に統合できること
      2. データの鮮度を保ちつつ、リアルタイムな分析が可能であること
      3. 最小限のコーディングで柔軟なカスタマイズが可能であること
      4. 将来的なビジネス変更にも容易に対応できること

      これらの要件を満たすソリューションとして、本稿では「TROCCO × Snowflake」の組み合わせをご紹介いたします。以降のセクションでは、このソリューションの全体像と各コンポーネントの役割について詳細に解説いたします。

      【解決策】システム全体像のご紹介。サーバを別に構築しなくても立派な分析アプリケーションは構築できる

      前述の課題を解決するための、これらのツールを組み合わせたシステムの全体像と分析アプリケーションを以下に示します：

      具体的な構成要素とデータの流れは以下の通りです：

      1. SalesforceのデータをTROCCOのSalesforceコネクタを使用してSnowflakeに連携
      2. BoardのデータをTROCCOのHTTPコネクタを使用してSnowflakeに連携
      3. Snowflake内でViewを作成して、Salesforc・TeamSpirit・Boardのデータを結合
      4. Tableauを使用して、Snowflake内のデータを元にダッシュボードを構築

      　

      特に注目していただきたいのは、データ中継サーバーや分析アプリケーションのためのサーバーの構築を行っていない点です。従来のアプローチでは、以下のような作業が必要でした：

      • データ連携用のサーバーを構築し、管理する
      • APIを呼び出すためのプログラムを作成し、定期実行の仕組みを整える
      • 分析用のデータベースサーバーを構築し、管理する
      • 分析アプリケーション用のWebサーバーを構築し、管理する

      　

      これらの作業には、相当な時間と専門知識が必要でした。しかし、今回のアプローチでは、これらのインフラ構築や複雑なプログラミングを最小限に抑えることができています。

      　

      では、このシステムの核となる各コンポーネントの特徴と利点について、もう少し詳しく見ていきましょう。

      TROCCOの強力な機能

      TROCCOは、ノーコードETLサービスとして、多様なSaaSサービス間のデータ連携を実現しています。特に注目すべき機能の１つがHTTPコネクタです。この機能により、専用コネクタが提供されていないSaaSとも連携が可能となり、今回のケースではBoardのデータをSnowflakeへ連携する際に活用しました。

      HTTPコネクタの強みは、単なる定期的なHTTPリクエストの実行にとどまりません。OAuth2.0などの標準化された認証方式に対応しており、大量データを複数回に分けて取得する必要がある場合のページング処理にも対応しています。これらの機能により、HTTPコネクタはさまざまなSaaSサービスとの連携を可能にし、その適用範囲を大きく広げています。

      Snowflakeの特徴と利点

      Snowflakeは、クラウドベースのデータウェアハウス（DWH）として、近年急速に注目を集めているサービスです。そのDBとしての性能の高さはさることながら、Snowflakeの役割は単なるデータストレージにとどまらず、包括的なデータ分析プラットフォームとして機能し、企業のデータ戦略の中核を担います。

      TROCCOのHTTPコネクタを活用したデータ連携のご紹介

      ここでは、TROCCOの設定について実際にご紹介します。特に、標準コネクタが用意されていないシステムとの連携方法に焦点を当てます。

      Salesforceからの連携

      まず、SalesforceからTROCCOへの連携について説明します。SalesforceとSnowflakeの両方に専用のコネクタが準備されているため、とても簡単に設定することができます。コーディングは一切不要で、GUIの操作だけで設定が可能です。

      BoardからTROCCOへの連携

      次に、より複雑なケースとして、BoardからTROCCOへの連携について説明します。Boardには専用のコネクタがありませんが、TROCCOのHTTPコネクタを使用することで連携が可能です。

      HTTPコネクタには以下のような強力な機能が搭載されています：

      1. OAuth2.0認証への対応
      2. 多数のデータを連携するための、ページング設定機能
      3. 連携するスキーマの自動表記機能
      4. JSONとRDBMSの表形式のスキーマ間の差を埋めるための画面設定

      これらの機能により、多くのREST APIに対応することができます。それでは、各機能について詳しく見ていきましょう。

      OAuth2.0認証への対応

      多くのSaaS APIがOAuth2.0認証に対応しています。TROCCOのHTTPコネクタはこの認証方式を使用してSaaSに対して接続することが出来るため、多くのSaaSに連携することが可能です。

      ページング設定機能

      多くのREST APIは、大量のデータを返却する場合、複数回のリクエストを通じてデータを分割して返します。例えば、1回のリクエストで200件まで取得可能な場合、1,000件のデータを取得するには5回のリクエストが必要となります。Salesforce、Backlog、Boardなども同様の形式のREST APIを採用しています。以下にBoardの例を示します：

      TROCCOのHTTPコネクタは、このようなページング設定に対応できる設定を持っています。具体的には以下のような設定が可能です：

      • ページングのパラメータ名
      • リクエスト回数
      • ページングのパラメータ初期値
      • ページングのパラメータの増分

      これらの設定により、大量のデータも漏れなく取得することが可能になります。
      注意すべき点が1つあります。上記のスライドでは、プログラム内で動的に「必要なページ数」を算出していましたが、この動的な計算だけは困難です。そのため、TROCCOに設定するリクエスト回数は、ビジネス要件を考慮した上で十分に大きな固定値を設定する必要があります。

      今回実際に設定したBoardのプロジェクトデータの転送
      HTTPコネクタのページング設定

      スキーマの自動表記機能

      APIから返却されるJSONデータの構造を解析し、自動的にスキーマを推測する機能があります。これにより、手動でスキーマを定義する手間が大幅に削減されます。

      TROCCOは自動的に以下のようなスキーマを推測します：

      この推測結果を基に、必要に応じて手動で調整することができます。

      JSON形式と表形式の差を埋める機能

      JSONデータの構造と、RDBMSの表形式では、データの表現方法に違いがあります。TROCCOはこの差を埋めるための機能を提供しています。

      1. ルートパスがどこになるのか： APIレスポンスの中で、実際に必要なデータがどの階層にあるかを指定できます。例えば、レスポンスが {"data": [...]} という構造の場合、$.data と指定することで、data 配列の中身だけを取り込むことができます。
      2. ネストされたオブジェクトの展開： JSONの階層構造を、フラットな表形式に変換できます。

      これらの機能により、複雑なJSON構造のAPIレスポンスでも、簡単にRDBMSの表形式データに変換することができます。

      Boardの請求データをSnowflakeに連携する実例

      それでは、実際にBoardの請求データをSnowflakeに連携する設定を行ってみましょう。以下は、その具体的な手順です：

      　

      新規に転送設定を作成： TROCCOの画面から転送設定を新規作成し、転送元として「HTTP」を選択します。

      HTTPコネクタの設定：BoardのAPIリファレンスを参照し、HTTPコネクタを設定します。Boardの認証方式はOAuth2.0ではなく、以下の方法を使用します：

      　

      • AuthorizationヘッダにAPIトークンを設定
      • x-api-keyヘッダにAPIキーを設定

      したがって、OAuth2.0の「利用する」オプションにはチェックを入れずに設定を進めます。Boardの請求データ取得用のURLを入力します。

      続いてJSONのどこに対象データがあるのかを指定し、Boardの仕様に沿ったページング設定を行います。

      最後に、認証用のHTTPヘッダの設定をします。

      データの確認と連携項目の対応の設定 : 先程の認証や設定が正しければ、プレビューでの連携データの確認と連携項目の対応の設定画面に進みます。

      　

      正しくデータが取得できているかどうかを確認します。

      次に、連携先テーブルとのマッピング定義を行います。この段階で、JSONデータの階層構造内にある深層のデータを取得するための設定も行います。

      連携を実行する : 設定が完了次第、初期連携を行って正しく連携できるかを検証します。

      　

      この例では、3,312件のBoard請求データが連携されました。1ページあたりのレコード数が100件に設定されていたため、複数ページにわたるデータが正しく取得できたことが確認できます。

      　

      Snowflake内に連携されたデータを使用して、収支を確認できるViewを作成する

      ここでは、Snowflakeに連携されたSalesforceとBoardのデータを結合し、分析用のViewを作成する過程をご紹介いたします。

      以下は、今回使用するサンプルダッシュボードです。このダッシュボードは、プロジェクト単位および月単位の利益率を表示しています。これらの情報を提供するViewの作成方法について、以下で詳しく説明いたします。

      データモデルの説明

      現状のデータモデルは、大きく3つに分かれます。以下では連携元のシステムのデータでの表記をしています：

      1. Salesforceの取引先・商談関連：
        • 取引先（Account）
        • 商談（Opportunity）
      2. TeamSpiritのプロジェクト・稼働関連：
        • プロジェクト（tsl__PcProject__c）
        • プロジェクト明細（tsl__PcProjectDetail__c）
        • 作業項目明細（tsl__PcWorkItemDetail__c）
        • 工数実績（tsl__PcManHourResult__c）
        • リソース（tsl__PcResource__c）
        • 勤怠社員（teamspirit__AtkEmp__c）
      3. Boardの請求関連：
        • 顧客（Client）
        • 商談（Project）
        • 請求（Invoice）

      Salesforceの主従関係項目や参照関係項目は、Snowflakeには18桁のID項目として連携されます。そのため、Salesforce組織のデータモデル関係をそのままJOIN句で結合すれば、元のオブジェクト間の関係性は維持されます。

      しかし、SalesforceのオブジェクトとBoardのデータモデルを関連付けるには工夫が必要です。弊社では、Salesforceの商談レコードにBoardの案件番号を必ず保存するようにしています。この情報をJOIN句に使用することで、Snowflake内のテーブルを適切に結合することが可能となります。

      データをViewとして結合する

      Tableauでの分析用に、これらのデータを結合させます。具体的には、以下のViewを作成します：

      • MONTHLY_REVENUE_COST：プロジェクトごと・月ごとの請求金額とコスト金額の集計結果

      上述したキーを用いて結合を行うため、これらのViewの定義は以下のようになります：

      create or replace view PL_ANALYTICS.VIEWS.MONTHLY_REVENUE_COST(
	SF_ACCOUNT_ID,
	SF_ACCOUNT_NAME,
	SF_OPPORTUNITY_ID,
	SF_OPPORTUNITY_NAME,
	SF_OPPORTUNITY_CLOSE_DATE,
	BOARD_PROJECT_ID,
	SF_OPPORTUNITY_LEAD_SOURCE,
	BASE_MONTH,
	TOTAL_REVENUE,
	TOTAL_COST
) as

WITH 
MONTHLY_REVENUE AS ( -- Board請求を集計(プロジェクト毎、月毎)
 SELECT
  BOARD_PROJECT.ID AS BOARD_PROJECT_ID,
  DATE_TRUNC('MONTH', BOARD_INVOICE.INVOICE_DATE) AS INVOICE_MONTH,
  SUM(BOARD_INVOICE.TOTAL) AS TOTAL
 FROM
  PL_ANALYTICS.BOARD.PROJECT AS BOARD_PROJECT
 LEFT JOIN
  PL_ANALYTICS.BOARD.INVOICE AS BOARD_INVOICE
 ON
  BOARD_PROJECT.ID = BOARD_INVOICE.PROJECT_ID
 WHERE
  BOARD_PROJECT.ORDER_STATUS NOT IN (8, 9) --失注もしくは除外ステータスでない
 GROUP BY
  BOARD_PROJECT_ID,
  INVOICE_MONTH
),
MONTHLY_COST AS ( -- 稼働を集計(プロジェクト毎、月毎)
 SELECT
  OPPORTUNITY.BOARD_PROJECT_NO__C AS BOARD_PROJECT_ID,  
  DATE_TRUNC('MONTH', PC_MAN_HOUR_RESULT.TSL_DATE__C) AS WORK_MONTH,
  SUM(ATK_EMP.UNIT_PRICE__C * PC_MAN_HOUR_RESULT.TSL_MAN_HOURS_ACTUAL_INPUT__C) AS TOTAL
 FROM
  PL_ANALYTICS.SALESFORCE.OPPORTUNITY AS OPPORTUNITY
 LEFT JOIN
  PL_ANALYTICS.SALESFORCE.PC_PROJECT AS PC_PROJECT
 ON
  OPPORTUNITY.ID = PC_PROJECT.TSL_OPPORTUNITY_ID__C
 LEFT JOIN
  PL_ANALYTICS.SALESFORCE.PC_PROJECT_DETAIL AS PC_PROJECT_DETAIL
 ON
  PC_PROJECT.ID = PC_PROJECT_DETAIL.TSL_PROJECT_ID__C
 LEFT JOIN
  PL_ANALYTICS.SALESFORCE.PC_WORK_ITEM_DETAIL AS PC_WORK_ITEM_DETAIL
 ON
  PC_PROJECT_DETAIL.ID = PC_WORK_ITEM_DETAIL.TSL_PROJECT_DETAIL_ID__C
 LEFT JOIN
  PL_ANALYTICS.SALESFORCE.PC_MAN_HOUR_RESULT AS PC_MAN_HOUR_RESULT
 ON
  PC_WORK_ITEM_DETAIL.ID = PC_MAN_HOUR_RESULT.TSL_WORK_ITEM_DETAIL_ID__C
 LEFT JOIN
  PL_ANALYTICS.SALESFORCE.PC_RESOURCE AS PC_RESOURCE
 ON
  PC_MAN_HOUR_RESULT.TSL_RESOURCE_ID__C = PC_RESOURCE.ID
 LEFT JOIN
  PL_ANALYTICS.SALESFORCE.ATK_EMP AS ATK_EMP
 ON
  PC_RESOURCE.TSL_EMP_ID__C = ATK_EMP.ID
 WHERE
  ATK_EMP.ID IS NOT NULL
 GROUP BY
  BOARD_PROJECT_ID,
  WORK_MONTH
)

SELECT
 -- 取引先 (Account)
 ACCOUNT.ID AS SF_ACCOUNT_ID,
 ACCOUNT.NAME AS SF_ACCOUNT_NAME,
 -- 商談 (Opportunity)
 OPPORTUNITY.ID AS SF_OPPORTUNITY_ID,
 OPPORTUNITY.NAME AS SF_OPPORTUNITY_NAME,
 OPPORTUNITY.CLOSE_DATE AS SF_OPPORTUNITY_CLOSE_DATE,
 OPPORTUNITY.BOARD_PROJECT_NO__C AS BOARD_PROJECT_ID,
 OPPORTUNITY.LEAD_SOURCE__C AS SF_OPPORTUNITY_LEAD_SOURCE,
 -- Board請求
 MONTHLY_REVENUE.INVOICE_MONTH AS BASE_MONTH,
 MONTHLY_REVENUE.TOTAL AS TOTAL_REVENUE,
 -- 稼働コスト
 MONTHLY_COST.TOTAL AS TOTAL_COST
FROM
 PL_ANALYTICS.SALESFORCE.ACCOUNT AS ACCOUNT
LEFT JOIN
 PL_ANALYTICS.SALESFORCE.OPPORTUNITY AS OPPORTUNITY
ON
 ACCOUNT.ID = OPPORTUNITY.ACCOUNT_ID
LEFT JOIN
 MONTHLY_REVENUE
ON
 OPPORTUNITY.BOARD_PROJECT_NO__C = MONTHLY_REVENUE.BOARD_PROJECT_ID
LEFT JOIN
 MONTHLY_COST
ON
 MONTHLY_REVENUE.BOARD_PROJECT_ID = MONTHLY_COST.BOARD_PROJECT_ID
 AND MONTHLY_REVENUE.INVOICE_MONTH = MONTHLY_COST.WORK_MONTH
WHERE
 MONTHLY_REVENUE.BOARD_PROJECT_ID IS NOT NULL;

      構築したビューのデータを確認します(デモデータを使用しており、実際の業務データではありません)

      MONTHLY_REVENUE_COSTのViewでは、プロジェクトごと・月ごとの売上とコストが同一行に表示されています。これにより、以下の計算式で収益性を算出することが可能となりました：

      • 利益 = 売上 – コスト
      • 利益率 = (売上 – コスト) / 売上

      これらのViewをTableauから参照することで、プロジェクトの収益性やリソースの稼働状況を容易に分析できるようになります。

      Tableauダッシュボードの作成

      このSnowflakeのViewをTableauから参照させ、目的のダッシュボードを構築していきます。
      今回は例としてTableau CloudからSnowflakeに接続をさせてみます。

      先程と同じデータが接続できました。
      こちらを使用すれば、目的のダッシュボードを作れるようになります。

      まとめ

      本稿では、TROCCOのHTTPコネクタとSnowflakeを活用した、ノーコードでのデータ分析基盤構築についてご紹介いたしました。近年、多様なSaaSが連携可能となり、ノーコードでデータ収集できる範囲が急速に拡大しています。Praztoは、このようなデータ集計基盤の構築を得意としております。ご興味がございましたら、お気軽にお問い合わせください。

      近日中に、Snowflakeの特有機能に焦点を当てた記事を公開予定です。ぜひご期待ください。

      この記事を書いた人

      代表取締役芳賀 怜史

      早稲田大学卒業後、SIerでエンジニアとしてキャリアをスタート。その後、外資系マーケティング会社、Salesforceゴールドパートナー企業と、3社での経験を通じて一貫してエンジニアリング技術を磨き、多くの顧客の課題解決に従事。2019年、Salesforceを中心とした導入支援・開発事業を行う株式会社Praztoを創業。2022年にSalesforceパートナーのルーキー企業として最優秀賞「Emerging Partner of the Year – Consulting –」を受賞し、年商3.7億円にまで事業を成長させる。「Technology and Design are True」というビジョンのもと、最適なシステムを提供するプロフェッショナルチームを統率している。

      他のオススメ記事

      【システム連携ポートフォリオ】Herokuとは？SalesforceとHerokuの連携に関してのよくある構成と実際の構築例のご紹介

      2024.09.08

      • Heroku
      • システム連携ポートフォリオ

      【システム連携ポートフォリオ】SalesforceからHerokuデータを閲覧する2つの方法：Heroku ConnectとSalesforce Connect

      2024.10.20

      • Heroku
      • システム連携ポートフォリオ
      一覧トップへ戻る