topic Re: tMap and lookup on dimension table (with versioning) in Talend Studio

tMap and lookup on dimension table (with versioning)

gorotman — Tue, 28 Jul 2015 13:49:37 GMT

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.

Re: tMap and lookup on dimension table (with versioning)

gorotman — Tue, 28 Jul 2015 13:52:24 GMT

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

Re: tMap and lookup on dimension table (with versioning)

gorotman — Tue, 27 Oct 2015 15:32:39 GMT

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

Re: tMap and lookup on dimension table (with versioning)

gorotman — Tue, 29 Dec 2015 10:18:30 GMT

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.

Re: tMap and lookup on dimension table (with versioning)

gorotman — Wed, 02 Mar 2016 09:16:12 GMT

Re: tMap and lookup on dimension table (with versioning)

gorotman — Thu, 05 May 2016 09:34:41 GMT

Re: tMap and lookup on dimension table (with versioning)

talendtester — Thu, 05 May 2016 18:24:23 GMT

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)

Re: tMap and lookup on dimension table (with versioning)

gorotman — Fri, 06 May 2016 10:13:29 GMT

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).

Re: tMap and lookup on dimension table (with versioning)

gorotman — Fri, 06 May 2016 10:24:45 GMT

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......

Re: tMap and lookup on dimension table (with versioning)

Anonymous — Mon, 29 Aug 2016 20:35:32 GMT

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.

Re: tMap and lookup on dimension table (with versioning)

gorotman — Tue, 30 Aug 2016 07:01:10 GMT

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