Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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;
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;
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 ) ...