ユーザー定義集計

Power BI の集計により、大規模な DirectQuery セマンティック モデルに対するクエリ パフォーマンスを向上させることができます。 集計を使用すると、メモリ内の集計レベルでデータをキャッシュします。 この記事の説明に従って、データ モデルの Power BI で集計を手動で構成できます。 Premium サブスクリプションの場合、モデル設定で 自動集計 機能を有効にして、それらを自動的に作成できます。

集計テーブルの作成

データ ソースの種類に応じて、テーブルまたはビューのネイティブ クエリとしてデータ ソースで集計テーブルを作成できます。 パフォーマンスを最大限に高めるには、Power Query で作成されたインポート テーブルとして集計テーブルを作成します。 Power BI Desktop の [集計の管理] ダイアログを使用して、集計、詳細テーブル、および詳細列のプロパティを含む集計列の集計を定義します。

ディメンション データ ソース (データ ウェアハウスやデータ マートなど) では、 リレーションシップベースの集計を使用できます。 Hadoop ベースの大規模なデータ ソースは、多くの場合 、GroupBy 列に基づいて集計を行います。 この記事では、データ ソースの種類ごとの一般的な Power BI データ モデリングの違いについて説明します。

集計の管理

Power BI Desktop ビューの [データ ] ウィンドウで、集計テーブルを右クリックし、[ 集計の管理] を選択します。

[ 集計の管理 ] ダイアログには、テーブル内の各列の行が表示され、集計の動作を指定できます。 次の例では、 Sales 詳細テーブルへのクエリは、Sales Agg 集計テーブルに内部的にリダイレクトされます。

このリレーションシップ ベースの集計の例では、GroupBy エントリは省略可能です。 DISTINCTCOUNT を除き、これらは集計動作には影響せず、主に読みやすくするために使用されます。 GroupBy エントリがない場合でも、リレーションシップに基づいて集計がヒットします。 この動作は、この記事で後述する ビッグ データの例 とは異なり、GroupBy エントリが必要です。

検証

[ 集計の管理 ] ダイアログでは、次の検証が適用されます。

• 詳細列には、集計列と同じデータ型が必要です。ただし、Count および Count テーブル行の集計関数を除きます。 Count と Count テーブル行は、整数集計列でのみ使用でき、一致するデータ型を必要としません。
• 3 つ以上のテーブルをカバーするチェーン集計は許可されません。 たとえば、テーブル A の集計では、テーブル C を参照する集計があるテーブル B を参照することはできません。
• 重複する集計では、2 つのエントリが同じ 集計 関数を使用し、同じ 詳細テーブル と 詳細列を参照することはできません。
• 詳細テーブルでは、インポートではなく DirectQuery ストレージ モードを使用する必要があります。
• 非アクティブなリレーションシップで使用される外部キー列によるグループ化と、集計ヒットの USERELATIONSHIP 関数への依存はサポートされていません。 代わりに、USERELATIONSHIP の代わりに TREATAS 関数を使用することもできます。 TREATAS を使用する場合は、テーブル間にアクティブなリレーションシップがないことを確認します。 この構成で TREATAS を使用する場合、集計は引き続き影響を受ける可能性があります。
• GroupBy 列に基づく集計では集計テーブル間のリレーションシップを使用できますが、Power BI Desktop では集計テーブル間のリレーションシップの作成はサポートされていません。 必要に応じて、XML for Analysis (XMLA) エンドポイントを使用して、サードパーティのツールまたはスクリプト ソリューションを使用して集計テーブル間のリレーションシップを作成できます。

ほとんどの検証は、ドロップダウン値を無効にし、ヒントに説明テキストを表示することによって適用されます。

集計テーブルは非表示

モデルへの読み取り専用アクセス権を持つユーザーは、集計テーブルに対してクエリを実行できません。 読み取り専用アクセスでは、 行レベル セキュリティ (RLS) で使用する場合のセキュリティ上の問題を回避できます。 コンシューマーとクエリは集計テーブルではなく詳細テーブルを参照するため、集計テーブルについて知る必要はありません。

このため、集計テーブルは レポート ビューに表示されません。 テーブルがまだ非表示になっていない場合、[ 集計の管理 ] ダイアログでは、[ すべて適用] を選択すると非表示に設定されます。

ストレージ モード

集計機能は、テーブル レベルのストレージ モードで動作します。 Power BI テーブルでは、 DirectQuery、 インポート、または デュアル ストレージ モードを使用できます。 DirectQuery はクエリをバックエンドに直接送信し、Import はデータをメモリにキャッシュし、キャッシュされたデータにクエリを送信します。 すべての Power BI Import データ ソースと非多次元 DirectQuery データ ソースは、集計で機能します。

集計テーブルのストレージ モードをインポートに設定してクエリを高速化するには、Power BI Desktop モデル ビューで集計テーブルを選択します。 [プロパティ] ウィンドウで、[詳細設定] を展開し、[ストレージ モード] で選択項目をドロップダウンして、[インポート] を選択します。 ストレージ モードを [インポート] に設定すると、もう一度変更することはできません。

テーブル ストレージ モードの詳細については、「 Power BI Desktop でのストレージ モードの管理」を参照してください。

集計の RLS

集計を正しく機能させるには、RLS 式で集計テーブルと詳細テーブルの両方をフィルター処理する必要があります。

次の例では、 Geography テーブルの RLS 式は集計に対して機能します。Geography は Sales テーブルと Sales Agg テーブルとのリレーションシップのフィルター処理側にあるためです。 集計テーブルを使用するクエリと、RLS が正常に適用されていないクエリ。

Product テーブルの RLS 式では、集計された Sales Agg テーブルではなく、詳細 Sales テーブルのみがフィルター処理されます。 集計テーブルは詳細テーブル内のデータのもう 1 つの表現であるため、RLS フィルターを適用できない場合は、集計テーブルからのクエリに応答するのは安全ではありません。 このロールのユーザー クエリは集計ヒットのメリットを得られないため、詳細テーブルのみをフィルター処理することはお勧めしません。

Sales Agg 集計テーブルのみをフィルター処理し、Sales 詳細テーブルをフィルター処理しない RLS 式は使用できません。

GroupBy 列に基づく集計の場合、集計テーブルのすべての GroupBy 列が詳細テーブルでカバーされるため、詳細テーブルに適用される RLS 式で集計テーブルをフィルター処理できます。 一方、集計テーブルの RLS フィルターでは詳細テーブルをフィルター処理できないため、許可されません。

リレーションシップに基づく集計

ディメンション モデルでは、通常、 リレーションシップに基づいて集計が使用されます。 データ ウェアハウスとデータ マートの Power BI モデルは、スター スキーマとスノーフレーク スキーマに似ています。ディメンション テーブルとファクト テーブル間のリレーションシップがあります。

次の例では、モデルは 1 つのデータ ソースからデータを取得します。 テーブルでは DirectQuery ストレージ モードが使用されます。 Sales ファクト テーブルには、数十億行が含まれています。 キャッシュのために Sales のストレージ モードをインポートに設定すると、かなりのメモリとリソースのオーバーヘッドが消費されます。

代わりに、 Sales Agg 集計テーブルを作成します。 Sales Agg テーブルの行数は、CustomerKey、DateKey、ProductSubcategoryKey でグループ化された SalesAmount の合計と等しくなります。 Sales Agg テーブルは Sales よりも細分性が高いので、数十億行ではなく数百万行が含まれている可能性があり、管理が容易になります。

次のディメンション テーブルがビジネス価値の高いクエリに最も一般的に使用されている場合、一対多リレーションシップや多対一リレーションシップを使用してSales Aggをフィルターすることができます。

• 地理学
• カスタマー
• 日付
• 製品サブカテゴリ
• 製品カテゴリ

次の図は、このモデルを示しています。

次の表は、 Sales Agg テーブルの集計を示しています。

集計された Sales Agg テーブルのストレージ モードを インポート に変更すると、関連するディメンション テーブルをストレージ モード Dual に設定できることを示すダイアログ ボックスが開きます。

関連するディメンション テーブルを Dual に設定すると、サブクエリに応じて Import または DirectQuery として機能します。 この例では、次のようになります。

• インポート モード の Sales Agg テーブルからメトリックを集計し、関連するデュアル テーブルの属性別にグループ化するクエリは、メモリ内キャッシュから結果を返します。
• DirectQuery Sales テーブルのメトリックを集計し、関連するデュアル テーブルの属性でグループ化するクエリは、DirectQuery モードで結果を返します。 GroupBy 操作を含むクエリ ロジックは、ソース データベースに渡されます。

デュアル ストレージ モードの詳細については、「 Power BI Desktop でのストレージ モードの管理」を参照してください。

通常のリレーションシップと制限されたリレーションシップ

リレーションシップに基づく集計ヒットには、通常のリレーションシップが必要です。

通常のリレーションシップには、次のストレージ モードの組み合わせが含まれます。両方のテーブルが 1 つのソースから取得されます。

ソース間リレーションシップが通常の場合は、両方のテーブルがインポートに設定されている場合のみです。 多対多リレーションシップは常に制限されます。

リレーションシップに依存しない ソース間 集計ヒットについては、「 GroupBy 列に基づく集計」を参照してください。

リレーションシップベースの集計クエリの例

Date テーブルの 列は集計を使用できる粒度であるため、次のクエリでは集計が使用されます。 SalesAmount 列では、合計集計が使用されます。

次のクエリでは、集計は使用されません。 SalesAmount の合計を要求したにもかかわらず、クエリは Product テーブル内の列に対して GroupBy 操作を実行しています。これは、集計を使用できる粒度ではありません。 モデル内のリレーションシップを観察すると、1 つの製品サブカテゴリに複数 の Product 行を含めることができます。 クエリでは、集計先の製品を特定できません。 この場合、クエリは DirectQuery に戻り、SQL クエリをデータ ソースに送信します。

集計は、単純な合計を実行する単純な計算だけではありません。 複雑な計算にもメリットがあります。 概念的には、複雑な計算は、SUM、MIN、MAX、COUNT ごとにサブクエリに分割されます。 各サブクエリは、集計を使用できるかどうかを判断するために評価されます。 このロジックは、クエリプランの最適化のためにすべてのケースで当てはまるわけではありませんが、一般的には適用する必要があります。 次の例では、集計を使用します。

COUNTROWS 関数は集計を利用して利点を得ることができます。 次のクエリでは、Count テーブル行の集計がSalesテーブルに対して定義されているため、集計を使用します。

AVERAGE 関数は集計の恩恵を受けることができます。 AVERAGE は内部的に COUNT で除算された SUM に折りたたまれるため、次のクエリでは集計が使用されます。 UnitPrice 列には SUM と COUNT の両方に対して集計が定義されているため、集計が使用されます。

場合によっては、DISTINCTCOUNT 関数は集計の恩恵を受けることができます。 次のクエリでは、CustomerKey の GroupBy エントリがあるため、集計テーブル内で CustomerKey の区別を維持しつつ、集計を使用しています。 この手法は、200 万から 500 万を超える個別の値がクエリのパフォーマンスに影響する可能性があるパフォーマンスしきい値に達する可能性があります。 ただし、詳細テーブルには何十億もの行があり、列には 2 ~ 500 万の個別の値があるシナリオで役立ちます。 この場合、DISTINCTCOUNT は、メモリにキャッシュされた場合でも、数十億行のテーブルをスキャンするよりも高速に実行できます。

データ分析式 (DAX) のタイム インテリジェンス関数は、集計に対応しています。 次のクエリでは、DATESYTD 関数によって CalendarDay 値のテーブルが生成され、集計テーブルは Date テーブル内のグループ化列に対応する粒度になっているため、集計が使用されます。 これは、集計を操作できる CALCULATE 関数に対するテーブル値フィルターの例です。

GroupBy 列に基づく集計

Hadoop ベースのビッグ データ モデルは、ディメンション モデルとは異なる特性を持ちます。 大きなテーブル間の結合を回避するために、ビッグ データ モデルではリレーションシップを使用せず、ファクト テーブルにディメンション属性を非正規化することがよくあります。 このようなビッグ データ モデルのロックを解除して、 GroupBy 列に基づく集計を使用して対話型分析を行うことができます。

次の表に、集計する 移動 の数値列を示します。 他のすべての列は、グループ化する属性です。 このテーブルには、IoT データと大量の行が含まれています。 ストレージ モードは DirectQuery です。 モデル全体で集計されるデータ ソースに対するクエリは、ボリュームが大きいため、低速です。

このモデルで対話型分析を有効にするには、ほとんどの属性でグループ化する集計テーブルを追加しますが、経度や緯度などのカーディナリティの高い属性は除外します。 この方法では、行数が大幅に減り、メモリ内キャッシュに快適に収まるように十分に小さくなります。

[集計の管理] ダイアログで、 ドライバー アクティビティ Agg テーブルの 集計マッピングを 定義します。

GroupBy 列に基づく集計では、 GroupBy エントリは省略可能ではありません。 これらを指定しないと、集計にヒットしません。 この動作は、GroupBy エントリが省略可能なリレーションシップに基づく集計の使用とは異なります。

次の表は、 Driver Activity Agg テーブルの集計を示しています。

集計された ドライバー アクティビティ Agg テーブルのストレージ モードを [インポート] に設定します。

GroupBy 集計クエリの例

次のクエリでは、 アクティビティの日付 列が集計テーブルの対象となるため、集計が使用されます。 COUNTROWS 関数は、 Counted テーブル行 の集計を使用します。

特に、ファクト テーブルにフィルター属性を含むモデルの場合は、 Count テーブル行 の集計を使用することをお勧めします。 ユーザーが明示的に要求していない場合、Power BI は COUNTROWS を使用してモデルにクエリを送信できます。 たとえば、フィルター ダイアログには、各値の行数が表示されます。

集計手法の組み合わせ

集計のリレーションシップと GroupBy 列の手法を組み合わせることができます。 リレーションシップに基づく集計では、非正規化ディメンション テーブルを複数のテーブルに分割する必要があります。 この要件が特定のディメンション テーブルに対してコストがかかるか実用的でない場合は、それらのディメンションの集計テーブルで必要な属性をレプリケートし、他のディメンションにリレーションシップを使用できます。

たとえば、次のモデルでは、Sales Agg テーブルの Month、Quarter、Semester、Year がレプリケートされます。 Sales Agg と Date テーブルの間にはリレーションシップはありませんが、Customer と Product Subcategory にはリレーションシップがあります。 Sales Agg のストレージ モードは Import です。

次の表は、Sales Agg テーブルの [集計の管理] ダイアログで設定されたエントリを示しています。 Date 属性でグループ化するクエリに集計を使用するには、Date が詳細テーブルである GroupBy エントリが必須です。 前の例と同様に、CustomerKey と ProductSubcategoryKey の GroupBy エントリは、リレーションシップが存在するため、DISTINCTCOUNT を除き、集計の使用には影響しません。

集計クエリの組み合わせの例

次のクエリでは、集計テーブルが CalendarMonth を対象とし、一対多リレーションシップを通じて CategoryName にアクセスできるため、集計を使用します。 このクエリでは、SalesAmount の SUM 集計が使用されます。

集計テーブルには CalendarDay が含まれていないため、次のクエリでは集計は使用されません。

DATESYTD 関数は CalendarDay 値のテーブルを生成し、集計テーブルは CalendarDay を対象としていないため、次のタイム インテリジェンス クエリでは集計は使用されません。

集計の優先順位

集計の優先順位により、1 つのサブクエリで複数の集計テーブルを考慮できます。

次の例は、複数のソースを含む 複合モデル です。

• Driver Activity DirectQuery テーブルには、大規模なデータ システムからソース化された数兆行を超える IoT データが含まれています。 ドリルスルー クエリを使用して、制御されたフィルター コンテキストで個々の IoT 読み取り値を表示します。
• Driver Activity Agg テーブルは、DirectQuery モードの中間集計テーブルです。 Azure Synapse Analytics (旧称 SQL Data Warehouse) には 10 億行を超える行が含まれており、列ストア インデックスを使用してソースで最適化されています。
• ドライバー アクティビティ Agg2 インポート テーブルは、グループ化属性が少なくカーディナリティが低いため、粒度が高くなります。 行数は数千行と同じくらい少ないため、メモリ内キャッシュに簡単に収めることができます。 これらの属性は、高いプロファイルを持つエグゼクティブ ダッシュボードで使用されるため、それらを参照するクエリは可能な限り高速にする必要があります。

このモデルのメモリ占有領域は比較的小さいが、巨大なモデルのロックを解除する。 これは、アーキテクチャのコンポーネント間でクエリの負荷を分散し、その長所に基づいてそれらを利用するため、バランスの取れたアーキテクチャを表します。

ドライバー アクティビティ Agg2 の [管理された集計] ダイアログでは、[優先順位] フィールドが 10 に設定されます。これは、ドライバー アクティビティ Agg の場合よりも高くなります。 優先順位の設定が高いほど、集計を使用するクエリでは、最初に ドライバー アクティビティ Agg2 が考慮されます。 ドライバー アクティビティ Agg2 が応答できる粒度ではないサブクエリは、代わりにドライバー アクティビティ Agg を考慮できます。 どちらの集計テーブルでも応答できない詳細クエリは、 ドライバー アクティビティに送ることができます。

詳細テーブル列に指定されたテーブルは、ドライバー アクティビティAgg ではなく、ドライバー アクティビティです。チェーン集計は許可されないためです。

次の表は、 ドライバー アクティビティ Agg2 テーブルの集計を示しています。

クエリが集計にヒットしたか見落とされたかを検出する

SQL Profiler は、クエリがメモリ内キャッシュ ストレージ エンジンから取得されたかどうか、または DirectQuery がクエリをデータ ソースにプッシュするかどうかを検出できます。 同じプロセスを使用して、集計が使用されているかどうかを検出できます。 詳細については、「 キャッシュにヒットまたはミスしたクエリ」を参照してください。

SQL Profiler では、 Query Processing\Aggregate Table Rewrite Query 拡張イベントも提供されます。

次の JSON スニペットは、集計が使用される場合のイベントの出力の例を示しています。

• matchingResult は、サブクエリが集計を使用することを示しています。
• dataRequest には、サブクエリで使用される GroupBy 列と集計列が表示されます。
• マッピング には、マップ先の集計テーブル内の列が表示されます。

キャッシュの同期を維持する

DirectQuery、インポート、デュアル ストレージ モードを組み合わせた集計では、インメモリ キャッシュがソース データと同期している場合を除き、異なるデータが返される可能性があります。 たとえば、クエリの実行では、キャッシュされた値と一致するように DirectQuery の結果をフィルター処理することで、データの問題をマスクしようとはしません。 ソースでこれらの問題を処理する必要がある場合があります。 パフォーマンスの最適化によって、ビジネス要件を満たす能力が損なわれることはありません。 データ フローを理解し、それに応じて設計する必要があります。

考慮事項と制限事項

• 集計では、 動的 M クエリ パラメーターはサポートされていません。

• 2022 年 8 月以降、機能の変更により、Power BI では、セキュリティ リスクの可能性があるため、シングル サインオン (SSO) が有効なデータ ソースを含むインポート モード集計テーブルは無視されます。 集計で最適なクエリ パフォーマンスを確保するには、これらのデータ ソースの SSO を無効にします。

Community

Power BI には、MVP、BI 担当者、同僚がディスカッション グループ、ビデオ、ブログなどの専門知識を共有する活気に満ちたコミュニティがあります。 集計について学習するときは、次のリソースを必ず確認してください。

• Power BI コミュニティ
• Bingで "Power BI 集計" を検索する

関連コンテンツ

• 自動集計
• 複合モデル