数据仓库开发之路之三--时间维度的创建

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

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

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

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

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

 

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

 

  

  
名称
  
  

  
代码
  
  

  
数据类型
  
  

  
主外键
  
 
 

  

  
序列号
  
  

  
C_ID
  
  

  
int
  
  

  
P
  
 
 

  

  
日期
  
  

  
C_DATE
  
  

  
datetime
  
  

  
 
  
 
 

  

  
星期
  
  

  
C_WEEK
  
  

  
varchar(12)
  
  

  
 
  
 
 

  

  
月份
  
  

  
C_MONTH
  
  

  
varchar(2)
  
  

  
 
  
 
 

  

  
年
  
  

  
C_YEAR
  
  

  
varchar(4)
  
  

  
 
  
 
 

  

  
季度
  
  

  
C_QUARTER
  
  

  
varchar(4)
  
  

  
 
  
 

b.半小时时间维度

 

  

  
名称
  
  

  
代码
  
  

  
数据类型
  
  

  
主外键
  
 
 

  

  
序列号
  
  

  
C_ID
  
  

  
Varchar(50)
  
  

  
 
  
 
 

  

  
时段
  
  

  
C_SHIDUAN
  
  

  
Varchar(4000)
  
  

  
 
  
 

c.时间段维度

 

  

  
名称
  
  

  
代码
  
  

  
数据类型
  
  

  
主外键
  
 
 

  

  
序列号
  
  

  
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;