2 Replies Latest reply: Apr 12, 2017 1:56 PM by moez ghattas

# time dimension/table script

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 ?

• ###### Re: time dimension/table script

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:

A,

B,...

Timestamp

from

source;

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

Fact:

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:

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:

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

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

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

MasterCalendar:

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:

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

D_Time_TMP2:

Resident D_Time_TMP;

D_Time:

,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;

• ###### Re: time dimension/table script

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 ) ...