OBIEE - Script for creating Time Dimension

A basic plsql script for creating time dimension table and populating it with data for years together.

CREATE TABLE mh_time_periods

(

time_period_id NUMBER NOT NULL

,YEAR NUMBER(4) NOT NULL

,quarter NUMBER(1) NOT NULL

,MONTH NUMBER(2) NOT NULL

,DAY NUMBER(2) NOT NULL

,month_end_flag char(1) DEFAULT 'N'

,year_end_flag char(1) default 'N'

,quarter_end_flag CHAR(1) DEFAULT 'N'

,period_date DATE NOT NULL

,CONSTRAINT mh_time_periods_pk PRIMARY KEY(time_period_id)

,CONSTRAINT mh_time_periods_chk1 CHECK(month_end_flag IN ('N','Y'))

,constraint mh_time_periods_chk2 check(year_end_flag in ('N','Y'))

,CONSTRAINT mh_time_periods_chk3 CHECK(quarter_end_flag IN ('N','Y'))

);

/

CREATE SEQUENCE mh_time_period_seq START WITH 1 INCREMENT BY 1;

/

Currently the time dimension table has basic settings. More columns can be added as and when required.

CREATE OR REPLACE PROCEDURE mh_fill_time_data(

p_end_date DATE

)

IS

l_year NUMBER(4) := 2005;

l_month NUMBER(2) := 1;

l_day NUMBER(2) := 1;

l_quarter NUMBER(1) := 0;

--l_flag BOOLEAN := FALSE;

l_month_end_flag CHAR(1) := 'N';

l_year_end_flag char(1) := 'N';

l_quarter_end_flag CHAR(1) := 'N';

l_loop_day_end NUMBER(2);

l_end_year NUMBER(4) := TO_NUMBER(TO_CHAR(p_end_date,'yyyy'));

l_end_month NUMBER(2) := TO_NUMBER(TO_CHAR(p_end_date,'mm'));

l_end_day NUMBER(2) := TO_NUMBER(TO_CHAR(p_end_date,'dd'));

BEGIN

SAVEPOINT start_insert;

LOOP

LOOP

IF l_month IN (1,2,3) THEN

l_quarter := 1;

ELSIF l_month IN (4,5,6) THEN

l_quarter := 2;

ELSIF l_month IN (7,8,9) THEN

l_quarter := 3;

ELSE

l_quarter := 4;

END IF;

IF l_month IN (1,3,5,7,8,10,12) THEN

l_loop_day_end := 31;

ELSIF l_month IN (4,6,9,11) THEN

l_loop_day_end := 30;

ELSE

l_loop_day_end := get_feb_day(l_year);

end if;

IF l_year = l_end_year AND l_month = l_end_month THEN

l_loop_day_end := l_end_day;

END IF;

LOOP

IF l_day = l_loop_day_end THEN

l_month_end_flag := 'Y';

IF l_month = 12 THEN

l_year_end_flag := 'Y';

END IF;

end if;

if l_month_end_flag = 'Y' then

if l_month IN (3,6,9,12) then

l_quarter_end_flag := 'Y';

END IF;

END IF;

INSERT INTO mh_time_periods(

time_period_id

,YEAR

,quarter

,MONTH

,DAY

,month_end_flag

,year_end_flag

,quarter_end_flag

,period_date

)

VALUES(

mh_time_period_seq.NEXTVAL

,l_year

,l_quarter

,l_month

,l_day

,l_month_end_flag

,l_year_end_flag

,l_quarter_end_flag

,TO_DATE(TO_CHAR(l_day)||'-'||TO_CHAR(l_month)||'-'||TO_CHAR(l_year),'DD-MM-YYYY')

);

l_day := l_day + 1;

EXIT WHEN l_day > l_loop_day_end;

END LOOP; -- end of day loop

l_quarter_end_flag := 'N';

l_month_end_flag := 'N';

l_day := 1;

l_month := l_month + 1;

EXIT WHEN l_month > 12 OR (l_year = l_end_year AND l_month > l_end_month);

END LOOP; -- End of month loop

l_year_end_flag := 'N';

l_month := 1;

l_year := l_year + 1;

EXIT WHEN l_year > l_end_year;

END LOOP; -- End of year loop

COMMIT;

dbms_output.put_line('Done');

EXCEPTION

WHEN others THEN

dbms_output.put_line(SQLERRM);

ROLLBACK TO start_insert;

END mh_fill_time_data;

/







CREATE OR REPLACE FUNCTION get_feb_day (nYr IN NUMBER) RETURN NUMBER IS

v_day varchar2(2);

begin

select to_char(last_day(to_date( '01-FEB-'|| to_char(nYr), 'DD-MON-YYYY')), 'DD') into v_day from dual;

return to_number(v_day);

END;

/

The above plsql populates from 01-JAN-2005. But can be changed to any starting date.

Note:

The code is open for scrutiny