Skip to main content
To homepage To homepage

Case:

In the DSA I have two tables (Employee and Employee Addresses).

The fist table has history in it from the source system (so no TX  history active).

On the second table we have TX History active (Type II) . It is not privided by the source system.

 

When joining the tables to a dimension, the timelines must be combined.

In what way can this be achived in TX?

 

Emp_ID RegistrationNumber ExpDate ValidFromDate ValidToDate SCD Is Current      
109030 39935270430 4-7-2020 1-01-00 1-10-24 0      
109030 39935270430 4-7-2029 2-10-24 31-12-99 1      
                 
                 
Emp_ID ZIP City SCD From DateTime SCD To DateTime SCD Is Current      
109030 1625 HV Hoorn 1-01-00 31-05-24 0      
109030 1000 AB Amsterdam 1-06-24 31-12-99 1      
                 
Result of join:                
DWH_ID Emp_ID RegistrationNumber ExpDate ZIP City SCD From DateTime SCD To DateTime SCD Is Current
1 109030 39935270430 4-7-2020 1625 HV Hoorn 1-01-00 31-05-24 0
2 109030 39935270430 4-7-2029 1000 AB Amsterdam 1-06-14 1-10-24 0
3 109030 39935270430 4-7-2029 1000 AB Amsterdam 2-10-24 31-12-99 1

Hi,

 

you can look up the active adress surrogate key from the Employee Address table to the Employee table. You simply add joins that check the from and to dates. If the date ranges are not synchronized between the tables you will need to decide how to use aggregation operators to choose an address for a given ValidFromDate - ValidToDate range (last know address withing the range, etc).

If you want to create new Employee records in the Employee table for each address change within an Employee date validity range, you will need to write a Custom View to do that.

Hi ​@PieterQ 

Does Rory’s answer above help answer your question? If so please help us by marking the best answer above. Please let us know if you have any follow up questions

Reply


Terms and ConditionsCookie settings