扩展数据模型

在本章中，您将学习如何扩展前面章节中创建的数据模型。扩展数据模型包括 -

  • 添加表格
  • 在现有表中添加计算列
  • 在现有表中创建度量

其中，创建度量至关重要，因为它涉及在数据模型中提供新的数据见解，这将使使用数据模型的人员避免返工，并在分析数据和决策时节省时间。

由于损益分析涉及时间段，并且您将使用 DAX 时间智能函数，因此数据模型中需要一个日期表。

如果您不熟悉日期表，请阅读“了解日期表”一章。

您可以按如下方式扩展数据模型 -

  • 要创建数据表（即财务数据表和日期表）之间的关系，您需要在财务数据表中创建计算列“日期”。

  • 要执行不同类型的计算，您需要在数据表（财务数据）和查找表（帐户和地理区域）之间创建关系。

  • 您需要创建各种度量来帮助您执行多项计算并进行所需的分析。

这些步骤本质上构成了使用数据模型进行损益分析的数据建模步骤。但是，这是您想要使用 Power Pivot 数据模型执行的任何类型的数据分析的步骤序列。

此外，您将在后续章节中了解如何创建度量以及如何在 Power PivotTables 中使用它们。这将使您充分了解使用 DAX 进行数据建模以及使用 Power PivotTables 进行数据分析。

将日期表添加到数据模型

为跨越财政年度的时间段创建一个日期表，如下所示 -

  • 在新的 Excel 工作表中创建一个包含单列的表格，该表格的标题为“日期”和范围从 2011 年 7 月 1 日到 2018 年 6 月 30 日的连续日期。

  • 从 Excel 复制表格并将其粘贴到 Power Pivot 窗口中。这将在 Power Pivot 数据模型中创建一个新表。

  • 将表命名为“日期”。

  • 确保“日期”表中的“日期”列的数据类型为“日期”(DateTime)。

接下来，您需要将计算列 - 财政年度、财政季度、财政月份和月份添加到日期表中，如下所示 -

财政年度

假设会计年度结束日期为 6 月 30日。那么，一个财政年度从7月1日到6月30日。例如，2011年7月1日（7/1/2011）到2012年6月30日（6/30/2012）期间将是2012财年。

在“日期”表中，假设您希望表示与 FY2012 相同的内容。

  • 您需要首先提取日期的财政年度部分并附加 FY。

    • 对于 2011 年 7 月至 2011 年 12 月期间的日期，财政年度为 1+2011。

    • 对于 2012 年 1 月至 2012 年 6 月期间的日期，财政年度为 0+2012。

    • 概括而言，如果财政年度结束月份是 FYE，请执行以下操作 -

      ((月 – 1)/FYE) + 年的整数部分

    • 接下来，取最右边的 4 个字符来获取财政年度。

  • 在 DAX 中，您可以表示为 -

    RIGHT(INT((MONTH('日期'[日期])-1)/'日期'[财政年度])+YEAR('日期'[日期]),4)

  • 使用 DAX 公式在日期表中添加计算列会计年度 -

    ="FY"&RIGHT(INT((MONTH('日期'[日期])-1)/'日期'[FYE])+YEAR('日期'[日期]),4)

财政季度

如果 FYE 代表财政年度结束月份，则财政季度的获取方式为

((((月份+FYE-1)/12) + 3)/3) 的整数部分

  • 在 DAX 中，您可以表示为 -

    INT((MOD(月份('日期'[日期])+'日期'[财政年]-1,12)+3)/3)

  • 使用 DAX 公式在日期表中添加计算列财政季度 -

    ='日期'[财政年度]&"-Q"&FORMAT( INT((MOD(MONTH('日期'[日期]) + '日期'[财政年度]-1,12) + 3)/3), "0" ）

财政月

如果 FYE 代表财政年度结束，则财政月份期间的获取方式为

((月+FYE-1)/12 的余数) + 1

  • 在 DAX 中，您可以表示为 -

    MOD(MONTH('日期'[日期])+'日期'[财政年]-1,12)+1

  • 使用 DAX 公式在日期表中添加计算列会计月份 -

    ='日期'[财政年度]&"-P" & FORMAT(MOD(月份([日期])+[财政年度]-1,12)+1,"00")

月

最后，添加代表财政年度中月份编号的计算列 Month，如下所示 -

=格式(MOD(月([日期])+[财政年]-1,12)+1,"00") & "-" & 格式([日期],"mmm")

生成的日期表类似于以下屏幕截图。

月

将表 - 日期标记为日期表，并将列 - 日期标记为具有唯一值的列，如以下屏幕截图所示。

标记为表

添加计算列

要在财务数据表和日期表之间创建关系，您需要在财务数据表中有一列日期值。

  • 使用 DAX 公式在财务数据表中添加计算列日期 -

    = DATEVALUE ('财务数据'[财政月份])

定义数据模型中表之间的关系

数据模型中有以下表格 -

  • 数据表-财务数据
  • 查找表 - 账户和地理 Locn
  • 日期表 - 日期

要定义数据模型中表之间的关系，请执行以下步骤 -

  • 查看 Power Pivot 图表视图中的表格。

  • 在表之间创建以下关系 -

    • 财务数据表和帐户表与帐户列之间的关系。

    • Finance Data 表和 Geography Locn 表与利润中心列之间的关系。

    • 财务数据表和日期表与日期列之间的关系。

人际关系

从客户端工具中隐藏列

如果数据表中有任何列不会用作任何数据透视表中的字段，则可以将它们隐藏在数据模型中。然后，它们在数据透视表字段列表中将不可见。

在财务数据表中，您有 4 列 - 会计月份、日期、帐户和利润中心，您不会将它们用作任何数据透视表中的字段。因此，您可以隐藏它们，以便它们不会出现在数据透视表字段列表中。

  • 在财务数据表中选择列 - 会计月份、日期、帐户和利润中心。

  • 右键单击并在下拉列表中选择从客户端工具隐藏。

从客户端工具中隐藏列

在表中创建度量

您已准备好使用数据模型和 Power PivotTables 通过 DAX 进行数据建模和分析。

在后续章节中，您将学习如何创建度量以及如何在 Power PivotTables 中使用它们。您将在数据表（即财务数据表）中创建所有度量。

您将在数据表 - 财务数据中使用 DAX 公式创建度量，您可以在任意数量的数据透视表中使用该度量进行数据分析。这些措施本质上是元数据。在数据表中创建度量是数据建模的一部分，在 Power PivotTables 中汇总它们是数据分析的一部分。