Dynamic key while joining tables? or any other solution?

[supriyabiware]

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.

[jonathandienst]

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.

[supriyabiware]

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.