Select Page

In the last two parts of this series, we discussed master calendars and link tables as plausible options for handling dates, situations where they could be effective, along with possible issues.

In much the same manner this time we are going to have a look at date islands, which are a form of a data island.

So let’s get cracking and spin up a will data set:

employee:
Load * Inline [
employee, startdate
jim, 2007-11-01,
jane , 2004-08-05,
peter, 2001-03-17,
geoff, 1997-07-12
];

salesorder:
Load * Inline [
saledate, customer, productid, employee, cost, price
2016-06-19, tracy, 1, jim, 1, 5,
2017-12-31, jan, 5, peter, 2,8,
2013-11-05, lars, 4, jane, 4, 12,
2005-08-01, brady, 3, geoff, 5, 9,
2018-02-02, alex, 5, jim, 2, 10
];

Now we have a nice little model which shows sales orders and the relationship between employees, along with the saledate and the employees startdate:

If we open up a sheet and add both dates we can see that they are operating independently:

The issue we are seeing here is that the dates attribute does not conform, in a data warehousing environment you would typically have a date dimension, as in a single table which unifies date attributes.

So with a calendar island, we look to build a single calendar table:

First we define a range, which could be a min or max from one of your tables, generally I like to specify something wild to account for most plausible dates:

Temp:
LOAD * Inline
[
minDate, maxDate
‘1900-01-01′,’2099-12-31’
];

Then put those values into parameters:

LET varMinDate = Num(Peek(‘minDate’, 0, ‘Temp’));
LET varMaxDate = Num(Peek(‘maxDate’, 0, ‘Temp’));
DROP Table Temp;

Work out the date range that we are building:

TempCalendar:
LOAD
$(varMinDate) + IterNo()-1 as Num,
Date($(varMinDate) + IterNo() – 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

And finally construct the calendar:

Calendar:
LOAD
TempDate AS date,
Day(TempDate) AS DayNum,
Week(TempDate) AS WeekNum,
Year(TempDate) AS YearNum,
WeekDay(TempDate) AS DayName,
Month(TempDate) AS MonthName,
Week(TempDate) & ‘-‘ & Year(TempDate) AS WeekYear,
Date(MonthStart(TempDate), ‘MM-YYYY’) AS MonthYear,
‘Q’ & Ceil(Month(TempDate) / 3) AS CalQuarter,
Quarterstart(TempDate, 0, 1) AS QuarterStart,
Quarterend(TempDate, 0 , 1) AS QuarterEnd,
Weekstart(TempDate) AS WeekStart,
Weekend(TempDate) AS WeekEnd,
Date(MonthStart(TempDate)) AS MonthStart,
Date(MonthEnd(TempDate)) AS MonthEnd,
TempDate – Date(MonthStart(TempDate)) AS DayOfMonth,
DayNumberOfQuarter(TempDate) AS DayOfQuarter,
DayNumberOfYear(TempDate) AS DayOfYear,
If(Match(WeekDay(TempDate), ‘Sun’, ‘Sat’) = 0, ‘N’, ‘Y’) AS WeekendFlag

Resident TempCalendar
Order By TempDate ASC;
DROP Table TempCalendar

As a result in the Data Model Viewer we end up with a calendar island which is independent of the other tables because there are no identical column names in the other tables:

Now that we have seen an island sitting on it own the though is probably crossing your mind “how is that going to work?” Well, the “stitching together” happens in the App development layer.

So what we want to do is create a bar chart and then plot Yearnum as the dimension, and we will use an element from each table with differing times as our measure:

count(if(startdate=date,employee))

sum(if(startdate=date,cost))

Now the chart should be plotting multiple metrics (I tidied the appearance of this chart slightly, so yours might not be identical) :

The one limitation worth discussing with this approach is that the code that’s driving this functionality is being calculated at execution time, with large volumes of data, and large date ranges it’s likely there would be performance degradation. So great method for a reasonably small data set, which with well-designed Apps and data processes is definitely possible.

So that’s all for this time, see you next time when we go through canonical dates.

Thomas – MacGyver of code

Thomas blogs about reporting platforms, data warehousing and the systems behind them.

Read Thomas’s other blog posts, including master calendars and link tables here.

We run regular business intelligence courses in both Wellington and Auckland.

%d bloggers like this: