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]

uhm, the site made a bit confusion with screenshot (but before save they were ok....).
this is the dimension table (I hope)

[gorotman]

Hi, I post here to ask for suggestions....
talend has component to manage SCD table.
But, how do you use these tables in your jobs? How do you resolve join with date range?
Problem with way described before is to handle failed lookup.

Thank

[gorotman]

I created a new feature request in Jira, to find a smart solution for this join type.
If someone is interested in it, please vote.

Join DW fact table to an SCD table

Thanks.

[gorotman]

Up

[gorotman]

Up

[talendtester]

If any of the 3 conditions are true, you want to remove that data, correct?

This will get rid of the data if there is any hit in the 3 checks:

row2.office_id == null
|| (!row1.invoice_date.before(row2.start_date)
|| (!row1.invoice_date.after(row2.end_date)

[gorotman]

talendtester wrote:
If any of the 3 conditions are true, you want to remove that data, correct?

This will get rid of the data if there is any hit in the 3 checks:

row2.office_id == null
|| (!row1.invoice_date.before(row2.start_date)
|| (!row1.invoice_date.after(row2.end_date)

Thanks for you post. Code you posted is right, may be (I'm not sure) that my filter (with condition1  || (condition 2 && condition 3) ) is faster because OR require to test every condition.

But my original request was for a different way to resolve fact -> dimensional (type with versioning) join.
I'll explain my worries:
this join (left outer join with all rows joined by office_id, neither date) moltiplicate fact rows for each office version.
So it tests date range and waistes many of them.

It's a worst solution I think.
Also I need to catch fact rows that don't match with dimension table (send alter to users).
It's not simple to catch them:
- I can't use inner join and reject flow
- system doesn't distinguish fact row that doesn't mach none rows (send alert) from fact row that matches one version and refuses others (ok, no alert).

[gorotman]

I designed and realized many dw/etl with different softwares (IBM datastage, BO Data integrator) and this type of join and rows management is common for me, in data warehouse projects. :rolleyes:
Am I the only one with this needs using TOS? I'm surprised......

[Anonymous]

hi , i would to load my fact table toracleOutput . with join my date_dimension and fileInputExcel this is my job:
 
my fileInputExcel:

and my dim_date:


 and this my tmap :


 problem is how to join my libelle_mois to table date_dim with mois in tFileExcelInput using tmap to load my fact table.