IntervalMatch vs. Extended IntervalMatch

[#1]

Hi,

I would like read time-dependent masterdata into my fact table, in order to solve a slowly-changing dimension problem. I can use the (standard) IntervalMatch functionality to achieve this. However, it takes me 2 joins. I've read in the help about the extended intervalmatch functionality. But it doesn't deliver the expected results.

To demonstrate, I've created a simple example. In this example there is a masterdata table (Seasons) and a fact table (Sales). I want to add the right season to my sales table. (This is not strictly a changing dimension, but it demonstrates the functionality.)

The source tables:

SET DateFormat='D-M-YYYY';

Seasons:
Load
 Season,
 Date#(StartDate,'D-M') AS StartDate,
 Date#(EndDate,'D-M') AS EndDate;
LOAD * INLINE [
 Season, StartDate, EndDate
 Winter, 1-1, 29-02
 Spring, 1-3, 31-5
 Summer, 1-6, 31-8
 Automn, 1-9, 20-11 Winter, 1-12, 31-12];
Sales:
Load *,
 MakeDate(year(date(0)),Month(SalesDate),Day(SalesDate)) AS MatchDate;
LOAD * INLINE [
Product, SalesDate, Amount
 Gloves, 15-12-2009, 100
 Shawl, 25-01-2010, 50
 Sunglasses, 14-07-2009, 150];

With standard IntervalMatch functionality, I get the expected results. However, I need 2 joins:

LEFT JOIN (Seasons)
IntervalMatch (MatchDate) LOAD StartDate, EndDate RESIDENT Seasons;

LEFT JOIN (Sales) LOAD MatchDate, Season RESIDENT Seasons;
DROP TABLE Seasons;

With extended IntervalMatch functionality, I would expect the same results, but with only 1 join.. However, I don't get any match anymore:

LEFT JOIN (Sales)
IntervalMatch (SalesDate, Season) LOAD StartDate, EndDate, Season RESIDENT Seasons;
DROP TABLE Seasons;

Any thoughts on this are appreciated.

[vidyut · Accepted solution]

Hi,

In my view, Extended Interval Match is used when you have some more keys (apart from just StartDate/EndDate ) to be matched. So, it does not reduce the number of joins as understood by you.

In the current example, you do not need Extended syntax, as you do not have any extra keys to be matched to determine the correct season for a date.

[#3]

Ok, your answer makes me understand the purpose of the extended intervalmatch functionality.

Still, I would like to know if there are better ways to read attributes of slowly changing dimensions to a fact table. I don't like having 2 joins, since it makes the code difficult to read. Any suggestions?

[vidyut]

Those joins are needed to avoid the synthetic keys, so no way to avoid these without making the model look messier