Jagadeesh Pithani

• Mar 31st, 2006

 

Usage of  Procedure(Oracle)  is common to Populate Time Dim.

U may have diffrent Hier.chies but still for ecah level accordingly u need to use some date or time format.

Becareful while populating time data in dim table... Ofcource Procedure will take care of everything.....(Taking on input data and output date is important in Procedure)

From

Jagadeesh

Rohith Yogendra

 Profile Answers by Rohith Yogendra 

• Apr 20th, 2007

 

Hi this would be the normal SP Code that i use to populate a time dimension,

well again it depends on the business requirements to know what level the Grain is required for a time dimension

CREATE OR REPLACE  PROCEDURE TIMEDIMBUILD 
 (p_start_date IN DATE,
 p_end_date IN DATE)
AS
 v_full_date DATE;
 v_day_of_month NUMBER;
 v_day_of_year NUMBER;
 v_day_full_name VARCHAR2(30);
 v_week_number  NUMBER;
 v_week_full_name VARCHAR2(30);
 v_month_full_name VARCHAR2(10);
 v_month_number NUMBER;
 v_calendar_year  NUMBER;
 v_quarter NUMBER;
 v_key NUMBER;
BEGIN
 DELETE FROM TimeDim;
 v_full_date := p_start_date;
 v_key:=1;
   WHILE v_full_date < p_end_date LOOP
   BEGIN
 v_day_of_month := TO_CHAR(p_start_date,'DD');
 v_day_of_year := TO_CHAR( p_start_date, 'DDD');
 v_day_full_name := UPPER(TO_CHAR(p_start_date,'DAY'));
 v_week_number := TO_CHAR(p_start_date,'WW');
 v_month_full_name := UPPER(TO_CHAR(p_start_date,'MONTH'));
 v_month_number := TO_CHAR(p_start_date,'MM');
 v_calendar_year := TO_CHAR(p_start_date, 'YYYY');
 v_quarter := TO_CHAR(p_start_date,'Q');
 INSERT INTO TimeDim
  (TimeKey,FullDateCode, DayOfMonth, DayOfYear
  ,DayFullName, WeekNumber, MonthFullName
  ,MonthNumber, Quarter, CalendarYear )
 VALUES
  (v_key,v_full_date, v_day_of_month, v_day_of_year
  ,v_day_full_name, v_week_number, v_month_full_name
  ,v_month_number, v_quarter, v_calendar_year );
     v_full_date :=v_full_date+1;
 v_key:=v_key+1;
    END;
    END LOOP;
END;

Rohith Yogendra

 Profile Answers by Rohith Yogendra 

• Apr 20th, 2007

 

CREATE OR REPLACE  PROCEDURE TIMEDIMBUILD 
 (p_start_date IN DATE,
 p_end_date IN DATE)
AS
 v_full_date DATE;
 v_day_of_month NUMBER;
 v_day_of_year NUMBER;
 v_day_full_name VARCHAR2(30);
 v_week_number  NUMBER;
 v_week_full_name VARCHAR2(30);
 v_month_full_name VARCHAR2(10);
 v_month_number NUMBER;
 v_calendar_year  NUMBER;
 v_quarter NUMBER;
 v_key NUMBER;
BEGIN
 DELETE FROM TimeDim;
 v_full_date := p_start_date;
 v_key:=1;
   WHILE v_full_date < p_end_date LOOP
   BEGIN
 v_day_of_month := TO_CHAR(p_start_date,'DD');
 v_day_of_year := TO_CHAR( p_start_date, 'DDD');
 v_day_full_name := UPPER(TO_CHAR(p_start_date,'DAY'));
 v_week_number := TO_CHAR(p_start_date,'WW');
 v_month_full_name := UPPER(TO_CHAR(p_start_date,'MONTH'));
 v_month_number := TO_CHAR(p_start_date,'MM');
 v_calendar_year := TO_CHAR(p_start_date, 'YYYY');
 v_quarter := TO_CHAR(p_start_date,'Q');
 INSERT INTO TimeDim
  (TimeKey,FullDateCode, DayOfMonth, DayOfYear
  ,DayFullName, WeekNumber, MonthFullName
  ,MonthNumber, Quarter, CalendarYear )
 VALUES
  (v_key,v_full_date, v_day_of_month, v_day_of_year
  ,v_day_full_name, v_week_number, v_month_full_name
  ,v_month_number, v_quarter, v_calendar_year );
     v_full_date :=v_full_date+1;
 v_key:=v_key+1;
    END;
    END LOOP;
END;