複数のExcelファイルをAzure Data FactoryでSQLサーバにインポートしてPower BIで可視化する

船井総研デジタルのよもぎたです。
この記事は株式会社船井総研デジタル Advent Calendar 2022の12日目の記事です。

サマリ

記事のタイトルの通り、複数のExcelファイルをAzure Data FactoryでSQLサーバにインポートしてPower BIで可視化してみようという記事です。
目論見としては、次のような流れを想定しています。

1. データの形式を考える
2. テスト用のExcelファイルを複数用意する。
3. ExcelファイルをBLOBストレージにアップロードする
4. Azure SQL Serverをデプロイして、DatabaseとTableを作成する
5. Azure Data Factory(以下ADFと略します)をデプロイする
6. ADFでBLOBストレージからExcelファイルを読み込んでAzure SQL ServerにインポートするPipelineを作成する
7. 作成したPipelineを実行する
8. Azure SQL Serverにデータがインポートされたことを確認する
9. Power BI DesktopアプリでAzure SQL Serverのデータをもとにレポートを作成する
10. 作成したレポートをPower BIサービスに発行する

実際にやってみる

データの形式を考える

今回は、正直なところ、ここは本題ではありません。最終的にグラフ化できればよいです。そこで、シンプルに1行につき日付と整数の値が2つある3列の形式にすることにしました。日付は2022/1/1～1/31とします。整数は乱数を使います。具体的には、Excelの数式でINT(RAND()*10)とします。

テスト用Excelファイルを用意する

Excelファイルは複数用意したいので、2022/1/1～1/10、1/11～1/20、1/21～1/31の3つの期間に分けて作成します。

例として1つ目のファイルのスクリーンショットは次の通りです。

ExcelファイルをBLOBストレージにアップロードする

ここも本題ではないので簡単に。Azure Portalでも、Azure Storage Explorerでも、その他の方法でも、任意のコンテナを作成してそこにExcelファイルをアップロードします。

Azure SQL Serverをデプロイして、DatabaseとTableを作成する

ここも本題ではないので簡単に。Azure SQLサーバーをSQL認証も有効にしてデプロイして、クライアントIPアドレス（自分のグローバルIPアドレス）からの接続を許可します。Azure Data Studioでデプロイしたサーバーに接続し、下記のSQL文でテーブルを作成します。

CREATE TABLE qiitaac2022
  (date DATE PRIMARY KEY,
   VALUE1 INTEGER,
   VALUE2 INTEGER);

ADFをデプロイする

ようやく本題に近づいてきました。

Azure Portalでリソースの作成を選択し、「分析」カテゴリで「Data Factory」を「作成」します。

「基本情報」で任意のサブスクリプションとリソースグループを選択し、Data Factoryの名前を設定します。名前はMicrosoft Azure 全体で一意でなければならず、名前の大文字と小文字を区別されません。

Git構成、ネットワーク、詳細はデフォルトのままでOKです。タグは必要に応じて付けてください。デプロイが完了したら、リソースに移動して「スタジオの起動」をクリックします。

「Azure Data Factory Studio」が新しいタブで起動します。

このAzure Data Factory StduioでデータをインポートするPipelineを作成していきます。

ADFでExcelファイルを読み込んでAzure SQL ServerにインポートするPipelineを作成する

Azure Data Factory Stduioの左の鉛筆のアイコンをクリックします。鉛筆のアイコンは「Author」でPipelineなどを作成するツールです。

Datasetsの右の数字（下記のスクリーンショットでは0）にカーソルを合わせると「Datasetsアクション」メニューになるので、そこから「新しいデータセット」を選びます。そうすると右側にソースとなるデータストアの選択画面が表示されます。そこで、「Azure」を選び、次に「Azure BLOBストレージ」を選択して続行をクリックします。

続いてデータ形式の選択画面に遷移するので、Excelを選んで「続行」をクリックします。

続いて読み込むファイルが置いてあるストレージアカウントの選択画面に遷移します。ファイルが置いてあるストレージアカウントを選択して、「作成」をクリックします。

続いて読み込むExcelファイルの選択画面に遷移します。ファイルパスでExcelファイルを置いたコンテナを選択します。ディレクトリ配下に置いた場合はディレクトリも選択してください。ファイル名は空欄のままでOKです。
「ワークシートモード」で読み込むデータがあるExcelのワークシートを選択します。少し時間がかかりますが、元ファイルから読み込んでプルダウンメニューになります。「先頭行をヘッダーとして」にチェックを入れます。
最後に、「OK」をクリックします。

もう一つ新しいデータセットを作ります。コピー先となるSQLサーバーです。新しいデータセットの選択で今度は「Azure SQL Database」を選択して「続行」をクリックします。

続いてコピー先のSQLサーバーの選択画面に遷移します。Azureサブスクリプション、サーバー名、データベース名を選択します。認証はSQL認証にして、サーバーをデプロイした際のユーザー名とパスワードを入力します。最後に「作成」をクリックします。

続いてデータをインサートするテーブルの選択画面に遷移します。目的のテーブルを選択して、「OK」をクリックします。

これで、コピー元とコピー先の準備ができました。

AuthorのメニューのPipelinesから、新しいパイプラインを作成します。アクティビティのうち「移動と変換」を展開し、「データのコピー」を右のエリアにドラッグアンドドロップします。

続いて、コピー元（ソース）とコピー先（シンク）のデータセットを設定します。
「データのコピー」をドラッグアンドドロップした下のエリアのメニューで、まずは「ソース」を選択し、「ソースデータセット」のプルダウンメニューからExcelのデータセットを選択します。「ファイルパスの種類」は「ワイルドカードファイルパス」を選択し、ファイル名に*を入力します。

次に「シンク」を選択し「ソースデータセット」のプルダウンメニューからSQLサーバーのデータセットを選択します。

次に「マップ」を選択し「スキーマのインポート」をクリックします。

最後に左上の「すべて発行」をクリックし、右に開くメニューで「発行」をクリックします。

これでパイプラインを実行する準備が整いました。

Pipelineを実行する

パイプラインの上のメニューの「トリガーの追加」をクリックします。そこで開いたメニューから「今すぐトリガー」をクリックします。右に開くメニューで下の「OK」をクリックします。

ポップアップの「パイプラインの実行の表示」をクリックして、実行のモニター画面に遷移します。右下の「状態」が「成功」になれば完了です。

Azure SQL Serverにデータがインポートされたことを確認する

Azure Data Studioでデータがテーブルにインサートされたことを確認します。

SELECT * FROM qiitaac2022;とSELECT COUNT(*) FROM qiitaac2022;を実行し、それぞれ結果を見ます。

Excelのデータがインポートされていることが確認できます。

テーブルに31行データがインサートされている、つまり、３つのExcelファイルすべてからデータを読み込んでいることが確認できます。

Power BI Desktopアプリでレポートを作成する

Power BI Desktopアプリをインストールしていない場合、Windows10/11ならばMicrosoft Storeから入手できます。

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

データの取得元を選択する画面に遷移しますので、「Azure」、「Azure SQL Database」の順に選択して、「接続」をクリックします。

接続先のデータベースサーバーの情報の入力画面に遷移しますので、サーバー名（FQDN）とデータベース名を入力して、「接続」をクリックします。サーバー名はAzure PortalのSQLサーバーの概要画面に記載されています。

SQLサーバーの認証情報を入力する画面に遷移しますので、まず左側の選択肢から「データベース」を選択し、右側でユーザー名とパスワードを入力して、「接続」をクリックします。完了するまで少し時間がかかりました。

データのインポート元となるテーブルの選択画面に遷移しますので、データがインサートされているテーブルを選択し、「読み込み」をクリックします。

これで、データを可視化する準備が整いました。今回は折れ線グラフを作成したいと思います。右の「視覚化」のメニューのなかから、「折れ線グラフ」のボタンをクリックします。中央のエリアに折れ線グラフの枠（領域）が追加されます。大きさは適宜調整してください。

折れ線グラフの枠ができたら、X軸とY軸を何にするかを決定します。今回は、X軸をdateに、Y軸をVALUE1に、第2Y軸をVALUE2とすることにします。「視覚化」の右の「フィールド」の下のテーブル名を展開し、それぞれのカラム名を各軸にドラッグアンドドロップします。

これで、テーブルのデータがグラフ化されました。

ここでファイルを保存します。

レポートをPower BIサービスに発行する

レポートをPower BIサービスに発行するために、まずPower BIサービスにサインアップする必要があります。Power BIサービスにはこちら(https://app.powerbi.com/)からアクセスできます。
サインアップするためには、会社や学校などの組織の、独自ドメインのメールアドレスが必要になります。

サインアップが完了したら、Power BI Desktopに戻ります。
「ホーム」を選択し、「発行」をクリックします。

発行先を選択する画面に遷移しますので、「マイワークスペース」を選択して「選択」をクリックします。

Power BIサービスへの発行が成功したら、「Power BIで'～～'を開く」を選択します。

ブラウザが開き、Power BIサービスでレポートが確認できます。

まとめ

Excelファイルを用意するところから、Power BIサービスのWebでレポートを確認するところまで一連の流れをご紹介しました。いかがでしたでしょうか。

私としては、ほぼコードを書かずにWebUI/GUIでこれだけのことができることに正直感心しました。

それなりのボリュームの記事になりましたが、まだ課題はあります。例えば、ADFで読み込んだ後のExcelファイルの扱いであるとか、ADFの定期実行／イベントトリガーでの実行であるとか、Power BIサービスのレポートの定期更新などです。そういったことも、おいおいご紹介できればと考えております。

最後までお読みいただきありがとうございました。