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