Power BI のデータソースって業務だと毎月更新されますよね？
はい、Power BI Tips のお時間です。
以前 Power BI 勉強会で
「Power BI のレポート作成を自動化できないんでしょうか？毎月 Power BI でレポートを作成しなければならず、困っています」
という質問をいただきました。
この質問への直の回答ではないのですが、もしかしたら解になるかもと思い、今回の記事を書こうと思いました。
まぁ簡単に言うとテーマは「自動化」ということになります。
まず、いただいた質問ですが、おそらく Power BI の使い方を間違えています。というか、たぶん、Power BI Desktop を Excel の代わりに使っていると思われます。
別に悪いことではないですし、ライセンス違反でもありませんので、イイっちゃあイイんですが、うん、やっぱり気になります。
つまり、それって本当の Power BI の使い方ではないよーってこと。
Power BI Desktop で満足しているようじゃ、甘いのです。
Power BI = Power BI Desktop っていうのは間違いです。
正確には Power BI というサービスの中でレポート作成ツールが Power BI Desktopであり、その他にもいっぱいあります というのが正しいです。
- Power BI Service
- Power BI Mobile
- Power BI dataflows
- Power BI Apps
- Power BI AI
- etc...
フォルダーコネクタってのがあります
話を元に戻しまして、Power BI Desktop にはたくさんのコネクタがあります。
最近数えていなかったので、ざっと数えたところ 133 個ありました。（2020.03.27 現在)
で、この中に フォルダー というコネクタがあります。
Power BI Desktop は Windows PC にインストールするデスクトップアプリケーションなので、当然レポートを作成する際は Windows 上で作業をすることになります。
そのマシンからアクセス可能な（ネットワーク越しも含む） フォルダー に接続することができるコネクタです。
フォルダーに接続することで、ファイルの一覧やファイルの属性が取れるのはもちろんのこと、ファイルの内容も読めます。
というか、ファイルの内容を読むことの方が本来の目的です。
今回の内容はこちらからダウンロードできます
ご紹介する方法のために一連のファイルは こちら からダウンロードできます。
ダウンロードした ZIP ファイルを解凍して、上記の画像のように Cドライブ に Work というフォルダを作成して C:\Work にコピーしてください。
フォルダコネクターの動きを確認してみる
例えばこんなフォルダーがあったとします。
C:\Work\SalesSampleReport\Data というフォルダに３つの CSV ファイルがあります。
CSV ファイルの中身はこんな感じです。↑ は Excel で見やすくしています。
飲食店の POS データのイメージです。
このフォルダに Power BI Desktop から接続してみます。
[データを取得] - [フォルダー] - [接続] です。
フォルダーパスを入力して OK
[データの変換] をクリック
はい、いかがでしょう？
これが指定されたフォルダーにあるファイル一覧です。
ファイル名、拡張子、最後にアクセスされた日、最終更新日、作成日、属性、フォルダーパスを持っています。
そして一番左の [Content] 列がファイルのバイナリです。通常、ファイルの中身が欲しい場合、これがターゲットになります。
ちなみに業務で使用している共有フォルダーだと、ひとつのフォルダーに複数の種類のドキュメントが保存されていることが珍しくないかと思います。
Excel、Word、PowerPoint、CSV、ZIP、TXT、JPEG などなど。そういったフォルダでも特定の条件で読み込むファイルを絞ることができます。一番わかりやすいのは、拡張子です。
[Extension] 列があるのでそこで絞り込んでしまえば、例えば Excel のみ相手にすることができます。あるいはファイル名に特徴があるなら [Name] 列を使用して、絞り込むのもありですね。
さて、ファイルの中身を読んでみましょう。
[Content] 列の右上のボタンをクリックしてください。（このボタンを [ファイルの結合] って呼びます）
はい、見事なまでの文字化けですねー。
この画面は左上に書いてある通り、フォルダーにある最初のファイルをサンプルファイルとして、開いてみたところ、最初の 200 行はこんな感じだよーって、解析して定義を読んでくれた結果です。日本語環境だとデフォルトで Shift-JIS でファイルを読みます。
今回の CSV は UTF-8 で保存されています。こういう場合でも焦ることなく、文字コードを指定してあげれば、ちゃんと読めます。
ね、今度はきちんと読んでくれましたね。
はい、[OK] ボタンをクリックしましょう。
試しに一番左の [Source.Name] の右上▽を開くと、きちんと３つの CSV ファイルが読み込まれていることが確認できます。
ではとりあえず、左上の [閉じて適用] をクリックしましょう
Power BI Desktop で [データ] ペインを開くと３つのファイルが読み込めていることがわかります
レポートペインを開き、行数をカウントするメジャーを作成しておきましょう。テーブル名の右側 [...] - [新しいメジャー] - [数式バー] に以下の様に入力してください
行数 = COUNTROWS(Data)
キャンパスに [カード] を置いて、作成したメジャー [行数] を指定します
はい、3000行ですね。これが３つの CSV ファイルの合計行数ということになります。
フォルダーにファイルを追加すると…？
2018年4月～6月までの３つの CSV ファイルがあったフォルダに2018年7月のファイルを追加しました
業務ではこういうことよくありますよね。特定の場所にある種類のファイルが月別や週別に追加されていく。Excel でマクロを組んだり、データを切り貼りして、報告書としてのレポートを作成する。。。
でも現代はこういうルーティンワークは自動化できる時代です。Power BI Desktop でももちろん可能です。その為にフォルダーコネクタを使ったわけです。
Power BI Desktop に戻りまして [更新] ボタンを押します。
クリックすると…
はい、4000行に増えました。
同じことを完成したレポートでやるとどうなるのか？
C:\Work\SalesSampleReport の中にある sales_sample_report.pbix ファイルで以下を試すことができます。
■更新前
■更新後
総売上高、総会計数、各種グラフ、それぞれの数値がひと月分増えていることが分かるかと思います。
これだけでも非常に便利ですよね。
ファイル置き場を決めて、フォルダーコネクタを使用して、Power BI Desktop で一度レポートを作成してしまえば、もう毎月レポートを作成する必要はないのです。
でも感動するのはまだ早いのです。Power BI Desktop で手動で更新しているところが、ナウくないですね。
本当の意味で自動化する前に Power BI のライセンス、持っていますか？
ここからは Power BI Service のアカウントをアクティベートしている人が対象です。
これまで Power BI Desktop のみを使っていた人、Power BI = Power BI Desktop だと思っている人、ぜひこの機会に Power BI Service にも手を出してみてください。
すごく世界が広がりますし、Power BI が単なる BI ツールではなく Data Platform だということがわかります。
Power BI のライセンスは
- Power BI 無料 (Power BI Free)
- Power BI Pro
- Power BI Premium
と 3 種類ありますが、いずれも 組織アカウント が必要です。MSアカウントは使えません。もし自由に使える組織アカウントがないという方は Office 365 の試用版をアクティベートするのが最も早いです。Office 365 の試用版は E3 と E5 がありますが、E5 には Power BI Pro が含まれているので、E5 がオススメです
この ページ を [InPrivate (Edge の場合)] or [シークレットウインドウ (Chrome の場合)] で開いてください。
上記の画像のページになりますので、矢印で示している一番左の Office 365 E5 の [無料で試す] から新たに Office 365 のアカウントを作成できます。
本当の意味で自動化するには
- オンプレミスデータゲートウェイを用意しておく
- Power BI Service へレポートを発行する
- Power BI Service で発行したレポートのデータセットに ゲートウェイの設定 と スケジュール更新 を設定する
上記の手順を踏むと、本当の意味で 自動化 が完了します。
オンプレミスデータゲートウェイについては以下を読んで概念を把握した後で、任意の Windows PC にインストールしてください
オンプレミス データ ゲートウェイとは
https://docs.microsoft.com/ja-jp/data-integration/gateway/service-gateway-onprem
ここから先は上記の 1 が完了していて、出来上がったレポートを発行する 2 から説明します。
ダウンロードしていただいたサンプルのレポートファイル (sales_sample_report.pbix) を開いて、リボンの [発行] をクリックしてください。もし、Power BI にサインインしていない場合は、このタイミングでサインインが求められるので、ご自身の組織アカウントでサインインしてください。
[発行] が成功すると上記のダイアログが表示されます。
さて Power BI Service ( https://app.powerbi.com ) をブラウザで開いてください。
ここでも [InPrivate (Edge の場合)] or [シークレットウインドウ (Chrome の場合)] をオススメします。
理由は皆さんが普段何らかの Office 365 や Dynamics 365 を使用されている場合、そのアカウント情報が既にブラウザに残っていて、普通に開くと、そちらの業務で使用しているアカウントでサインインしようとしてしまう可能性があるからです。InPrivate や シークレットウインドウ ならば、ブラウザキャッシュが使用されないので、毎回必ずサインインを聞かれるので、意図したアカウントでサインインをすることが可能です。
Power BI Service でのゲートウェイの設定
画面を開いたら、右上の [歯車マーク] - [ゲートウェイの管理] をクリックしてください。
私はオンプレミスデータゲートウェイをインストールする際に [PowerBIWorking] という名前を付けたので、既にその名前が [ゲートウェイクラスター] として表示されています。
この名前を選択し(①)、右端にマウスを持っていくと [...] が表示される (②) のでこれをクリックします。それから ③ の [データソースの追加] をクリックします。
なお、今回のレポート用にデータソースを２つ追加する必要があります。sales_sample_report.pbix ではマスタデータを CSV で、トランザクションデータを フォルダーコネクタ で取得しています。これを確認するには、pbix ファイルで以下の手順で画面を開いてください。
- データの変換
- Power Query エディターでリボンの [表示] - [クエリの依存関係]
クエリの依存関係はデータソースから Power Query でどう加工しているのかを図示してくれるので非常に便利です。最初はよくわからないかもですが、慣れてくるとわかるようになるので、わからなくても問題なしです。さて、今回見るべきは赤枠で囲った右上の２つ。①がフォルダ、②がユーザーマスタの CSV を表しています。そう、これらがオンプレミスにあるデータソースということになるので、オンプレミスデータゲートウェイで設定する必要があります。それぞれのパスはコピーしておいてください。
フォルダ： C:\Work\SalesSampleReport\Data
ファイル： C:\Work\SalesSampleReport\User_M.csv
Power BI Service に戻って [データソースの追加] をクリックします
まずは フォルダ から。
- データソース名：sales_sample_report_folder
- データソースの種類：フォルダー
- 完全なパス：C:\Work\SalesSampleReport\Data
- Windows ユーザー名：（オンプレミスデータゲートウェイをインストールした Windows PC のユーザ名を {マシン名\ユーザー名} という形で入力）
- Windows パスワード：4 のユーザーのパスワード
- このデータソースのプライバシーレベルの設定：組織（説明すると長いのでとりあえず、組織で OK です）
続いて ファイル。
- データソース名：sales_sample_report_user_master
- データソースの種類：File
- 完全なパス：C:\Work\SalesSampleReport\User_M.csv
- Windows ユーザー名：（オンプレミスデータゲートウェイをインストールした Windows PC のユーザ名を {マシン名\ユーザー名} という形で入力）
- Windows パスワード：4 のユーザーのパスワード
- このデータソースのプライバシーレベルの設定：組織（説明すると長いのでとりあえず、組織で OK です）
はい、これでゲートウェイに対するデータソースの設定を完了です。
次はデータセットへの設定です。
Power BI Service でのデータセットの設定
データセットの設定を開くには、画面左側の [データセット] から該当のデータセット名の右側 [...] - [更新のスケジュール設定] をクリックします。
[ゲートウェイ接続] を開きます
該当のゲートウェイを選択し、データソースをマップします。
- File{"path":"c:\work\salessamplereport\user_m.csv"} ← sales_sample_report_user_master
- Folder{"path":"c:\work\salessamplereport\data"} ← sales_sample_report_folder
マップしたら [適用] をクリックします
先ほどはグレーアウトされていてクリックできなかった [スケジュールされている更新] がアクティブになっているので、これを開き、
- データを最新の状態に保つ：オン
- 時刻：任意の時刻を設定します。ここは Power BI Pro の場合 8 回/日まで設定が可能です
- （任意）更新が失敗したときにこれらのユーザーに電子メールを送信する：メールを飛ばしたい人のメールアドレスを入力（ただし、Power BI のライセンスを持っているユーザーのみ）
最後に [適用] ボタンをクリックしてください
これで、データセットへの設定も完了です。
指定した時刻になると、該当のファイルとフォルダからデータを取得して、最新化してくれます。
では次はそれを手動で試してみましょう。
動作確認
C:\Work\SalesSampleReport\Data を以下のように3つのファイルにします。
この状態で一度 Power BI Service の該当データセットを手動で更新します。
データセットの名前の右側 [...] - [今すぐ更新] をクリックしてください。
画面右上に「更新の準備をしています」というメッセージが出るので、しばらく待ちます。
メッセージが消えたら、更新履歴 をクリックしてください。オンデマンドの更新 が 完了 になっていれば OK です。
画面左の該当レポートをクリックしましょう
確かに 2018年4月 - 6月のデータになっていますね。
では該当フォルダに 2018年7月 の csv をコピーしましょう。
そしてもう一度データセットを手動で更新します。
[更新のスケジュール設定] をクリックし、データセットの設定画面に移動
データセット名の右側 [...] から [今すぐ更新] をクリック。メッセージが消えたら、更新履歴を確認してください。
更新が完了したら、再びレポートを開きます。
「あれ！？更新されてない…！？」
ご安心をー。ブラウザキャッシュが残っていて、前回の内容を表示しているだけですので、ブラウザを更新 (F5) してください
はい、無事に更新されていましたね！
もしうまくいかなかったという方、落ち着いて、設定を見直してみてください。
おそらくオンプレミスデータゲートウェイかデータセットの設定です。
まとめ
いかがででしたでしょうか？
本当の意味で自動化する、という意味がわかりましたね。
さらに言うと、該当フォルダにファイルを置く部分も Power Automate で自動化することも可能です。なぜなら オンプレミスデータゲートウェイ は Power Platform のサービスすべてで使用することができるからです。
ね、一生懸命毎月やられているルーティンワーク、なくせそうでしょ？
その可能性を感じた方はぜひトライしてみてください！！
実現できた時のことを考えるとワクワクしますよ。特に経営層の方なら、なおさらそう感じるはず。
以上ですー。最後まで読んでいただきありがとうございました。また気が向いたら、Power BI Tips シリーズ書きます。
皆様からのリクエスト、お待ちしております。
何かリクエストがあれば、以下までー🤗
Twitter: https://twitter.com/yugoes1021
Facebook: https://www.facebook.com/yugoes1021
LinkedIn: https://www.linkedin.com/in/yugoes1021/