time dimension/table script

[#1]

Hi i'm looking for a script to generate time dimension/table  for the year 2013-2018: year, month, week, day, hour can anyone help me ?

[OmarBenSalem · Accepted solution]

Suppose you have a table in your model containing a timestam field (DD/MM/YYYY hh;mm:ss)

Let's suppose this field is called Timestamp:

Fact:

load

A,

B,...

Timestamp

from

source;

What you do is create a date and time field from the timestamp field:

Fact:

load

A,

B,...

Timestamp,

DATE(Timestamp) as date,

Time(Timestamp) as time

from

source;

With that being added, we create 2 new tables:

1) Master calendar:

Temp: 

Load 

min(date) as minDate, 

max(date) as maxDate 

Resident Fact; 

Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load 

  TempDate AS date, 

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month, 

  Day(TempDate) As Day, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

2) we create a time table:

Let vMinDate=floor(TimeStamp#('00:00:00', 'hh:mm:ss'));

Let vMaxDate=floor(TimeStamp#('23:59:59', 'hh:mm:ss'));

D_Time_TMP: 

LOAD TimeStamp($(vMinDate) + (RecNo()/60/60/24) + (IterNo() -1),'hh:mm:ss') AS AddedTimeStamp

AUTOGENERATE 86400 WHILE Num($(vMinDate) + IterNo() -1) <= Num($(vMaxDate));  

D_Time_TMP2:

LOAD RowNo() as [%Time SEQ]

  ,time([AddedTimeStamp]) as time

  ,Hour([AddedTimeStamp]) as Hour

  ,Minute([AddedTimeStamp]) as Minute

  ,Time(Class(Timestamp#([AddedTimeStamp],'hh:mm:ss'),MakeTime(0,15)),'hh:mm:ss') as QHour

Resident D_Time_TMP;

D_Time:

Load [%Time SEQ]

  ,time

  ,Hour

  ,Minute

  ,QHour

  ,AutoNumber( [QHour],'%Quarter of Hour SEQ ID' )  as [%Quarter of Hour SEQ ID]

Resident D_Time_TMP2;

DROP Table D_Time_TMP,D_Time_TMP2;

[#3]

Thank you ! it's very helpful and very clear!

But Do i have to do that for every timestamp field ?

i have like 12 time stamp fields with this format DD/MM/YYYY hh;mm:ss . (start_time end_time ) ...