PowerPivot for Excel - データウェアハウスのルックアップテーブル

PowerPivot for Excelの最も重要な項目の 1つは、データセットにルックアップテーブルを追加する機能です。 ほとんどの場合、作業しているデータには分析に必要なすべてのフィールドがありません。 たとえば、日付フィールドがありますが、四半期ごとにデータをグループ化する必要があります。 数式を書くこともできますが、PowerPivot環境で簡単なルックアップテーブルを作成する方が簡単です。

また、このルックアップテーブルを、月の名前や年の最初/後半などの別のグループに使用することもできます。 データウェアハウジング用語では、実際には日付ディメンション表を作成しています。 この記事では、PowerPivot for Excelプロジェクトを強化するためのディメンションテーブルの例をいくつか紹介します。

新しいテキスト次元（ルックアップ）テーブル

オーダーデータを持つテーブルを考えてみましょう（MicrosoftのContosoデータには、このようなデータセットが含まれています）。 テーブルに顧客、受注日、受注合計、注文タイプのフィールドがあるとします。 注文タイプフィールドに焦点を当てます。 注文タイプフィールドに次のような値が含まれているとします。

• ネットブック
• デスクトップ
• モニター
• プロジェクター
• プリンタ
• スキャナー
• デジタルカメラ
• デジタル一眼レフカメラ
• フィルムカメラ
• ビデオカメラ
• オフィス電話
• スマートフォン
• PDA
• 携帯電話アクセサリー

実際には、これらのコードがありますが、この例を単純なものにするために、これらが注文表の実際の値であると仮定してください。

PowerPivot for Excelを使用すると、オーダーの種類ごとに簡単にオーダーをグループ化できます。 別のグループ化が必要な場合はどうなりますか？ たとえば、コンピュータ、カメラ、電話などの「カテゴリ」グループが必要であるとします。 注文テーブルには「カテゴリ」フィールドはありませんが、PowerPivot for Excelのルックアップテーブルとして簡単に作成できます。

完全なサンプルルックアップテーブルは、 表1の下にあります。 手順は次のとおりです。

• ステップ1：ルックアップテーブルのタイプフィールドとは別のリストが必要です。 これはあなたの検索フィールドになります。 データセットから、オーダータイプフィールドから値の別個のリストを作成します。 Excelワークブックに「タイプ」の別個のリストを入力します。 Type列にラベルを付けます。
• ステップ2：ルックアップ列（タイプ）の横にある列に、グループ化する新しいフィールドを追加します。 この例では、Categoryというラベルの列を追加します。
• ステップ3：個別の値リスト（この例ではタイプ）の各値に対して、対応する「カテゴリ」値を追加します。 私たちの簡単な例では、コンピュータ、カメラ、または電話のいずれかをCategoryカラムに入力します。
• ステップ4：タイプとカテゴリのデータテーブルをクリップボードにコピーします。
• 手順5： PowerPivot for Excelの注文データを使用してExcelブックを開きます。 PowerPivotウィンドウを起動します。 [貼り付け]をクリックすると、新しいルックアップテーブルが表示されます。 テーブルに名前を付けて、「最初の行を列見出しとして使用する」をチェックしてください。 [OK]をクリックします。 PowerPivotでルックアップテーブルを作成しました。
• 手順6： OrderテーブルのTypeフィールドとルックアップテーブルのCategoryフィールドの間にリレーションシップを作成します。 デザインリボンをクリックし、リレーションシップの作成を選択します。 Create Relationshipダイアログで選択を行い、Createをクリックします。

PowerPivotデータに基づいてExcelでピボットテーブルを作成すると、新しいカテゴリフィールドでグループ化することができます。 PowerPivot for Excelは内部結合のみをサポートしています。 ルックアップテーブルに「注文タイプ」がない場合、そのタイプの対応するすべてのレコードは、PowerPivotデータに基づいてピボットテーブルに存在しません。 これは時々チェックする必要があります。

日付次元（ルックアップ）テーブル

ほとんどのPowerPivot for Excelプロジェクトでは、日付ルックアップテーブルが必要になります。 ほとんどのデータセットには、いくつかのタイプの日付フィールドがあります。 年と月を計算する関数があります。

ただし、実際の月のテキストまたは四半期が必要な場合は、複雑な数式を記述する必要があります。 Dateディメンション（ルックアップ）テーブルを組み込み、メインデータセットの月番号と照合する方がずっと簡単です。 注文日付フィールドから月番号を表すために、注文テーブルに列を追加する必要があります。 この例の "month"のDAX式は、 "= MONTH（[Order Date]）です。これは、レコードごとに1〜12の数値を返します。ディメンションテーブルは、月の数値にリンクする代替値を提供します。分析の柔軟性を提供します。完全なサンプル日付ディメンション表は、下の表2のとおりです。

日付ディメンションまたはルックアップテーブルには12レコードが含まれます。 月の列の値は1〜12です。他の列には、短縮月のテキスト、完全な月のテキスト、四半期などが含まれます。

• 手順1：下記の表2の表をコピーして、PowerPivotに貼り付けます。 Excelでこのテーブルを作成することもできますが、時間を節約できます。 Internet Explorerを使用している場合は、下の選択したデータから直接貼り付けることができます。 PowerPivotはテストでテーブルの書式設定を取得します。 別のブラウザを使用している場合は、最初にExcelに貼り付けてExcelからコピーして、表の書式設定を取得する必要があります。
• 手順2： PowerPivot for Excelの注文データを使用してExcelブックを開きます。 PowerPivotウィンドウを起動します。 [貼り付け]をクリックすると、ルックアップテーブルが下の表またはExcelからコピーされます。 テーブルに名前を付けて、「最初の行を列見出しとして使用する」をチェックしてください。 [OK]をクリックします。 PowerPivotで日付ルックアップテーブルを作成しました。

• 手順3 ：注文テーブルの月フィールドとルックアップテーブルのMonthNumberフィールドの間に関係を作成します。 デザインリボンをクリックし、リレーションシップの作成を選択します。 Create Relationshipダイアログで選択を行い、Createをクリックします。

再度、日付ディメンションを追加すると、日付ルックアップテーブルの異なる値のいずれかを使用して、ピボットテーブル内のデータをグループ化できます。 4分の1または月の名前でグループ化するのは簡単です。

サンプルディメンション（ルックアップ）テーブル

表1

表2