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)
2 REPLIES
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';

Enthusiast

Re: Temporal and fact/dimension joins

I am having simellar case Now in my organization, 

I want to generate a surrogate key which represent state of the record at a certain point of time , and the primary key for the dimeension table will be this surrogate key column ONLY, 

 

Joining a daily snapshoot fact table with one of the dimmenssion table should be done through One and only One column ,

 

If you find a solution for your cases , can you please share it ?