Select Page

In my last blog we went over master calendars, how they function, and of course advantages and disadvantages. If you missed that blog you can catch up here.

This time around we are going to have a look at the implementation of a link table to stitch a data model together, so without further ado, let’s get into these aptly named link tables.

To begin this example we need to spin up a fictitious 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
];

So now we have something that looks fairly common, some products are sold and some employees sold them. We have a natural association in the Data Model viewer based on employee, and we have two date fields, one being the employees start date and the other being the sale date:

If we want to introduce some sort of conforming date dimension, we would run into a loop between these two tables, which clearly will cause some issues. So the first thing to do is generate some unique keys on our tables be concatenating columns (obviously not what I have done with this inline data set):

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

 

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

Next, we need to generate a staging link table, notice how we rename the date related columns so they match, and then concatenate the two tables, this is similar to a union statement in SQL:

linktabletemp:
Load *,
startdate as date,
’employee’ as datetype
Resident employee;

 

Concatenate Load *,
saledate as date,
‘salesorder’ as datetype
Resident salesorder;

If we take a look at linktabletemp in the Data Model viewer we will see a concatenated list of all the various dates, along with the source or datetype:

Next, we stitch things together with a more formalized table of data and include date type conversions that may be relevant, and bring in our source tables to join on:

link:
LOAD
datetype,
date,
Day(date) as day,
Month(date) as month,
Year(date) as year,
customer,
productid,
employee,
cost,
price,
employeekey,
saleskey
Resident linktabletemp;

 

employees:
load
employeekey,
startdate
Resident employee;

 

salesorders:
load
saleskey,
saledate
Resident salesorder;

 

drop table linktabletemp, employee, salesorder;

Now we have a nice link table which does not resolve any issues:

And we can preview the data contained to conceptualize the structure:

While the link table enables us to compare different elements in the same time series, employing a link table approach has the potential to get quite messy. It’s not uncommon for data models to have hundreds of tables, some with multiple date fields. So much like Master Calendars, there is a place for the Link Table approach, certainly if the goal is to throw something together quickly.

It’s worth considering what would happen with slowly changing dimensions, try adding this to the model if you feel like playing around:

product:
Load * Inline [
productid, productdesc, price, pricestart, priceend
1, widget1 white, 5, 1900-01-01, 2099-12-31,
2, widget2 red, 7, 1900-01-01, 2099-12-31,
3, widget3 black, 9, 1900-01-01, 2099-12-31,
4, widget4 purple, 12, 1900-01-01, 2099-12-31,
5, widget5 orange, 8, 1900-01-01, 2018-01-01,
5, widget5 orange, 10, 2018-01-02, 2099-12-31
];

That’s all until next time when we take a look at Calendar Islands and their application.

Thomas – MacGyver of code

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

You can read Thomas’s other blog posts here.

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

%d bloggers like this: