Microsoft Fabric Semantic link での Power BI セマンティックモデルデータのファイルエクスポート

## はじめに

「Power BI セマンティックモデルは分析エンジンなので、テーブル出力としては最適化されていない」とはいうものの、根強い csv 出力へのニーズがあります。

ページ分割レポートなどを使用してある程度の規模のテーブル出力には対応できますが、今回は 他システムへの連携を意識してコードで ファイル出力する案として、Semantic link を使用してみます。

https://learn.microsoft.com/en-us/fabric/data-science/read-write-power-bi-python


## 準備

10,000,000 件のデータをインポートしたセマンティックモデルを使用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/50593223-4946-da83-e46d-1dfde6200816.png)

## 制限事項

見つけ次第記載

- XMLAエンドポイントが必要になるようなので、Premium User または Fabric ワークスペースである必要があります。

## spark sql による抽出

参考：https://learn.microsoft.com/en-us/fabric/data-science/read-write-power-bi-spark

以下のようなクエリでセマンティックモデルにアクセスできます。

```sql:sql

select * from pbi.`セマンティックモデル名`.<エンドポイント種類>.`テーブル名`　

```


エンドポイント種類は以下が記入可能です。
- xmla : 省略した場合のデフォルト。 XMLA エンドポイントを使用
- rest : Power BI REST API に送信
- onelake : OneLake 統合または Direct Lake モードでのみ利用可能。直接 Delta Parquet にクエリする※確認中

### spark sql での取得

まずは取得してみます。
ワークスぺース上のセマンティックモデルは pbi という名前空間に格納されています。

```python:pyspark

df = spark.sql("SHOW TABLES FROM pbi")
display(df)

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/caa0917e-803d-51ed-4c27-4cbc4ac5438f.png)

対象のテーブルがわかったので、クエリしてみます。（ちょっと負荷のかかりそうな連番付与をしています）

```python:pyspark

df_spark=spark.sql("select *,row_number() OVER (order by S_SUPPKEY) as row_num from pbi.tpch_sample.TPCH_SF1000_SUPPLIER")
df_spark.count()

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/614835fd-9cf6-9887-549c-b2ff97ed8eb8.png)

結果を保存します。

```python:pyspark

df_spark.write.format("csv").save("Files/sparksql_tpchsample_csv/data.csv")


```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/96ee05a2-ca01-3902-8c95-52e6a43339ca.png)


### spark sql の使用するセマンティックモデル用のエンドポイントについて

画像では抽出できてしまってますが、rest エンドポイントを使用した場合には制限事項が適用されます。

このような Power BI REST API に対する制限は通常、 XMLA エンドポイントを使用して回避します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/87683cad-5514-e7d2-e82d-e737f51062f1.png)
https://learn.microsoft.com/ja-jp/fabric/data-science/read-write-power-bi-spark#read-access-limitations


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/e431c850-0850-a753-2cef-41659ab5dfbc.png)
https://learn.microsoft.com/ja-jp/rest/api/power-bi/datasets/execute-queries#limitations


### spark to csv について

spark は内部の処理でデータを分割してしまうため、使用される処理の状況により csv が分割されることがあります。
明示的に1ファイルにしたい場合や、逆に数を指定して分割したい場合、は以下のようにします。
※分割した場合、ファイル名は指定できません。

```

df_spark.repartition(4).write.format("csv").save("Files/part_sparksql_tpchsample_csv")
# または以下など
df_spark.coalesce(1).write.format("csv").save("Files/part_sparksql_tpchsample_csv/data.csv")


```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/34ce64e1-08c0-f378-14c3-89a2141c7af1.png)

使い分けの参考：https://ktksq.hatenablog.com/entry/pyspark-save-csv#repartition%E3%83%A1%E3%82%BD%E3%83%83%E3%83%89%E3%81%A8coalesce%E3%83%A1%E3%82%BD%E3%83%83%E3%83%89%E3%81%AE%E9%81%95%E3%81%84





## sempy による抽出

evaluate_dax と、evaluate_measureを確認します。

参考：
1. https://learn.microsoft.com/ja-jp/fabric/data-science/read-write-power-bi-python
2. https://learn.microsoft.com/ja-jp/fabric/data-science/tutorial-power-bi-measures

なお、read_table の場合は、エンドポイントの指定が可能です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/9a0ec113-6fe5-dcb7-2bdb-57d45a1fa52d.png)
https://learn.microsoft.com/ja-jp/python/api/semantic-link-sempy/sempy.fabric?view=semantic-link-python#sempy-fabric-read-table

### evaluate_dax
https://learn.microsoft.com/ja-jp/python/api/semantic-link-sempy/sempy.fabric?view=semantic-link-python#sempy-fabric-evaluate-dax


dax の結果を使用する方法を試します。
Power BI Desktop から [DAX は簡単に確認](https://learn.microsoft.com/ja-jp/power-bi/create-reports/desktop-performance-analyzer) できるため、Power BI ユーザーはこの方法が使いやすいと思います。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/93d3403c-80f3-210e-e37d-7342c088bcd4.png)

この部分の DAX を使用してみます。ただし、レポートで生成される DAX には TOPN が使われているので、そこ以外を流用します

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/1417007e-4a6c-0230-0049-282099e09a2b.png)

```python:pyspark

import sempy.fabric as fabric 

dax= """
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			'TPCH_SF1000_SUPPLIER'[S_NAME],
			"sample_measure", 'TPCH_SF1000_SUPPLIER'[sample_measure]
		)
EVALUATE
	__DS0Core

ORDER BY
	[sample_measure] DESC, 'TPCH_SF1000_SUPPLIER'[S_NAME]
"""

df_dax_result = fabric.evaluate_dax(
    "tpch_sample",
    dax
)

df_dax_result.count()

```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/0872e140-3760-9d8e-fbfd-5d86ced96579.png)

内容も見てみます。

```python:pyspark

display(df_dax_result)

```


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/3f7359ea-2271-89f2-be7d-7dac58e6d234.png)

出力します。


```python:pyspark

import os

folder_path = '/lakehouse/default/Files/evaluatedax_tpchsample_csv'
file_path = os.path.join(folder_path, 'data.csv')

if not os.path.exists(folder_path):
    os.makedirs(folder_path)

df_dax_result.to_csv(file_path)


```


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/71ad82f4-4a4d-5f29-9f58-f23dbfeb5f2a.png)


:::note info

sempy のデータフレームは pandas と同じように動作するため、ファイルシステムのAPI パス を出力先にします。

https://learn.microsoft.com/ja-jp/fabric/data-engineering/lakehouse-notebook-load-data#load-data-with-pandas-api

:::

### evaluate_measure
https://learn.microsoft.com/ja-jp/python/api/semantic-link-sempy/sempy.fabric?view=semantic-link-python#sempy-fabric-evaluate-measure

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/b6b1f304-91d2-a9b8-5384-7f436ccc2e9b.png)

:::note info

XMLA エンドポイントを利用せずにバックエンド制限もない特殊な API を使用している模様です。

明示的に XMLA エンドポイントを使わせるには  use_xmla=True を使用します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/46928482-c3f9-e8c6-17e4-5c12b9b3bd01.png)
https://learn.microsoft.com/ja-jp/fabric/data-science/tutorial-power-bi-measures#use-power-bi-xmla-connector

:::


メジャーの評価結果を使用する方法を試します。
先ほどの DAX と同じような結果となる内容で作成します。

use_xmla を使わないと非常にレスポンスが重かったためこれを使用しています。

```python:pyspark

import sempy.fabric as fabric 

df_measure_result = fabric.evaluate_measure(
    "tpch_sample",
    "sample_measure",
    ["'TPCH_SF1000_SUPPLIER'[S_NAME]"],
    use_xmla=True 
)
df_measure_result.count()


```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/11e3c71e-6b1f-611e-8d96-fca2f5307c3c.png)


結果も見てみます。

```python:pyspark

display(df_measure_result)

```

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/824440fa-ccf9-7868-c196-cf9b22118c79.png)

最後に出力

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/db0ed903-e250-ee87-2be7-15a08b5fe38a.png)



## 出力されたファイルの使用について

レイクハウスに出力されたファイルは [Azure Data Lake Storage Gen2 と同じプロトコル](https://learn.microsoft.com/ja-jp/fabric/onelake/onelake-access-api)でアクセスできるので、azcopy などを使用して転送してもよいですし、[Onelake File Explorer （Preview）](https://learn.microsoft.com/ja-jp/fabric/onelake/onelake-file-explorer) を使用して、ローカルファイルとしてアクセスすることも可能です。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/281819/1386f2b2-80dd-636a-26ec-62db67e96fb5.png)