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.

Oracle Business Intelligence Application 7.9.6.3 and Google Views – Real Value Addition – Heat Map

Google charts would be very useful to enhance the Oracle BI Application deliverables. The Google Visualization API allows developer to create charts and reporting applications over structured data and helps integrate these directly into your website or on a Gadget. You can find more information on Google chart by using following link

https://developers.google.com/chart/

One of the important charts which would be useful for BI Apps Spend and Procurement to demonstrate analysis is ‘Heat Map’; it is a perfect representation of using size (Invoice Amount) and color ( Invoice Count) variations. This blog is an attempt to demonstrate how Google Organization charts can be embedded into an OBIEE analysis.

Report

Narrative View

Prefix	<script type="text/javascript" src="https://www.google.com/jsapi"></script> <script type="text/javascript"> if(document.URL.indexOf("saw.dll?Answers") == -1) {       google.load("visualization", "1", {packages:["treemap"]});       google.setOnLoadCallback(drawHMChart); }       function drawHMChart() { if(document.URL.indexOf("saw.dll?Answers") == -1) {           // Create and populate the data table.           var data = new google.visualization.DataTable();           data.addColumn('string', 'Quarter');           data.addColumn('string', 'Parent');           data.addColumn('number', 'InvoiceAmt');           data.addColumn('number', 'InvoiceNum');           myHMArray=[];           myHMArray.push(["All Quarter",null,0,0]); }
Narrative	myHMArray.push(["@1","All Quarter",@2,@3]);
Row separator	Nil
Rows to display	Nil
Postfix	if(document.URL.indexOf("saw.dll?Answers") == -1) {                   data.addRows(myHMArray);           // Create and draw the visualization.           var tree = new google.visualization.TreeMap(document.getElementById('div_Heatmap'));           tree.draw(data, {             minColor: '#f00',             midColor: '#ddd',             maxColor: '#0d0',             headerHeight: 15,             fontColor: 'black',             showScale: true});       }    } </script> <b>Note: Invoice Amount represented by size and Invoice Count  represented by color</b>     <div id="div_Heatmap" style="width: 700px; height: 350px;"></div>

Narrative View

Heat Map

Oracle Business Intelligence Application 7.9.6.3 and Google Views – Real Value Addition - HR Organization Chart

Google charts would be very useful to enhance the Oracle BI Application deliverables. The Google Visualization API allows developer to create charts and reporting applications over structured data and helps integrate these directly into your website or on a Gadget. You can find more information on Google chart by using following link

https://developers.google.com/chart/

One of the important charts which would be useful for BI Apps HR Profile to demonstrate employee -supervisor relation is ‘organization chart’.  This blog is an attempt to demonstrate how Google Organization charts can be embedded into an OBIEE analysis.

Report

Contruct a report - Supervisor , Employee Attribute and Employee Organisation from workforce profile.

Narative View

Google API’s -  Prefix , Narrative and Postfix to generate the graph

Prefix	<script type='text/javascript' src='https://www.google.com/jsapi'></script>     <script type='text/javascript'> if(document.URL.indexOf("saw.dll?Answers") == -1) {       google.load('visualization', '1', {packages:['orgchart']});       google.setOnLoadCallback(drawOrgChart); }       function drawOrgChart() { if(document.URL.indexOf("saw.dll?Answers") == -1) {        var data = new google.visualization.DataTable();         data.addColumn('string', 'Name');         data.addColumn('string', 'Manager');         data.addColumn('string', 'ToolTip');         var myOrgArray=[];         var tempStr = "";
Narrative	myOrgArray.push([{v:"@2".replace("  "," "), f:"@2<br/><font color=red><i>@3<i></font>".replace("  "," ")}, "@1".replace("--",""), "@3"]);
Row separator	Nil
Rows to display	Nil
Postfix	data.addRows(myOrgArray);         var chart = new google.visualization.OrgChart(document.getElementById('div_orgchart'));         chart.draw(data, {allowHtml:true}); }       }     </script>     <div id='div_orgchart'></div>

Narrative View

Organization Chart View

Org Chart - User would love this ..

Oracle BI 11g – Port Numbers and staticports.ini

Which default ports OBIEE 11g standard installation uses ? What is the option if one would like to change the default ports ?

This blog is an attempt to list down default port and along with the description about how to other ports can be assigned than the defaults.

Default Port Numbers

By default Oracle BI 11g uses the following default ports for the communication between clients

WebLogic Server

Domain Port No = 7001

Oracle WLS BIEE Managed Server Port No = 9704

Oracle WLS BIEE Managed Server SSL Port No = 9804

OPMN Process Control

Oracle Process Manager Local Port No = 6100

Oracle Process Manager Remote Port No = 6200

Oracle Process Manager Request Port No = 6003

BI 11g BI-Foundation

Oracle BI Server Port No = 9703

Oracle BI Server Monitor Port No = 9701

Oracle BI Presentation Services Port No = 9710

Oracle BI Scheduler Port No = 9705

Oracle BI Scheduler Monitor Port No = 9708

Oracle BI Scheduler Script RPC Port No = 9707

Oracle BI ClusterController Port No = 9706

Oracle BI ClusterController Monitor Port No = 9700

Oracle BI JavaHost Port No = 9810

Defining other ports than the defaults

During the installation other ports can be assigned than the defaults. To do that a file “staticports.ini” must be used. You need to supply this in the following format:

staticports.ini

01

[WEBLOGIC]

02

03

#The Domain port no. This is the listen port of Weblogic Adminserver for the domain.

04

Domain Port No = 7001

05

06

# The "content" port for the BIEE apps. This is the Weblogic Managed Server port on which BIEE applications are deployed.

07

Oracle WLS BIEE Managed Server Port No = 9704

08

09

#The SSL port for the Weblogic Managed Server

10

Oracle WLS BIEE Managed Server SSL Port No = 9804

11

12

[OPMN]

13

14 #Process Manager Local port no

15

Oracle Process Manager Local Port No = 6700

16

17

#Process Manager Remote port no

18

Oracle Process Manager Remote Port No = 6701

19

20

#Process Manager Request port no

21

Oracle Process Manager Request Port No = 6702

22

23

[BIFOUNDATION]

24

25

#The listen port for OracleBIServer component

26

Oracle BI Server Port No = 9703

27

28

#The monitor port for OracleBIServer component

29

Oracle BI Server Monitor Port No = 9701

30

31

#The listen port for OracleBIPresentationServices component

32

Oracle BI Presentation Services Port No = 9710

33

34

#The listen port for OracleBIScheduler component

35

Oracle BI Scheduler Port No = 9705

36

37

#The monitor port for OracleBIScheduler component

38

Oracle BI Scheduler Monitor Port No = 9708

39

40

#The script RPC port for OracleBIScheduler component

41 Oracle BI Scheduler Script RPC Port No = 9707

42

43

# The listen port for OracleBIClusterController component

44 Oracle BI ClusterController Port No = 9706

45

46

# The monitor port for OracleBIClusterController component

47 Oracle BI ClusterController Monitor Port No = 9700

48

49

# The listen port for OracleBIJavaHost component

50 Oracle BI JavaHost Port No = 9810