はじめに
すこし複雑なメジャーを記述しようとすると、避けては通れないテーブル操作関数。主要なSUMMARIZE、ADDCOLUMNS、 SUMMARIZECOLUMNSなどを研究してみます。
テーブル操作関数シリーズ
続きものです。
- SUMMARIZE関数の研究～集計列の追加はなぜ非推奨なのか🤔～
- ADDCOLUMNS関数の研究～集計列追加のベストプラクティス🤔～
- 集計列追加のベストプラクティス🤔～とはいえ例外だってあるぞ～
- SUMMARIZECOLUMNS関数の研究～SUMMARIZE関数は非推奨?🤔～
- GROUPBY関数の研究🤔～基本動作確認編～
DAXクエリ＆DAX.do
今回の検証はDAX.do上でDAXクエリを書いて検証していきます。
DAXクエリがわからない方は先にこの記事を読んで、DAX.doで少し触ってからのほうが理解が進むでしょう。
DAXクエリについてはこちらの記事を参照してください。
参考記事
SUMMARIZECOLUMNS関数の文法
まず書いてみる
SUMMARIZE関数やADDCOLUMNS関数と違って、SUMMARIZECOLUMNS関数は元のテーブルを必要としないため、直感的に記述していくことができます。
EVALUATE
SUMMARIZECOLUMNS ( 'Product'[Category], Customer[Country])
ORDER BY
'Product'[Category]
結果
列のグループ化は簡単。リレーションシップが直接つながっていないテーブル同士でも問題なく動作している。
CROSSJOIN 関数でも同じことがかける
EVALUATE
CROSSJOIN ( VALUES ( 'Product'[Category] ), VALUES ( Customer[Country] ) )
ORDER BY 'Product'[Category]
もちろんSUMMARIZE関数でも同じことが書ける
リレーションがつながっていないため、SUMMARIZE関数で最初に指定するテーブルにProductテーブルやCustomerテーブルを指定しても、エラーが返ってくる。
EVALUATE
SUMMARIZE ( 'Product', 'Product'[Category], Customer[Country] )
ORDER BY 'Product'[Category]
EVALUATE
SUMMARIZE ( 'Customer', 'Product'[Category], Customer[Country] )
ORDER BY 'Product'[Category]
列が見つからないよとエラーが出る。
正しい記述方法
EVALUATE
SUMMARIZE ( 'Sales', 'Product'[Category], Customer[Country] )
ORDER BY 'Product'[Category]
結果
SUMMARIZE関数でも同じ結果を出すことができました。
集計列の追加をしてみる。
SUMMARIZECOLUMNS関数
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Category],
Customer[Country],
"SalesAMT",
SUMX (
'Sales', Sales[Net Price] * Sales[Quantity]
)
)
ORDER BY 'Product'[Category]
正しい数値がでています。
フィルターコンテキストを変更してみる。
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Category],
Customer[Country],
"SalesAMT", SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ),
"TotalAMT",
CALCULATE (
SUMX ( 'Sales', Sales[Net Price] * Sales[Quantity] ),
REMOVEFILTERS ( 'Product'[Category] ),
REMOVEFILTERS ( Customer[Country] )
)
)
ORDER BY [SalesAMT] DESC
SUMMARIZE関数だけでは難しかったフィルターコンテキストの操作も直感的に行うことができました。
パフォーマンス比較 （SUMMARIZE関数とSUMMARIZECOLUMNS関数）
同じ結果がでるクエリを書いて実行してみる。
EVALUATE
ADDCOLUMNS (
SUMMARIZE (
Sales,
'Product'[Brand],
Customer[Country]
),
"SalesAMT",
CALCULATE (
SUMX (
Sales,
Sales[Net Price] * Sales[Quantity]
)
)
)
ORDER BY [SalesAMT] DESC
EVALUATE
SUMMARIZECOLUMNS (
'Product'[Brand],
Customer[Country],
"SalesAMT",
SUMX (
Sales,
Sales[Net Price] * Sales[Quantity]
)
)
ORDER BY [SalesAMT] DESC
結果は同じ。
DAX Studioで結果を確認。発行されているクエリの数がSUMMARIZECOLUMNS関数では１つだけ。SUMMARIZE関数で作成したDAXクエリの方は２回スキャンが走ってしまっており、行数が増えてきたときにパフォーマンスの差がでそうです。
SUMMARIZE関数は非推奨？🤔
参考記事から引用します。
SUMMARIZE関数はほとんど非推奨と考えた方がよいでしょう。SUMMARIZEの唯一の安全な使用法は、追加式を含まず、テーブルを結合してSQL言語のSELECT DISTINCTと同等の結果を得るためにのみSUMMARIZEを使用する場合です。
SUMMARIZECOLUMNS関数は、非常に最適化されたクエリプランを生成します。そして、SUMMARIZEやADDCOLUMNS/SUMMARIZEパターンの代わりとして考慮すべきです。
Conclusion
You should consider the SUMMARIZE function almost like a deprecated one. The only safe use of SUMMARIZE is when you do not include additional expressions, and you use SUMMARIZE only to join tables and obtain a result equivalent to a SELECT DISTINCT in SQL language.
The SUMMARIZECOLUMNS function produces a very optimized query plan, and you should consider it as a replacement to SUMMARIZE and ADDCOLUMNS/SUMMARIZE pattern.
注意点
便利で処理も早いSUMMARIZECOLUMNS関数ですが、比較的最近追加された関数のため、使用できるモデルに制限があります。
- Excel 2010: サポートされていません。
- Excel 2013: サポートされていません。
- Excel 2016: 外部フィルターコンテキストがない場合のみサポートされます (*)
- Analysis Services 2012: サポートされていません。
- Analysis Services 2014: サポートされていません。
- Analysis Services 2016: サポートされています。
- Power BI Desktop: サポートされています。
(*) Excel 2016 では、SUMMARIZECOLUMNS に外部フィルター コンテキスト（CALCULATE や CALCULATETABLE など）を適用できないため、メジャー式で使用することはできません。
まとめ
まだ理解しているとは言い難いけど、手を動かして調べていくと少しわかってくるもんだ。