Teradata Historisation Part 4/5: Straight Continuation cases

0
422

teradata historisation

In part 4 of our series on Teradata historisation merge scenarios we present those cases that start and end at one and the same level of historisation. While these cases sound like the simplest of merges, they are, in fact, some of the most demanding ones. There are three such cases: (0-0-0, 1-1-1, 2-2-2).

 

Case 0-0->0 is, indeed, trivial. Just (full outer) join the two tables A and B and insert the result into the target table.

 

Case 1-1->1 We have to take care to match divergent streams of historic changes together to one consistent history that leads to a new entry per key whenever there was a change in table A or B.

We summarize the snapshot history by picking the minimum and maximum CDD from A per key and non-key attribute combination over time. The following code gathers all points in time where either Table A or Table B start a new historic layer for an entity, calculates the resulting RSD and RED row and joins A and B table contents together along these time constellations. We arrive at a unified history layer that opens a new time layer whenever either A or B change the row content.

— CASE 1-1-> 1

SELECT ENT_ID, THESTARTDT, (CASE WHEN THEENDDT>CURRENT_DATE THEN DATE’3000-12-31′ ELSE THEENDDT END) AS THEENDDT,

C_STATUS ,D_LEVEL_CD , E_VALUE,F_STATUS, G_LEVEL_CD, H_VALUE

FROM

(

SELECT

ENT_ID, C_STATUS ,D_LEVEL_CD , E_VALUE,F_STATUS, G_LEVEL_CD, H_VALUE, MIN(THEDATE) AS THESTARTDT, MAX(THEEND) AS THEENDDT

FROM

(

SELECT U.ENT_ID, U.RSD AS THEDATE,

MAX(U.RSD) OVER (PARTITION BY U. ENT_ID ORDER BY U. ENT_ID, U.RSD ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING ) -1 as THEEND,

A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE, A.RSD, A.RED,B.F_STATUS, B.G_LEVEL_CD, B.H_VALUE

FROM

(select ENT_ID, RSD

FROM

(— Convert A history to RSD, RED

sel A.ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE,

MIN(CDD) AS RSD,

MAX(CDD) AS RED

FROM TABLE_A1 A

GROUP BY 1,2,3,4 ) A

GROUP BY 1,2

UNION

select ENT_ID, RED

FROM

(— Convert A history to RSD, RED

sel A.ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE,

MIN(CDD) AS RSD,

MAX(CDD) AS RED

FROM TABLE_A1 A

GROUP BY 1,2,3,4 ) A

GROUP BY 1,2

UNION ALL

select ENT_ID, RSD

FROM

TABLE_B1 B

group by 1,2

UNION

select ENT_ID, RED

FROM

TABLE_B1 B

group by 1,2

) U

LEFT OUTER JOIN

(— Convert A history to RSD, RED

sel A.ENT_ID, A. C_STATUS ,A.D_LEVEL_CD , A.E_VALUE,

MIN(CDD) AS RSD,

MAX(CDD) AS RED

FROM TABLE_A1 A

GROUP BY 1,2,3,4 ) A

ON U.ENT_ID=A.ENT_ID

AND U.RSD BETWEEN A.RSD AND A.RED

LEFT OUTER JOIN

TABLE_B1 B

ON U.ENT_ID=B.ENT_ID

AND U.RSD BETWEEN B.RSD AND B.RED

) B

GROUP BY ENT_ID, C_STATUS ,D_LEVEL_CD , E_VALUE,F_STATUS, G_LEVEL_CD, H_VALUE

HAVING THESTARTDT <=CURRENT_DATE

) F

order by 1,2,3

;

 

Case 2-2->2 This appears to be the most complicated case. We have to align not just two possibly asynchronous RSD and RED histories but given this alignment, we also have to decide on alignments of intermediate layers of already devalidated histories. Besides the technical complexity, we assume that at the time we merge, we are only interested in the histories deemed valid. Space considerations are another concern: a complete treatment of this case could lead to a merged target table being much bigger than the two merged tables together. Therefore we simplify this case to being similar to Case 1-1->1 with an additional condition on the valid time layers being selected. Therefore we can use the SQL of Case 1-1->1 as a pattern. All that remains is to add present and default openness timestamps to the resulting column set.

Our Reader Score
[Total: 2    Average: 5/5]
Teradata Historisation Part 4/5: Straight Continuation cases written by Paul Timar on April 30, 2014 average rating 5/5 - 2 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here