[分享]微软BI专题-基于OLAP的时间维度设计

  时间维度在OLAP中是一个使用的很频繁的概念，通常要根据实际需求进行定义和架构，使其不仅能够满足现有基于时间分析的需求，还具有良好的可扩展性和可持续性。本文主要以SQL Server 2005为平台较为全面地介绍了时间维度的常见设计方法，并对其在OLAP中的架构方式进行了简单的阐述。

 

      时间维度简介

      在SQL Server 2005 Analysis Services（SSAS）中，时间维度是指那些属性表示时间段（如年、半期、季度、月和天）的维度类型。时间维度中的时间段可提供用于分析和报告的基于时间的粒度级别。属性将按层次结构进行组织，并且时间维度的粒度主要由历史数据的业务和报表需求决定。例如，商业智能应用程序中的大多数财务数据和销售数据使用月粒度或季度粒度。

 

      一般情况下，Analysis Services中的多维数据集会以某种形式共用一个时间维度。有些情况也会需要用到多个时间维度，这些具体的时间维度架构将取决于数据的粒度和前端展示需求。但并非所有多维数据集都需要时间维度，如果多维数据集所要实现的所有功能都与时间无关，也就没有必要再定义一个时间维度了。

 

      与时间维度相关的分析非常多，在此以销售为例：

1、  如何获得一周中的每一天的销售额？

2、  如何获得一年中每个月的销售总量？

3、  如果获得一年中每个财务月的销售总额？

4、  如果获得一年中所有节假日超市促销的销售总额？

日常使用的时间维度通常只有日期维度，大致如图1所示。

图1：日常使用的时间维度

 

      时间维度设计注意事项

      在Kimball的维度设计理念中提到，所有维度都应当使用代理键，这一点既有基于跟踪维度属性变化的需求，也有集成多个系统所带来的挑战。

 

      时间维度可以使用代理键，也可以使用时间作为主键，比如日期维度可以使用日期作为主键，也可以使用有意义的数值作为代理键。具体来说，可以使用年月日顺序表示日期对应的整数，故2005年1月1日可以表示为20050101，如用theDate表示日期“2005-01-01”，则相应的timeKey可以使用下面的方法（T-SQL）生成：

 

以下是代码： set timeKey = cast((left(convert(nvarchar,thedate,23),4)+substring(convert(nvarchar,thedate,23),6,2)+substring(convert(nvarchar,thedate,23),9,2)）as int)

      对关系数据库来说，这样的做法可以显著提高查询性能，同时也简化实现基于日期的划分，并且划分也变得容易理解。

 

      但也并不是所有情况下都可以教条地使用代理键，对于一个日期属性，当其具有业务上的意义时才使用代理键。

 

      时间维度分类

在SSAS中，时间维度按照存储的方式不同分为两类：

 

1、普通时间维度：与其它维度类似，时间维度也使用维度表提供的维度属性，事实数据表通过外键与时间维度关联。时间维度存在一个单独物理数据表。

2、服务器时间维度：通过SSAS基于时间段定义的一个虚拟维度，通过总线矩阵建立度量值组与时间维度之间的关系。

 

      时间维度与常规维度的区别

      时间维度在本质上与任何其它标准维度没有区别。虽然如此，在SSAS中，一旦定义了时间维度，便具备了比其它标准维度更方便的功能：这一点主要体现在时间智能上。

 

      在此解释一下时间智能：时间智能是SSAS的一项增强功能，可以通过时间智能将时间计算（或时间视图）添加到所选层次结构中，并支持以下类别的计算：

² 到现在为止的时间段

² 随时间段增长

² 移动平均

² 并行时间段比较

当然，要实现时间智能，必须先按SSAS向导或与向导相同方式定义好层次。

 

      时间维度粒度选取时的考虑

      时间维度粒度的大小往往由业务及报表的需求决定，一般粒度，如到天的情况下，建一个日期维度足以应对日常需求。但对于粒度要求较为苛刻的场合，比如一个报表需要统计到每分钟乃至10秒作为最小的时间粒度，此时更需要重点考虑的就是处理时间维度的效率了。

 

      那么时间维度表（注：对其它常规维度也适用）在多大的时候会比较危险呢？在数据仓库中，数据量过大，将导致数据的加载和查询都会面临很大的问题。根据Kimbal的经验，当维度表中的记录数达到百万或千万时，维度的处理会变得比较危险，此时若处理不当，数据仓库的性能就会出现问题。

 

      比如粒度到分钟的时间维度，如果只使用一张表记录，那么一年的记录数将是52万余条，几十年的数据累计将达到上千万条的时间记录。此时显然需要考虑将时间维度划分为两张物理表。

 

      时间维度常用的设计模式

      服务器时间维度：在SSAS中，通过服务器维度向导定义一个虚拟的时间维度，该维度可以按照维度模板自动生成层次结构架构，如图2所示。最后的时间维度属性及层次如图3所示。

图2：通过服务器维度向导定义一个虚拟的时间维度

 

图3：时间维度属性及层次

 

      当然，简单也有简单的代价，那就是日常很多具体针对时间的需求可能在这个向导里无法实现。比如节假日的标识。

 

自定义时间维度：通过自定义编写T-SQL脚本可以实现日常几乎所有的需求，按对时间粒度要求的不同又分为两种：

◇  粒度到天的日期时间维度：日期时间维度的实现结果基本如图1所示。列表1给出一种比较通用的日期维度T-SQL脚本。

 

列表1

/*假定当前数据库为TEMPA，创建Dim_Date表，表示时间维度，并插入时间段-1-1到-12-31内的所有表列*/ 以下是代码： IF EXISTS（_select_ * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_Date]'）AND type in（N'U')) Drop table [dbo].[Dim_Date] GO CREATE TABLE [dbo].[Dim_Date](     [TimeKey] [int] NOT NULL,     [TheDate] [datetime] NULL, --DateTime格式的日期     [TheDateName] [nvarchar](10）NULL, --日期名称     [TheYear] [smallint] NULL,--年份     [TheYearName] [nvarchar](10)NULL,--年份名称     [TheMonth] [smallint] NULL,--月份     [TheMonthName] [nvarchar](10)NULL,--月份名称     [TheDay] [smallint] NULL,--日     [TheDayName] [nvarchar](10)NULL,--日的名称     [TheQuarter] [smallint] NULL,--季度     [TheQuarterName] [nvarchar](10）NULL,--季度名称     [TheWeek] [smallint] NULL,--星期     [TheWeekName] [nvarchar](10）NULL,--星期名称       [Vacation_Mark] [smallint] NULL--节假日标志 ）ON [PRIMARY] DECLARE @TheDate datetime, @TheDateName nvarchar(10),                     @TheYear smallint,     @TheYearName nvarchar(10), @TheMonth smallint, @TheMonthName nvarchar(10), @TheDay smallint, @TheDayName nvarchar(10), @TheQuarter smallint, @TheQuarterName nvarchar(10), @TheWeek smallint,                     @TheWeekName nvarchar(10), @Vacation_Mark smallint,   @dDate DATETIME,--存储起始日期和结束日期 @adddays smallint--存储日期增量 _select @adddays = 1 --日期增量 _select @dDate = '1/1/2000'--当前日期 WHILE @dDate <= '12/31/2010'--结束日期 BEGIN     set timeKey = cast((left(convert(nvarchar,thedate,23),4)+substring(convert(nvarchar,thedate,23),6,2)+ substring(convert(nvarchar,thedate,23),9,2)）as int)     _select @TheDate = @dDate     _select @TheDateName = REPLACE(CONVERT(nvarchar(20),@dDate,111),'/','-')     _select @TheYear = DATENAME（yy, @dDate)     _select @TheYearName = CAST(@TheYear as nvarchar)+'年'     _select @TheMonth = DATENAME(mm, @dDate)     _select @TheMonthName =CAST(@TheMonth as nvarchar)+'月'     _select @TheDay = DATENAME（dd, @dDate)     _select @TheDayName = CAST(@TheDay as nvarchar)+'日'     _select @TheQuarter = DATENAME(Quarter, @dDate)     _select @TheQuarterName = '第' + CAST(DATENAME（Quarter, @dDate)as varchar(1))+'季度'     _select @TheWeek = DATEPART(dw, @dDate)     _select @TheWeekName = DATENAME(dw,@dDate)     _select @Vacation_Mark = CASE WHEN（@TheWeek = 1 OR @TheWeek = 7）THEN 1 ELSE 0 END     _insert INTO Dim_Date(TheDate,TheDateName,TheYear,TheYearName,TheMonth,TheMonthName,TheDay,                         TheDayName,TheQuarter,TheQuarterName,TheWeek,TheWeekName,Vacation_Mark）VALUES   （@TheDate, @TheDateName,@TheYear,@TheYearName,@TheMonth,@TheMonthName,@TheDay,@TheDayName,@TheQuarter,     @TheQuarterName,@TheWeek,@TheWeekName,@Vacation_Mark)    _select @dDate = @dDate + @adddays END GO

      这种情形下，一般采用事实表与维度表通过日期主键关联。

◇粒度到小时以下的日期维度与时刻维度的结合体：日期维度同上，时间维度的T-SQL脚本如列表2所示。

列表2

/*在Tempa库中创建Dim_Time表*/ 以下是代码：   USE Tempa IF EXISTS（_select_ * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Dim_Time]'）AND type in（N'U')) Drop table [dbo].[dim_Time] CREATE TABLE [dbo].[Dim_Time](     [TimeKey]  int IDENTITY(1,1）NOT NULL,     [TheHour]  smallint NULL, --小时     [TheHourName] nvarchar(5）NULL,--小时名称     [HalfHour] smallint NULL, --半小时     [HalfHourName] nvarchar(10）NULL, --半小时名称     [Min] smallint NULL, --分钟     [minName] nvarchar(10）NULL --分钟名称 ）ON [PRIMARY] DECLARE @dHour smallint, @addhours smallint, @chour nvarchar(5), @dhhour smallint, @hhourname nvarchar(10), @dMin smallint, @dMinName nvarchar(10) _select @dHour = 0       --起始小时 _select @dhhour = 1      --起始半小时 _select @dmin = 0        --起始分钟 WHILE  @dHour <= 23 BEGIN    WHILE @dmin <=59          BEGIN           _select @dhhour =         CASE         WHEN @dMin >=0  and @dMin <=29  THEN 1         WHEN @dMin >=30 and @dMin <=59  THEN 2 END           _select @hhourname =         CASE         WHEN @dhhour =1  THEN '前半小时'         WHEN @dhhour =2  THEN '后半小时' END         _select @dMinName = cast(@dhour as nvarchar)+':'+(case when len(@dmin)=1 then（'0'+cast(@dmin as nvarchar)）                                                         else cast(@dmin as nvarchar）end)          _select @chour = cast(@dhour as nvarchar）+ ':00' _insert INTO Dim_Time(TheHour,TheHourName,HalfHour,HalfHourName,Min,MinName）VALUES        （@dHour,@chour,@dhhour,@hhourname,@dMin,@dMinName)         _select @dMin = @dMin + 1         END _select @dmin = 0 _select @dHour = @dHour + 1 END GO

 

      时间维度常用的架构模式

      时间维度通常通过时间主键与事实表中的时间外键关联形成星形架构。常用的架构方式如图4所示，其中Dim_Date是日期维度，Dim_Time是时刻维度，并假定事实表是一个网络日志Fact_log，报表需求为分析时间粒度到每分钟的网络活动。

 

图4：常用的架构方式

 

      时间维度的维护

      良好的设计总是能够使系统具有更好的可持续性和稳定性，尽可能的避免额外的维护压力。时间维度也是如此。所以，要想使得时间维度尽量少的去手工维护，一个较好的办法是设置时间维度表中的时间数据随实际时间自动增长。比如，设置一个时间段，当未来的最大日期比当前日期小于这个时间段时，系统自动插入一个定长的时间段，使得新的时间维度表能够持续满足系统需求。列表3所示的T-SQL脚本是基于上面的时间维度表编写的一个可扩充时间段的示例。

 

列表3

以下是代码： DECLARE @TheDate datetime, @TheDateName nvarchar(10),                     @TheYear smallint,     @TheYearName nvarchar(10), @TheMonth smallint, @TheMonthName nvarchar(10), @TheDay smallint, @TheDayName nvarchar(10), @TheQuarter smallint, @TheQuarterName nvarchar(10), @TheWeek smallint,                     @TheWeekName nvarchar(10), @Vacation_Mark smallint,   @dDate DATETIME,--存储起始日期和结束日期 @adddays smallint--存储日期增量 _select @adddays = 1 --日期增量 _select @dDate = max(TheDate）from Dim_Date –取当前系统时间维度表最大日期 WHILE @dDate <= dateAdd(year,5,getDate())--结束日期:当前日期往后顺延5年，可以根据实际需求设置时长 BEGIN     set timeKey = cast((left(convert(nvarchar,thedate,23),4)+substring(convert(nvarchar,thedate,23),6,2)+ substring(convert(nvarchar,thedate,23),9,2)）as int)     _select @TheDate = @dDate     _select @TheDateName = REPLACE(CONVERT(nvarchar(20),@dDate,111),'/','-')     _select @TheYear = DATENAME（yy, @dDate)     _select @TheYearName = CAST(@TheYear as nvarchar)+'年'     _select @TheMonth = DATENAME(mm, @dDate)     _select @TheMonthName =CAST(@TheMonth as nvarchar)+'月'     _select @TheDay = DATENAME（dd, @dDate)     _select @TheDayName = CAST(@TheDay as nvarchar)+'日'     _select @TheQuarter = DATENAME(Quarter, @dDate)     _select @TheQuarterName = '第' + CAST(DATENAME（Quarter, @dDate)as varchar(1))+'季度'     _select @TheWeek = DATEPART(dw, @dDate)     _select @TheWeekName = DATENAME(dw,@dDate)     _select @Vacation_Mark = CASE WHEN（@TheWeek = 1 OR @TheWeek = 7）THEN 1 ELSE 0 END     _insert INTO Dim_Date(TheDate,TheDateName,TheYear,TheYearName,TheMonth,TheMonthName,TheDay,                         TheDayName,TheQuarter,TheQuarterName,TheWeek,TheWeekName,Vacation_Mark）VALUES   （@TheDate, @TheDateName,@TheYear,@TheYearName,@TheMonth,@TheMonthName,@TheDay,@TheDayName,@TheQuarter,     @TheQuarterName,@TheWeek,@TheWeekName,@Vacation_Mark)    _select @dDate = @dDate + @adddays END GO

      总结

      时间维度的设计基于业务需求，从而确定是使用服务器时间维度还是物理表的时间维度。若采用物理表形式的时间维度，还要考虑时间粒度，到天以下的较细粒度一般需要建两张时间维度表：日期维度和时刻维度。时间维度与常规维度并无本质的区别，所以不一定要在SSAS中选择时间维度特有的设计方式。设计日期维度可以使用日期代理键，也可以直接使用日期作为主键，推荐使用代理键（时刻维度同此）。通过自定义设计时间维度能够满足更广泛的业务需求。时间维度在OLAP中一般采用星形架构。