About EDW时间维度表的建立参考(DWD_CALENDAR)

最新推荐文章于 2023-06-13 20:11:25 发布

ddttoop

最新推荐文章于 2023-06-13 20:11:25 发布

阅读量372

点赞数

分类专栏： DataWarehouse Oracle

本文链接：https://blog.csdn.net/weixin_45883933/article/details/105563366

版权

本文档介绍了如何建立企业数据仓库(EDW)的时间维度表DWD_CALENDAR，包括详细步骤：首先从0开始阐述维度表的概念，接着详细讲解创建表的流程，最后探讨了用于维护表的存储过程。

摘要由CSDN通过智能技术生成

0. 维度表

在这里插入图片描述

1. 创建表

-- Create table
create table DWD_CALENDAR
(
  site                VARCHAR2(40),
  factory             VARCHAR2(40),
  period_date         DATE,
  period              VARCHAR2(5),
  shift_start_timekey VARCHAR2(40),
  shift_end_timekey   VARCHAR2(40),
  date_start_timekey  VARCHAR2(15),
  date_end_timekey    VARCHAR2(15),
  shift_timekey       VARCHAR2(40),
  shift_name          VARCHAR2(20),
  date_timekey        VARCHAR2(15),
  week_timekey        VARCHAR2(40),
  month_timekey       VARCHAR2(40),
  quarter_timekey     VARCHAR2(21),
  year_timekey        VARCHAR2(16),
  interface_time      DATE
)
tablespace EDW_COM_DAT
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index IDX_DWD_CALENDAR_1 on DWD_CALENDAR (SHIFT_TIMEKEY)
  tablespace EDW_COM_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_DWD_CALENDAR_2 on DWD_CALENDAR (DATE_TIMEKEY, WEEK_TIMEKEY, MONTH_TIMEKEY)
  tablespace EDW_COM_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_DWD_CALENDAR_3 on DWD_CALENDAR (WEEK_TIMEKEY)
  tablespace EDW_COM_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_DWD_CALENDAR_4 on DWD_CALENDAR (MONTH_TIMEKEY)
  tablespace EDW_COM_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
create index IDX_DWD_CALENDAR_5 on DWD_CALENDAR (PERIOD_DATE, FACTORY, PERIOD)
  tablespace EDW_COM_IDX
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  )
  nologging;
-- Grant/Revoke object privileges 
grant select on DWD_CALENDAR to EDWREAD;
grant select on DWD_CALENDAR to EDWTEAM;

2. 存储过程

CREATE OR REPLACE PROCEDURE P_dwd_calendar (
   PVVI_SITE           IN     VARCHAR2,
   PVVI_FACTORY        IN     VARCHAR2,
   PVVI_YEAR           IN     VARCHAR2,
   PVVO_RETURN_VALUE      OUT VARCHAR2)
IS
   --=================================================================================
   --DESCRIPTION : calendar Information I/F

   --=================================================================================
   --DATE             NAME             VERSION(x.xx)       DESCRIPTION
   --2017-11-07                        0.01                Initial Release

   --=================================================================================
   --                               VARIALBLE DECLARATION
   --=================================================================================
   --SYSTEM VARIALBLE--------------------------------------------------------------
   L_EXP_USER                EXCEPTION;
   LVV_PROCEDURE_NAME        VARCHAR2 (30);
   LVV_RESULT_COUNT          VARCHAR2 (20);
   LVN_DML_COUNT             NUMBER;
   LVV_MESSAGE               VARCHAR2 (500);
   LVV_START_TIMEKEY         VARCHAR2 (40);
   LVV_END_TIMEKEY           VARCHAR2 (40);
   LVD_INTERFACE_TIME        DATE;
   LVV_ETL_LOG_HEAD          VARCHAR2 (500);
   LVV_FIRST_START_TIMEKEY   VARCHAR2 (40);
   LVV_LAST_END_TIMEKEY      VARCHAR2 (40);

   --CUSTOMIZED VARIALBLE----------------------------------------------------------
   lvd_start_date            DATE;
   lvd_end_date              DATE;
   lvv_week                  VARCHAR2 (40);
   lvv_factory               VARCHAR2 (40);

   cursor cur_dayweek_list is
         select f.site,
                f.factory,
                'D' period,
                TO_DATE(to_char(wwm, 'YYYYMMDD') || ' 080000','YYYYMMDD HH24MISS') period_date,
                to_char(wwm, 'yyyymmdd') || ' 080000' shift_start_timekey,
                to_char(wwm, 'yyyymmdd') || ' 200000' shift_end_timekey,
                to_char(wwm, 'yyyymmdd') date_start_timekey,
                to_char