Power BIの動的RLS（行レベルセキュリティ）再考

動的RLSについては2年前に以下で書いたところではあるが、いろいろ思うところがあり、また書いてみる。

業務要件は、先の記事に続き、自分の部署/店舗だけの情報を見せたいというもの。Power BIで実現する方法を考えてみよう。

RLSの基本的な話は以下を読んで理解しておくこと:

1. 実現方法の比較

業務要件の実現方法はひとつではない。それぞれを比較した上で選択すること。

1.1. 具体的な要件

以下を満たすようにレポートを設計する:

1. 部署別売上は自部署のみ表示
2. データは複数年度
3. 年度間の部署異動あり
4. 異動があった場合には、現在の自部署のみ
5. データ接続方式はインポート モードのみ

1.2. 実現方法

セマンティック モデルまたはレポートを複数作る

部署の数だけセマンティック モデルまたはレポートを用意する。アクセス権は組織アプリ上で、セキュリティ グループまたはM365グループで設定する。

静的RLS

部署の数だけRLSロールを用意する（がんばる）。RLSロールへの割り当ては、ユーザー単位またはセキュリティー グループで設定する。

動的RLS（前回＆今回解説）

RLSロールはひとつ。閲覧者全員をこのRLSロールに割り当てるが、具体的なアクセス権の設定はRLS用のセキュリティ テーブル上で行う。

1.3. 実現方法の比較

それぞれの特性を見極めてから使うこと:

1. パフォーマンス : データの更新、レポート表示の速度が遅すぎないか？
2. メンテナンス : アクセス権/RLSロール割り当ての手間はどのくらいか？
3. 機能の理解 : 使う側が中身を理解できているか？難しすぎないか？
4. 機能の制限 : 方法によって機能に制限がかかるか？

2. 前に紹介した動的RLS（概略）

2.1. データ モデリング

• セマンティック モデルは、日付、社員、売上の3テーブルで構成。
• 社員の異動はSCD Type 2で表現
• 社員テーブルはセキュリティテーブルを兼ねる
• RLS用のDAXが複雑

2.2. 問題点

個人的には特にないんじゃないかと。1対多、片方向のきれいなスター スキーマになっているし。

そうじゃないって？ええ、わかっています。多分このモデリングが 難しい ということを:

1. SCD Type 2って何？ : SCDを理解しなければならないし、データ整形もむずい（ストックから差分を作るなら）
2. RLS用のDAXが難しい : CALCULATEとか分かりませんという声が聞こえてきた。

VAR _current_depts = CALCULATETABLE(
    VALUES('従業員'[部署]),
    ALL('従業員'),
    '従業員'[userprincipalname] = USERPRINCIPALNAME(), 
    '従業員'[適用終了日]=Date(2999,12,31)
)

VAR _ret = '従業員'[部署] in _current_depts

RETURN _ret

3. 今回紹介する動的RLS（簡単な方法）

3.1. データ モデリング

• セマンティック モデルは、 C_日付 、 D_社員 、 D_部署 、 F_売上 、 S_RLS の5テーブル
• D_社員 は個人の属性（名前、メールアドレス）のみ
• D_部署 は部署名のみ（必要なら階層も）
• F_売上 に社員、部署の情報を含む
• S_RLS は、現時点の部署、社員、メールの情報。 売上 とは 多対多、片方向 のリレーションシップで結ぶ。多対多なので 制限付きリレーションシップ となっている。
• RLS用のDAXはシンプル: S_RLS[Email]=USERPRINCIPALNAME()

F_売上

日付	部署	社員	Email	_売上
2022/4/1	文化部	山岡	[e-mail]	100
2022/5/1	文化部	栗田	[e-mail]	200
2022/6/1	文化部	富井	[e-mail]	300
2022/7/1	美食倶楽部	海原	[e-mail]	4000
2022/8/1	美食倶楽部	岡星	[e-mail]	5000
2023/4/1	美食倶楽部	山岡	[e-mail]	1000
2023/5/1	文化部	栗田	[e-mail]	200
2023/6/1	文化部	富井	[e-mail]	300
2023/7/1	美食倶楽部	海原	[e-mail]	4000
2023/8/1	文化部	岡星	[e-mail]	500

社員

社員	Email
山岡	[e-mail]
栗田	[e-mail]
富井	[e-mail]
海原	[e-mail]
岡星	[e-mail]

部署

部署
文化部
美食倶楽部

RLS

社員	Email	部署
山岡	[e-mail]	美食倶楽部
栗田	[e-mail]	文化部
富井	[e-mail]	文化部
海原	[e-mail]	美食倶楽部
岡星	[e-mail]	文化部

3.2. 動作確認、xmSQL

山岡さん（23年4月に文化部から美食倶楽部に異動）で試してみる。ちゃんと現在の部署（＝美食倶楽部）でフィルターされている。ええやん。

ちなみに、DAXクエリとDAX Studioで確認したxmSQLは以下。DAXクエリはフィルターウィンドウでRLS動作を再現したもの（実際のRLS動作時も多分同じ）。xmSQLの細かい理解はできていないが、素直には見える（=パフォーマンス的に悪くなさそう）。

DAXクエリ

EVALUATE
	SUMMARIZECOLUMNS(
		'C_日付'[日付], 'D_社員'[社員], 'D_部署'[部署], 
		TREATAS({"[e-mail]"}, 'S_RLS'[Email]), 
		"売上", 'F_売上'[売上]
	)

xmSQL

DEFINE TABLE '$TTable2' :=
SELECT
    'S_RLS'[部署]
FROM 'S_RLS'
WHERE
    'S_RLS'[Email] = '[e-mail]',

DEFINE TABLE '$TTable3' :=
SELECT
    RJOIN ( '$TTable2'[S_RLS$部署] )
FROM '$TTable2'
    REVERSE BITMAP JOIN 'F_売上' ON '$TTable2'[S_RLS$部署]='F_売上'[部署],

DEFINE TABLE '$TTable1' :=
SELECT
    'D_社員'[社員],
    'D_部署'[部署],
    'C_日付'[日付],
    SUM ( 'F_売上'[_売上] )
FROM 'F_売上'
    LEFT OUTER JOIN 'D_社員'
        ON 'F_売上'[社員]='D_社員'[社員]
    LEFT OUTER JOIN 'D_部署'
        ON 'F_売上'[部署]='D_部署'[部署]
    LEFT OUTER JOIN 'C_日付'
        ON 'F_売上'[日付]='C_日付'[日付]
WHERE
    'F_売上'[部署] ININDEX '$TTable3'[$SemijoinProjection];

4. ふたつの方法の比較

前者の方がきれいではあるが、難しい。後者はきれいではないが、簡単。

どっちを選ぶべきか？個人的には前者（私はそっちの方が好き）であるが、使う人が中身を理解しないままだと危険。なので、チームメンバーの実力と相談しながら、こだわりよりもまずはビジネス要件を実現することを優先することも（育成してからではコスパが悪くなることも）。まあ、後者の方が悪いかというとそうでもないし。

ただ、制限付きリレーションシップ（Limited Relationship）となっている点には留意。参照整合性違反の場合とか。

5. 感想

Power BIは難しい。

やり方はいろいろある。レポート作成の締め切り、いつまで使われるか、データ数・パフォーマンス要件、作る人・チームのスキル、インポートモードなどなど、状況によって最適なものは異なるので、それに合わせよう。そのためにいろんなやり方でできる（or アドバイスできる）ようにしておく。

そして、何がいいかどうかの判断軸は、常にベストプラクティスとの比較から。だから勉強は必要（全員とは言わないが）。やっぱりPower BIは難しいし、面白い。