DAX 入門 (6) DAX 行フィルターを使用した動的なセキュリティ [SSAS]
Microsoft Japan Data Platform Tech Sales Team
川野 純
DAX 入門 第 6 回目では動的なセキュリティについて説明していきます。
先日、お客様から SQL Server Analysis Services ( 以下、SSAS ) 表形式のロール定義で Active Directory のグループをマップし、そのロールにおける固定値でフィルター条件を定義できるが、同じグループのメンバーごとにきめ細かくアクセス権を設定できないか、という相談を受けました。
確かに、Active Directory 上の組織と各データへのアクセス権限が一致している企業であれば固定値でフィルター条件を定義しても事足りますが、現実の組織では様々な事情により Active Directory の階層とは関係なく各データへのアクセス制御を行わなければならないケースが多々あります。
このような場合に便利なのが USERNAME 関数 と LOOKUPVALUE 関数 という 2 つの DAX 関数です。
これらを DAX 行フィルター式に適用する際に、Active Directory とは独立した「お客様の組織の独自ルールを定義したテーブルのデータ」と組み合わせることでも、分析データへの動的なアクセス制御を実現できます。
また、この方法は Power BI Desktop と Power BI サービスを組みわせた場合や、Power BI Desktop と Power BI サービス のバックエンドに オンプレミス データ ゲートウェイ経由で SSAS に接続する場合にも適用されます。今回はお話の発端が SSAS 表形式 (以下、簡略のために SSAS と表記) であったことから SSAS をベースにご説明します。
行フィルターを使用した動的なセキュリティの実装
SSAS のロールではユーザー/グループ マッピングと行フィルター条件を疎関係で柔軟に定義できる良さがあります。
Books OnLine : 行フィルターを使用した動的なセキュリティの実装
上記のオンラインマニュアルで USERNAME 関数 と LOOKUPVALUE 関数 という 2 つの DAX 関数を使用した動的セキュリティの設定例が紹介されています。
しかし、若干？わかりづらいので、本記事ではポイントを絞って解説していきたいと思います。下図はこの設定例を簡単に図式化したものです。
ここでポイントになる部分は、ロールの上部に出ている吹き出しに記述されている DAX 式です。この DAX 式で動的な行フィルターを実現しています。
ではこの DAX 式を見ていきましょう。
1 FILTER (
2 'Sales Territory',
3 'Sales Territory'[Sales Territory Id]
4 = LOOKUPVALUE (
5 'Employee Security'[Sales Territory Id],
6 'Employee Security'[Login Id], USERNAME (),
7 'Employee Security'[Sales Territory Id], 'Sales Territory'[Sales Territory Id]
8 )
9 )
最初は難しいかもしれませんが、一つ一つ紐解いていきましょう。
使用されている関数の意味を理解し、DAX 式を読み解こう
最初に出てきている FILTER 関数が何をしているのか理解しておく必要があります。
ざっくり言うと、table パラメーター ( DAX 式 2 行目 ) はフィルターする対象のテーブルで、filter パラメーター ( DAX 式 3 ~ 8 行目 ) はフィルタリング条件です。
’Sales Territory’ テーブルから、filter パラメーターに指定した条件で抜き出したデータをテーブル形式で返してね、と記述しています。
FILTER 関数別のテーブルまたは式のサブセットを表すテーブルを返します。構文FILTER(<table>,<filter>)パラメーターtableフィルターを適用するテーブル。 このテーブルは、結果としてテーブルが得られる式として指定することもできます。filterテーブルの行ごとに評価するブール式。 たとえば、[Amount] > 0 または [Region] = "France" のようになります。戻り値フィルター選択された行のみが含まれているテーブル。
次に、FILTER 関数の filter パラメーターの中で登場してきているのが、少々ややこしい LOOKUPVALUE 関数です。
result_columnName パラメーターは戻り値として返してほしい列を指定します。DAX 式では ’テーブル名’[列名] 形式で表現される点に注意してください。
SQL 言語の SELECT 文における SELECT リストに含める列名と、FROM 句に指定する表名を同時に指定しているようなイメージです。
search_columnName パラメーターおよび search_value パラメーターはペアで指定します。
SQL 言語の SELECT 文における WHERE 句に指定する等価条件のようなイメージです。このペアは複数指定できますが、AND 条件で結ばれる点に注意が必要です。
'Sales Territory'[Sales Territory Id] ( DAX 式 3 行目 ) と等価条件で一致するデータを LOOKUPVALUE 関数で抽出しています。
抽出するのは 'Employee Security' テーブルの [Sales Territory Id] 列の値です。( DAX 式 5 行目 )
LOOKUPVALUE 関数 (DAX)search_columnName および search_value によって指定されたすべての条件を満たす行の result_columnName 内の値を返します。構文LOOKUPVALUE(<result_columnName>,<search_columnName>, <search_value>[, <search_columnName>, <search_value>]…)パラメーターresult_columnName取得する値が含まれている既存の列の名前。標準 DAX 構文を使用して名前を指定する必要があります (通常は完全修飾名)。式を指定することはできません。search_columnName参照の対象となる、result_columnName と同じテーブル内または関連テーブル内の既存の列の名前。標準 DAX 構文を使用して名前を指定する必要があります (通常は完全修飾名)。式を指定することはできません。search_value検索対象の同じテーブル内のどの列も参照しないスカラー式。戻り値search_column および search_value のすべてのペアが一致する行の result_column の値。検索値のすべてを満たす一致がない場合は、空白が返されます。つまり、条件の一部しか一致しない場合、関数は参照値を返しません。複数の行が検索値と一致し、すべてのケースで result_column 値が等しい場合は、その値が返されます。ただし、result_column から異なる値が返される場合は、エラーが返されます。
そして、'Employee Security' テーブルの [Login Id] 列の値と USERNAME () の戻り値が一致するもの、というのが一つ目の検索条件です。( DAX 式 6 行目 )
ここで使用されている USERNAME 関数は「ドメイン名\ユーザー名」を返す、シンプルで分かりやすい関数です。
USERNAME 関数接続時にシステムに指定された資格情報のドメイン名とユーザー名を返します。構文USERNAME()戻り値接続時にシステムに指定された資格情報のユーザー名
最後に 'Employee Security' テーブルの [Sales Territory Id] 列の値のうち、'Sales Territory' テーブルの [Sales Territory Id] 列に存在するもの、というのが二つ目の検索条件です。( DAX 式 7 行目)
説明が長くなりましたが、下図は動的行フィルターで定義している内容をテーブルのイメージとともに図式化してまとめたものです。
ご理解の一助になればと思います。
補足情報１：ロール マネージャー のダイアログボックスで指定しているもの
ところで、SQL Server Data Tools ( 以下、SSDT ) でロールを定義する際に使用するロール マネージャーのダイアログボックスにおいて、行フィルター タブでは何をしているか？
ここまで読み進めてくればもうお判りだと思います。
テーブル 列 ＝ FILTER 関数の table パラメーター
DAX フィルター 列 ＝FILTER 関数の filter パラメーター
ということで FILTER 関数の引数を指定しています。
補足情報２：FILTER 関数の filter パラメーターを複合条件にするには
&& 演算子や || 演算子も使えますし、AND 関数や OR 関数を使用する方法もあります。
FILTER(<table>, (<filter1>) && (<filter2>))
FILTER(<table>, AND((<filter1>), (<filter2>)))
FILTER(<table>, (<filter1>) || (<filter2>))
FILTER(<table>, OR((<filter1>), (<filter2>)))
補足情報３：DaxStudio と SSDT の使い分け
DAX 式のテストは DaxStudio が便利です。とはいえ、SSDT にもいいところはあります。
あくまで私見ですが、それぞれのツールには以下のような特徴があると感じています( DAX に絞った話です )。
SSDT でのテスト
・ユーザーを指定したロールテストは便利
・複数ロールを組合せたテストは便利
・DAX 式を小さいボックスに IntelliSense なしで記述して、動作確認するのに毎回 Excel を起動するのはちょっと...
DaxStudio でのテスト
・実際に SSAS に接続しており、DAX 式を記述する際に IntelliSense が動作するので生産性が高い
・記述した DAX 式の整形 ( Format Query ) したり、実行 ( Run ) ボタンですぐに結果を確認できるところがとても良い
最後に
DAX は少々とっつきにくいところはありますが、使いこなすと非常に強力な言語です。
この記事をきっかけに興味を持たれた方は以下の連載記事もぜひご覧ください。
関連記事
関連リンク