テクテク日記

テクテク=テクノロジー&一歩ずつ(テクテク)

データフロー(Power Query Online)②_簡単なデモ

前回はデータフローの概要について話をしましたが、今回はもう少し掘り下げつつ、実際にハンズオンで構築してみたいと思います。データフローのユースケースは前回少し話をしましたが、個人+組織の両方で活用を広げていくことが目的となります。データフローに限らず、Power BI Desktopで作ったレポートをPower BIサービスに発行してレポートを共有すること自体、チームコラボレーションを実現するものとなりますが、データフローに関してはスケジュール更新やステージングクエリ*1を構築することでクエリのパフォーマンスを最適化させることを目的としています。

データフローの概念

データフローの概念は下図の通り、aとbの2つのパートで構築されます。bで構築されたデータフローがPower BIのセマンティックモデルであるc) データセットとなり、このデータセット(リレーショナルやメジャー等が定義されたモデル)を参照してd) Power BIレポートが作られます。

一方で、aはデータフローを作るためのデータソースですが、2023年1月時点、約130種類のコネクタが用意されています。

最初のフローチャートでピンと来ない方はまずPower Queryの概念について学んでおくと良いと思いますが、イメージとしてExcelやPower BI Desktopに搭載されているPower Queryで処理していたものを、クラウドサービスであるPower BIサービス(バックエンドではAzure)が処理を行ってくれていることを理解しておくと良いでしょう。

デスクトップ・アプリケーション → Power Query → モデリング・レポート

から

Power BIサービス → Power Query Online(データフロー) → データフローの再利用 or モデリング・レポート

となります。

データフローで処理を行う場合、最後の部分で「データフローの再利用」が追加されているのがポイントです。Power BIはMicrosoft Azureが提供するサービスの1つで「データフローの再利用」とは、Power BIサービスが用意してくれたAzure Data Lake Storage Gen2に実際のデータを格納し、自分あるいはチームメンバーがそれに接続を行うことを意味します。

なお、データはCSVで保存されていますが、実際にはスキーマファイル(データフローの各種変換ステップやデータ型等を定義したJSONファイル)も入っており、これを再利用することで異なるワークスペースに同じデータフローを作ることも簡単にできます。

また、データフローはクラウドサービスですので、デスクトップ・アプリケーションでは実現が困難な「スケジュール更新」も設定できます。データフローを使ったほうが良い理由は色々ありますが、「スケジュール更新」「組織におけるPower BIの利用を拡大させる手段」「SSOT -Single Source of Truth(信頼できる唯一の情報源)」「お手軽に構築できるデータマート的な存在」等が考えられます。

データフローを使う

概念ばかりでは飽きると思いますので、実際にデータフローを1つ作ってみます。デスクトップ版Power Queryでは少ないショートカットメニュー(Power Query Online用)を公式Docsから抽出する例で見ていきます。なお、データフローは基本的にワークスペースから作りますが、留意点として以下2つがあります。

  1. ExcelやPower BI Desktopで作ってデータフローに張り付けでも対応可
  2. Power BIサービスのテナント設定で「データフローの作成と使用」を有効化

1番はこの後紹介していきますが、2は以下のように設定を行う必要があります。Power BIサービスで⚙アイコンをクリックし、管理ポータル > テナント設定 > データフローの設定と進み、「有効化」をクリック。

なお、④の「データフローの設定」がなかなか見つからない場合、Ctrl + Fで検索を掛けると一発で対象設定を探し出すことができます。

「データフローの作成と使用」が有効化されていない場合、以下のようなエラーメッセージが出現します。管理者権限を持っている方がこちらを設定し、「有効化」を行ってください。

それでは実際に1つ簡単なデータフローを作っていきたいと思います。

Power BI Desktopで作るデータフロー

Power BI Desktopでの準備
  • Power BI Desktopを開く
  • ファイル > データの変換
  • 下記コードをコピー
let
    Source = 
  Web.Page(
       Web.Contents("https://learn.microsoft.com/ja-jp/power-query/keyboard-shortcuts")
        ),
//Tableとなっている行だけを選択し、最後にCaptionとDataという2つの列だけを残す
    SelectSource = Table.SelectRows(Source, each [Source] = "Table")[[Caption], [Data]],
  //列を展開する
    Expanded = 
        Table.ExpandTableColumn(SelectSource, "Data"
            {"アクション", "ショートカット キー"}, 
            {"アクション", "ショートカット キー"}
        )
in
    Expanded
  • Power Queryエディタに戻り、空のクエリを作る
  •  詳細エディターを立ち上げる

  • Ctrl + Vでソースコードを張り付けて「完了」

  • 資格情報の編集
    MS Learnに接続していますが、過去Power Queryで接続したことがない場合、以下のような「資格情報の編集」が出現します

    Webからデータを取得するため、接続は全て「匿名」となります。

  • 名前をDocumentに変更し、データが取得できていることを確認

    なお、右側にある「適用したステップ」からどのような変換を行ったかを確認できます。
    これでPower BI Desktopでサンプルクエリを作れましたので、これをそのままPower BIデータフローへ持っていきます。
Power BIデータフローでの準備
  • Power BIサービスへサインインし、ワークスペースを選択

    前提条件として、Power BI ProもしくはPremiumPPU含む)を使っている必要があります。

  • ワークスペースより「新規」 > 「データフロー」
  •  「新しいテーブルの定義」から「新しいテーブルの追加」

  •  空のクエリを作る

    以下のような画面が出てきますが、そのまま「次へ」をクリック

  •  Power BI Desktopのクエリをコピー
  •  Power Query Onlineのクエリエディタに戻り、Ctrl + V(右クリック > 貼り付けはNG)で張り付け

    Power BI Desktopと同じように、資格情報に関する表示が出てきますので、「接続の構成」をクリック。成功すると、以下の画面となります。

    ちなみに、Ctrl + Vではなく、右クリック > 張り付けでクエリをペーストしようとした場合、以下のポップアップが出現し、Ctrl + Vで張り付け直すよう指示されます。変な話ですが、右クリックがNGならばその項目を非表示にしてほしいのですが、Power Queryの開発チームに所属するMiguel Escobarさんに確認したところ、「当該機能が元々ブラウザベースで実装された際の技術的な制約」であることが確認できました(下図)。いずれ改善されるでしょうが、2023年1月時点ではまだこのテーマを再検討するに至らずとのこと。

  • クエリを吟味
    ここで右下の「保存して閉じる」をクリックしても良いですが、せっかくなのでクエリを少し吟味してみます。
    • 不要なクエリを削除
      データフローをペーストして作る場合、どうしても空のクエリを作る必要があり、必要のないクエリは消しておきましょう。
    • 警告表示
      Power Query Onlineの特徴の1つとして、警告を出してくれることがあります。「2個の警告」をクリックすると、以下のようなメッセージが出現します。

      なかなかスマートな表示だと思いますが、デスクトップ版のPower Queryにはない機能の1つです。
  • 不要クエリを削除し、型指定をせずに「保存して閉じる」
    Power Query Onlineの親切心を無視して、そのまま「保存して閉じる」をクリックすると、Power Query Onlineは”更に親切”にも「クエリの問題を修正しました」と問題クエリを修正してくれます。
    その後、データフローを保存して更新する必要がありますが、ここがデスクトップ版のPower Queryと大きく異なるところです(理由は下記に記載)。「データフローの保存」はPower BI DesktopやExcel等からデータフローにアクセスするために必要なネーミングで、1つのデータフローは通常、複数のクエリ(テーブル)を持つことが多いですが、今回の例は1つのクエリだけで構成されています。
    例として、今作ったデータフローをExcel Power Queryで参照できるか確認してみます。Excelを開き、下記のように「データの取得」から接続します。


    「データの変換」をクリックすると、以下2つのうち、どちらかのパターンに遭遇します。

    a) テーブルが空

    b) テーブルにデータあり


    a)は上述したデータフローを保存した後に「今すぐ更新」を行わなかった場合で、データフローを保存しただけでは”実際のデータは内部ストレージであるAzure Data Lake Storage Gen2に読み込まれていない”ことに注意する必要があります。「データフローの保存」はあくまでETLプロセスの定義を保存しただけと覚えておくと良いでしょう。

  • クエリの問題を確認
    せっかくなので、データフローを保存した際に出ていた「クエリの問題を修正しました」という部分について確認してみます。

     データフローの画面に戻り、名前をクリック >(右上の)テーブルの編集


    右側の「適用されたステップ」より、身に覚えのないステップが2つ追加されているのが分かります。

    ここで構文を見てみると、Table.TransformColumnTypesというM関数が適用されているようです。


    実はこのステップ、いつだったか忘れましたが、以前は列のデータ型を指定しないとデータフローを「保存して閉じる」ことが出来なかったのです。ExcelやPower BI Desktopで型を敢えて指定しなくてもデータモデルに読み込みができていたので、データフローのこの仕様に対してかなり煩わしさを感じていました。
    データフローはデータベース(データマート)的な要素を持っているため、ソース側(データフロー)でデータ型を指定することになります。データ型の処理が実はメモリインテンシブな作業であることや、データソース(上流)でこの作業を予めやっておくことがその後のETL作業を楽にしたり、更新パフォーマンスを良くしてくれるわけです。
    ここからもう一つのステップ(最後のステップ)を見ると、今度はTable.ReplaceErrorValuesというM関数が使用されています。

    この関数は「アクション」と「ショートカット キー」の両方の列で、エラーとなっているセルをnullに置き換えることを目的としており、今回の例で実際にエラーはないものの、Power Query Onlineが敢えてこのステップを追加しているところを見ると、エラーがセル内に含まれていた場合の”大惨事”が理由だと考えられます。
    ここで”大惨事”という表現を使いましたが、これは自分が以前経験したからであり、クエリの構築が終わり「保存して閉じる」を押すと、データフローは様々なチェックを経て、「データフローの保存」や「今すぐ更新」という画面になりますが、そのチェックに要する時間が構築したデータフローの複雑さによって、非常に長くなってしまうことがあります。
    長い時間をかけてチェックしたあげく、エラーで保存できませんでした
    という表示を目の当たりにしてテンションが一気に下がった記憶が今でも鮮明に残っています。当然ながら、当時はまだデータフローがここまで”スマート”になる前でしたので、この気の利いたステップが自動的に挿入されたのは個人的に非常に喜ばしいと感じています。とはいえ、エラーをnullに強制的に置き換えていますので、本来気付くべきエラーに気づかないまま、データを構築してしまう、といったデメリットも存在します。

まとめ

前回の記事のまとめで、短くコンパクトにまとめるという話をしましたが、やはり無理だったようです。今回はデータフローの作り方について、Power BI Desktopから作ったクエリをコピペして簡単に作ることができることを紹介しましたが、次回はExcel等も交えながら、もう少しDeep Diveしていきたいと思います。

*1:ソースデータを変換プロセスを経てデータモデルにインポートできるよう、1から最後まで行うのではなく、ソース側の負担やデータ変換による処理負荷を減らすため、途中まで変換したクエリ。Power BIはこのようなクエリに接続し、更に変換を加えて最終クエリに仕上げることでデータ抽出やデータ更新のパフォーマンスを大幅に引き上げることが可能