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

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

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

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

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

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

不要な列の除外方法

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

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

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

[Datetime] 列の変更

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

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

• 時間は必要ですか。

• 時間のレベルで時刻が必要ですか。 分は必要ですか。 秒は必要ですか。 ミリ秒は必要ですか。

• 複数の [Datetime] 列の差異を計算するため、またはデータを年別、月別、四半期別などで集計するために、それを必要としていますか。

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

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

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

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

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

SQL クエリの変更

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

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

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

   セントが必要な場合で、セントの小数点以下が不要な場合は、0 を 2 に置き換えます。 負の数字を使用する場合は、一の位、十の位、百の位に丸めることができます。

2. dbo.Bigtable.[Date Time] という名前の [Datetime] 列があり、時刻部分が不要な場合は、この構文を使用して時刻を除外します。

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

3. 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]”

   必要なだけの列を使用して、個別の列の必要な部分を保存します。

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

   Timefromparts(datepart(hh, dbo.Bigtable.[Date Time]), datepart(mm, dbo.Bigtable.[Date Time])) as [Date Time HourMinute]

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

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

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

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

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

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

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