从头开始在 Microsoft SSAS 中创建日期维度
4.56/5 (5投票s)
用于为指定日期范围生成 DimDate 的脚本
引言
在本文中，您将学习如何：
- 创建日历和财政年度的 DimDate 表
- 填充 DimDate 表
- 在 SSAS 中创建和配置日期维度
时间。Google 对它的定义是“存在和事件在过去、现在和未来被视为一个整体的无限持续进展”。时间是人类生活中非常重要的一个方面，因为人们通常根据时间来分析他们的成功/失败：“上个月我减了多少公斤？”“第三季度我赚了多少钱？”等等。这就是为什么 99.9% 的数据仓库都有日期（时间）维度，它能够按时间段进行分析。实际上，日期（时间）在数据仓库中非常重要且频繁，以至于 Microsoft 创建了一套专门用于时间分析的函数。
在本文中，我将解释如何从零开始创建日期维度——从创建 DimDate 表，到填充它，最后在 Microsoft SQL Server Analysis Services (SSAS) 中创建日期（时间）维度。Microsoft SSAS 有一个内置的向导，可以为我们创建日期（时间）维度。它可以只在 SSAS 中创建维度，也可以在 SQL Server 中创建底层表，这取决于我们的偏好。在本文中，我不会使用这个方便的向导，而是手动完成所有操作，这是我更喜欢的方式，因为它给了我更大的灵活性。
DimDate 表
DimDate 表的设计方式有很多种，但在本文中，我将创建一个完美符合 Microsoft SSAS 且基于 Kimball 方法论的表。Microsoft SSAS 为几个“特殊”维度预定义了一组 属性类型，其中之一就是日期（时间）。本文只涵盖 **日历**（下一版本涵盖 **财政年度**）数据类型组。
了解了所有这些之后，我们就可以创建并填充 DimDate 表了。
创建 DimDate 表
为了创建 DimDate 表，请使用本文附加的 T-SQL 脚本，名为“DimDateCreate.sql”。首先，脚本会检查 DimDate 表是否已存在，如果不存在，则会创建一个新的 DimDate 表。
CREATE TABLE [dbo].[DimDate] (
[Date] [int] NOT NULL
,[FullDateAlternateKey] [date] NOT NULL
,[DateEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- D
,[DayOfWeek] [smallint] NOT NULL DEFAULT (-1)
,[DayOfWeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[DayOfMonth] [smallint] NOT NULL DEFAULT (-1)
,[DayOfMonthEnglishName] [varchar](20) DEFAULT ('Unknown')
,[DayOfQuarter] [smallint] NOT NULL DEFAULT (-1)
,[DayOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[DayOfTrimester] [smallint] NOT NULL DEFAULT (-1)
,[DayOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[DayOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[DayOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[DayOfYear] [smallint] NOT NULL DEFAULT (-1)
,[DayOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[Weekday] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- W
,[Week] [int] NOT NULL DEFAULT (-1)
,[WeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[WeekOfYear] [int] NOT NULL DEFAULT (-1)
,[WeekOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- t
,[TenDays] [int] NOT NULL DEFAULT (-1)
,[TenDaysEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[TenDaysOfMonth] [smallint] NOT NULL DEFAULT (-1)
,[TenDaysOfMonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[TenDaysOfQuarter] [smallint] NOT NULL DEFAULT (-1)
,[TenDaysOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[TenDaysOfTrimester] [smallint] NOT NULL DEFAULT (-1)
,[TenDaysOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[TenDaysOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[TenDaysOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[TenDaysOfYear] [smallint] NOT NULL DEFAULT (-1)
,[TenDaysOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
--M
,[Month] [int] NOT NULL DEFAULT (-1)
,[MonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[MonthOfQuarter] [smallint] NOT NULL DEFAULT (-1)
,[MonthOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[MonthOfTrimester] [smallint] NOT NULL DEFAULT (-1)
,[MonthOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[MonthOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[MonthOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[MonthOfYear] [smallint] NOT NULL DEFAULT (-1)
,[MonthOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- Q
,[Quarter] [smallint] NOT NULL DEFAULT (-1)
,[QuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[QuarterOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[QuarterOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[QuarterOfYear] [smallint] NOT NULL DEFAULT (-1)
,[QuarterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- T
,[Trimester] [smallint] NOT NULL DEFAULT (-1)
,[TrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[TrimesterOfYear] [smallint] NOT NULL DEFAULT (-1)
,[TrimesterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- H
,[HalfYear] [smallint] NOT NULL DEFAULT (-1)
,[HalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[HalfYearOfYear] [smallint] NOT NULL DEFAULT (-1)
,[HalfYearOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- Y
,[Year] [smallint] NOT NULL DEFAULT (-1)
,[YearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- FD
,[FiscalDay] [int] NOT NULL DEFAULT (-1)
,[FiscalDayEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalDayOfWeek] [smallint] NOT NULL DEFAULT (-1)
,[FiscalDayOfWeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalDayOfMonth] [smallint] NOT NULL DEFAULT (-1)
,[FiscalDayOfMonthEnglishName] [varchar](20) DEFAULT ('Unknown')
,[FiscalDayOfQuarter] [smallint] NOT NULL DEFAULT (-1)
,[FiscalDayOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalDayOfTrimester] [smallint] NOT NULL DEFAULT (-1)
,[FiscalDayOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalDayOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalDayOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalDayOfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalDayOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- FW
,[FiscalWeek] [int] NOT NULL DEFAULT (-1)
,[FiscalWeekEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalWeekOfMonth] [int] NOT NULL DEFAULT (-1)
,[FiscalWeekOfMonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalWeekOfQuarter] [int] NOT NULL DEFAULT (-1)
,[FiscalWeekOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalWeekOfTrimester] [int] NOT NULL DEFAULT (-1)
,[FiscalWeekOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalWeekOfHalfYear] [int] NOT NULL DEFAULT (-1)
,[FiscalWeekOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalWeekOfYear] [int] NOT NULL DEFAULT (-1)
,[FiscalWeekOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
--FM
,[FiscalMonth] [int] NOT NULL DEFAULT (-1)
,[FiscalMonthEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalMonthOfQuarter] [smallint] NOT NULL DEFAULT (-1)
,[FiscalMonthOfQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalMonthOfTrimester] [smallint] NOT NULL DEFAULT (-1)
,[FiscalMonthOfTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalMonthOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalMonthOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalMonthOfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalMonthOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- FQ
,[FiscalQuarter] [smallint] NOT NULL DEFAULT (-1)
,[FiscalQuarterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalQuarterOfHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalQuarterOfHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalQuarterOfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalQuarterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- FT
,[FiscalTrimester] [smallint] NOT NULL DEFAULT (-1)
,[FiscalTrimesterEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalTrimesterOfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalTrimesterOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- FH
,[FiscalHalfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalHalfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
,[FiscalHalfYearOfYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalHalfYearOfYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- FY
,[FiscalYear] [smallint] NOT NULL DEFAULT (-1)
,[FiscalYearEnglishName] [varchar](20) NOT NULL DEFAULT ('Unknown')
-- Primary Key
,CONSTRAINT [PK_DimDate] PRIMARY KEY CLUSTERED ([Date] ASC)
)
另一方面，如果 DimDate 已存在，脚本将引发错误并停止执行。然后，您可以通过两种方式解决此问题：
- 删除现有的 DimDate 表并重新运行脚本，或者
- 修改现有的 DimDate 表，使其结构与脚本中的一致。
请记住，如果数据库对象被删除和重新创建，安全设置（如果有）必须重新应用。
填充 DimDate 表
准备好 DimDate 表后，请使用第二个脚本，名为“DimDatePopulate.sql”，来填充具有所需数据范围的表。在运行脚本之前，请确保检查脚本开头的参数。这些参数应根据您的业务需求进行调整（要生成的数据范围、一周的开始日期等）。以下脚本代表了正确设置参数的示例。
declare @startDate date = '20100101'
declare @endDate date = '20191231'
declare @firstDayOfWeek tinyint = 1
declare @fiscalDay tinyint = 21
declare @fiscalMonth tinyint = 5
declare @fiscalYearPlus1 tinyint = 1
设置参数
@startDate
日期维度中出现的第一个日期。在我们的例子中，维度中的第一个日期是 2010 年 1 月 1 日。
可能的值：任何有效日期，小于 @endDate 定义的日期。
@endDate
日期维度中出现的最后一个日期。在我们的例子中，维度中的最后一个日期是 2019 年 12 月 31 日。
可能的值：任何有效日期，大于 @startDate 定义的日期。
@firstDayOfWeek
此参数定义一周的开始日期。SQL Server 默认的英语（美国）设置是 7（星期日），但您可以根据您的偏好进行调整：1 = 星期一，2 = 星期二，...，7 = 星期日。
可能的值：1, 2,..., 7。
@fiscalDay
财政年度的第一天。如果您的财政年度从 7 月 1 日开始，则将参数设置为 1。在我们的例子中，财政年度从 5 月 21 日开始，因此我们将参数设置为 21。
可能的值：1, 2,..., 28。（您不应使用 29、30 和 31，因为可能会出现意外结果）。
@fiscalMonth
财政年度的第一个月。如果您的财政年度从 7 月 1 日开始，则将参数设置为 7。在我们的例子中，财政年度从 5 月 21 日开始，因此我们将参数设置为 5。
可能的值：1, 2,..., 12。
@fiscalYearPlus1
确定财政年度是日历年度 + 1 还是等于日历年度。例如，如果财政年度从 7 月 1 日开始，日历年度是 2015 年，并且此参数为 1，则从 2015 年 7 月 1 日到 2016 年 6 月 30 日（日历日期），财政年度将是 2016 年。
| 日历日期 | 参数为 0 时的财政年度 | 参数为 1 时的财政年度 |
|---|---|---|
| ... | ... | ... |
| 2015 年 6 月 29 日 | 2014 | 2015 |
| 2015 年 6 月 30 日 | 2014 | 2015 |
| 2015 年 7 月 1 日 | 2015 | 2016 |
| 2015 年 7 月 2 日 | 2015 | 2016 |
| ... | ... | ... |
可能的值：0 和 1。
运行脚本
设置参数后，您可以在之前创建 DimDate 表的同一数据库上运行脚本。首先，脚本将收集关于 DimDate 表约束（外键）的信息（如果有）。然后，它会将这些约束存储在内部并从数据库中删除它们，以确保我们可以在不“破坏”任何参照完整性的情况下删除表中的所有记录。接下来，脚本将删除表中的所有记录，并准备好接收新数据。
之后，脚本将使用 Tally Table 技术来生成所需范围的日期，这些日期用于各种转换，以计算每个列的值。以下是 Day 列的一些计算示例：
,convert(smallint, datepart(weekday, d)) as [DayOfWeek]
,convert(smallint, datepart(day, d)) as [DayOfMonth]
,convert(smallint, datediff(day, dateadd(quarter, datediff(quarter, 0, d), 0),d) + 1) as [DayOfQuarter]
,convert(smallint, datediff(day, dateadd(month, datediff(month, 0, d) - datediff(month, 0, d) % 4, 0), d) + 1) as [DayOfTrimester]
,convert(smallint, datediff(day, dateadd(month, datediff(month, 0, d) - datediff(month, 0, d) % 6, 0), d) + 1) as [DayOfHalfYear]
,convert(smallint, datepart(dayofyear, d)) as [DayOfYear]
,isnull(convert(varchar(20), case when datename(weekday, d) in ('Saturday', 'Sunday') then 'Weekend' else 'Weekday' end), 0) as [Weekday]
...月份
,convert(int, datepart(year, d) * 100 + datepart(month, d)) as [Month]
,convert(smallint, (datepart(month, d) - 1) % 3 + 1) as [MonthOfQuarter]
,convert(smallint, (datepart(month, d) - 1) % 4 + 1) as [MonthOfTrimester]
,convert(smallint, (datepart(month, d) - 1) % 6 + 1) as [MonthOfHalfYear]
,convert(smallint, datepart(month, d)) as [MonthOfYear]
...和季度
,convert(smallint, datepart(year, d) * 10 + ((datepart(month, d) - 1) / 4 + 1)) as [Trimester]
,convert(smallint, (datepart(month, d) - 1) / 4 + 1) as [TrimesterOfYear]
最后，脚本将重新创建在脚本开头收集的所有约束。
由于脚本是常规的 T-SQL，您可以轻松地对其进行调整，以适应您和您的业务需求。此外，如果您的数据仓库不需要某些级别（季度、半年等），请随时通过从 DimDate 表中删除相应的数字和文本列来删除这些级别。
脚本将创建您提供的输入参数之间的连续日期范围（所有日期）。**不要随意删除数据行（日期），即使您知道您永远不会使用它们！** 例如，如果您知道您的源系统永远不会有周末的数据，您可能会想删除 DimDate 表中的星期六和星期日，但这是一种不良做法，因为 MDX 函数期望有一个完整的日期维度才能返回正确的数据。
假设您的 BI 系统在 2014 年 7 月没有数据，并且您决定删除 2014 年 7 月 DimDate 中的所有日期。然后，假设您运行一个查询来查看 2015 年第三季度（7 月、8 月、9 月）的销售额，并想将其与 2014 年第三季度的销售额进行比较。通常，您会为此目的使用 ParallelPeriod。不幸的是，ParallelPeriod 以及其他 MDX 函数都是“日历无关”的，因此如果您运行前面提到的 ParallelPeriod MDX 查询，期望得到 2014 年 7 月、8 月和 9 月销售额的总和，您实际上会得到 **6 月**、8 月和 9 月的销售额！
这个故事的重点是——不要从 DimDate 表中删除任何日期（行）！！！
在 SSAS 中创建日期维度
我假设您熟悉如何在 BIDS / SSDT 中 创建 SSAS 项目，因此您应该知道如何创建 数据源和 数据源视图。
启动“新建维度”向导，并在第一个屏幕上保留默认选择“**使用现有表**”。
在接下来的屏幕中，选择您的数据源视图，并将 **DimDate** 选为主表。您也可以在此处选择键列的文本表示（名称列），或者稍后进行。在我们的例子中，我已将其设置为 **EnglishDateName**。
在向导的下一页，您需要选择要用于维度中的属性。请确保 **仅选择数字属性**（不要选择以“English”开头的属性），并为每个选定的列选择适当的 属性类型。
DimDate 中的列名创建方式与定义的 SSAS 属性类型相匹配，因此您应该很容易找到并为每个选定的维度属性分配适当的属性类型。在此示例中，我选择了以下属性和属性类型：
| 属性名称 | 属性类型 |
|---|---|
| 日期 | 日期 |
| 星期几 | 常规 |
| 周 | 周 |
| 十天 | 十天 |
| 月 | 月 |
| 月份 | 常规 |
| 季度 | 季度 |
| 季度 | 季度 |
| 半年 | 半年 |
| 年份 | 年份 |
完成属性选择和属性类型分配后，您的向导应如下所示：
最后，通过为您的维度命名来完成向导。
维度创建完成后，您将看到“**维度设计器**”，在“维度结构”选项卡中，您可以看到之前在向导中选择的属性。
如果您选择维度（如上图所示）并转到对象属性，您会注意到 Date 维度类型被设置为 **Time**。
这非常重要，因为 SSAS 引擎将能够应用和使用某些针对日期（时间）操作的特定优化。
此外，请确保为维度中的每个属性分配文本表示（名称列），方法是单击“维度设计器”中的属性，转到“属性”窗格，并将 DimDate 表中的文本列分配给该属性的“名称列”属性。例如，对于 Year 维度属性，您应该将其文本表示从 DimDate 表分配，如下面的图片所示。
下面可以找到分配给属性的所有名称列列表：
| 名称 | 名称列 |
|---|---|
| 日期 | EnglishDateName |
| Day Of Week | EnglishDayOfWeekName |
| 周 | EnglishWeekName |
| 十天 | EnglishTenDaysName |
| 月 | EnglishMonthName |
| 月份 | EnglishMonthOfYearName |
| 季度 | EnglishQuarterName |
| 季度 | EnglishTrimesterName |
| 半年 | EnglishHalfYearName |
| 年份 | EnglishYearName |
在任何维度中创建自然层次结构始终是一个好习惯，日期（时间）维度也不例外。最初，您可以创建所有可能的日历层次结构，让用户决定他们想保留哪些，然后再删除其余的。
如上图所示，所有用户层次结构名称下方都有“蓝色波浪线”。这是因为 SSAS 警告我们属性之间的关系尚未创建。在某些情况下，我们无法在层次结构中的属性之间创建关系，这些类型的层次结构被称为非自然层次结构。在我们当前创建的日期（时间）维度的情况下，情况并非如此，因为我们知道天“自然地”属于月份，月份“自然地”属于季度，季度属于半年，半年属于年，季度属于年，等等。这些层次结构被称为自然层次结构。
属性关系是维度设计中非常重要的一个方面，应尽可能定义。有两种类型的属性关系：灵活（Flexible）和固定（Rigid）。灵活关系是默认设置，本质上意味着“子”属性可以更改“父”。在日期（时间）维度的情况下并非如此，因为一月总是属于第一季度、第一季度和第一学期。因此，我们可以将属性关系设置为**固定**。
就是这样。保存、部署和处理您的日期维度，就可以开始使用了！
历史
2015 年 12 月 - 初始版本（无）
2015 年 12 月 - 添加了财政年度日历，在脚本中添加了“健全性检查”控件，并在填充脚本末尾添加了测试。