GROUPBY 関数(DAX)の怪しい使い方に気づくのは簡単です。

table パラメータにセマンティックモデルで定義しているテーブルを使用していること。このとき、ただちにエラーになるということではないけれど、結果を得ることができなくなったなど、あとから解決できない問題が発生する。とてもよくないのは、それを予見できていないことだ。
GROUPBY 関数(DAX)のユースケースは多くないし、他に効果的なワークアラウンドがたくさんある。なので、動作をよーく観察して理解し、得られる作用が必要になったときだけに使えばいい。

もっとも重要なポイント

この意味が解らないのであれば、GROUPBY を効果的に使用することはできないでしょう。

GROUPBY は主に、DAX テーブル式からの中間結果に対して集計を実行するために使用されます。モデル内の物理テーブルに対して効率的に集計を行うには、SUMMARIZECOLUMNS 関数または SUMMARIZE 関数の使用を検討してください。

例に挙げられている DAX query は次の通りなのだけど、

Example にある DAX query

DEFINE
    VAR SalesByCountryAndCategory =
        SUMMARIZECOLUMNS(
            Geography[Country],
            Product[Category],
            "Total Sales",
                SUMX(
                    Sales,
                    Sales[Price] * Sales[Qty]
                )
        )

EVALUATE
    GROUPBY(
        SalesByCountryAndCategory,
        Geography[Country],
        "Max Sales",
            MAXX(
                CURRENTGROUP(),
                [Total Sales]
            )
    )

次のクエリで充分なはずだ。

DAX query

EVALUATE
    SUMMARIZECOLUMNS(
        Geography[Country],
        "Max Sales",
            MAXX(
                VALUES( 'Product'[Category] ),
                CALCULATE(
                    SUMX(
                        Sales,
                        Sales[Price] * Sales[Qty]
                    )
                )
            )        
    )

GROUPBY 関数(DAX)の動作を理解し、それに基づいてユースケースを考えなければならない。

中間結果とは、Storage engine(SE)によるクエリ結果のことを指す。たとえば、ストレージモードが Import mode の場合、セマンティックモデルに含まれるデータ本体は圧縮符号化されている。SE はマテリアライズとシンプルな計算だけを担当し、大量の行数を効率よく処理する。そして、その結果を中間結果として Formula engine(FE) に提供する。

GROUPBY 関数(DAX)の特徴的な動作は、集計や計算をSEではなくFEが処理することである。そのため、この関数を利用するには注意が必要である。特に、巨大な中間結果が生成されると、リソースの過剰消費が起こりやすい。その結果としてパフォーマンスの低下やリソースのしきい値を超えたことにより結果が得られないということが起きる。このような状況を避けるためには、GROUPBY 関数の使用を適切に制御することが重要である。

DirectQuey mode (SQL)のときは、データソースでの集計処理ができなくなるということだ。

動作を知る

なぜ GROUPBY ではなく、SUMMARIZE や SUMMARIZECOLUMNS を検討すべきなのか。

DAX query : GROUPBY

EVALUATE
    GROUPBY(
        Sales,
        'Product'[区分名],
        "Sale amount",
            SUMX(
                CURRENTGROUP(),
                Sales[単価] * Sales[数量]
            )
    )

xmSQL : GROUPBY

SET DC_KIND="AUTO";
SELECT
    'Sales'[RowNumber],
    'Sales'[単価],
    'Sales'[数量],
    'Product'[区分名]
FROM 'Sales'
    LEFT OUTER JOIN 'Product'
        ON 'Sales'[商品ID]='Product'[商品ID];

GROUPBY で追加した列 Sales amount は SE で集計されないので、SE は Sales テーブル全行を FE に提供する。'Sales'[RowNumber] が全行出力していることを示していて、マテリアライズされるデータ量に関するエラーやそれ以前にパフォーマンス低下が頻繁に起きる。

DAX query : SUMMARIZE

EVALUATE
    SUMMARIZE(
        Sales,
        'Product'[区分名],
        "Sales amount",
            SUMX(
                Sales,
                Sales[単価] * Sales[数量]
            )
    )

xmSQL : SUMMARIZE

SET DC_KIND="AUTO";
WITH
    $Expr0 := ( PFCAST ( 'Sales'[単価] AS INT ) * PFCAST ( 'Sales'[数量] AS INT ) ) 
SELECT
    'Product'[区分名],
    SUM ( @$Expr0 )
FROM 'Sales'
    LEFT OUTER JOIN 'Product'
        ON 'Sales'[商品ID]='Product'[商品ID];

SET DC_KIND="AUTO";
SELECT
    'Product'[区分名]
FROM 'Sales'
    LEFT OUTER JOIN 'Product'
        ON 'Sales'[商品ID]='Product'[商品ID];

GROUPBY のように Sales テーブル全行がマテリアライズされることはなく効率的だ。しかし、SUMMARIZE で集計列を追加するとき、考えた通りに集計できない問題と潜在的なパフォーマンス低下が起きやすいはずだ。

DAX query : SUMMARIZECOLUMNS

EVALUATE
    SUMMARIZECOLUMNS(
        'Product'[区分名],
        "Sales amount",
            SUMX(
                Sales,
                Sales[単価] * Sales[数量]
            )
    )

xmSQL : SUMMARIZECOLUMNS

SET DC_KIND="AUTO";
WITH
    $Expr0 := ( PFCAST ( 'Sales'[単価] AS INT ) * PFCAST ( 'Sales'[数量] AS INT ) ) 
SELECT
    'Product'[区分名],
    SUM ( @$Expr0 )
FROM 'Sales'
    LEFT OUTER JOIN 'Product'
        ON 'Sales'[商品ID]='Product'[商品ID];

同じクエリ結果が得られるとしても、SUMMARIZECOLUMNS がもっとも効率よいということだ。

DAX workout - GROUPBY function

もっとも重要なポイント

動作を知る

その他