BI Direct Useful Tips- A -Date Dimension Data Seeding - Store Procedure

It is very common to have the date dimension pre-seeded. This blog is an attempt to list down the parameter driven store procedure and package to support date dimension data seeding. I believe it covers almost all attributes on date dimension which would be useful for time related analysis of transactional data.

Typical Date Dimension Script

CREATE TABLE W_DAY_D
(
  ROW_WID                    NUMBER(10)         NOT NULL,
  CALENDAR_DATE              DATE               NOT NULL,
  CAL_HALF                   NUMBER(2),
  CAL_MONTH                  NUMBER(2),
  CAL_QTR                    NUMBER(1),
  CAL_TRIMESTER              NUMBER(10),
  CAL_WEEK                   NUMBER(2),
  CAL_YEAR                   NUMBER(4),
  DATASOURCE_NUM_ID          NUMBER(10)         NOT NULL,
  DATE_KEY                   NUMBER(10),
  DAY_AGO_DT                 DATE,
  DAY_AGO_KEY                NUMBER(10),
  DAY_AGO_WID                NUMBER(10),
  DAY_DT                     DATE,
  DAY_NAME                   VARCHAR2(30 CHAR),
  DAY_OF_MONTH               NUMBER(2),
  DAY_OF_WEEK                NUMBER(1),
  DAY_OF_YEAR                NUMBER(3),
  FSCL_DAY_OF_MONTH          NUMBER(2),
  FSCL_DAY_OF_WEEK           NUMBER(2),
  FSCL_DAY_OF_YEAR           NUMBER(3),
  FSCL_HALF                  NUMBER(1),
  FSCL_MONTH                 NUMBER(2),
  FSCL_QTR                   NUMBER(1),
  FSCL_TRIMESTER             NUMBER(10),
  FSCL_WEEK                  NUMBER(2),
  FSCL_YEAR                  NUMBER(4),
  FSCL_FST_DAY_KEY           NUMBER(10),
  HALF_AGO_DT                DATE,
  HALF_AGO_KEY               NUMBER(10),
  HALF_AGO_WID               NUMBER(10),
  INTEGRATION_ID             VARCHAR2(30 CHAR),
  JULIAN_DAY_NUM             NUMBER(10),
  JULIAN_MONTH_NUM           NUMBER(10),
  JULIAN_QTR_NUM             NUMBER(10),
  JULIAN_TER_NUM             NUMBER(10),
  JULIAN_WEEK_NUM            NUMBER(10),
  JULIAN_YEAR_NUM            NUMBER(10),
  MONTH_AGO_DT               DATE,
  MONTH_AGO_KEY              NUMBER(10),
  MONTH_AGO_WID              NUMBER(10),
  MONTH_NAME                 VARCHAR2(30 CHAR),
  PERIOD_KEY                 NUMBER(10),
  PER_NAME_FSCL_HALF         VARCHAR2(50 CHAR),
  PER_NAME_FSCL_MNTH         VARCHAR2(50 CHAR),
  PER_NAME_FSCL_QTR          VARCHAR2(50 CHAR),
  PER_NAME_FSCL_TER          VARCHAR2(50 CHAR),
  PER_NAME_FSCL_WEEK         VARCHAR2(50 CHAR),
  PER_NAME_FSCL_YEAR         VARCHAR2(50 CHAR),
  PER_NAME_HALF              VARCHAR2(50 CHAR),
  PER_NAME_MONTH             VARCHAR2(50 CHAR),
  PER_NAME_QTR               VARCHAR2(50 CHAR),
  PER_NAME_TER               VARCHAR2(50 CHAR),
  PER_NAME_WEEK              VARCHAR2(50 CHAR),
  PER_NAME_YEAR              VARCHAR2(50 CHAR),
  QUARTER_AGO_DT             DATE,
  QUARTER_AGO_KEY            NUMBER(10),
  QUARTER_AGO_WID            NUMBER(10),
  TRIMESTER_AGO_DT           DATE,
  TRIMESTER_AGO_KEY          NUMBER(10),
  TRIMESTER_AGO_WID          NUMBER(10),
  WEEK_AGO_DT                DATE,
  WEEK_AGO_KEY               NUMBER(10),
  WEEK_AGO_WID               NUMBER(10),
  YEAR_AGO_DT                DATE,
  YEAR_AGO_KEY               NUMBER(10),
  YEAR_AGO_WID               NUMBER(10),
  M_END_CAL_DT_WID           NUMBER(10),
  M_STRT_CAL_DT_WID          NUMBER(10),
  CAL_WEEK_END_DT_WID        NUMBER(10),
  CAL_WEEK_START_DT_WID      NUMBER(10),
  CAL_QTR_END_DT_WID         NUMBER(10),
  CAL_QTR_START_DT_WID       NUMBER(10),
  CAL_YEAR_END_DT_WID        NUMBER(10),
  CAL_YEAR_START_DT_WID      NUMBER(10),
  FST_DAY_CAL_WK_FLG         CHAR(1 CHAR),
  LAST_DAY_CAL_WK_FLG        CHAR(1 CHAR),
  FST_DAY_CAL_MNTH_FLG       CHAR(1 CHAR),
  LAST_DAY_CAL_MNTH_FLG      CHAR(1 CHAR),
  FST_DAY_CAL_QTR_FLG        CHAR(1 CHAR),
  LAST_DAY_CAL_QTR_FLG       CHAR(1 CHAR),
  FST_DAY_CAL_YEAR_FLG       CHAR(1 CHAR),
  LAST_DAY_CAL_YEAR_FLG      CHAR(1 CHAR),
  FSCL_WEEK_START_DT         DATE,
  FSCL_WEEK_END_DT           DATE,
  FSCL_MNTH_START_DT         DATE,
  FSCL_MNTH_END_DT           DATE,
  FSCL_QTR_START_DT          DATE,
  FSCL_QTR_END_DT            DATE,
  FSCL_YEAR_START_DT         DATE,
  FSCL_YEAR_END_DT           DATE,
  FSCL_WEEK_START_DT_WID     NUMBER(10),
  FSCL_WEEK_END_DT_WID       NUMBER(10),
  FSCL_MONTH_START_DT_WID    NUMBER(10),
  FSCL_MONTH_END_DT_WID      NUMBER(10),
  FSCL_QTR_START_DT_WID      NUMBER(10),
  FSCL_QTR_END_DT_WID        NUMBER(10),
  FSCL_YEAR_START_DT_WID     NUMBER(10),
  FSCL_YEAR_END_DT_WID       NUMBER(10),
  FSCL_DIM_QTR_NUM           NUMBER(3),
  FSCL_DIM_MONTH_NUM         NUMBER(5),
  FSCL_MONTH_WEEK_NUM        NUMBER(1),
  FSCL_DIM_WEEK_NUM          NUMBER(6),
  FSCL_DIM_YEAR_NUM          NUMBER(4),
  W_CURRENT_CAL_DAY_CODE     VARCHAR2(50 CHAR),
  W_CURRENT_CAL_WEEK_CODE    VARCHAR2(50 CHAR),
  W_CURRENT_CAL_MONTH_CODE   VARCHAR2(50 CHAR),
  W_CURRENT_CAL_QTR_CODE     VARCHAR2(50 CHAR),
  W_CURRENT_CAL_YEAR_CODE    VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_WEEK_CODE   VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_MONTH_CODE  VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_QTR_CODE    VARCHAR2(50 CHAR),
  W_CURRENT_FSCL_YEAR_CODE   VARCHAR2(50 CHAR),
  FST_DAY_FSCL_WEEK_FLG      CHAR(1 CHAR),
  LAST_DAY_FSCL_WEEK_FLG     CHAR(1 CHAR),
  FST_DAY_FSCL_MNTH_FLG      CHAR(1 CHAR),
  LAST_DAY_FSCL_MNTH_FLG     CHAR(1 CHAR),
  FST_DAY_FSCL_QTR_FLG       CHAR(1 CHAR),
  LAST_DAY_FSCL_QTR_FLG      CHAR(1 CHAR),
  FST_DAY_FSCL_YEAR_FLG      CHAR(1 CHAR),
  LAST_DAY_FSCL_YEAR_FLG     CHAR(1 CHAR),
  W_INSERT_DT                DATE,
  W_UPDATE_DT                DATE,
  TENANT_ID                  VARCHAR2(80 CHAR),
  X_CUSTOM                   VARCHAR2(10 CHAR)
);

Data Seeding Script for Date Dimension 

Note :- Choose start date and end date to populate the date dimension. 

CREATE OR REPLACE PACKAGE W_DAY_D_DATASEED
AS
P_ST_DT DATE := null;
P_ED_DT DATE := null;
PROCEDURE W_DAY_D_PROC;
PROCEDURE BI_TIME_DRIVER;
END W_DAY_D_DATASEED;
/
sho err

CREATE OR REPLACE PACKAGE BODY W_DAY_D_DATASEED
AS
PROCEDURE BI_TIME_DRIVER
as
v_stdt date:='01/JAN/1995';
v_eddt date:='31/DEC/2050';BEGIN
P_ST_DT := v_stdt;
P_ED_DT := v_eddt;
W_DAY_D_PROC;      
END BI_TIME_DRIVER;
PROCEDURE W_DAY_D_PROC
IS
V_ST_DT DATE := P_ST_DT;
V_ED_DT DATE := P_ED_DT;
BEGIN
WHILE V_ED_DT >= V_ST_DT LOOP
INSERT INTO W_DAY_D (ROW_WID ,
CALENDAR_DATE                 ,
CAL_HALF                      ,
CAL_MONTH                     ,
CAL_QTR                       ,
CAL_TRIMESTER                 ,
CAL_WEEK                      ,
CAL_YEAR                      ,
DATASOURCE_NUM_ID             ,
DATE_KEY                      ,
DAY_AGO_DT                    ,
DAY_AGO_KEY                   ,
DAY_AGO_WID                   ,
DAY_DT                        ,
DAY_NAME                      ,
DAY_OF_MONTH                  ,
DAY_OF_WEEK                   ,
DAY_OF_YEAR                   ,
FSCL_DAY_OF_MONTH             ,
FSCL_DAY_OF_WEEK              ,
FSCL_DAY_OF_YEAR              ,
FSCL_HALF                     ,
FSCL_MONTH                    ,
FSCL_QTR                      ,
FSCL_TRIMESTER                ,
FSCL_WEEK                     ,
FSCL_YEAR                     ,
FSCL_FST_DAY_KEY              ,
HALF_AGO_DT                   ,
HALF_AGO_KEY                  ,
HALF_AGO_WID                  ,
INTEGRATION_ID                ,
JULIAN_DAY_NUM                ,
JULIAN_MONTH_NUM              ,
JULIAN_QTR_NUM                ,
JULIAN_TER_NUM                ,
JULIAN_WEEK_NUM               ,
JULIAN_YEAR_NUM               ,
MONTH_AGO_DT                  ,
MONTH_AGO_KEY                 ,
MONTH_AGO_WID                 ,
MONTH_NAME                    ,
PERIOD_KEY                    ,
PER_NAME_FSCL_HALF            ,
PER_NAME_FSCL_MNTH            ,
PER_NAME_FSCL_QTR             ,
PER_NAME_FSCL_TER             ,
PER_NAME_FSCL_WEEK            ,
PER_NAME_FSCL_YEAR            ,
PER_NAME_HALF                 ,
PER_NAME_MONTH                ,
PER_NAME_QTR                  ,
PER_NAME_TER                  ,
PER_NAME_WEEK                 ,
PER_NAME_YEAR                 ,
QUARTER_AGO_DT                ,
QUARTER_AGO_KEY               ,
QUARTER_AGO_WID               ,
TRIMESTER_AGO_DT              ,
TRIMESTER_AGO_KEY             ,
TRIMESTER_AGO_WID             ,
WEEK_AGO_DT                   ,
WEEK_AGO_KEY                  ,
WEEK_AGO_WID                  ,
YEAR_AGO_DT                   ,
YEAR_AGO_KEY                  ,
YEAR_AGO_WID                  ,
M_END_CAL_DT_WID              ,
M_STRT_CAL_DT_WID             ,
CAL_WEEK_END_DT_WID           ,
CAL_WEEK_START_DT_WID         ,
CAL_QTR_END_DT_WID            ,
CAL_QTR_START_DT_WID          ,
CAL_YEAR_END_DT_WID           ,
CAL_YEAR_START_DT_WID         ,
FST_DAY_CAL_WK_FLG            ,
LAST_DAY_CAL_WK_FLG           ,
FST_DAY_CAL_MNTH_FLG          ,
LAST_DAY_CAL_MNTH_FLG         ,
FST_DAY_CAL_QTR_FLG           ,
LAST_DAY_CAL_QTR_FLG          ,
FST_DAY_CAL_YEAR_FLG          ,
LAST_DAY_CAL_YEAR_FLG         ,
FSCL_WEEK_START_DT            ,
FSCL_WEEK_END_DT              ,
FSCL_MNTH_START_DT            ,
FSCL_MNTH_END_DT              ,
FSCL_QTR_START_DT             ,
FSCL_QTR_END_DT               ,
FSCL_YEAR_START_DT            ,
FSCL_YEAR_END_DT              ,
FSCL_WEEK_START_DT_WID        ,
FSCL_WEEK_END_DT_WID          ,
FSCL_MONTH_START_DT_WID       ,
FSCL_MONTH_END_DT_WID         ,
FSCL_QTR_START_DT_WID         ,
FSCL_QTR_END_DT_WID           ,
FSCL_YEAR_START_DT_WID        ,
FSCL_YEAR_END_DT_WID          ,
FSCL_DIM_QTR_NUM              ,
FSCL_DIM_MONTH_NUM            ,
FSCL_MONTH_WEEK_NUM           ,
FSCL_DIM_WEEK_NUM             ,
FSCL_DIM_YEAR_NUM             ,
W_CURRENT_CAL_DAY_CODE        ,
W_CURRENT_CAL_WEEK_CODE       ,
W_CURRENT_CAL_MONTH_CODE      ,
W_CURRENT_CAL_QTR_CODE        ,
W_CURRENT_CAL_YEAR_CODE       ,
W_CURRENT_FSCL_WEEK_CODE      ,
W_CURRENT_FSCL_MONTH_CODE     ,
W_CURRENT_FSCL_QTR_CODE       ,
W_CURRENT_FSCL_YEAR_CODE      ,
FST_DAY_FSCL_WEEK_FLG         ,
LAST_DAY_FSCL_WEEK_FLG        ,
FST_DAY_FSCL_MNTH_FLG         ,
LAST_DAY_FSCL_MNTH_FLG        ,
FST_DAY_FSCL_QTR_FLG          ,
LAST_DAY_FSCL_QTR_FLG         ,
FST_DAY_FSCL_YEAR_FLG         ,
LAST_DAY_FSCL_YEAR_FLG        ,
W_INSERT_DT                   ,
W_UPDATE_DT                   ,
TENANT_ID                     ,
X_CUSTOM                      ) VALUES
(
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMMDD'))   ,
V_ST_DT ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>6 THEN 2 ELSE 1 END ,
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM')) ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=3 THEN 1 WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>3 AND
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=6 THEN 2 WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>6 AND
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=9 THEN 3 ELSE 4 END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=4 THEN 1 WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=8 AND
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>4 THEN 2 ELSE 3 END ,
TO_NUMBER(TO_CHAR(V_ST_DT, 'WW')) ,
TO_NUMBER(TO_CHAR(V_ST_DT, 'YYYY')) ,
0 ,
TO_NUMBER(TO_CHAR(V_ST_DT,'J')) ,
V_ST_DT-1 ,
TO_NUMBER(TO_CHAR(V_ST_DT-1,'J')),
TO_NUMBER(TO_CHAR(V_ST_DT-1,'YYYYMMDD')),
V_ST_DT ,
TO_CHAR(V_ST_DT,'DAY'),
TO_NUMBER(TO_CHAR(V_ST_DT,'DD')),
TO_NUMBER(TO_CHAR(V_ST_DT,'D')),
TO_NUMBER(TO_CHAR(V_ST_DT,'DDD')),
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMMDD')) ,
TO_NUMBER(TO_CHAR(V_ST_DT,'J')),
((TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))+ 4713) * 12)  + TO_NUMBER(TO_CHAR(V_ST_DT,'MM')),
((TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'YYYY'))+ 4713) * 4)  +
CASE WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=3
THEN 1 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))>3
AND TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=6
THEN 2 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))>6
AND TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=9 THEN 3 ELSE 4 END ,
((TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'YYYY'))+ 4713) * 3)  +
CASE WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=4
THEN 1 WHEN TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))>4
AND TO_NUMBER(TO_CHAR(TO_DATE(TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM')),'YYYYMM'),'MM'))<=8
THEN 2 ELSE 3 END ,
Null,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))+ 4713 ,
ADD_MONTHS(V_ST_DT,-1) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-1),'J')) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-1),'YYYYMMDD')) ,
TO_CHAR(V_ST_DT,'MONTH'),
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMMDD')),
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
NULL ,
TO_CHAR(V_ST_DT,'YYYY')||' HALF'||TO_CHAR(CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM')) <6 THEN 1 ELSE 2
END),
TO_CHAR(V_ST_DT, 'YYYY / MM'),
TO_CHAR(V_ST_DT,'YYYY')||' Q '||TO_CHAR(V_ST_DT,  'Q'),
TO_CHAR(V_ST_DT,'YYYY')|| 'T' ||TO_CHAR(CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=4 THEN 1 WHEN
TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))<=8 AND TO_NUMBER(TO_CHAR(V_ST_DT, 'MM'))>4 THEN 2 ELSE 3 END) ,
TO_CHAR(V_ST_DT,'YYYY')|| ' WEEK' ||  TO_NUMBER(TO_CHAR(V_ST_DT, 'WW')),
TO_NUMBER(TO_CHAR(V_ST_DT, 'YYYY')),
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=3 THEN ADD_MONTHS(V_ST_DT,-3) WHEN
TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))>3 AND TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=6 THEN ADD_MONTHS(V_ST_DT,-3) WHEN
TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))>6 AND TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=9 THEN  ADD_MONTHS(V_ST_DT,-3) ELSE
ADD_MONTHS(V_ST_DT,-3)  END ,
NULL ,
NULL ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=4 THEN ADD_MONTHS(V_ST_DT,-4) WHEN
TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))>4 AND TO_NUMBER(TO_CHAR(V_ST_DT,'Q'))<=8 THEN ADD_MONTHS(V_ST_DT,-4)  
ELSE ADD_MONTHS(V_ST_DT,-4)  END ,
NULL ,
NULL ,
V_ST_DT-7 ,
TO_NUMBER(TO_CHAR(V_ST_DT-7,'J')) ,
TO_NUMBER(TO_CHAR(V_ST_DT-7,'YYYYMMDD')) ,
ADD_MONTHS(V_ST_DT,-12) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-12),'J')) ,
TO_NUMBER(TO_CHAR(ADD_MONTHS(V_ST_DT,-12),'YYYYMMDD')) ,
TO_NUMBER(TO_CHAR(LAST_DAY(V_ST_DT),'YYYYMMDD')),
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYYMM'))||'01' ,
NULL ,
NULL ,
NULL ,
NULL ,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))||'1231' ,
TO_NUMBER(TO_CHAR(V_ST_DT,'YYYY'))||'0101'  ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'D')) = 1 THEN 'Y'ELSE 'N'  END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'D')) = 7 THEN 'Y'ELSE 'N'  END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD')) = 1 THEN 'Y' ELSE 'N' END,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD')) = TO_NUMBER(TO_CHAR(LAST_DAY(V_ST_DT),'DD')) THEN 'Y' ELSE 'N'
END,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD'))=1 THEN CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=1 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=4 OR TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=7 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=10 THEN 'Y'ELSE 'N' END ELSE 'N' END  ,
CASE WHEN CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DD')) = TO_NUMBER(TO_CHAR(LAST_DAY(V_ST_DT),'DD')) THEN 'Y'
ELSE 'N' END  = 'Y' THEN CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=3 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=6 OR TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=9 OR
TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=12 THEN 'Y' ELSE 'N' END ELSE 'N' END,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'DDD'))=1 THEN 'Y' ELSE 'N' END ,
CASE WHEN TO_NUMBER(TO_CHAR(V_ST_DT,'MM'))=12 AND TO_NUMBER(TO_CHAR(V_ST_DT,'DD'))=31 THEN 'Y' ELSE 'N'
END ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
NULL  ,
SYSDATE              ,
SYSDATE              ,
NULL                   ,
NULL       )                ;
V_ST_DT := V_ST_DT + 1;
END LOOP;

UPDATE W_DAY_D A
SET
QUARTER_AGO_KEY = (SELECT TO_NUMBER(TO_CHAR(QUARTER_AGO_DT,'J')) FROM W_DAY_D A1 WHERE A.DATE_KEY = A1.DATE_KEY),
QUARTER_AGO_WID = (SELECT TO_NUMBER(TO_CHAR(QUARTER_AGO_DT,'YYYYMMDD')) FROM W_DAY_D B1 WHERE A.DATE_KEY = B1.DATE_KEY),
TRIMESTER_AGO_KEY =(SELECT TO_NUMBER(TO_CHAR(TRIMESTER_AGO_DT,'J')) FROM W_DAY_D C1 WHERE A.DATE_KEY = C1.DATE_KEY),
TRIMESTER_AGO_WID =(SELECT TO_NUMBER(TO_CHAR(TRIMESTER_AGO_DT,'YYYYMMDD')) FROM W_DAY_D D1 WHERE A.DATE_KEY = D1.DATE_KEY),
CAL_QTR_START_DT_WID = 10000*CAL_YEAR+100*3*CAL_QTR+1,
CAL_QTR_END_DT_WID = CASE  WHEN CAL_QTR = 1 OR CAL_QTR = 4 THEN 10000*CAL_YEAR+100*3*CAL_QTR+31 ELSE 10000*CAL_YEAR+100*3*CAL_QTR+30 END;
COMMIT;
Exception
 when others then
  dbms_output.put_line ('Exception ' || sqlerrm);
END  W_DAY_D_PROC;
END  W_DAY_D_DATASEED;

/
sho err

OBIEE 11g How to Resolve Many to Many Relationship - Solutions

It is common to want to model a Many-to-Many relationship between Dimensions and Facts.  For example, it may be necessary to see all employees associated with an opportunity, not just the primary.  This blog presents a series of tools and techniques that may be applied to solve a particular case. This is just an attempt to share this valuable information which has been written by one of my colleague while working on customer site.

Technique #1: Select a Primary

Although not a technical solution, the best way to solve the M:M problem is to eliminate it.  By selecting one of the many dimensional records that are associated with a fact, the entire problem can be avoided.  In the Siebel OLTP, Primaries are used throughout the model, which are carried over and used in the Analytics model.  If it is at all possible to identify a primary, and the use of the primary is acceptable to the user community, then it is recommended to use this technique.

Technique #2: Direct Modeling into the Dimension

A straightforward technique where the table that serves as the intersection table is modeled into a lower level in the Dimension.  The specifics of this technique are similar to those outlined in Solution B of the No direct physical link between a base Dimension and a Fact table section above.

Note that over-counting will occur when performing the many-to-many join.

Technique #3a: Use of a Bridge Table

Instead of modeling the relationship table into a new lower level in the dimension as in Technique #2, the relationship table can become a separate logical table that servers as the Bridge between the dimension and the facts.  Create a new Logical table with the M:M relationship table as the source, mark the logical table as a Bridge table, and adjust the Business model to show the relationship of Facts:Bridge as 1:M and Bridge:Dimension as M:1.  The indication that the Logical Table is a Bridge table is merely an indicator to Analytics that the table is not a Fact table, which it assumes to be any lowest-level table in the data model.

Note that over-counting will occur when performing the many-to-many join

Technique #3b: Use a Weighted Bridge Table

Similar to Technique #3a, this technique is the classic Kimball approach, where the Bridge table employs weighting factors to prorate a total value over multiple records.  For example, if there is one Opportunity worth $1,000,000 and there are two Employees associated with it, the bridge table might contain a record for each with a weighting factor of 0.5.  In this way, each employee will be associated with 0.5 of the whole amount of $1,000,000, or $500,000.  If it is determined that Employee A should receive 75% of the credit, then the weighting factors would be stored as 0.75 and 0.25, which would give Employee A 75 of the total or $750,000.

It is important to note that the weighting factors must all add up to 1 (One), as they are effectively percentages of a whole.  Additional ETL effort will be required to complete this solution.

This technique eliminates over-counting, but may be difficult to implement if users are not comfortable prorating a value over several records.

Technique #4: Use Level Based Measures

As an enhancement to Techniques 2 and 3, the use of level based measures can help prevent the over counting problem associated with each.  When a metric or measure is explicitly bound to a specific level in a dimension, it is indicating that the metric will be viewed at that level. If the metrics in a fact table are to be viewed by a Dimension with which it has a M:M relationship, those metrics can be set to a level in the dimension, thereby forcing that the records be broken out across that dimension.  By forcing a breakout of rows (one fact row for each dimensional row), aggregation is prevented, and therefore over counting will not occur.

As an example, suppose there is a M:M between Employee and Fact_Opty_Revenue.  The data in the tables indicate that Tom, Larry and Bill are all linked to an Opportunity worth $9 million.  The user makes a report that asks for the Opportunity Type and the total Potential Opportunity Revenue.  Without level setting the metrics on the fact table, a report that does not include the employee dimension will overcount, as each of the three dim records will be brought into the query and aggregated into one:

Opportunity Type	Potential Opportunity Revenue
Software Sales	$27,000,000

By level setting the Revenue metrics to the Employee level in the Employee Dimension, this same report will return the following:

Opportunity Type

Potential Opportunity Revenue

Software Sales	$9,000,000
Software Sales	$9,000,000
Software Sales	$9,000,000

Although not intuitively obvious as to the cause of the breakout to the end user, the over counting scenario is prevented.  When the user adds the Employee to the report, the breakout becomes clearer:

Opportunity Type	Employee	Potential Opportunity Revenue
Software Sales	Larry	$9,000,000
Software Sales	Tom	$9,000,000
Software Sales	Bill	$9,000,000

Technique #5: Lower the Fact Table

The most complicated and involved solution is to lower the level of the fact table, and create a 1:M between the Dimensions and the Facts.  This involves a business rule to split up the metrics and spread them over all possible dimensional records.  In the example above, the simplest spread would be to assign Larry, Tom and Bill each 1/3 of the total amount of $9,000,000, or $3,000,000.  Thus, a report that does not break out by Employee will still total to the correct $9,000,000.  Note that this would require three records in the fact table instead of one, hence the concept of lowering the level of detail in the fact.

I will leave final call to choose an appropriate technique on designer or developer. The selection should be based on customer requirement as I understand technology design is to support the customer aspiration.