PCA 商魂・商管とExcel 予算データで予実管理をビジュアライズする方法

はじめに

Microsoft Power BI はマイクロソフト社が提供するBI ツールです。RDBの他にAzureやExcel、Accessなどのファイルデータ、Dynamics、SharePointをはじめとするクラウドサービスなど、様々なデータソースに対応していますが、CData ODBC Driversを使うと、さらに多種多様なデータソースを活用することが可能となります。今回は、PCA商魂・商管DXのデータをPower BIからビジュアライズしてみます。PCA商魂・商管DXは、ピー・シー・エー株式会社が提供するSaaS型の基幹業務ソフトです。

前提

• Microsoft Power BI Desktopがインストール済みである

使用するCData製品

• CData ODBC Driver for PCA Sales

CData ODBC Driver for PCA Salesのインストールと接続設定

インストール後は「DSN構成」画面が立ち上がります。これはインストール先のフォルダにあるConfigureODBC.exeファイルから起動することもできます。

「DSN構成」では、下記の項目を入力する必要があります。

PCA Salesの接続情報	設定項目
クライアントID	OAuth Client Id
クライアントシークレット	OAuth Client Secret
コールバックURL	Callback URL
デフォルトデータ領域	Default Data Area
プロダクトコード	Product Code
Web APIサーバのURL	Url

※コールバックURL、クライアントID、クライアントシークレットについては、PCA Web API 利用準備をご覧ください。

入力後、「接続テスト」ボタンをクリックするとOAuth認証のためアプリケーションの認証画面がブラウザで立ち上がります。ここでは、「サービスユーザーID」と「パスワード」を入力し、サービス認証をクリックします。

続いて、PCA商魂・商管DXのユーザーアカウントの情報を入力し、ログオンをクリックします。

ログオンすると、アクセス許可画面が表示されるので、内容を確認の上、「データの利用を許可する」をクリックします。

ブラウザで「OAuth Authorization Successful!」の画面が表示されれば、認証完了です。

その後、Web APIサーバへの接続が完了すると、下記のようなダイアログが表示さます。最後に、「DSN構成」画面で「OK」をクリックして接続を確立します。

データを取得・加工する

Microsoft Power BI Desktopを起動します。起動すると下記のダイアログが表示されるので「データを取得」をクリックします。

「データの取得」ダイアログが表示されるので、「その他 > ODBC」を選択して「接続」をクリックします。

「ODBCからインポート」ダイアログが表示されるので、「データソース名（DSN）」に「CData PCASales Source」を選択します。

「ナビゲータ」画面が表示されます。左側の階層を開いていくと、テーブルのリストが表示されます。適当なテーブルを選択すると右側にデータのプレビューが表示されますので「読み込み」ボタンをクリックします。今回は、下記のテーブルを読み込みます。

• InputJUC : 各受注案件
• MasterBumon : 会社の部門名
• MasterSms : 商品マスター

データの読み込みが完了すると、下記のような「レポート」のキャンパスの画面が表示されます。画面右側には「フィールド」の欄に選択したテーブルの各フィールド名が表示されます。また画面左にある3つのアイコンから「レポート」、「データ」、「リレーションシップ」の3つの画面を行き来できます。「データ」画面では、読み込んだデータそのものを見ることができます。

先ず、テーブル名を分かりやすくするためリネームします。テーブル名を右クリックして表示されるメニューから「名前の変更」を選択して変更します。

• InputJUC　→　受注案件
• MasterBumon　→　部門
• MasterSms　→　商品

データを読み込んだ後は必要に応じてデータを加工します。ここでは「受注案件」テーブルを加工します。「クエリを編集」をクリックし「クエリエディター」を開きます。クエリエディターでは左側のペインで「受注案件」を選択した状態で「カスタム列」をクリックします。

下記のようなウィンドウが開きますので「新しい列名」には“受注日”、式は下記のコードをコピー＆ペーストして「OK」をクリックください。受注案件を月単位で集計したいため受注日の情報が必要となりますが「受注案件」テーブルが元々持っている受注日フィールド、「InputJUCH_Juchubi」は数値データとなっているため日付情報として認識されません。そこで新しく「受注日」フィールドを追加します。

= Date.From(Text.From([InputJUCH_Juchubi]))

同じ手順で“商品コード”フィールドを追加します。式は下記のとおりです。元データは「InputJUCDList」フィールドの中に商品コードの値を持っていますが、XML形式で商品コードとして使えないため、下記の式で先頭の商品コードを1つ抽出します。

Text.Middle([InputJUCDList], Text.PositionOf([InputJUCDList],"</SyohinCode>") - 4, 4)

その後は下記のように右端にカラムが追加されます。「閉じて適用」をクリックして「クエリエディター」を閉じてください。

次に、受注案件を月単位で集計するため使う日付テーブルを追加します。先ず、「モデリング」タブのメニューにある「新しいテーブル」をクリックします。下記のような画面が開きますので、「テーブル =」と書かれたエディアの中に、下記の式をコピー＆ペーストしてください。

dateTable = var FullCalendar = ADDCOLUMNS(CALENDAR("2017/1/1","2018/12/31"),"EveryDay",FORMAT([Date],"yyyy/MM/dd"),"Year-Month",LEFT(FORMAT([Date],"yyyy/MMdd"),7))
return 
SUMMARIZE(FullCalendar,[EveryDay],[Year-Month])

数式入力後は、下記のようにテーブルが作成されます。

最後に、下記のような予算データをExcelファイルから読み込みます。先ほどと同様に「データを取得」から「Excel」を選択しExcelファイルを読み込みます。テーブル名は「Budget」とします。

Excelファイル読み込み後は「クエリエディター」を開き、テーブルを加工します。1番下の行と1番右の列は不要なので削除し、また「1行目をヘッダーとして使用」をクリックします。

次に「部門」カラムを選択後に右クリックし、表示されるメニューから「その他の列のピボット解除」をクリックします。

下記のようにテーブルが変換されます。「属性」カラムが作られるので、分かりやすいように「年月」とリネーム後「閉じて適用」をクリックし、「クエリエディター」を閉じます。

リレーションを設定する

ここで、これまで追加・加工したテーブルのリレーションを設定します。テーブルの適当なフィールドを、もう1つのテーブルの適当なフィールドにドラッグ＆ドロップすることで、リレーションの設定を行います。下記のようにリレーションを設定します。

• 「部門」テーブルの「BumonCode」フィールド　→　「受注案件」テーブルの「InputJUCH_BumonCode」フィールド
• 「受注案件」テーブルの「受注日」フィールド　→　「dateTable」テーブルの「EveryDay」フィールド
• 「Budget」テーブルの「年月」フィールド　→　「dateTable」テーブルの「EveryDay」フィールド
• 「受注案件」テーブルの「商品コード」フィールド　→　「商品」テーブルの「SyohinCode」フィールド

メジャーを追加する

「データ」画面で適当なテーブルを選択した状態で「モデリング」タグから「新しいメジャー」をクリックすると、エディターが有効となる（デフォルトは“メジャー =”）ため数式を入力します。
※数式の左辺が、そのままメジャーの名称となります。

各テーブルに追加するメジャーは下記のとおりです。

• Budgetテーブル

予算総額 = SUM(Budget[値])

• 受注案件テーブル

下記の3つのメジャーを追加します。

最終受注日 = MAX('受注案件'[受注日])

受注総額 = SUM('受注案件'[InputJUCT_KingakuGokei])　// InputJUCT_KingakuGokei は受注金額のフィールドです

予算達成率 = '受注案件'[受注総額] / 'Budget'[予算総額]

ビジュアライズする

「レポート」画面に移動し、適当な位置に「積み上げ縦棒グラフ」を追加し、下記のように各項目に適当なフィールドをドラッグ＆ドロップします。

• 軸　：　「dateTable」テーブルの「Year-Month」フィールド
• 凡例　：　「部門」テーブルの「BumonMei」フィールド
• 値　：　「受注案件」テーブルの「InputJUCT_KingakuGokei」フィールド

グラフは必要に応じて「並べ替え」を行ってください。下記のように「並べ替え条件」で「Year-Month」を選択すると、グラフの棒が時系列にソートされます。このグラフでは、毎月の実績が部門で内訳されて可視化されます。

続いて、「集合縦棒グラフ」を追加します。各項目のフィールドは下記のとおりです。

• 軸　：　「dateTable」テーブルの「Year-Month」フィールド
• 値　：　「Budget」テーブルの先ほど追加した「予算総額」メジャーと「受注案件」テーブルの「InputJUCT_KingakuGokei」フィールド

このグラフでは毎月の予算と実績を比較することができます。

さらに、3つの「カード」を追加します。先ほど追加したメジャー「最終受注日」、「受注総額」、「予算達成率」を選択します。

「受注総額」と「予算達成率」の書式を変更します。データ画面で書式を変更するメジャーを選択し、「モデリング」タブの「書式設定」メニューで「書式」を選択します。「受注総額」の書式は「通貨全般」、「予算達成率」は「パーセンテージ」を選択します。

最後に「テーブル」を追加し、「値」の項目に下記の項目を追加します。

• 「受注案件」テーブル

  • 「InputJUCH_Id」フィールド　※受注案件のID
  • 「受注日」フィールド
  • 「TokuisakiMei1」フィールド　※顧客名
  • 「InputJUCT_KingakuGokei」フィールド　※受注金額
  • 「商品コード」フィールド

• 「商品」テーブル

  • 「SyohinMei」フィールド　※商品名

また、このテーブルには、全てのレコードではなく、金額が大きい案件のみを表示したいのでフィルターを追加します。下記のように「ビジュアルレベルフィルター」の中から金額のフィールドである「InputJUCT_KingakuGokei」に「次の値より大きい」を選択し、適当な値を設定します。

視覚化コンポーネントの見出しや色などを設定し、最終的には下記のようなダッシュボードが完成しました。

必要に応じて左上の保存ボタンをクリックして作成したレポートを「Power BI ファイル（*.pbix）」として保存してください。今回は、Microsoft Power BI Desktopを使ってPCA商魂・商管DXのデータをビジュアライズしてみました。