How to populate Time dimension table ? and what is the best way ?

  I want to populate 1 year Data of Time dimension in Oracle 10g database for our DW project.  Can any one tell me how  to do that.  Below are the fields to be populated

       DimDateTimeID        NUMBER NOT NULL,
       FullDate             DATE NULL,
       BiWeeklyDate         DATE NULL,
       SQLDate              DATE NULL,
       Quarter              NUMBER NULL,
       Month                NUMBER NULL,
       MonthName            VARCHAR2(20) NULL,
       Day                  NUMBER NULL,
       DayName              VARCHAR2(20) NULL,
       Week                 NUMBER NULL,
       HolidayFlag          NUMBER NULL,
       WeekendFlag          NUMBER NULL,
       Hour24               NUMBER NULL,
       Hour12               NUMBER NULL,
       PRIMARY KEY (DimDateTimeID)

Any help would be appreciated


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Something like this should do it:

  sdate date := to_date('2006-01-01','yyyy-mm-dd');
  edate date := to_date('2006-12-31','yyyy-mm-dd');
  idate date;
  idttm date;
  dimID integer;
  for j in to_char(sdate,'j') .. to_char(edate,'j') loop
    idate := to_date(j,'j');
    for i in 0 .. 23 loop
      idttm := idate + i/24;
      select dtDimSeq.next_val into dimID from dual;
      insert into DimDateTime(DimDateTimeID, FullDate, Quarter, Month, MonthName, Day, DayName, Week, HolidayFlag, WeekendFlag, Hour24, Hour12)
      values (dimId, idate, to_char(idate,'q'), to_char(idate,'mm'), trim(to_char(idate,'Month')), to_char(idate,'dd'), to_char(idate,'Day'),
          to_char(idate,'ww'), 'N', decode(to_char(idate,'d'),1,'Y',7','Y','N'), to_char(idttm,'hh24'), to_char(idttm,'hh') );
    end loop;
  end loop;

As you have Hour24 and Hour12, I asumed you wanted 1 record for every hour of the year - hence the "for i 0 .. 23" loop
Not sure what the BiWeeklyDate and SQLDate are supposed to be, so I have left them out.
Assumed a sequence for generating the ID field, but this could just as easily be generated in the script.
The decode of the WeekendFlag, and the setting of the Week field will be dependent on your NLS setting, also you may want to you 'iw' for the week.
HolidayFlag would need to be set separately outside the script.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.