版本控制商业智能 SQL Server 2016 数据数据库开发 SQL Server SQL Windows

在商业智能场景中使用时间表在数据仓库中实现时间旅行概念的实用方法

作者：Jesus Carroll

5.00/5 (5投票s)

2016 年 2 月 10 日

CPOL

6分钟阅读

17170

SQL Server 2016 引入了对系统版本化时态表的支持，这是一项数据库功能，它内置了对提供表中存储的任何时间点的数据信息的支持，而不仅仅是当前时刻的正确数据。

引言

在比较业务系统和所谓的商业智能 (BI) 解决方案之间的差异时，一个事实是，业务系统关注的是当前、最新的数据值，而商业智能的重要性在于能够分析历史趋势和数据随时间变化的模式。因此，如果不定义时间上下文，就无法分析数据。至少在分析多个数据源之前，必须定义时空连续体。这种数据上下文意味着我们需要为任何数据研究中的命题添加一个“语义值”。

上下文中的数据：语义

语义数据是让机器能够理解信息含义的信息 [Th1998]。它描述了传达信息含义的技术和方法。这个术语是由蒂姆·伯纳斯-李（Tim Berners-Lee）创造的。语义数据模型是一种基于存储符号与现实世界之间关系的软件工程模型 [Be2001]。数据的组织方式使得它可以在没有人工干预的情况下被有意义地解释。语义数据的历史可以追溯到 20 世纪 70 年代，目前已广泛应用于各种数据管理系统和应用程序。围绕这一概念设计的数据库具有更强的适用性，并且更容易与其他数据库集成。如果您在没有特定时间段的情况下运行查询，时间线数据会缺乏解释性，但它能提供更多关于数据前景的有用信息。但是，如果我们给这组数据一个“记忆”，我们至少可以在我们的思维模型中引入一个控制变量，当然，这也会带来有用的信息。

在商业智能中使用通用实践

在数据仓库 (DW) 设计中，支持历史分析的一种常见做法是定义缓慢变化维度 (SCD)。数据仓库本身提供了一个历史数据库，作为支持“维度模型”中事实和维度的业务分析的基础。事实被定义为用于评估业务的数值，例如收入、成本、利润、薪水等。另一方面，维度为事实分析添加了上下文，例如，按地区分析销售额，按年份分析利润，或按部门分析经理的平均薪水。在这些示例中，地区、年份和部门将是数据仓库中的维度，而销售额、利润和薪水则作为事实来实现。

深入代码

在上述场景中，请思考：当一个新部门被分配到预算中时，该部门的费用是应该应用于全年的平均值，还是只应用于其被分配的时间段？如果我们选择前者，我们定义的是“SCD 类型 1”，它只存储当前的数据值，这可能导致详细分析不准确。

为了实现对时间段的真实分析，有必要选择后一种方案，这需要为历史数据精心设计一个“SCD 类型 2”。SCD 类型 2 的典型设计方法是通过添加“代理”键，允许为给定的维度成员提供多行数据，以便存储历史变化。代理键提供了唯一性，并允许在业务系统中使用重复的业务键值。因此，为了进行精确的时间分析，还需要添加一个开始日期/时间和结束日期/时间列。这种类型维度的设计和填充通常是一个复杂、手动且耗时的过程。

然而，我们可以基于最后的 SCD 类型 2，使用“时态数据”构建第三种方案。例如，通过 SQL Server 2016 中名为“时态表”的内置数据类型实现此过程的自动化是可能的。此功能完全符合 ANSI SQL 2011 规范。

此功能引入了对系统版本化时态表的支持，这是一项数据库功能，它内置了对提供表中存储的任何时间点的数据信息的支持，而不仅仅是当前时刻的正确数据 [MS2016]。以下脚本是时态表 DML 定义的一个示例。

DROP TABLE IF EXISTS [dbo].[DimDepartment]
;
CREATE TABLE [dbo].[DimDepartment](
	[DimDepartmentKey] [int] IDENTITY(1,1) NOT NULL,
	[ParentDimDepartmentKey] [int] NULL,
	[DimDepartmentName] [nvarchar](50) NULL,
	[SysStartTime] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
	[SysEndTime]   datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
	PERIOD FOR SYSTEM_TIME  (SysStartTime , SysEndTime),	
	CONSTRAINT [PK_DimDepartment] PRIMARY KEY CLUSTERED 
(	[DimDepartmentKey] ASC )
WITH (	PAD_INDEX = OFF, 
		STATISTICS_NORECOMPUTE = OFF, 
		IGNORE_DUP_KEY = OFF, 
		ALLOW_ROW_LOCKS = ON, 
		ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;

ALTER TABLE [dbo].[DimDepartment] 
   SET (SYSTEM_VERSIONING = ON ) 
;

/*** Another relationships ***/

ALTER TABLE [dbo].[DimDepartment]  
WITH CHECK ADD  CONSTRAINT [FK_DimDepartment_DimDepartment] 
FOREIGN KEY([ParentDimDepartmentKey])
REFERENCES [dbo].[DimDepartment] ([DimDepartmentKey])
;

ALTER TABLE [dbo].[DimDepartment]
CHECK CONSTRAINT [FK_DimDepartment_DimDepartment]
;

一旦创建，时态表将自动跟踪更改并维护开始和结束日期时间列，通过历史表有效实现 SCD 类型 2，同时保持当前表为包含最新数据值的普通表。这两个表在 SSMS 对象资源管理器中都是可见的，如图 1 所示，并且可以根据需要作为单独的表进行查询。上述代码的匿名历史表名称为 MSSQL_TemporalHistoryFor_1650104919。

SysEndTime 列被设置为 datetime2 数据类型的最大值，以指示当前值，如图 2 所示。

如果应用了可选语句，行上的更改会存储在系统版本化时态表中。

ALTER TABLE [dbo].[DimDepartment] 
   SET (SYSTEM_VERSIONING = ON) 
;

时态数据可用于审计或在意外更新后恢复数据值。此表的元素可以复制到数据仓库的维度表中，这样，在增量加载数据时，就不需要进行任何复杂的处理操作。

为了解决最初的问题，即当新部门被分配到预算中，其费用只应应用于该时间段时，我们首先有一个包含每个部门预算分配信息的事实表。因此，我们将使用此脚本中的可用代码来描述这个事实。在此，为了一致性，我们假设每个表都还不存在。

CREATE TABLE [dbo].[DimDate](
	[DateKey] [int] NOT NULL,
	[FullDateAlternateKey] [date] NOT NULL,
	[DayNumberOfWeek] [tinyint] NOT NULL,
	[EnglishDayNameOfWeek] [nvarchar](10) NOT NULL,
	[SpanishDayNameOfWeek] [nvarchar](10) NOT NULL,
	[FrenchDayNameOfWeek] [nvarchar](10) NOT NULL,
	[DayNumberOfMonth] [tinyint] NOT NULL,
	[DayNumberOfYear] [smallint] NOT NULL,
	[WeekNumberOfYear] [tinyint] NOT NULL,
	[EnglishMonthName] [nvarchar](10) NOT NULL,
	[SpanishMonthName] [nvarchar](10) NOT NULL,
	[FrenchMonthName] [nvarchar](10) NOT NULL,
	[MonthNumberOfYear] [tinyint] NOT NULL,
	[CalendarQuarter] [tinyint] NOT NULL,
	[CalendarYear] [smallint] NOT NULL,
	[CalendarSemester] [tinyint] NOT NULL,
	[FiscalQuarter] [tinyint] NOT NULL,
	[FiscalYear] [smallint] NOT NULL,
	[FiscalSemester] [tinyint] NOT NULL,
 CONSTRAINT [PK_DimDate_DateKey] PRIMARY KEY CLUSTERED 
(
	[DateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [AK_DimDate_FullDateAlternateKey] UNIQUE NONCLUSTERED 
(
	[FullDateAlternateKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, _
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;

CREATE TABLE [dbo].[DimAccount](
	[AccountKey] [int] IDENTITY(1,1) NOT NULL,
	[ParentAccountKey] [int] NULL,
	[AccountCodeAlternateKey] [int] NULL,
	[ParentAccountCodeAlternateKey] [int] NULL,
	[AccountDescription] [nvarchar](50) NULL,
	[AccountType] [nvarchar](50) NULL,
	[Operator] [nvarchar](50) NULL,
	[CustomMembers] [nvarchar](300) NULL,
	[ValueType] [nvarchar](50) NULL,
	[CustomMemberOptions] [nvarchar](200) NULL,
 CONSTRAINT [PK_DimAccount] PRIMARY KEY CLUSTERED 
(
	[AccountKey] ASC
)WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;

CREATE TABLE [dbo].[DimOrganization](
	[OrganizationKey] [int] IDENTITY(1,1) NOT NULL,
	[OrganizationName] [nvarchar](50) NULL
CONSTRAINT [PK_DimOrganization] PRIMARY KEY CLUSTERED 
(	[OrganizationKey] ASC)
WITH (
PAD_INDEX = OFF, 
STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF,     
ALLOW_ROW_LOCKS = ON, 
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;

CREATE TABLE [dbo].[DimScenario](
	[ScenarioKey] [int] IDENTITY(1,1) NOT NULL,
	[ScenarioName] [nvarchar](50) NULL,
 CONSTRAINT [PK_DimScenario] PRIMARY KEY CLUSTERED 
(	[ScenarioKey] ASC)
WITH (
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
;

CREATE TABLE [dbo].[FactBudget](
	[FinanceKey] [int] IDENTITY(1,1) NOT NULL,
	[DateKey] [int] NOT NULL,
	[OrganizationKey] [int] NOT NULL,
	[DepartmentKey] [int] NOT NULL,
	[ScenarioKey] [int] NOT NULL,
	[AccountKey] [int] NOT NULL,
	[Amount] [float] NOT NULL,
	[Date] [datetime] NULL
) ON [PRIMARY]
;

ALTER TABLE [dbo].[FactBudget] 
WITH CHECK ADD  CONSTRAINT [FK_FactBudget_DimAccount_1] FOREIGN KEY([AccountKey])
REFERENCES [dbo].[DimAccount] ([AccountKey])
;

ALTER TABLE [dbo].[FactBudget] CHECK CONSTRAINT [FK_FactBudget_DimAccount_1]
;

ALTER TABLE [dbo].[FactBudget]
WITH CHECK ADD  CONSTRAINT [FK_FactBudget_DimDate_1] FOREIGN KEY([DateKey])
REFERENCES [dbo].[DimDate] ([DateKey])
;

ALTER TABLE [dbo].[FactBudget] CHECK CONSTRAINT [FK_FactBudget_DimDate_1]
GO

ALTER TABLE [dbo].[FactBudget] 
WITH CHECK ADD  CONSTRAINT [FK_FactBudget_DimDepartment_1] 
FOREIGN KEY([DepartmentKey])
REFERENCES [dbo].[DimDepartment] ([DimDepartmentKey])
;

ALTER TABLE [dbo].[FactBudget] CHECK CONSTRAINT [FK_FactBudget_DimDepartment_1]
;

ALTER TABLE [dbo].[FactBudget] 
WITH CHECK ADD  CONSTRAINT [FK_FactBudget_DimOrganization_1] FOREIGN KEY([OrganizationKey])
REFERENCES [dbo].[DimOrganization] ([OrganizationKey])
;

ALTER TABLE [dbo].[FactBudget] CHECK CONSTRAINT [FK_FactBudget_DimOrganization_1]
;

ALTER TABLE [dbo].[FactBudget] 
WITH CHECK ADD  CONSTRAINT [FK_FactBudget_DimScenario_1] FOREIGN KEY([ScenarioKey])
REFERENCES [dbo].[DimScenario] ([ScenarioKey])
;

ALTER TABLE [dbo].[FactBudget] CHECK CONSTRAINT [FK_FactBudget_DimScenario_1]
;

在使用上述表格插入数据后，最好检查一下日期。在这里，我们可以看到 GlobalDate 列中显示的最大值属于最后的预算。

Figure 3. Exploring Maximum Date values for Budget Fact Table by Department.

然而，由于该表是最近创建的，所有部门也都是最近创建的。下面的代码演示了如何使用时间旅行功能进行查询。

  SELECT DimDepartmentName, sum(FactBudget.Amount) AS Total
  FROM DimDepartment 
  FOR SYSTEM_TIME  
  BETWEEN '2016-01-01 00:00:00.0000000' AND '2016-03-01 00:00:00.0000000' 
   INNER JOIN [FactBudget] on DimDepartment.DimDepartmentKey = FactBudget.DepartmentKey
  WHERE DimDepartmentKey = 7
  GROUP BY DimDepartmentName
  ORDER BY DimDepartmentName;

并且，下面的代码警告说，如果我们尝试查看此期间之前的事件，我们预计不会有任何结果。

      SELECT DimDepartmentName, sum(FactBudget.Amount) AS Total
  FROM DimDepartment 
  FOR SYSTEM_TIME  
  BETWEEN '2005-01-01 00:00:00.0000000' AND '2005-03-01 00:00:00.0000000' 
  INNER JOIN [FactBudget] on DimDepartment.DimDepartmentKey = FactBudget.DepartmentKey
  WHERE DimDepartmentKey = 7
  GROUP BY DimDepartmentName
  ORDER BY DimDepartmentName;

两个结果如下图所示。

Figure 4. Checking Time-Traveling query with several periods with respect to the same fact

如果由于某些组织重组而发生更新，例如，一个名为“销售与市场”的部门在表创建日期之后现在将重命名为“全球销售”，那么版本控制属性可以记录此事件。我们检查 MSSQL_TemporalHistoryFor_1650104919 表，以测试旧值，其中在子句中使用的时间段内，与预算事实表相关的有效数据得到了验证：

    FOR SYSTEM_TIME BETWEEN '2016-01-01 00:00:00.0000000' AND '2016-03-01 00:00:00.0000000'

下图说明了这种行为。

Figure 5. Temporal Table – Using inherited System Versioning.

使用时态表属性测试的期间包括以下时间间隔：

更新之前

 '2016-01-01 00:00:00.0000000' AND '2016-02-05 00:00:00.0000000'

包括使用旧名称和新名称的事件

'2016-02-10 01:00:00.0000000' AND '2016-03-01 00:00:00.0000000'

更新语句之后

'2016-02-10 02:00:00.0000000' AND '2016-03-01 00:00:00.0000000'

图 6 中的下图显示了通过时间旅行概念保持数据上下文的 SQL 语句，这证明了在商业智能场景中正确使用了语义。

Figure 6. Example of query using time-traveling concept.

参考文献

[Th1998] 数据库中的语义。THALHEIM, Bernhard; LIBKIN , Leonid。计算机科学讲义。第 1358 卷。Springer-Verlag Berlin Heidelberg。柏林，1998 年。平装本 ISBN: 978-3-540-64199-5。
[Be2001] Berners-Lee, Tim; James Hendler; Ora Lassila (2001 年 5 月 17 日)。“语义网：一种对计算机有意义的新形式的 Web 内容将引发一场新可能性的革命”。《科学美国人》284: 34–43。doi:10.1038/scientificamerican0501-34。
[MD2016] 微软开发者网络 (Microsoft Development Network)。
URL: https://msdn.microsoft.com/en-US/library/dn935015.aspx
[MS2016] SQL Server 2016 CTP 示例。
URL: http://msftdbprodsamples.codeplex.com/releases/view/618193