tMap and lookup on dimension table (with versioning)

[gorotman]

Hi all,
I have a doubt. What's the best solution to do a lookup from fact table to a dimension table with versioning?
Please consider performance and code maintenance.
This is my solution (that works):
Fact table

dim table


job

 tMap



Expression filter in output flow


Result (correct)



Problem is how to do a complex join to find the correct version of a dimension occurrence, using application id and the invoice date. (It's a left outer join because I want to keep missing values and mark it with a default missing dimension key).
Using tMap, it's required to use left outer join and all matches. The filter expression for date range join, is in the output expression filter.
I didn't find any working alternative. Any idea or suggestion?
Fact table could be big (5-20 Mil rows ), so I 'm looking for a better performance.

Thank's for help.

[gorotman]

Hi saadia, is better if you create a new post for you request, then insert more informations and some schema to exlpain it.
bye