0.00.0100011NY5000N-2007/06/07 12:04:59.781Prepare24286025Input261224425Transformation2521210425Output273025425DWlocalhostMYSQLNativeDW3306DWEncrypted 2be98afc86aa7f2e4cb79ce10bef28bedEXTRA_OPTION_MYSQL.characterEncodingUTF8EXTRA_OPTION_MYSQL.defaultFetchSize500EXTRA_OPTION_MYSQL.useCursorFetchtrueIS_CLUSTEREDNMAXIMUM_POOL_SIZE10PORT_NUMBER3306SQL_CONNECTset sql_mode := ''STREAM_RESULTSYUSE_POOLINGNDay SequenceCalculate Dimension AttributesYCreate DIM_DATEGenerate 10 yearsYGenerate 10 yearsDay SequenceYCalculate Dimension AttributesSelect valuesYSelect valuesLoad DIM_DATEYCalculate Dimension AttributesScriptValueModY1none0Script 1//Create a Locale according to the specified language code
var locale = new java.util.Locale(
language_code.getString()
, country_code.getString()
);
//Create a calendar, use the specified initial date
var calendar = new java.util.GregorianCalendar(locale);
calendar.setTime(initial_date.getDate());
//set the calendar to the current date by adding DaySequence days
calendar.add(calendar.DAY_OF_MONTH,DaySequence.getInteger() - 1);
//get the calendar date
var date = new java.util.Date(calendar.getTimeInMillis());
//en-us example: 9/3/07
var date_short = java.text.DateFormat.getDateInstance(
java.text.DateFormat.SHORT
, locale
).format(date);
//en-us example: Sep 3, 2007
var date_medium = java.text.DateFormat.getDateInstance(
java.text.DateFormat.MEDIUM
, locale
).format(date);
//en-us example: September 3, 2007
var date_long = java.text.DateFormat.getDateInstance(
java.text.DateFormat.LONG
, locale
).format(date);
//en-us example: Monday, September 3, 2007
var date_full = java.text.DateFormat.getDateInstance(
java.text.DateFormat.FULL
, locale
).format(date);
//day in year: 1..366
var simpleDateFormat = java.text.SimpleDateFormat("D",locale);
var day_in_year = simpleDateFormat.format(date);
//day in month: 1..31
simpleDateFormat.applyPattern("d");
var day_in_month = simpleDateFormat.format(date);
//en-us example: "Monday"
simpleDateFormat.applyPattern("EEEE");
var day_name = simpleDateFormat.format(date);
//en-us example: "Mon"
simpleDateFormat.applyPattern("E");
var day_abbreviation = simpleDateFormat.format(date);
//week in year, 1..53
simpleDateFormat.applyPattern("ww");
var week_in_year = simpleDateFormat.format(date);
//week in month, 1..5
simpleDateFormat.applyPattern("W");
var week_in_month = simpleDateFormat.format(date);
//month number in year, 1..12
simpleDateFormat.applyPattern("MM");
var month_number = simpleDateFormat.format(date);
//en-us example: "September"
simpleDateFormat.applyPattern("MMMM");
var month_name = simpleDateFormat.format(date);
//en-us example: "Sep"
simpleDateFormat.applyPattern("MMM");
var month_abbreviation = simpleDateFormat.format(date);
//2 digit representation of the year, example: "07" for 2007
simpleDateFormat.applyPattern("y");
var year2 = simpleDateFormat.format(date);
//4 digit representation of the year, example: 2007
simpleDateFormat.applyPattern("yyyy");
var year4 = simpleDateFormat.format(date);
//handling Quarters is a DIY
var quarter_name = "Q";
var quarter_number;
switch(parseInt(month_number)){
case 1: case 2: case 3: quarter_number = "1"; break;
case 4: case 5: case 6: quarter_number = "2"; break;
case 7: case 8: case 9: quarter_number = "3"; break;
case 10: case 11: case 12: quarter_number = "4"; break;
}
quarter_name += quarter_number;
//get the local yes/no values
var yes = local_yes.getString();
var no = local_no.getString();
//initialize for week calculations
var first_day_of_week = calendar.getFirstDayOfWeek();
var day_of_week = java.util.Calendar.DAY_OF_WEEK;
//find out if this is the first day of the week
var is_first_day_in_week;
if(first_day_of_week==calendar.get(day_of_week)){
is_first_day_in_week = yes;
} else {
is_first_day_in_week = no;
}
//calculate the next day
calendar.add(calendar.DAY_OF_MONTH,1);
//get the next calendar date
var next_day = new java.util.Date(calendar.getTimeInMillis());
//find out if this is the first day of the week
var is_last_day_in_week;
if(first_day_of_week==calendar.get(day_of_week)){
is_last_day_in_week = yes;
} else {
is_last_day_in_week = no;
}
//find out if this is the first day of the month
var is_first_day_of_month;
if(day_in_month == 1){
is_first_day_in_month = yes;
} else {
is_first_day_in_month = no;
}
//find out if this is the last day in the month
var is_last_day_of_month;
if(java.text.SimpleDateFormat("d",locale).format(next_day)==1){
is_last_day_in_month = yes;
} else {
is_last_day_in_month = no;
}
//date = year4 + "-" + month_number + "-" + day_in_month
var year_quarter = year4 + "-" + quarter_name;
var year_month_number = year4 + "-" + month_number;
var year_month_abbreviation = year4 + "-" + month_abbreviation;
datedateDate-1-1date_shortdate_shortString-1-1date_mediumdate_mediumString-1-1date_longdate_longString-1-1date_fulldate_fullString-1-1day_in_yearday_in_yearString-1-1day_nameday_nameString-1-1day_abbreviationday_abbreviationString-1-1month_abbreviationmonth_abbreviationString-1-1month_namemonth_nameString-1-1quarter_namequarter_nameString-1-1year_quarteryear_quarterString-1-1year_month_abbreviationyear_month_abbreviationString-1-1is_last_day_in_monthis_last_day_in_monthString-1-1is_first_day_in_monthis_first_day_in_monthString-1-1year2year2String-1-1year4year4String-1-1day_in_monthday_in_monthString-1-1week_in_yearweek_in_yearString-1-1week_in_monthweek_in_monthString-1-1month_numbermonth_numberString-1-1quarter_numberquarter_numberString-1-1year_month_numberyear_month_numberString-1-1is_last_day_in_weekis_last_day_in_weekString-1-1is_first_day_in_weekis_first_day_in_weekString-1-1336216YCreate DIM_DATEExecSQLY1noneDWNDROP TABLE IF EXISTS dim_date
;
CREATE TABLE IF NOT EXISTS dim_date (
date_key smallint unsigned NOT NULL,
date date NOT NULL,
date_short char(12) NOT NULL,
date_medium char(16) NOT NULL,
date_long char(24) NOT NULL,
date_full char(32) NOT NULL,
day_in_year smallint unsigned NOT NULL,
day_in_month tinyint unsigned NOT NULL,
is_first_day_in_month char(10) NOT NULL,
is_last_day_in_month char(10) NOT NULL,
day_abbreviation char(3) NOT NULL,
day_name char(12) NOT NULL,
week_in_year tinyint unsigned NOT NULL,
week_in_month tinyint unsigned NOT NULL,
is_first_day_in_week char(10) NOT NULL,
is_last_day_in_week char(10) NOT NULL,
month_number tinyint unsigned NOT NULL,
month_abbreviation char(3) NOT NULL,
month_name char(12) NOT NULL,
year2 char(2) NOT NULL,
year4 year NOT NULL,
quarter_name char(2) NOT NULL,
quarter_number tinyint NOT NULL,
year_quarter char(7) NOT NULL,
year_month_number char(7) NOT NULL,
year_month_abbreviation char(8) NOT NULL,
PRIMARY KEY(date_key),
UNIQUE(date)
)
ENGINE=MyISAM
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_unicode_ci18132YDay SequenceSequenceN1noneDaySequenceNDWSEQ_Y119999999181216YGenerate 10 yearsRowGeneratorN1nonelanguage_codeStringen-1-1country_codeStringuk-1-1initial_dateDateyyyy-MM-dd2005-01-01-1-1local_yesStringyes-1-1local_noStringno-1-13660180123YLoad DIM_DATETableOutputY1noneDW