テクテク日記

テクテク＝テクノロジー&一歩ずつ（テクテク）

Power BIデータセットをドキュメントする2

DAX / Data Model Power BI Power Query 可視化

以前に「Power BIデータセットをドキュメントする」についての記事を執筆しましたが、今回はその続編となります。Power BIのデータセットに関して、DMV*1経由で様々なメタデータ情報を取得できますが、Power BIサービスにあるセマンティックモデル（データセット）からDAXメジャーやその数式を取得するのではなく、Power BI Desktop（ローカルモデル）から取得する方法をについて解説していきます。

※注意
「データセット」は別名「セマンティックモデル」に変更となっています。
Datasets renamed to semantic models | Microsoft Power BI Blog | Microsoft Power BI

DAXメジャー、Power Query等のメタ情報を取得

前回紹介した方法は、PPUやPremium容量（Power BI Premium、もしくはFabric容量）が必要でしたが、今回はそれらの条件は必要ありません。前提条件として

  • 完成されたPower BIデータモデルが存在していること
  • DAX Studioがインストールされていること

の2つが完了している必要があります。

DAXに関するのメタ情報
  1. Power BI DesktopからDAX Studioを立ち上げます
  2. 下記式を入力し、Runをクリック
    //メジャー及び定義式を取得する。全データを取得したい場合、select * fromに変更
    select 
       [MEASUREGROUP_NAME], 
       [MEASURE_NAME], 
       [DESCRIPTION],
       [EXPRESSION]
    from $SYSTEM.MDSCHEMA_MEASURES
    where MEASURE_AGGREGATOR = 0
    order by MEASUREGROUP_NAME

  3. 成功すると上図のように、メジャーを保持しているテーブル名（MEASUREGROUP_NAME）、メジャー名（MEASURE_NAME）、解説（DESCRIPTION）、メジャー式（EXPRESSION）の4つが抽出されます
  4. ここから、以下のように出力を選択すれば、様々な書式で取得情報をファイルにエクスポートできます（DAX Studioによるエクスポート）

Power Queryに関するのメタ情報

データモデルで使用されているPower Queryのメタ情報も取得できます。ただし、ステージングクエリとして作成したモデルへ読み込みを行わなかった情報は取得できません。

//読み込み済テーブルのクエリ式を取得。全データを取得したい場合、select * fromに変更
select 
    [Name],
    [Description],
    [QueryDefinition],
    [RefreshedTime]
from $SYSTEM.TMSCHEMA_PARTITIONS
order by [Name] DESC

なお、テーブルであればDAX式で作った計算テーブルの定義も取得できるようになります。

出力方法は上述と同じやり方ですので、ここでは割愛します。

Power BI DesktopからPower BI Desktopのモデル情報を取得

前回の記事ではテーブル等の情報を取得するためのSQL文は以下の通りであると解説しました。

  • テーブル情報
    select * from $SYSTEM.TMSCHEMA_TABLES
  • リレーションシップ情報
    select * from $SYSTEM.TMSCHEMA_RELATIONSHIPS
  • メジャーと計算列情報
    select * from $SYSTEM.TMSCHEMA_MEASURES
    select * from $SYSTEM.TMSCHEMA_COLUMNS

これらの情報はPower BIサービス側に発行したセマンティックモデルから取得することになりますが、Power BI Desktopからでも同じく取得することができます。

  1. 対象となるモデルがあるpbixを開いたままにする
  2. 外部ツール > DAX Studioを立ち上げる
  3. DAX Studioからlocalhostを取得する（下図のアイコンを左クリック）

    ※ 追記
    localhost情報ですが、2023年10月のPower BI Desktopがインストールされていた場合、モデルビューから「モデル」>「セマンティックモデル」>プロパティの「サーバー」を確認すれば、同じ情報を取得できるようになります。ただし、下記に示した「Database Id」「Database Name」は取得できないため、結局はDAX Studioに頼る必要があります。

    追記（2023年12月時点）:
    上記サーバーは簡単にコピーできるようになりました。「Database Id」も今後は搭載予定です。
  4. 同様に、左上にあるデータベースのところを右クリック > Copy Database Id（実際にはCopy Database NameでもOK）
  5. ステップ3とステップ4でコピーされたものを控えておく
  6. 新しいpbixを立ち上げる
  7. 立ち上げたpbixから、データを取得 > Analysis Services
  8. サーバーに"localhost"の値（ステップ3で指定）を貼り付け、データベースにステップ4でコピーした値を貼り付け、インポートモードを選択し、上記のSQL文のどれかを使用
  9. ポップアップが出現するので、「データの変換」をクリック
  10. Power Queryエディタの画面となり、前回シリーズのようにデータを取得することが可能に
    この後、localhostのナンバー（Portナンバー）とデータベース名をパラメーター化すれば、他のクエリを簡単に作成でき、再利用も容易になるでしょう。ただし、セキュリティの観点から問題があり、Power BI Desktopを毎回開くたびにPortナンバーが変わるため、そのたびにPortナンバーを再取得する必要があります。

最後に

今回紹介した方法は、主にPower BI Desktopを使用してデータモデル内の情報を整理（ドキュメンテーション）したいユーザーに役立つものです。Portナンバーが頻繁に変わるのは面倒ですが、賢い取得方法は限られている現実です。以前に公開された「BICCOUNTANT」ブログ（2016年）に、この問題に対処する手法が紹介されていますので、興味のある方はぜひ参照してみてください。

www.thebiccountant.com

なお、間もなく登場予定のDAX Query View（Power BI Desktop内でDAXクエリを可視化できるView）で、全てのメジャーをボタン1つで定義（抽出）することができるようになりますので、メジャーだけをシンプルに抽出したいという人はそちらの機能を使うと良いでしょう。

*1:DMV（Dynamic Management View）は、Microsoft SQL Server Analysis Services（SSAS）などのデータベース管理システムで使用される特殊なビューです。DMVは、データベースの内部状態や動作に関する情報を提供し、パフォーマンスの監視、トラブルシューティング、クエリ最適化などの目的で使用されます