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
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
Quote from: starschema on 18 Aug 2011 10:24:48 PM
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.