OBIEE11g - Custom BI Time Dimension Populate Database Script.

1) Create database function to populate the time table in the database.

CREATE OR REPLACE PROCEDURE update_day_dim (start_date IN DATE, no_of_days IN NUMBER,Delete_data IN varchar2)
AS
   mcount   NUMBER;
   mdate    DATE;
BEGIN
if delete_data = 'Y' then
   delete from xx_bi_time_day_d;
   commit;
end if;
   BEGIN
      mcount := 1;
      mdate := start_date;

      FOR mcount IN 1 .. no_of_days
      LOOP
   
         INSERT INTO xx_bi_time_day_d
              VALUES (TO_CHAR (mdate, 'MM/DD/YYYY'), mdate
                    , TO_CHAR (mdate, 'DD'), TO_CHAR (mdate, 'Day')
                    , TO_CHAR (mdate, 'MM-YYYY'), TO_CHAR (mdate, 'MM')
                    , TO_CHAR (mdate, 'W'), 'W' || TO_CHAR (mdate, 'W')
                    , TO_CHAR (mdate, 'YYYY'), TO_CHAR (mdate, 'Q')
                    , 'Q' || TO_CHAR (mdate, 'Q'), TO_CHAR (mdate, 'Month')
                    , TO_CHAR (mdate, 'MM')
                    , DECODE (TO_CHAR (mdate, 'Q')
                            , '1', 'H1'
                            , '2', 'H1'
                            , 'H2'
                             )
                    , DECODE (TO_CHAR (mdate, 'Q'), '1', '1', '2', '1', '2'));

         mdate := mdate + 1;
      END LOOP;

commit;

   END;
END;*/


2) Create below table in the data base.

create table xx_bi_time_day_d
(current_date  varchar2(15),
current_date_date  date,
day_num   number(15),
day_name   varchar2(25),
period_name  varchar2(15),
period_num   number(15),
week_num     number(15),
week_name      varchar2(15),
period_year varchar2(4),
quater_num    number(15),
quater_name    varchar2(3),
month_name   varchar2(25),
month_num  number(15),
half_year_name varchar2(15),
half_year_num number(15)
);

3) To Populate the data in the time table, Execute the below function.

execute update_day_dim('01-JAN-1980',50000,'Y');