Dimension.Date
The below code is used to create a Date dimension.
Process Code
vb
//version 1.2
//author: MODLR
//date: 05.12.2017
var monthsCal = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
var hierarchyDefault = "Default";
var hierarchyAnnual = "Annual Assumptions";
var hierarchyYear = " Financial Year List";
var hierarchyQuarter = "Quarters List";
var hierarchyMonth = "Month List";
var hierarchyWeek = "Week List";
var hierarchyDate = "Date List";
var hierarchyHour = "Hour List";
var hierarchyMonthYTD = "Month YTD";
var hierarchyDateMTD = "Date MTD";
function dimensionCreate(dim) {
if( dimension.exists(dim) == false ) {
dimension.create(dim, "time");
} else {
dimension.wipe(dim);
}
}
function hierarchyCreate(dim, hier) {
if( hierarchy.exists(dim, hier) == false ) {
hierarchy.create(dim, hier);
} else {
hierarchy.wipe(dim, hier);
}
}
function pre() {
//this function is called once before the processes is executed.
//Use this to setup prompts.
script.log('process pre-execution parameters parsed.');
var d = new Date();
var yearFrom = (d.getFullYear()-3)+"";
var yearTo = (d.getFullYear()+1)+"";
script.prompt("Dimension Name","dimName","Time");
script.prompt("Year From","yearFrom",yearFrom);
script.prompt("Year To","yearTo",yearTo);
script.log('process pre-execution parameters parsed.');
}
function begin() {
//this function is called once at the start of the process
script.log('process execution started.');
dimensionCreate(dimName);
var timeFinancialYear = script.variableGet("model.Financial Year").toLowerCase();
var timeMonthBuild = script.variableGet("model.Month Build").toLowerCase();
var timeGranularity = script.variableGet("model.Granularity").toLowerCase();
var monthStart = 0;
if( timeFinancialYear == "jan to dec" ) {
} else if( timeFinancialYear == "feb to jan" ) {
monthStart = 1;
} else if( timeFinancialYear == "mar to feb" ) {
monthStart = 2;
} else if( timeFinancialYear == "apr to mar" ) {
monthStart = 3;
} else if( timeFinancialYear == "may to apr" ) {
monthStart = 4;
} else if( timeFinancialYear == "jun to may" ) {
monthStart = 5;
} else if( timeFinancialYear == "jul to jun" ) {
monthStart = 6;
} else if( timeFinancialYear == "aug to jul" ) {
monthStart = 7;
} else if( timeFinancialYear == "sep to aug" ) {
monthStart = 8;
} else if( timeFinancialYear == "oct to sep" ) {
monthStart = 9;
} else if( timeFinancialYear == "nov to oct" ) {
monthStart = 10;
} else if( timeFinancialYear == "dec to nov" ) {
monthStart = 11;
}
var monthFinish = monthStart;
if( monthFinish > 0 ) {
yearTo=Math.floor(yearTo)+1;
}
hierarchyCreate(dimName,hierarchyDefault);
hierarchyCreate(dimName,hierarchyAnnual);
hierarchyCreate(dimName,hierarchyYear);
if( timeGranularity != "year" ) {
hierarchyCreate(dimName,hierarchyQuarter);
if( timeGranularity != "quarter" ) {
hierarchyCreate(dimName,hierarchyMonth);
if( timeGranularity == "month" ) {
hierarchyCreate(dimName,hierarchyMonthYTD);
}
if( timeGranularity != "month" ) {
hierarchyCreate(dimName,hierarchyWeek);
if( timeGranularity != "week" ) {
hierarchyCreate(dimName,hierarchyDate);
if( timeGranularity != "date" ) {
hierarchyCreate(dimName,hierarchyHour);
if( timeGranularity != "time" ) {
}
}
}
}
}
}
hierarchyCreate(dimName,"No Time");
hierarchy.group(dimName,"No Time", "", "No Time");
var dFrom = new Date(yearFrom, monthStart, 1, 1, 1, 1, 1);
var dTo = new Date(yearTo, monthFinish, 1, 1, 1, 1, 1);
script.log("Time: Generating from " + dFrom + " to " + dTo);
//loop through the last 30 days and go forward 2 days.
var dateArray = getDates( dFrom, dTo);
for (i = 0; i < dateArray.length-1; i ++ ) {
var day = dateArray[i].getDate();
var month = dateArray[i].getMonth();
var year = dateArray[i].getFullYear();
var week = dateArray[i].getWeek();
month = month*100/100 + 1;
if( month <= monthStart ) {
year=Math.floor(year)-1;
}
if( ("" + month).length == 1 )
month = "0" + month;
if( ("" + day).length == 1 )
day = "0" + day;
var monthName = monthsCal[parseInt(month,10)-1];
var quarterName = "Q" + (Math.floor((month-1-monthStart)/3)+1);
var yearStrFy = (year + 1) + "";
var fyYearStr = "FY" + (year + 1) + "";
if( timeFinancialYear == "jan to dec" ) {
yearStrFy = (year) + "";
fyYearStr = "FY" + (year) + "";
}
var monthFullString = year + " - " + monthName;
var quarterFullString = yearStrFy + " - " + quarterName;
var dateStr = day + "-" + month + "-" + year;
var weekStr = year + " - Week " + week ;
var yearStr = year + "";
if( month <= monthStart ) {
monthFullString = (year+1) + " - " + monthName;
dateStr = day + "-" + month + "-" + (year+1);
quarterName = "Q" + (Math.floor((month-1-monthStart)/3)+1+4);
quarterFullString = yearStrFy + " - " + quarterName;
}
if( timeGranularity == "year" ) {
hierarchy.group(dimName,hierarchyDefault, "All Time", yearStrFy);
hierarchy.group(dimName,hierarchyYear, "", fyYearStr);
} else if( timeGranularity != "year" ) {
hierarchy.group(dimName,hierarchyAnnual, "", yearStrFy + " - Annual");
hierarchy.structure(dimName,hierarchyDefault, "All Time", yearStrFy);
hierarchy.structure(dimName,hierarchyYear, fyYearStr, "");
if( timeGranularity == "quarter" ) {
hierarchy.group(dimName,hierarchyDefault, yearStrFy, quarterFullString);
hierarchy.group(dimName,hierarchyQuarter, "", quarterFullString);
hierarchy.group(dimName,hierarchyYear, fyYearStr, quarterFullString);
} else {
hierarchy.structure(dimName,hierarchyDefault, yearStrFy, quarterFullString);
hierarchy.structure(dimName,hierarchyQuarter, quarterFullString, "");
if( timeGranularity == "month" ) {
//add month ytd hierarchy
var mCount = parseInt(month,10);
if( month <= monthStart )
mCount += 12;
for(var m=monthStart+1;m<=mCount;m++) {
var mName = "";
var mFullName = "";
if( m <= 12 ) {
mName = monthsCal[parseInt(m,10)-1];
mFullName = year + " - " + mName;
} else {
mName = monthsCal[parseInt(m-12,10)-1];
mFullName = (year+1) + " - " + mName;
}
if( timeGranularity == "month" ) {
hierarchy.group(dimName,hierarchyMonthYTD, monthFullString + " YTD", mFullName);
} else {
hierarchy.structure(dimName,hierarchyMonthYTD, monthFullString + " YTD", mFullName);
}
}
}
if( timeGranularity == "month" ) {
hierarchy.group(dimName,hierarchyDefault, quarterFullString, monthFullString);
hierarchy.group(dimName,hierarchyMonth, "", monthFullString);
hierarchy.group(dimName,hierarchyQuarter, quarterFullString, monthFullString);
hierarchy.group(dimName,hierarchyYear, fyYearStr, monthFullString);
} else {
hierarchy.structure(dimName,hierarchyDefault, quarterFullString, monthFullString);
hierarchy.structure(dimName,hierarchyMonth, monthFullString, "");
if( timeMonthBuild == "calendar days" ) {
if( timeGranularity == "date" ) {
hierarchy.group(dimName,hierarchyDefault, monthFullString, dateStr);
hierarchy.group(dimName,hierarchyDate, "", dateStr);
hierarchy.group(dimName,hierarchyMonth, monthFullString, dateStr);
hierarchy.group(dimName,hierarchyQuarter, quarterFullString, dateStr);
hierarchy.group(dimName,hierarchyYear, fyYearStr, dateStr);
} else {
hierarchy.structure(dimName,hierarchyDefault, monthFullString, dateStr);
hierarchy.structure(dimName,hierarchyDate, dateStr, "");
if( timeGranularity == "time" ) {
for(var h=0;h<24;h++) {
var hourVal = "" + h;
if( hourVal.length == 1 )
hourVal = "0" + hourVal;
var hourStr = dateStr + " " + hourVal + ":00";
hierarchy.group(dimName,hierarchyDefault, dateStr, hourStr);
hierarchy.group(dimName,hierarchyHour, "", hourStr);
hierarchy.group(dimName,hierarchyMonth, monthFullString, hourStr);
hierarchy.group(dimName,hierarchyQuarter, quarterFullString, hourStr);
hierarchy.group(dimName,hierarchyYear, fyYearStr, hourStr);
}
}
}
} else {
if( timeGranularity == "week" ) {
hierarchy.group(dimName,hierarchyDefault, monthFullString, weekStr);
hierarchy.group(dimName,hierarchyWeek, "", weekStr);
hierarchy.group(dimName,hierarchyQuarter, quarterFullString, weekStr);
hierarchy.group(dimName,hierarchyYear, fyYearStr, weekStr);
} else {
hierarchy.structure(dimName,hierarchyDefault, monthFullString, weekStr);
hierarchy.structure(dimName,hierarchyWeek, weekStr, "");
if( timeGranularity == "date" ) {
hierarchy.group(dimName,hierarchyDefault, weekStr, dateStr);
hierarchy.group(dimName,hierarchyDate, "", dateStr);
hierarchy.group(dimName,hierarchyWeek, weekStr,dateStr);
hierarchy.group(dimName,hierarchyMonth, monthFullString, dateStr);
hierarchy.group(dimName,hierarchyQuarter, quarterFullString, dateStr);
hierarchy.group(dimName,hierarchyYear, fyYearStr, dateStr);
} else {
hierarchy.structure(dimName,hierarchyDefault, weekStr, dateStr);
hierarchy.structure(dimName,hierarchyDate, dateStr, "");
if( timeGranularity == "time" ) {
for(var h=0;h<24;h++) {
var hourVal = "" + h;
if( hourVal.length == 1 )
hourVal = "0" + hourVal;
var hourStr = dateStr + " " + hourVal + ":00";
hierarchy.group(dimName,hierarchyDefault, dateStr, hourStr);
hierarchy.group(dimName,hierarchyHour, "", hourStr);
hierarchy.group(dimName,hierarchyWeek, weekStr,hourStr);
hierarchy.group(dimName,hierarchyMonth, monthFullString, hourStr);
hierarchy.group(dimName,hierarchyQuarter, quarterFullString, hourStr);
hierarchy.group(dimName,hierarchyYear, fyYearStr, hourStr);
}
}
}
}
}
}
}
}
}
}
function data(record) {
//this function is called once for each line of data on the second cycle
//use this to build dimensions and push data into cubes
}
function end() {
//this function is called once at the end of the process
script.log('process execution finished.');
}
Date.prototype.addDays = function(days) {
var dat = new Date(this.valueOf())
dat.setDate(dat.getDate() + days);
return dat;
}
Date.prototype.removeDays = function(days) {
var dat = new Date(this.valueOf())
dat.setDate(dat.getDate() - days);
return dat;
}
Date.prototype.getWeek = function() {
var onejan = new Date(this.getFullYear(),0,1);
return Math.ceil((((this - onejan) / 86400000) + onejan.getDay()+1)/7);
}
function getDates(startDate, stopDate) {
var dateArray = new Array();
var currentDate = startDate;
while (currentDate <= stopDate) {
dateArray.push(currentDate)
currentDate = currentDate.addDays(1);
}
return dateArray;
}