PowerPivot for Excel - 數據倉庫中的查找表

我列出的關於PowerPivot for Excel的最重要的功能之一是能夠將查找表添加到數據集中。 大多數情況下，您正在使用的數據並不包含您分析所需的每個字段。 例如，您可能有一個日期字段，但需要按季度對數據進行分組。 您可以編寫一個公式，但在PowerPivot環境中創建一個簡單的查找表更容易。

您也可以將此查找表用於其他分組，如月份名稱和年份的前半/後半部分。 在數據倉庫方面，您實際上正在創建日期維度表。 在本文中，我將給你幾個示例維度表來增強您的PowerPivot for Excel項目。

新的文本維度（查找）表

讓我們考慮一張帶有訂單數據的表格（來自Microsoft的Contoso數據包含一個與此相似的數據集）。 假設表格中包含客戶，訂單日期，訂單總額和訂單類型的字段。 我們將關注訂單類型字段。 假設訂單類型字段包含如下值：

• 上網本
• 台式機
• 顯示器
• 投影機
• 打印機
• 掃描儀
• 數碼相機
• 數碼單反相機
• 膠片相機
• 攝像機
• 辦公電話
• 智能手機
• 掌上電腦
• 手機配件

實際上，你可以為這些代碼編寫代碼，但為了簡化這個例子，假設這些是訂單表中的實際值。

使用PowerPivot for Excel，您可以輕鬆地按訂單類型對訂單進行分組。 如果你想要一個不同的分組呢？ 例如，假設您需要像電腦，相機和電話那樣的“類別”分組。 訂單表沒有“類別”字段，但可以在PowerPivot for Excel中輕鬆地將其創建為查找表。

完整的樣本查詢表如表1所示 。 這裡是步驟：

• 步驟1：您需要查找表的類型字段中的不同列表。 這將是您的查找字段。 從您的數據集中，從訂單類型字段中創建一個清晰的值列表。 在Excel工作簿中輸入不同的“類型”列表。 標記欄類型。
• 步驟2：在查找列（類型）旁邊的列中，添加您想要分組的新字段。 在我們的示例中，添加一個名為Category的標籤。
• 步驟3：對於不同的值列表（本例中為類型）中的每個值，添加相應的“類別”值。 在我們的簡單示例中，將“計算機”，“相機”或“電話”輸入到“類別”列中。
• 第4步：將Type和Category數據表複製到剪貼板中。
• 第5步：使用PowerPivot for Excel中的訂單數據打開Excel工作簿。 啟動PowerPivot窗口。 點擊粘貼，將會帶來新的查找表。 為表格命名並確保選中“使用第一行作為列標題”。 點擊確定。 您已在PowerPivot中創建查找表。
• 步驟6：在Order表中的Type字段和查找表中的Category字段之間創建一個關係。 點擊設計功能區並選擇創建關係。 在“創建關係”對話框中進行選擇，然後單擊“創建”。

當您在Excel中基於PowerPivot數據創建數據透視表時，您將能夠按照您的新分類字段進行分組。 請記住，PowerPivot for Excel僅支持內部聯接。 如果您的查找表中缺少“訂單類型”，則基於PowerPivot數據的任何數據透視表都將缺少該類型的所有相應記錄。 您需要不時檢查。

日期維度（查找）表

日期查找表很可能在大多數PowerPivot for Excel項目中都需要。 大多數數據集都有一些類型的日期字段。 有功能來計算年和月。

但是，如果您需要實際的月份文本或季度，則需要編寫複雜的公式。 包含日期維（查找）表並將其與主數據集中的月份數匹配起來要容易得多。 您需要在訂單表中添加一列以表示訂單日期字段中的月份編號。 在我們的例子中，“月份”的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中復制您的查找表格。 為表格命名並確保選中“使用第一行作為列標題”。 點擊確定。 您已在PowerPivot中創建日期查找表。

• 第3步 ：在Order表中的Month字段和查找表中的MonthNumber字段之間創建關係。 點擊設計功能區並選擇創建關係。 在“創建關係”對話框中進行選擇，然後單擊“創建”。

同樣，通過添加日期維度，您可以使用日期查找表中的任何不同值將數據透視表中的數據分組。 按季度或本月的名稱分組將很快。

示例維（查找）表

表格1

表2