Temporal and fact/dimension joins

Database

Temporal and fact/dimension joins

I am considering using Temporal for an upcoming project. The project will involve a (semantic) dimensional model for reporting.

I have spent a considerable amount of time trying to understand Temporal, and I think I am missing something, because Temporal does not seem to satisfy one of the fundamental use cases needed in a dimensional model. Maybe it does.... but I'm not sure.

How do I apply an AS OF to the dimension based on an event date in the fact? When I join a fact to a dimension, I don't want to apply a single AS OF to the entire join (which appears to be the limitation of the AS OF clause -- it has to evaluate to a single value, I think). Each fact record has to match the dimension record that was valid (business validity) as of the date of the event represented by the fact. (This gets more complicated when you have slowly changing facts, I know, but let's focus on a non-temporal event fact table.)

If I want to run a time-series analysis, each fact record must be associated with the version of the dimension that was valid at the point in time that fact occurred. I know how to do this with traditional dimensional modeling and slowly changing dimensions. But I'd like to see if Temporal can make implementing SCDs simpler.

I'm thinking I could do something like this:

SELECT *
FROM MyFact
JOIN MyDim
ON MyDim.MyDimKey = MyFact.MyDimKey
AND MyDim.Validity CONTAINS MyFact.OccurredOnDate
WHERE MyFact.OccurredOnDate BETWEEN ... AND ...;

This would seem to achieve the desired result, but it doesn't feel right. Now I've got to worry about whether I'm doing a SEQUENCED or NONSEQUENCED VALIDTIME join (which I haven't thought a lot about yet). I have seem some articles on the internet talking about a concept called AS AT, but that is not valid temporal SQL.

Am I missing something obvious? What is the proper way to join a non-temporal table (with an event date in each row) to a validtime temporal table (where the valid time must be as of the event date in the joined row)?

Thanks for the help,

-eric

Tags (1)
1 REPLY
Enthusiast

Re: Temporal and fact/dimension joins

Did you try AS OF qualifier on table level.

We can have multi ple AS OF qualifier in a SQL at table level.

For Ex:

CREATE MULTISET TABLE FACT_TBL_VT(

SLS_NO INT,

AREA1_SLS INT,

AREA1_SLS INT,

VT PERIOD(date) as validtime);

CREATE MULTISET TABLE DIM_TBL_VT(

SLS_NO INT,

SLS_NM CHAR(10),

VT PERIOD(date) as validtime);

SEL * FROM FACT_TBL_VT AS OF DATE'2015-07-16' , DIM_TBL_VT AS OF DATE'2014-07-16';