一般来说,时间维度的创建要根据实际的数据仓库应用来,基本上可以分为天、月的时间维度表,更细的可以分为半小时时间段,小时时间段等等,一般数据量大的时间维度都是利用存储过程来生成的。
下面介绍一些时间维度表的设计结构。 <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) | |
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' 有时候需要精确度到月份的时间维度表,由于上面的日期时间维度表相对信息比较丰富,可以建立一个视图来影射一个月度时间维度表,如下图所示: 具体的视图Sql如下所示: Code 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;
|