前回はDataflow Gen2を使用してソースからデータをインポートし、Fabricを介してデータをLakehouseにロードする方法を紹介しました。今回は、ここから日付テーブルを追加し、Power BIで可視化する前まで見ていきたいと思います。たかが日付テーブルということですが、データエンジニアリングが入ってきますので、少し解説が必要となります。なお、既に手順の概要を説明しましたが、Power BIの可視化において最も一般的な方法の1つは、Dataflow Gen2 → Lakehouse → Power BIデータセット（モデリング）→ Direct LakeモードでPower BIレポートを作成する流れとなります。
Fabricには様々な新しい用語が登場しており、各用語についての詳細解説は必要に応じて行っていきたいと思います。
※注意
「データセット」は別名「セマンティックモデル」に変更となっています。
Datasets renamed to semantic models | Microsoft Power BI Blog | Microsoft Power BI
各種アイテム
Fabricではワークロード*1を構成するものとして、アイテム（旧名: アーティファクト）が存在します。
上図①～⑥は前回までに作ったアイテムですが、
- ①は自動的に追加されたアイテム（削除してはいけないもの）
- ④～⑥はLakehouseを作った際に生成されたアイテム
- ③はオンプレミスSQLサーバーからDataflow Gen2を使用してLH_Contosoにデータをロードした場合のデータフロー
- ②は③が失敗した場合に備えて、Dataflow Gen1からSQLサーバーに繋げ、それをソースとしてDataflow Gen2を使ってLH_Contosoにデータをロードしたデータフロー
となります。③は前回紹介した内容には含まれていませんが、Dataflow Gen2から直接オンプレミスSQLサーバーに接続し、その後Lakehouseにデータをロードしようとするとエラーが発生する問題がありました。しかし、2023年6月30日にこの処理が改善されたため、この問題は解決されました。②と③は両方LH_Contosoに読み込みましたので、④のLH_Contosoを見ると、以下のように重複したテーブルが存在することになります（retryがあるテーブルはDataflow Gen2のみを使用）。
なお、テーブルに▲がついていますが、これはデルタ▲を意味しており、Dataflow Gen2を使用してLakehouseにデータをロードすると、Delta Parquet形式に変換されます。Delta Parquetは下図のように、Fabricの全てのワークロードで共通して活用される形式であり、Power BIはこの場合、データをインポートするのではなく、OneLake内にストアされたDelta Parquetファイルを直接クエリ（Direct Lakeを使用）して、Power BIレポートを生成していきます。
Direct Lakeは別名、シースルーモードと呼ばれますが、DirectQueryとImport Modeの弱点をカバーしつつ、両方の強みを活かしたイカしてるテクノロジーです。
データモデルの作成
ショートカット
Power BIのデータモデルに必要なテーブルは
- FactSales
- FactInventory
- DimProduct
- DimStore
とほぼ揃いましたが、時系列分析を行うためにはDimCalendarが必要となります。こちらのテーブルはFabric容量が割り当てられた別のワークスペースにあると仮定し、これをショートカットという機能で持ってきます。なお、ショートカットはデータを物理的に移動させることなく、Windowsで作るファイルのショートカットのように使用する際に該当するデータにアクセスできる機能となります。
下図の通り、クラウド上にあるデータに対してショートカットを作ることで、Azure Data Lake Store Gen2にあるデータだけでなく、外部クラウドベンダーのデータレイク内にあるデータへもアクセスできます。
今回はFabricというワークスペース内のLH_ContosoにあるCalendarMasterというDelta Parquet形式のテーブルに対して、本デモで使用しているDemo_FabricワークスペースにあるLH_Contosoに対してショートカットを作ります。
Demo_Fabricワークスペースより、LH_Contosoを開きます。
- ホーム > データの取得 > 新しいショートカット
- 新しいショートカット > Microsoft OneLakeを選択
- 対象ワークスペースにあるLakehouseを選択し、「次へ」
- CalendarMasterを選択し、作成
- Demo_FabricのLH_ContosoにCalendarMasterが追加されたことを確認
重要な点は、データは複製されていないということであり、単にショートカットが作成されているだけであることです。OneCopyという概念は、データの所有者が管理するCalendarMasterを複製することなく、再利用することができるという点にあります。
ちなみに、残念なお知らせですが、ショートカットで持ってきたテーブルは名前の変更ができませんので、名前を変更するには少し工夫が必要です（後述）。
これで全てのパズルがそろいましたが、CalendarMaster（日付テーブル）とFactテーブルの日付がいつからいつまであるのかを調べてみる必要がありそうです。Power BIでは日付テーブルとFactテーブルはリレーションシップを用いて紐づけを行いますが、Factテーブルの期間を全てカバーしきれているかどうかを確認しないといけません。
日付テーブルの最大・最小値の確認
Lakehouse内のデータを確認するには、SQLエンドポイントを使用します。SQLエンドポイントはSQLベースの環境であり、T-SQL言語を使用してデルタテーブル内のデータを分析したり、関数を保存したり、ビューを作成したり、SQLセキュリティを適用したりすることができます。Power Queryは、バックエンドではデータ処理がT-SQLに変換されるため、構造的にはそのような処理が行われます。ExcelやPower BIのみに経験がある人にとっては、最初は少し敷居が高く感じるかもしれませんが、学んでおくと仕事で役立つ言語と言えます。
以下のように、スクリーン右上にあるLakehouseボタンから、「SQL エンドポイント」をクリックします。
少しすると画面が切り替わりますので、以下の順番でSQLステートメントを記述していきます。
3つのSQLを記述しましたので、結果は以下のように3つとなります。
なお、aだけがMinDate, MaxDateという順番になっていますが、Power BI DesktopのPower Queryエディタと同様、Alt + ↑ or ↓でラインを移動させることができます。
上記結果ですが、aとbは2007/1/1～2009/12/31までが日付レンジ、cだけが2007/1/1～2019/12/31となっているようです。Factテーブルの全ての期間をカバーできているので一安心ですが、日付テーブルが無駄に10年分余計な期間が含まれており、不要な期間を削除しておきましょう。
ショートカットで作られたテーブルの変換
CalendarMasterに対して、期間をフィルターしていきます。セルフサービスBIユーザーに残念なお知らせですが、Lakehouse内にある▲デルタ形式のテーブルに対してはNotebookと言われるアイテムを使用し、pySpark*2やSpark SQL*3を記述してデータ処理を行う必要があります。これらの言語はデータエンジニアリングの領域に属しており、私もまったくの初心者として分類されます。Fabricはペルソナをベースとしたサービスであることを前回話をしましたが、まさに今回これを感じることになりました。
他に方法がないので、まずはSQL文を作ってみましょう。ここではCalendarMasterの日付を2007年～2009年に絞るコードを記述してみます。SQLが苦手な人は以下①～④の流れでまずはビジュアルクエリ（Power Queryエクスペリエンス）を作ってみると良いでしょう。
Power Queryエディタが出現しますので、⑤～⑩で期間を絞ります。
Select構文でハードコーディング選択された列を全て消し、Select * fromという構文に変更し、Runをクリックすると、同じ結果が得られます。
なお、Select count(*)にすると、行数をカウントしてくれますので、結果が正しいかどうかのざっくり確認することが可能です。下記より、合計1,096行となりましたので閏年を含む年度の日付レンジを全て取得できたと分かりました。
ちなみに、Select * 構文は下記赤枠のように書き換えることも可能ですので、[]を含む構文がごちゃごちゃしていると思う方は書き直してみるのも良いでしょう。
ここから、上記赤枠構文をpySpark、もしくはSpark SQLに変換し、CalendarMasterをDimCalendar（3年分）に変換していきます。こちらの作業がデータエンジニアリングの領域になりますので、コードメインの世界になってきます。もはや記事のタイトル完全無視していますが、非常にシンプルなので焦る必要はありません。
CalendarMasterの右にある・・・をクリックし、「新しいノートブック」を選択
すると、Notebookが立ち上がり、以下のような画面が表示されます。
試しに以下のように記述してみます。言語をSpark SQLに変更し、同じようにSQL文を入力します。絞り込み条件が3日だけなので、3行だけ返されました。ここでの操作はSQLですが、ビッグデータ処理に強いSparkエンジンによるものとなります。
ここでやりたいことはWHEREの部分を20070101～20091231で絞り、この条件を元に新しいデルタテーブルを作ることです。従って、まずはChatGPTに聞いてみましょう。
上記のようなシンプルなコードが生成されましたので、最初の2をコメントアウトして、dim_dateから以下を実行します。
見た目あまりよろしくないですが、最後の8行目にresult.show()とコマンドがありますので、結果が表示されました。もう一回ChatGPTに聞いてみます。
なるほど、もう答えが出そうですね。上記result.show()がresult.write...に書き換えられましたので、こちらで置き換えてあげれば、新たなデルタテーブルが生成できそうです。以下のように記述し、Runをクリックします。
続いて、エクスプローラーのほうより、テーブルビューを更新します。
DimCalendarの行数をチェックしてみます（⑦～⑩）。
新しいSpark ジョブを追加し、右下の言語をSpar SQLに変更すると、%%sqlが追加され、規定がPySpark環境でもSQLステートメントを記述できるようになります。結果的に⑩の通り、1,096行が新たにDimCalendarというテーブルで作ることができました。それほど難しい操作ではなかったこともあり、日付テーブルの再利用法として参考できそうです。なお、この%%というのはマジックコマンドと呼ばれており、以下の言語を追加処理ができます。
- %%pyspark
Spark コンテキストに対して、Python クエリを実行 - %%spark
Spark コンテキストに対して、Scala クエリを実行 - %%sql
Spark コンテキストに対して、Spark SQL クエリを実行 - %%html
Spark コンテキストに対して HTML クエリを実行 - %%sparkr
Spark コンテキストに対して、R クエリ
ちなみに、これらの処理を終えると、ワークスペースにはNotebook 1というアイテムが保存されるようになりますので、分かりやすい名称に変更しておくと良いでしょう。
Notebookはデータフローと同じように、スケジュールさせて定期的に実行させることもできるし、エクスポートして他の変換作業で再利用することも可能となります。
次回、DimCalendar, DimProduct, DimStore, FactIventory, FactSalesを使って、Power BIのモデリングに際して留意すべきポイントについて詳細に解説していきたいと思います。
まとめ
- ショートカットはLakehouse内の▲デルタテーブルを自由に参照できるため、データサイロ（データの分断化）の排除に繋がる
- ショートカットで自分のLakehouseにマップした▲デルタテーブルは名前を変更できないが、データエンジニアリング（PySpark等の言語）を行うことで新たなテーブルとして生成可能
- Sparkジョブは様々な言語を使用することができ、自分に最も合ったものを使用すると良い
- 分からない場合はChatGPTへGo！
*1:Data Factory, Data Engineering, Data Science, Data Warehousing, Real Time Analytics, Power BI等のエクスペリエンス
*2:pySparkは、Pythonを使用してApache Sparkを操作するためのPythonライブラリです。Apache Sparkは、分散データ処理のための高速で拡張性のあるクラスターコンピューティングフレームワークです。pySparkは、Python開発者がSparkの強力な機能を利用できるようにします。データの読み込み、変換、クエリ、分析、機械学習などのタスクを実行するためのAPIや関数を提供します。
*3:Spark SQLは、Sparkのコンポーネントの1つであり、関係データ処理を行うためのSparkのモジュールです。Spark SQLは、SQLやDataFrameといった高レベルの抽象化を提供し、データの構造化とクエリ処理を行うためのインターフェースを提供します。Spark SQLは、Hiveのメタストアと連携してデータの管理やメタデータの操作も行えます。また、Spark SQLはDataFrame APIを提供しており、PythonやScala、Javaなどのプログラミング言語を使用してデータの操作や分析を行うことができます。
