扩展数据模型
在本章中,你将学习如何扩展在前几章中创建的数据模型。扩展数据模型包括:
- 添加表格
- 在现有表中添加计算列
- 在现有表中创建度量
其中,创建度量至关重要,因为它涉及在数据模型中提供新的数据洞察力,这将使那些使用数据模型的人避免返工,并在分析数据和决策时节省时间。
由于损益分析涉及处理时间段,并且你将使用 DAX 时间智能函数,因此你需要数据模型中的日期表。
如果你不熟悉日期表,请阅读本章 - 了解日期表。
你可以按如下方式扩展数据模型:
-
要创建数据表之间的关系,即财务数据表和日期表,你需要在财务数据表中创建一个计算列日期。
-
要执行不同类型的计算,你需要在数据表 - Finance Data 和查找表 - Accounts and Geography Locn 之间创建关系。
-
你需要创建各种度量来帮助你执行多项计算并执行所需的分析。
这些步骤本质上构成了使用数据模型进行损益分析的数据建模步骤。但是,这是你要使用 Power Pivot 数据模型执行的任何类型的数据分析的步骤序列。
此外,你将在后续章节中学习如何创建度量以及如何在 Power PivotTables 中使用它们。这将使你充分了解使用 DAX 进行数据建模和使用 Power PivotTables 进行数据分析。
将日期表添加到数据模型
为跨会计年度的时间段创建日期表,如下所示:
-
在新的 Excel 工作表中创建一个带有标题的单列表格 - 日期和连续日期,范围从 2011 年 7 月 1 日到 2018 年 6 月 30 日。
-
从 Excel 复制表格并将其粘贴到 Power Pivot 窗口中。这将在 Power Pivot 数据模型中创建一个新表。
-
将表命名为日期。
-
确保 Date 表中的 Date 列是数据类型 - Date (DateTime)。
接下来,你需要将计算的列 - 会计年度、会计季度、会计月份和月份添加到日期表中,如下所示:
财政年度
假设财政年度结束是 6 月 30 日 th .然后,一个会计年度跨度从 1 st 7月至30日 th 六月。例如,7 月 1 日期间 st , 2011 年 (7/1/2011) 至 6 月 30 日 th , 2012 (6/30/2012) 将是 2012 财年。
在 Date 表中,假设你要表示与 FY2012 相同的值。
-
你需要首先提取日期的财政年度部分并将其附加到 FY。
-
对于 2011 年 7 月至 2011 年 12 月的日期,财政年度为 1+2011。
-
对于 2012 年 1 月至 2012 年 6 月的日期,财政年度为 0+2012。
-
概括地说,如果财政年度结束的月份是 FYE,请执行以下操作:
((Month – 1)/FYE) + Year 的整数部分
-
接下来,取最右边的 4 个字符来获得财政年度。
-
-
在 DAX 中,你可以表示为:
RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)
-
用 DAX 公式在 Date 表中添加计算列 Fiscal Year:
="FY"&RIGHT(INT((MONTH('Date'[Date])-1)/'Date'[FYE])+YEAR('Date'[Date]),4)
财政季度
如果 FYE 代表财政年度结束的月份,则财政季度为
(((Month+FYE-1)/12) + 3)/3) 的整数部分
-
在 DAX 中,你可以表示为:
INT((MOD(MONTH('日期'[日期])+'日期'[FYE]-1,12)+3)/3)
-
使用 DAX 公式在 Date 表中添加计算列 Fiscal Quarter:
='Date'[FiscalYear]&"-Q"&FORMAT( INT((MOD(MONTH('Date'[Date]) + 'Date'[FYE]-1,12) + 3)/3), "0" )
财政月
如果 FYE 代表财政年度结束,则财政月期间获得为
((月+FYE-1)/12的剩余部分)+ 1
-
在 DAX 中,你可以表示为:
MOD(MONTH('日期'[日期])+'日期'[FYE]-1,12)+1
-
使用 DAX 公式在 Date 表中添加计算列 Fiscal Month:
='日期'[财政年度]&"-P" & FORMAT(MOD(MONTH([日期])+[FYE]-1,12)+1,"00")
Month
最后,添加表示一个财政年度的月份数的计算列Month,如下所示:
=FORMAT(MOD(MONTH([Date])+[FYE]-1,12)+1,"00") & "-" & 格式([日期],“嗯”)
生成的日期表类似于以下屏幕截图。
将表标记为日期表,将列标记为日期表,并将其标记为具有唯一值的列,如下面的屏幕截图所示。
添加计算列
要在 Finance Data 表和 Date 表之间创建关系,你需要 Finance Data 表中的一列 Date 值。
-
使用DAX公式在Finance Data表中添加计算列Date:
= DATEVALUE ('财务数据'[财政月份])
在数据模型中定义表之间的关系
你在数据模型中有以下表格:
- 数据表 - 财务数据
- 查找表 - Accounts and Geography Locn
- Date table - Date
在数据模型中定义表之间的关系,步骤如下:
-
在 Power Pivot 的图表视图中查看表格。
-
在表之间创建如下关系:
-
Finance Data 表和 Accounts 表与 Account 列之间的关系。
-
Finance Data 表和 Geography Locn 表与列利润中心之间的关系。
-
财务数据表和日期表与日期列之间的关系。
-
从客户端工具中隐藏列
如果数据表中有任何列不会用作任何数据透视表中的字段,则可以将它们隐藏在数据模型中。然后,它们将在数据透视表字段列表中不可见。
在财务数据表中,你有 4 列 - 会计月份、日期、帐户和利润中心,你不会将它们用作任何数据透视表中的字段。因此,你可以隐藏它们,使它们不会出现在数据透视表字段列表中。
-
在财务数据表中选择列 - 会计月份、日期、帐户和利润中心。
-
右键单击并在下拉列表中选择从客户端工具中隐藏。
在表中创建度量
你已准备好使用数据模型和 Power PivotTables 使用 DAX 进行数据建模和分析。
在后续章节中,你将学习如何创建度量以及如何在 Power PivotTables 中使用它们。你将在数据表中创建所有度量,即财务数据表。
你将使用数据表 - 财务数据中的 DAX 公式创建度量,你可以在任意数量的数据透视表中使用它来进行数据分析。这些措施本质上是元数据。在数据表中创建度量是数据建模的一部分,在 Power PivotTables 中汇总它们是数据分析的一部分。