在数据仓库中，无一例外地需要和时间维度打交道，因此设计合理的时间维度，也是一个数据仓库项目开始必备的资源储备，如果有这方面的积累，就不用到处寻找合适的设计模型以及存储过程的代码了，否则可能需要花费一定的时间去寻找符合该项目合适的存储过程，或者自己动手编写。

一般来说，时间维度的创建要根据实际的数据仓库应用来，基本上可以分为天、月的时间维度表，更细的可以分为半小时时间段，小时时间段等等，一般数据量大的时间维度都是利用存储过程来生成的。

下面介绍一些时间维度表的设计结构。 

<1>  时间维度表1（两列都是字段）

维度属性	维度属性
日期关键字	日历年月
日期完全描述	日历季度
星期	日历半年度
纪元日编号	日历年
纪元周编号	财政周
纪元月编号	年度财政周数
日历日期编号	财政月
日历周编号	年度财政月数
日历月编号	财政年月
财政月日编号	财政季度
财政周编号	财政季年度
财政月编号	财政半年度
周末指示符	财政年
月末指示符	节假日指示符
日历周结束日期	星期指示符
年度日历周数	销售时令
日历月名	重大事件
年度日历月数	其它

 

<1>  时间维度表2a.日期维度

名称	代码	数据类型	主外键
序列号	C_ID	int	P
日期	C_DATE	datetime
星期	C_WEEK	varchar(12)
月份	C_MONTH	varchar(2)
年	C_YEAR	varchar(4)
季度	C_QUARTER	varchar(4)

名称	代码	数据类型	主外键
序列号	C_ID	Varchar(50)
时段	C_SHIDUAN	Varchar(4000)

名称	代码	数据类型	主外键
序列号	C_ID	int	P
时间段	C_Section	varchar(50)
开始时间	C_Begin	varchar(50)
结束时间	C_End	varchar(50)

3、比较完整的时间维度设计

Name	Type	Nullable	Comments
DAY_CODE	VARCHAR2(8)	N	日代码
DAY_LONG_DESC	VARCHAR2(30)	N	日完整名称
DAY_MEDIUM_DESC	VARCHAR2(30)	N	日中等长度名
DAY_SHORT_DESC	VARCHAR2(30)	N	日短名
WEEK_CODE	VARCHAR2(8)	N	周代码
WEEK_LONG_DESC	VARCHAR2(30)	N	周完整名称
WEEK_MEDIUM_DESC	VARCHAR2(30)	N	周中等长度名
WEEK_SHORT_DESC	VARCHAR2(30)	N	周短名
TEN_DAY_CODE	VARCHAR2(8)	N	旬代码
TEN_DAY_LONG_DESC	VARCHAR2(30)	N	旬完整名称
TEN_DAY_MEDIUM_DESC	VARCHAR2(30)	N	旬中等长度名
TEN_DAY_SHORT_DESC	VARCHAR2(30)	N	旬短名
MONTH_CODE	VARCHAR2(8)	N	月代码
MONTH_LONG_DESC	VARCHAR2(30)	N	月完整名称
MONTH_MEDIUM_DESC	VARCHAR2(30)	N	月中等长度名
MONTH_SHORT_DESC	VARCHAR2(30)	N	月短名
QUARTER_CODE	VARCHAR2(8)	N	季代码
QUARTER_LONG_DESC	VARCHAR2(30)	N	季完整名称
QUARTER_MEDIUM_DESC	VARCHAR2(30)	N	季中等长度名
QUARTER_SHORT_DESC	VARCHAR2(30)	N	季短名
HALF_YEAR_CODE	VARCHAR2(8)	N	半年代码
HALF_LONG_DESC	VARCHAR2(30)	N	半年完整名称
HALF_MEDIUM_DESC	VARCHAR2(30)	N	半年中等长度名
HALF_SHORT_DESC	VARCHAR2(30)	N	半年短名
YEAR_CODE	VARCHAR2(8)	N	年代码
YEAR_LONG_DESC	VARCHAR2(30)	N	年完整名称
YEAR_MEDIUM_DESC	VARCHAR2(30)	N	年中等长度名
YEAR_SHORT_DESC	VARCHAR2(30)	N	年短名
ALL_TIME_CODE	VARCHAR2(8)	N	全部时间代码
ALL_TIME_DESC	VARCHAR2(30)	N	全部时间名称
DAY_TIMESPAN	NUMBER(3)	N	日时间跨天
DAY_END_DATE	VARCHAR2(8)	N	结束日期
WEEK_TIMESPAN	NUMBER(3)	N	周跨天数
WEEK_END_DATE	VARCHAR2(8)	N	周结束日期
TEN_DAY_TIMESPAN	NUMBER(3)	N	旬跨天数
TEN_DAY_END_DATE	VARCHAR2(8)	N	旬结束日期
MONTH_TIMESPAN	NUMBER(3)	N	月跨天数
MONTH_END_DATE	VARCHAR2(8)	N	月结束日期
QUARTER_TIMESPAN	NUMBER(3)	N	季跨天数
QUARTER_END_DATE	VARCHAR2(8)	N	季结束日期
HALF_YEAR_TIMESPAN	NUMBER(3)	N	半年跨天数
HALF_YEAR_END_DATE	VARCHAR2(8)	N	半年结束日期
YEAR_TIMESPAN	NUMBER(3)	N	年跨天数
YEAR_END_DATE	VARCHAR2(8)	N	年结束日期
BDISABLED	CHAR(1)	N

create

 

or

 

replace

 package body PKG_LOAD_DIM_TIME

as

  

/*

***********************************

  ** 是否合理自然日

  ** 如果是合理自然日则返回1,否则返回0

  ***********************************

*/

  

function

 F_Is_Day_ID

  (

    p_TIME_ID DIW.DW_DIM_TIME.DAY_CODE

%

type

  ) 

return

 

number

  

is

    v_Date date;

  

begin

    v_Date :

=

 to_date(p_TIME_ID, 

'

YYYYMMDD

'

);

    

return

 

1

;

  exception

    

when

 others 

then

      

return

 

0

;

  

end

 F_Is_Day_ID;

  

/*

***********************************

  ** 加载时间维度数据

  ***********************************

*/

  

procedure

 P_BUILD_DIM_TIME

  (

    p_START_DATE DIW.DW_DIM_TIME.DAY_CODE

%

type,  

--

开始日期

    p_END_DATE DIW.DW_DIM_TIME.DAY_CODE

%

type     

--

结束日期

  )

  

is

    v_START_DATE date;  

--

开始日期

    v_END_DATE date;    

--

结束日期

    v_DATE date;

    v_YEAR 

varchar2

(

4

);

    v_QUARTER 

varchar2

(

1

);

    v_MONTH 

varchar2

(

2

);

    v_TEN_DAY 

varchar2

(

1

);

    v_WEEK 

varchar2

(

2

);

    v_WEEK_YEAR 

varchar2

(

4

);

    v_DAY 

varchar2

(

2

);

  

begin

    

--

BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行开始...');

    

execute

 immediate 

'

TRUNCATE TABLE DIW.ODS_DIM_TIME

'

;

    

if

 F_Is_Day_ID(p_START_DATE)

=

1

 

and

 F_Is_Day_ID(p_END_DATE)

=

1

 

then

        v_START_DATE :

=

 TO_DATE(p_START_DATE,

'

YYYYMMDD

'

);

        v_END_DATE :

=

 TO_DATE(p_END_DATE,

'

YYYYMMDD

'

);

        

if

 v_END_DATE 

-

 v_START_DATE 

>=

 

0

 

then

          

--

开始生成

          

for

 i 

in

 

0

..v_END_DATE 

-

 v_START_DATE loop

            v_DATE :

=

 v_START_DATE

+

i;

            v_YEAR :

=

 TO_CHAR(v_DATE,

'

YYYY

'

);

            v_QUARTER :

=

 TO_CHAR(v_DATE,

'

Q

'

);

            v_MONTH :

=

 TO_CHAR(v_DATE,

'

MM

'

);

            v_TEN_DAY :

=

 

case

                           

when

 TO_CHAR(v_DATE,

'

DD

'

)

<

'

11

'

 

then

 

'

1

'

                           

when

 TO_CHAR(v_DATE,

'

DD

'

)

<

'

21

'

 

then

 

'

2

'

                           

when

 TO_CHAR(v_DATE,

'

DD

'

)

<

'

32

'

 

then

 

'

3

'

                         

end

;

            

--

周方案一:星期被年分开,实际是7天分段不是自然周

            

--

v_WEEK := TO_CHAR(v_DATE,'WW');

            

--

v_WEEK_YEAR := v_YEAR;

            

--

周方案二：本星期星期四所在年

            

--

v_WEEK := TO_CHAR(v_DATE,'IW');

            

--

select TO_CHAR(NEXT_DAY(v_DATE-7,2)+3,'YYYY') into v_WEEK_YEAR from dual;

            

--

--v_WEEK_YEAR := TO_CHAR(NEXT_DAY(v_DATE-7,2)+3,'YYYY');--奇怪的错误？？

            

--

周方案三：自然周，周被年分,没找到函数支持。

            

select

              LPAD(TO_CHAR(TRUNC((v_DATE

              

-

to_date(v_YEAR

||

'

0101

'

,

'

yyyymmdd

'

)

              

+

DECODE(TO_NUMBER(to_CHAR(to_date(v_YEAR

||

'

0101

'

,

'

yyyymmdd

'

),

'

D

'

)),

1

,

7

,

              TO_NUMBER(to_CHAR(to_date(v_YEAR

||

'

0101

'

,

'

yyyymmdd

'

),

'

D

'

))

-

1

)

-

1

)

/

7

+

1

)),

2

,

'

0

'

)

              

INTO

 v_WEEK

            

from

 dual;

            v_WEEK_YEAR :

=

 v_YEAR;

            v_DAY :

=

 TO_CHAR(v_DATE,

'

DD

'

);

            

insert

 

into

 DIW.DW_DIM_TIME

            (

              DAY_CODE,

              DAY_LONG_DESC,

              DAY_MEDIUM_DESC,

              DAY_SHORT_DESC,

              WEEK_CODE,

              WEEK_LONG_DESC,

              WEEK_MEDIUM_DESC,

              WEEK_SHORT_DESC,

              TEN_DAY_CODE,

              TEN_DAY_LONG_DESC,

              TEN_DAY_MEDIUM_DESC,

              TEN_DAY_SHORT_DESC,

              MONTH_CODE,

              MONTH_LONG_DESC,

              MONTH_MEDIUM_DESC,

              MONTH_SHORT_DESC,

              QUARTER_CODE,

              QUARTER_LONG_DESC,

              QUARTER_MEDIUM_DESC,

              QUARTER_SHORT_DESC,

              HALF_YEAR_CODE,

              HALF_LONG_DESC,

              HALF_MEDIUM_DESC,

              HALF_SHORT_DESC,

              YEAR_CODE,

              YEAR_LONG_DESC,

              YEAR_MEDIUM_DESC,

              YEAR_SHORT_DESC,

              ALL_TIME_CODE,

              ALL_TIME_DESC,

              DAY_TIMESPAN,

              DAY_END_DATE,

              WEEK_TIMESPAN,

              WEEK_END_DATE,

              TEN_DAY_TIMESPAN,

              TEN_DAY_END_DATE,

              MONTH_TIMESPAN,

              MONTH_END_DATE,

              QUARTER_TIMESPAN,

              QUARTER_END_DATE,

              HALF_YEAR_TIMESPAN,

              HALF_YEAR_END_DATE,

              YEAR_TIMESPAN,

              YEAR_END_DATE

            )

            

values

            (

              v_YEAR

||

v_MONTH

||

v_DAY,

              v_YEAR

||

'

年

'

||

v_MONTH

||

'

月

'

||

v_DAY

||

'

日

'

,

              

/*

v_MONTH||'月'||

*/

v_DAY

||

'

日

'

,

              v_YEAR

||

'

-

'

||

v_MONTH

||

'

-

'

||

v_DAY,

              v_WEEK_YEAR

||

'

W

'

||

v_WEEK,

              v_WEEK_YEAR

||

'

年第

'

||

v_WEEK

||

'

周

'

,

              

'

第

'

||

v_WEEK

||

'

周

'

,

              v_WEEK_YEAR

||

'

-W

'

||

v_WEEK,

              v_YEAR

||

v_MONTH

||

'

X

'

||

v_TEN_DAY,

              v_YEAR

||

'

年

'

||

v_MONTH

||

'

月

'

||

decode(v_TEN_DAY,

'

1

'

,

'

上

'

,

'

2

'

,

'

中

'

,

'

下

'

)

||

'

旬

'

,

              decode(v_TEN_DAY,

'

1

'

,

'

上

'

,

'

2

'

,

'

中

'

,

'

下

'

)

||

'

旬

'

,

              v_YEAR

||

'

-

'

||

v_MONTH

||

'

-X

'

||

v_TEN_DAY,

              v_YEAR

||

v_MONTH,

              v_YEAR

||

'

年

'

||

v_MONTH

||

'

月

'

,

              v_MONTH

||

'

月

'

,

              v_YEAR

||

'

-

'

||

v_MONTH,

              v_YEAR

||

'

Q

'

||

v_QUARTER,

              v_YEAR

||

'

年第

'

||

v_QUARTER

||

'

季度

'

,

              

'

第

'

||

v_QUARTER

||

'

季度

'

,

              v_YEAR

||

'

-

'

||

'

Q

'

||

v_QUARTER,

              v_YEAR

||

'

H

'

||

decode(v_QUARTER,

'

1

'

,

'

1

'

,

'

2

'

,

'

1

'

,

'

2

'

),

              v_YEAR

||

'

年

'

||

decode(v_QUARTER,

'

1

'

,

'

上

'

,

'

2

'

,

'

上

'

,

'

下

'

)

||

'

半年

'

,

              decode(v_QUARTER,

'

1

'

,

'

上

'

,

'

2

'

,

'

上

'

,

'

下

'

)

||

'

半年

'

,

              v_YEAR

||

'

-

'

||

'

H

'

||

decode(v_QUARTER,

'

1

'

,

'

1

'

,

'

2

'

,

'

1

'

,

'

2

'

),

              v_YEAR,

              v_YEAR

||

'

年

'

,

              v_YEAR

||

'

年

'

,

              v_YEAR,

              

'

ALL

'

,

              

'

ALL_TIME

'

,

              

1

,

              v_YEAR

||

v_MONTH

||

v_DAY,

              

case

                

when

                  TO_CHAR(NEXT_DAY(v_DATE

-

7

,

2

),

'

YYYY

'

)

<

v_WEEK_YEAR

                

then

                  NEXT_DAY(v_DATE,

2

)

-

TO_DATE(v_WEEK_YEAR

||

'

0101

'

,

'

YYYYMMDD

'

)

                

when

                  TO_CHAR(NEXT_DAY(v_DATE,

2

),

'

YYYY

'

)

>

v_WEEK_YEAR

                

then

                  TO_DATE(v_WEEK_YEAR

||

'

1231

'

,

'

YYYYMMDD

'

)

-

NEXT_DAY(v_DATE

-

7

,

2

)

+

1

                

else

 

7

              

end

,

              

case

                

when

                  TO_CHAR(NEXT_DAY(v_DATE,

2

),

'

YYYY

'

)

>

v_WEEK_YEAR

                

then

 v_WEEK_YEAR

||

'

1231

'

                

else

 TO_CHAR(NEXT_DAY(v_DATE,

2

)

-

1

,

'

YYYYMMDD

'

)

              

end

,

              decode(v_TEN_DAY,

'

3

'

,LAST_DAY(v_DATE)

-

TO_DATE(v_YEAR

||

v_MONTH

||

'

21

'

,

'

YYYYMMDD

'

)

+

1

,

10

),

              decode(v_TEN_DAY,

'

3

'

,TO_CHAR(LAST_DAY(v_DATE),

'

YYYYMMDD

'

),

'

2

'

,v_YEAR

||

v_MONTH

||

'

20

'

,v_YEAR

||

v_MONTH

||

'

10

'

),

              TO_NUMBER(TO_CHAR(LAST_DAY(v_DATE),

'

DD

'

)),

              TO_CHAR(LAST_DAY(v_DATE),

'

YYYYMMDD

'

),

              decode(v_QUARTER,

'

1

'

,TO_DATE(v_YEAR

||

'

0331

'

,

'

YYYYMMDD

'

)

-

TO_DATE(v_YEAR

||

'

0101

'

,

'

YYYYMMDD

'

)

+

1

,

'

2

'

,

91

,

92

),

              decode(v_QUARTER,

'

1

'

,v_YEAR

||

'

0331

'

,

'

2

'

,v_YEAR

||

'

0630

'

,

'

3

'

,v_YEAR

||

'

0930

'

,v_YEAR

||

'

1231

'

),

              decode(v_QUARTER,

'

3

'

,

184

,

'

4

'

,

184

,TO_DATE(v_YEAR

||

'

0630

'

,

'

YYYYMMDD

'

)

-

TO_DATE(v_YEAR

||

'

0101

'

,

'

YYYYMMDD

'

)

+

1

),

              decode(v_QUARTER,

'

1

'

,v_YEAR

||

'

0630

'

,

'

2

'

,v_YEAR

||

'

0630

'

,v_YEAR

||

'

1231

'

),

              TO_DATE(v_YEAR

||

'

1231

'

,

'

YYYYMMDD

'

)

-

TO_DATE(v_YEAR

||

'

0101

'

,

'

YYYYMMDD

'

)

+

1

,

              v_YEAR

||

'

1231

'

            )

            ;

          

end

 loop;

          

commit

;

        

end

 

if

;

    

end

 

if

;

    

    

--

BICODE.PKG_ETL_CONTROLLER.P_UPDATE_ETL_TIME_INFO('DW','DIM_TIME');

    

--

BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('一般','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行结束...');

    exception

      

when

 others 

then

        

rollback

;

        

--

BICODE.PKG_ETL_CONTROLLER.P_ETL_WRITE_LOG('异常','BICODE.PKG_LOAD_DIM_TIME.P_BUILD_DIM_TIME执行失败...');    

    

  

end

 P_BUILD_DIM_TIME;

end

 PKG_LOAD_DIM_TIME;

有时候需要精确度到月份的时间维度表，由于上面的日期时间维度表相对信息比较丰富，可以建立一个视图来影射一个月度时间维度表，如下图所示：

具体的视图Sql如下所示：

 

CREATE

 

OR

 

REPLACE

 

VIEW

 DW_DIM_TIME_MONTH_V 

AS

SELECT

 

DISTINCT

  T.MONTH_CODE,

  T.MONTH_LONG_DESC,

  T.MONTH_MEDIUM_DESC,

  T.MONTH_SHORT_DESC,

  T.QUARTER_CODE,

  T.QUARTER_LONG_DESC,

  T.QUARTER_MEDIUM_DESC,

  T.QUARTER_SHORT_DESC,

  T.HALF_YEAR_CODE,

  T.HALF_LONG_DESC,

  T.HALF_MEDIUM_DESC,

  T.HALF_SHORT_DESC,

  T.YEAR_CODE,

  T.YEAR_LONG_DESC,

  T.YEAR_MEDIUM_DESC,

  T.YEAR_SHORT_DESC

FROM

 DIW.DW_DIM_TIME T;