数据仓库之日期维度表构建

本文主要是介绍数据仓库之日期维度表构建，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

导读

大家好，今日立秋，立秋是阳气渐收、阴气渐长，由阳盛逐渐转变为阴盛的转折。我们上一篇文章 数据仓库之维度表 介绍了数据仓库中维度表，本篇文章在此基础上介绍和构建维度表中的日期维度。Kimball 维度建模中的设计原则提到确保每一个事实表都具有关联的日期维度。数据仓库项目中，日期维度是最常见的，不可缺少的维度表之一。

本文中 ETL 转换使用 Kettle 9.3 版本实现，关注公众号回复关键字 dim_date 获取。

为什么需要日期维度表

Kimball 认为数据仓库中最重要的三个主题是向下钻取，横向钻取和时间处理。时间处理正式其中之一，而日期维度为数据仓库提供了时间处理的可能性。这里我们复习一下数据仓库的概念，Inmon 对数据仓库的描述是：“面向主题的，整合的，随时间变化的，包含汇总和明细的，稳定的历史数据集合”。 数据仓库中存储的是企业中某个时间段的数据，数据仓库中的数据像快照一样，每一个张快照都反映了某个时点的数据状态。这也意味着，无论用户什么时间到数据仓库查询，都会得到某个时间段的相同结果。

四维时空理论中，零维是点，一维是线，二维是面，三维是提，四维是由无穷多个三维加一个时间维度，也称为 3+1维时空。四维时空中的时间维是一直向前的，科学家都认为时间为是一种伪维度。

日期维度的特点

虽然人类的世界是三维的，但是在数据仓库中，时间维度确保数据仓库具有一下几个主要特征：

1，时间有效性
数据仓库的日期维度可以确保数据仓库的数据在某个时间段的明确有效性。通过日期维度可以明确了解这部分数据的有效时间区间。这里主要是对事实表中的度量的时间有效性的描述。

2，时间变化性

除了事实表可以通过日期维度的关联确保数据在某个时点的数据准确性外，维度表也可以通过 SCD ( Slowly Changing Dimension ) 代表某个业务数据在起始时间这段时间的快照属性信息。SCD 维度表就是我们常称为拉链表的维度表。时间变化性确保了事实表中的某个度量关联了正确的实体，确保了针对企业不同数据实体随着时间变化不同时期版本的正确性。这里主要是对维度表中相同实体随着时间变化而属性不同的需求

3，时间变化带来的数据查询方式变化

前文中有见到事实表其实分为事务粒度，定期快照粒度和累计快照粒度。这三种事实表自然粒度代表的就是不同时间窗口变化。企业中日常交易数据是按照企业业务流程中粒度最细的明细数据，也就是事务数据。我们会在后文中用一篇文章讲解事实表。
1），简单理解，事务粒度可以理解为空间和时间中的一个点。事务粒度代表的就是这点的相关业务过程事实表中的该业务流程发生了什么？往往事实粒度取决于源系统，数据也来源于源系统，源系统一般为企业中的 OLTP 系统。

2），定期快照粒度也代表某个时间点，但是与事务粒度不同的是，定期快照表示的是一个总的时间点，比如常见于财务报告中的会计期间，这里的期间代表可能是某个季度，或者某个年度，但必须是重复的测量。

3），累计快照粒度代表的具有有限起止节点的过程的当前状态。累计快照与前两种事实表不同的是累计快照具有一组多个（4~12）描述业务流程中典型场景的日期。比如一张卡从申请到注销，往往具备申请日期，开卡日期，首笔交易日期，最近一笔交易日期，注销时间等等。这里需要企业根据实际业务过程对不同快照时间点的定义。在累计快照粒度的事实表中需要记录或者更新业务实体数据在全生命周期中的某个业务场景的日期值。

日期维度属性

日期维度中一般具有一组含有多个日期格式的属性，国家法律法规相关要求的日历特征以及业务需求相关的日期特征。我们需要考虑以下集中于日期维度相关的主要需求点。

1），日期具有不同的表示格式，不同国家或者不同部门对日期的格式有不同的定义，比如 yyyy-mm-dd，yyyymmdd，mmddyyyy等等，这里不一一列举，日期维度表中需要具备与企业中周边系统对接，以及不同部门对BI报表需求所具备的日期格式能力。

2），国家法律法规相关的日历属性，除了国际通用性假期，如五一之外，不同地区具有不同的假期安排，需要根据实际也需要扩充日期维度的属性字段。再比如国内具有除了阳历之外，还有公历日期，同样在日期维度表中可以根据实际需要增加公历日期的字段。

3），业务需求相关的日期特征，比如 ERP 系统中财务模块需要的对账日期，或者不同企业财务部分制定的不同财年等等，都与日期维度相关。

代理键和自然键

是的，数据仓库中永远存在两种声音，是否使用代理键，这种讨论在大数据技术的出现后愈加激烈。不可忽视的是代理键为数据仓库各层之间的解耦和快速数据定位带来了更大的便利性，本文中不做代理键和自然键的优劣分析。日期维度上既可以使用代理键或者直接使用自然键作为主键都可以。当然在构建数据仓库过程中如果使用 "unknow"/"n/a" 作为默认值时，是不存在自然键的，此时使用代理键貌似更合理一些。

如何构建日期维度表

我们对日期维度表已经了解了许多了，需要在数据仓库中实现日期维度表的实现，采用何种技术实现日期维度表与使用的平台有很大的关系。如果是在传统数仓的关系型数据库中，存储过程会是一种很快的选择，存储过程的开发是数据开发者的必修课，存储过程的开发也很简单，需要符合关系型数据存储过程开发的规则，变量的定义规则，符合逻辑的程序结构定义以及异常的捕捉就可以了。我们这里采用的是开源 ETL 工具 Kettl 实现日期维度表的开发。 Kettle 是一款使用 Java 开发的开源 ETL 框架，基于使用简单，组件丰富的特点。，Kettle 的使用用户也非常庞大，尤其在互联网企业中应用最为广泛。Kettle 的入门文章可以参考本公众号的 Kettle 系列文章。

1），日期维度表建表语句

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
)
);

2），创建维度表转换

ETL 开发之前，需要了解数据流与控制流的定义，数据流，可以理解为 Kettle 中的 Transformation，其定义了数据从数据源，经过满足业务需求的数据转换后流向目标数据源的过程。控制流，就可以认为是 Kettle 中的 Job，可以包含一个或者多个数据流，以及数据流之间的先后与依赖关系。转换开发完成后，可以手动执行测试转换的正确性，我们这里在 Kettle 工作面板上执行转换，执行后可以看到如下截图，可以看到每个组件的右上角为绿色的，代表执行成功，如果执行失败，组件上会出现红色的符号，非常容易理解。

本次的转换主要是生成日期维度表所需要的数据，如果执行成功后，可以到目标表中检查日期维度表数据是否创建成功，此时也可以通过转换步骤中设置的创建天数以及开始日期到日期维度表中检查数据创建成功以及每个属性的正确性（包括日期格式，假期名称以及其中年月日等描述字段）。

3，创建作业并部署计划任务

转换测试成功后，根据需要创建日期维度表的作业任务并定期执行，由于此次是一次性创建了10年的日期数据，该任务可以设置为请求任务，无需设置为每日/每年的定时任务。当然还需要根据实际项目需要，本次联系任务就不再添加计划任务了。

至此我们已经完成了维度表中日期维度表的创建，除了日期维度表，和时间相关的就是小时维度表了，为了更精确的统计业务指标，数据仓库中加入小时维度表成为了必须。根据日期维度表的构建，相信小时维度表的构建也更简单与容易。

往期文章

数据仓库系列

数据仓库之维度表

Hive 系列

Hive 必知必会（一）介绍

Hive 必知必会（二）基本操作

Hive 必知必会（三）基本操作（续）

SQL Server 优化

一文读懂 SQL Server 执行计划

本文由mdnice多平台发布

这篇关于数据仓库之日期维度表构建的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！