Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a fact table - F_PLANT
I have dimension to derive the information - D_PLANT
Unfortunately the plant dimensions have new IDs whereas the fact is updated with Old IDs only. hence there is a new table created for IDs - X_PLANT_UPDATED - which has following columns
OLD_ID,
NEW_ID,
VALID_FROM,
VALID_UPTO
Now i have to derive information from dimension(with new ids) into fact(with old ids) using the table X_PLANT_UPDATED.
Any ideas?
Thanks
Supriya.
This is best handled using interval match ("extended syntax"):
// Load the interval table
Update:
LOAD OLD_ID,
NEW_ID,
VALID_FROM,
VALID_UPTO
FROM X_PLANT_UPDATED;
// Load the fact table. Alias the ID to OLD_ID
F_PLANT:
LOAD ID as OLD_ID,
DATE
...
FROM F_PLANT;
// Match the updates using IntervalMatch
Left Join (F_PLANT)
IntervalMatch(DATE, OLD_ID)
LOAD VALID_FROM,
VALID_UPTO,
OLD_ID
Resident Update;
// Fetch the new ID and alias it to ID
Left Join (F_PLANT)
LOAD OLD_ID,
NEW_ID as ID,
VALID_FROM,
VALID_UPTO
Resident Update;
// Clean up
DROP Table Update;
DROP Fields VALID_FROM, VALID_UPTO;
// Now load the dimension
D_PLANT:
LOAD ID
...
FROM D_PLANT;
This assumes that the ID field is called "ID" date field is called "DATE". Modify the script above with the correct field names if necessary.
Thankyou for your reply Jonathan,
As i understand , in this case we are replacing the Old IDs with New IDs matching the valid from and upto from update table , but my old ids need to stay in the table(in the ID column) since ID is joined to other facts or dimensions accordingly.
Is there any way wherein i can have both columns OLD ID and NEW ID in the fact and join the dimension using a logic?
*valid from and upto not much imp.