Excel と Power Pivot アドインを使用して、メモリを効率的に使用するデータモデルを作成する

Excel では、数百万行を含むデータモデルを作成し、これらのモデルに対して強力なデータ分析を実行できます。データモデルは、同じワークブックで任意の数のピボットテーブル、チャート、Power View の可視化をサポートする、PowerPivot アドインを使用して作成することも、使用せずに作成することもできます。

Excel では大量のデータモデルを簡単に構築できますが、いくつかの理由で作成できない場合もあります。まず、複数のテーブルと列を含む大きなモデルはほとんどの分析の限界を超えるもので、厄介なフィールドリストが作成されます。次に、貴重なメモリを消費する大きなモデルは、他のアプリケーションに悪影響を及ぼし、同じシステムリソースを共有していることが報告されます。最後に、 Microsoft 365では、SharePoint Online と Excel Web App の両方で Excel ファイルのサイズが 10 MB に制限されます。膨大な数の行を含むワークブックのデータモデルでは、すぐに 10 MB の制限に達してしまいます。詳細については、「データモデルの仕様と制限」を参照してください。

この記事では、作業が容易でメモリの使用量が少ない、緊密に構成されたモデルの構築方法について説明します。効率的なモデル設計のベストプラクティスを時間をかけて学ぶと、Excel、sharePoint Online、 Microsoft 365Office Online Server、または SharePoint で表示する場合でも、作成して使用するすべてのモデルの道のりが報われます。

Workbook Size Optimizer の実行も検討してください。 Excel ブックを分析し、可能な場合は圧縮してくれるツールです。 Workbook Size Optimizer は、こちらからダウンロードできます。

この記事の内容

比率とメモリ内分析エンジンの比較
メモリ使用量を減らすには、列の数を少なくする
- 常に実行すべき列の 2 つの例
- 不要な列の除外方法
必要な行をフィルター処理する場合
列が必要な場合に、その容量を削減する方法
- [Datetime] 列の変更
- SQL クエリの変更
列の代わりに DAX 計算メジャーを使用する
- どの 2 つの列を維持すべきでしょうか。
結論
関連項目

比率とメモリ内分析エンジンの比較

Excel のデータモデルは、メモリ内分析エンジンを使用してデータをメモリに格納します。このエンジンは強力な圧縮技術を搭載していて、記憶域の要件を削減し、記憶域の元のサイズの分数となるまで、一連の結果を縮小します。

概して、同じデータの元の状態に比べて、データモデルは 7 倍から 10 倍小さくなります。たとえば、SQL Server データベースから 7 MB のデータをインポートする場合、Excel では 1 MB 以下となります。圧縮の程度は、実際には主にそれぞれの列の一意の値の数によって決まります。一意の値が多い場合は、保存するのにより多くのメモリが必要になります。

圧縮と一意の値について説明する理由メモリ使用量を最小限に抑える効率的なモデルを構築することは圧縮の最大化に関することであり、これを行う最も簡単な方法は、特にそれらの列に多数の一意の値が含まれている場合に、実際に必要のない列を削除することです。

注: 個別の列によって、記憶域要件の違いは大きなものになります。場合によっては、一意の値が多い 1 つの列よりも、一意の値が少ない複数の列のほうが好ましい場合もあります。 Datetime 最適化のセクションで、この手法の詳細を説明します。

メモリ使用量を減らすには、列の数を少なくする

メモリにとって最も効率的な列は、最初にインポートされることはありません。効率的なモデルを構築する場合は、それぞれの列に注目して、実行する分析でそれが役立つがどうかを検討します。役立たない場合またはわからない場合、その列は使用しません。必要な場合は、常に新しい列を追加できます。

常に実行すべき列の 2 つの例

最初の例は、データウェアハウスからのデータに関連するものです。データウェアハウスでは、ウェアハウスにデータを読み込んで更新する ETL プロセスのアイテムがあるのが一般的です。「作成日」、[更新日」、「ETL 実行」のような列は、データが読み込まれたときに作成されます。これらの列はいずれもモデルでは不要で、データをインポートするときには選択を解除する必要があります。

2 番目の例には、ファクトテーブルのインポート時の主キー列の省略が含まれます。

ファクトテーブルなどの多くのテーブルには、主キーがあります。顧客、従業員、または販売データなどが含まれるほとんどのテーブルでは、テーブルの主キーをモデル内での関係を作成するのに使用します。

ファクトテーブルの場合は異なります。ファクトテーブルでは、主キーはそれぞれの行を一意に識別するのに使用されます。正規化の目的で必要であっても、それらの列を分析のみに使用するか、テーブルの関係の確立にのみ使用する場合は、データモデルでは役に立ちません。そのため、ファクトテーブルからインポートする場合、その主キーは含めません。ファクトテーブルの主キーはモデルのスペースを大量に消費しますが、関係の作成には使用できないため、そのメリットはありません。

注: データウェアハウスと多次元データベースでは、ほとんどの数値データで構成される大きなテーブルは、多くの場合、"ファクトテーブル" と呼ばれます。ファクトテーブルには、通常、組織単位、製品、市場セグメント、地理的地域などに合わせて集計および調整される売上データやコストデータポイントなど、ビジネスパフォーマンスまたはトランザクションデータが含まれます。ビジネスデータを含むファクトテーブル内のすべての列、または他のテーブルに格納されているデータを相互参照するために使用できる列は、データ分析をサポートするためにモデルに含める必要があります。除外する列はファクトテーブルの主キー列であり、ファクトテーブルにのみ存在し、他の場所には存在しない一意の値で構成されます。ファクトテーブルは非常に大きいため、モデル効率の最大の向上の一部は、ファクトテーブルから行または列を除外したことから得られます。

不要な列の除外方法

効率的なモデルに含まれる列は使用するワークブックで実際に必要となる列のみです。モデルに含める列を指定したい場合は、Excel の [データのインポート] ダイアログボックスではなく、Power Pivot アドインでテーブルのインポートウィザードを使用してデータをインポートする必要があります。

テーブルのインポートウィザードを起動したら、インポートするデータを選びます。

PowerPivot アドインのテーブルのインポートウィザード

それぞれのテーブルで、[プレビュー] と [フィルター] をクリックして、本当に必要なテーブルの一部を選びます。分析が必要であるかどうかを考慮した後で、最初にすべての列の選択を解除してから、必要な列をチェックすることをお勧めします。

テーブルのインポートウィザードの [プレビュー] ウィンドウ

必要な行をフィルター処理する場合

会社のデータベースには数多くのテーブルがあり、データウェアハウスには長期間にわたる履歴データが蓄積されています。また、関心のあるテーブルには、特定の分析には必要のない地域のビジネス情報が含まれている場合もあります。

テーブルのインポートウィザードを使用して、履歴データまたは関連のないデータを除外し、モデルに大きなスペースを確保します。次の画像は、今年のデータのみを含む行のみを取得して不要な履歴データを除外するのに使用する日付フィルターです。

テーブルのインポートウィザードの [フィルター] ウィンドウ

列が必要な場合に、その容量を削減する方法

列をさらに圧縮して整理するのに使用できる方法がいくつかあります。圧縮に影響を与える列の唯一の特性は、一意の値の数であることを思い出してください。このセクションでは、いくつかの列を変更して、一意の値の数を削減する方法を学習します。

[Datetime] 列の変更

多くの場合、[Datetime] 列は多くのスペースをとります。この日付タイプに必要な記憶域を削減する方法がいくつかあります。この手法は列をどのように使用するのかと、SQL クエリ構築の快適さのレベルによって変わります。

[Datetime] 列には日付部分と時刻部分があります。列が自分にとって必要であるかどうかを考えるときに、同じく [Datetime] 列に複数の時刻部分が必要であるかどうかも考えます。

時間は必要ですか。
時間のレベルで時刻が必要ですか。分は必要ですか。秒は必要ですか。ミリ秒は必要ですか。
複数の [Datetime] 列の差異を計算するため、またはデータを年別、月別、四半期別などで集計するために、それを必要としていますか。

それぞれの質問に答えていくことで、[Datetime] 列を処理するオプションが決まります。

これらの解決方法のすべてで、SQL クエリの変更が必要になります。クエリの変更を簡単に行うためには、すべてのテーブルで少なくとも 1 つの列を除外する必要があります。列を除外することで、クエリの構造を省略された形式 (SELECT *) から、変更がはるかに容易な完全修飾列名を含む SELECT ステートメントに変更します。

作成されたクエリを見てみましよう。 [表のプロパティ] ダイアログボックスでクエリエディターに切り替えて、それぞれのテーブルの現在の SQL クエリを表示できます。

[表のプロパティ] コマンドが表示された [Power Pivot] ウィンドウ

[表のプロパティ] で、[クエリエディター] を選びます。

[表のプロパティ] ダイアログボックスでクエリエディターを開く

クエリエディターには、テーブルの入力に使用される SQL クエリが表示されます。インポート中に列を除外した場合は、クエリに完全修飾列名が含まれます。

データの取得に使用した SQL クエリ

一方、列をすべて選択した状態またはフィルターを適用せずにテーブル全体をインポートした場合は、クエリが「Select * from (から選択)」で表示されます。これは変更がより困難です。

既定の短い構文を使用した SQL クエリ

SQL クエリの変更

これで、クエリを見つける方法がわかりました。さらにクエリを変更して、モデルのサイズを小さくしていきます。

通貨または小数点付きデータを含む列の場合、小数点以下を必要としない場合は、次の構文を使用して小数点以下を除外します。

"SELECT ROUND([Decimal_column_name],0)... .”

セントが必要な場合で、セントの小数点以下が不要な場合は、0 を 2 に置き換えます。負の数字を使用する場合は、一の位、十の位、百の位に丸めることができます。
dbo.Bigtable.[Date Time] という名前の [Datetime] 列があり、時刻部分が不要な場合は、この構文を使用して時刻を除外します。

“SELECT CAST (dbo.Bigtable.[Date time] as date) AS [Date time]) “
dbo.Bigtable.[Date Time] という名前の [Datetime] 列があり、日付部分と時刻部分が必要な場合は、SQL クエリで単一の [Datetime] 列の代わりに複数の [Datetime] 列を使用します。

“SELECT CAST (dbo.Bigtable.[Date Time] as date ) AS [Date Time],

datepart(hh, dbo.Bigtable.[Date Time]) as [Date Time Hours],

datepart(mi, dbo.Bigtable.[Date Time]) as [Date Time Minutes],

datepart(ss, dbo.Bigtable.[Date Time]) as [Date Time Seconds],

datepart(ms, dbo.Bigtable.[Date Time]) as [Date Time Milliseconds]”

必要なだけの列を使用して、個別の列の必要な部分を保存します。
時間と分が必要な場合で、それらをまとめて 1 つの時刻列として参照する場合は、次の構文を使用します。

Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]
[Start Time] と [End Time] のような 2 つの [Datetime] 列があり、それらの時刻の差 [Duration] という列に秒単位で必要としている場合は、リストから両方の列を削除して、次の構文を追加します。

“datediff(ss,[Start Date],[End Date]) as [Duration]”

ss の代わりに ms をキーワードとして使用すると、期間がミリ秒単位で得られます。

列の代わりに DAX 計算メジャーを使用する

これまでに DAX 記述言語を使用して作業したことがある場合は、モデル内の他の列に基づいて、既に計算された列を使用して新しい列が作成されることを知っていると思います。計算メジャーはモデルで定義されていますが、ピボットテーブルまたは他のレポートで使用された場合のみ評価されます。

メモリ節約の技法は、正規の列または計算された列を計算メジャーで置き換えることにあります。典型的な例として、単価、数量、合計などがあります。 3 つの列のすべてがある場合は、DAX を使用して合計を計算することで、2 つの列だけを維持してスペースを節約できます。

どの 2 つの列を維持すべきでしょうか。

上記の例では、数量と単価を維持します。これら 2 つの列は、合計よりも値の数が少なくなっています。合計を計算するには、次のように計算メジャーを追加します。

“TotalSales:=sumx(‘Sales Table’,’Sales Table’[Unit Price]*’Sales Table’[Quantity])”

計算された列は、モデルで両方のスペースを使用した正規の列のように表示されます。一方で、計算メジャーは簡易計算されてスペースを使用しません。

結論

この記事では、メモリをより効率的に使用できるモデルを構築するための、いくつかの方法について説明しました。ファイルのサイズとデータモデルで必要となるメモリ要件を減らすための方法とは、全体的な列と行の数を減らし、それぞれの列の一意の値の数を減らすことにあります。下記に、説明した技法をまとめます。

列を減らすことは、言うまでもなくスペース節約のベストの方法です。本当に必要な列を判断します。
テーブルで、列を削除して計算メジャーに置き換えることが可能な場合もあります。
テーブルにすべての行が必要なわけではありません。テーブルのインポートウィザードで行を除外することができます。
一般に、単一の列を複数の個別部分に分割することは、列の一意の値を減らす有効な方法となります。各部分にはそれぞれ少数の一意の値があり、それらを合算した合計は元の列をまとめたものよりも小さくなります。
多くの場合、個別部分はレポートのスライサーとして使用する必要もあります。部分から、時間、分、秒のような階層を適宜作成できます。
多くの場合、列には必要としているもの以上の情報が含まれています。たとえば、ある列に小数部が格納されていても、書式設定を適用してすべての小数点以下を非表示にしたとします。小数点以下を丸めることは、数値列のサイズを減らすのにとても効果的です。

これで、ブックのサイズを小さくするためにできることを完了したので、ブックサイズオプティマイザーの実行も検討してください。 Excel ブックを分析し、可能な場合は圧縮してくれるツールです。 Workbook Size Optimizer は、こちらからダウンロードできます。