Short cutting Temporal Joins

Introduction

When you create temporal data models, either with Data Vault, Anchor modeling or any other way, you end up with several tables with timelines in them. These timelines often need to be combined for going to Data Marts or other processing. This combining is done with a temporal JOIN

Example

• Table Product
• Table Product Cost
• Table Product Price
• Calculating Product Revenue = Sales Price- Manufacturing Cost

Conceptually the query will look like this (using the Allen operator)

SELECT "product cost".cost                            Cost,

       "product price".price                          AS Price,

       "product price".price - "product cost".cost    AS Revenue,

       "product cost".period INTERSECTS "product price".period AS Period

FROM   "product cost"

       INNER JOIN "product price"

               ON "product price".id = "product cost".id

WHERE  "product cost".period OVERLAPS "product price".period

The issues here are twofold, how to implement this in plain SQL, and what to define for Revenue for the omissions. Here we could state that the revenue is undefined, so in this case the first period is not available. Alas, this is not always the correct result.

SQL Implementation

There are a lot of ways to construct an SQL query that implements an intersection of 2 timelines. The most simplest are checking overlap between each 2 periods. This will quickly create unwieldy queries when more timelines need to be intersected. A more sophisticated approach rewrite, given x,y,z are periods:

y OVERLAPS y OVERLAPS y to NOT EMPTY(x INTERSECTS y INTERSECTS z) and to

say a = (x INTERSECTS y INTERSECTS z) then a.start_data

max(x.start_date,y.start_date,z.start_date) to min(x.end_date,y.end_date,z.end_date) is a valid period, which means:
max(x.start_date,y.start_date,z.start_date) < min(x.end_date,y.end_date,z.end_date)

Basic Greatest/Least  SQL Query

Timeline Alignment

All these kinds of queries only work correctly when the different tables have aligned their timelines (have the same start and end dates for each key). For the end date this works ou as long am 'high end date' is used. For start dates additional records need to be created with empty/default/null values to align on a 'low start date' (say 1753-01-01).

DBMS implementation

These types of queries work in Both Oracle, Teradata and SQL Server. In Oracle and Teradata these queries can be used because the functions greatest and least are defined. In SQL Server however this is not the case.

SQL Server (T-SQL) implementation

To create this type of query in SQL Server we need both a Least and Greatest function. we can either code a .NET assembly for this, or an user defined function. Alas the udf won't perform at all. A trick here is to rewrite a scalar udf to an inline view udf. These will get optimized and peform OK. We only need to (CROSS) APPLY them to the query to get our wanted result.
One of the issues here is that you cannot use the helper functions in indexed views, you need to substitue the case statement, and duplicate it both in the select and where clause.

Helper functions

Here are the SQL Server helper functions greatest and least and their usage pattern.

1. CREATE FUNCTION [dbo].[ivf_least] (@1 datetime=null,@2 datetime = null ,@3 datetime = null,@4 datetime=null,@5datetime = null)
2. RETURNS TABLE WITH SCHEMABINDING
3. RETURN
4. (WITH coal(a1,a2,a3,a4,a5)
5. AS (SELECT COALESCE(@1,@2,@3,@4,@5),COALESCE(@2,@3,@4,@5,@1),COALESCE(@3,@4,@5,@1,@2),COALESCE(@4,@5,@1,@2,@3),COALESCE(@5,@1,@2,@3,@4))
6. SELECT case when a1<=a2 and a1<=a3 and a1<=a4 and a1<=a5 then a1  
7. when a2<=a3 and a2<=a4 and a2<=a5 then a2
8. when a3<=a4 and a3<=a5 then a3
9. when a4<a5 then a4
10. else a5 end as out from coal);
11. GO
13. CREATE FUNCTION [dbo].[ivf_greatest] (@1 datetime=null,@2 datetime = null ,@3 datetime = null,@4 datetime=null,@5datetime = null)
14. RETURNS TABLE WITH SCHEMABINDING
15. RETURN
16. (WITH coal(a1,a2,a3,a4,a5)
17. AS (SELECT COALESCE(@1,@2,@3,@4,@5),COALESCE(@2,@3,@4,@5,@1),COALESCE(@3,@4,@5,@1,@2),COALESCE(@4,@5,@1,@2,@3),COALESCE(@5,@1,@2,@3,@4))
18. SELECT case when a1>=a2 and a1>=a3 and a1>=a4 and a1>=a5 then a1  
19. when a2>=a3 and a2>=a4 and a2>=a5 then a2
20. when a3>=a4 and a3>=a5 then a3
21. when a4>a5 then a4
22. else a5 end as out from coal);
23. GO

Using this functions in the following way:

1. CREATE TABLE SAT1 (DV_ID int,START_DT datetime,END_DT datetime) PRIMARY KEY (DV_ID,START_DT)
2. GO
3. CREATE TABLE SAT2 (DV_ID int,START_DT datetime,END_DT datetime) PRIMARY KEY (DV_ID,START_DT)
4. GO
5. select
6.         SAT1.DV_ID,
7.         GREATEST_DT.out as START_DT,
8.         LEAST_DT.out as END_DT
9. from SAT1 inner join SAT2 on SAT1.DV_ID=SAT2.DV_ID
10. cross apply ivf_greatest(SAT1.START_DT,SAT2.START_DT,NULL,NULL,NULL) as GREATEST_DT
11. cross apply ivf_least(SAT1.END_DT,SAT2.END_DT,NULL,NULL,NULL) as LEAST_DT
12. where
13. GREATEST_DT.out < LEAST_DT.out -- assuming an [Closed,Open) period
14. GO

Repeating the Past: Repairing your history in with temporal data.

Those that forget the past are Condemned to repeat it

^ George Santayana (1905) Reason in Common Sense, volume 1 of The Life of Reason

Introduction

When you forget the past, you are condemned to repeat it. This also holds true when you model history and history of history in your database. When you recreate a transaction history in a fully temporal information system with loading data from a source that does not record one you are alas forgetting the past. While there is no way to reconstruct the pas from thin air, when the source information system maintains a valid timeline (a user controllable timeline that among others allows for backdating), you can still recreate an approximation of the past by repeating your valid timeline into your transaction timeline.

Forgetting the past

Temporal Data

Temporal Databases or temporal data modeling techniques allow you to record timelines. You can record when somebody lived at a certain address. We have 2 different classes of timelines; those that are connected to our own time, usually called logging, transaction, actual or asserted time, and time fully controllable by the end users, usually called valid, stated or user time. We talk about bi-temporal time when data is registered against 2 timelines, of each class one.

Data warehousing and logging

Data warehouses that log all data (implement a transaction/logged timeline) complement source systems that only register stated time so that they record a bi-temporal time. This is typically done with e.g. a Data Vault. Of course, a Data vault can never show the logged time for the past that it did not log, even if the source recorded a stated/valid time. However, we can create an approximation of the past with the source stated time.

Repeating the past

There are 2 strategies to repair the past, extension of the first recorded occurence into the past, and transposing the source valid timeline to the past. The first strategy is simple and can always be applied, but is a gross simplification. The second strategy only works when there is a valid timeline of the past. It assumes that this valid timeline is a 1-1 with the transaction timeline.

Example

Lets show a detailed example on how the repair the past by repeating it. we will show the second strategy, the first is left as an exercise to the reader. We start with a source table example that registers a stated and logged timeline for a certain value in the Data warehouse. We assume the source has been registering the data for some time. We also assume that for the past there is an 1-1 between the stated and logged time. This is a serious assumption, and the reconstructed past is certainly not auditable in the sense that it has been properly logged. We start processing the source data at time t3 (@Tran_hist_start).

Source Table:

LOAD DT	END DT	VALID FROM_DT	VALID TO_DT	VALUE
T3	T4	T0	T1	1
T3	-	T1	T2	3
T3	T5	T2	T3	4
T3	-	T3	-	6
T4	-	T0	T1	2
T5	-	T2	T3	5

This Source Table in a picture:

We can for example read the time points as  months, with T0 being January. T1=February and so on. The Value can be seen as e.g. Status values, 1='Created',2='Open',.. 6='Destroyed'. We can read the lower boxes in the picture as: we started to record in april that from jan to feb the status was Created.In May we recorded that in the the jan to feb time frame the status was 'Open'. This is currently still true (true until end of time).

Transposed Situation

Lets transpose the valid timeline at time T3(@Tran_hist_start) and use that as a basis to reconstruct the unknown past. We assume here that in that past we don't know the future as known at T3.

Our bi temporal table will now look like this:

Table Values:

LOAD DT	END DT	VALID FROM_DT	VALID_ TO_DT	VALUE
T0	T1	T0	-	1
T1	T3	T0	T1	1
T1	T2	T1	-	3
T2	T3	T1	T2	3
T2	T3	T2	-	4
T3	T4	T0	T1	1
T3	-	T1	T2	3
T3	T5	T2	T5	4
T3	-	T3	-	6
T4	-	T0	T1	2
T5	-	T2	T3	5

a Visual representation of the data and the transformation:

Let's look at the 'SQL' Code to produe this past. We see that we create set of vertical oriented timeslices and a set of horizontal timelsieces to fill in the missing past.

Pseudo SQL Code

Vertical History:

Horizontal History

Total History:

Conclusion

Assuming the user controlled timeline (valid timeline, stated timeline) does not diverge significantly from the unknown transaction/logged timeline you can reconstruct the non logged past in a information system/data warehouse by pivoting and substituting the target transaction timeline with the source user controlled timeline for the missing past. By repeating this information we can 'repair the past'. This is especially handy when querying the past. Note that we are now repeating the past from the present because we 'forgot' to register this past in the first place. The question if we should actually persist this past or just derive this information at query time is a separate performance discussion.