Map dimension to fact columns

[wyconian]

Hey Everyone

Can someone please help me with this?  I'm trying to lookup from a fact to a dimension to return the surrogate key based on the dimension business key. 

The business key on the dim (which identifies a unique row) is username||efftv_from_date, the fact table has username and created_date. 

I want to be able to lookup the surrogate key where fact.username = dim.username and fact.created_date between dim.efftv_from_date and dim.efftv_to_date.

I'm struggling to do this.  I tried using a derived dimension thinking I could add a calculation to do this but I couldn't see any reference elements.

Any ideas on how I can do this?

Appreciate your help as always.

Thx

Simon

[wyconian]

Quick Update

Think I managed to resolve this using the process late arriving facts functionality.

Thanks to StarSchema on a previous post for the idea

Have a read of the "Process Late Arriving Facts" section of the Data Manager documentation, specifically the "Use closest reference member" option, it should do what you want.