topic How to retrieve historical data using some transformation rules in Talend-DI in Talend Studio

How to retrieve historical data using some transformation rules in Talend-DI

Anonymous — Tue, 11 Sep 2018 08:59:46 GMT

Here i'm given a task to take the data from the AS400(DB2-DB) which has all the data since ages and, do TRANSFORMATION'S using some transformation rules:

1) keep 30 days old data from the current date
2) delete the data older than 30 days .
3) keep the data of the 1st days of every month untill the previous 2 years.
4) Delete the data which is more than 2 yrs(24 months) old.

And, after the transformation, LOADING the transformed data in to the Oracle DB using Talend.

Any suggestions, in this regard is highly appreciated.

Thanks..

Re: How to retrieve historical data using some transformation rules in Talend-DI

fdenis — Tue, 11 Sep 2018 09:12:51 GMT

good luck!

start by a data map with the true field definition (what, from where, ….).
be careful of date data field! are they usable to shrink datas?
be careful of data who have to be sum-up!

good luck again.

Re: How to retrieve historical data using some transformation rules in Talend-DI

Anonymous — Tue, 11 Sep 2018 09:38:12 GMT

Hi fdenis,

I'm just a beginner in Talend-DI.

But, to answer your question, yes the date data field is allowed to shrink the data.

Also, do i write the transformation rules using tjava component or, a stored procedure on the destination DB(Oracle)

Can you explain me step by step to perform this task. I would really appreciate.

Thanks in Advance..

Re: How to retrieve historical data using some transformation rules in Talend-DI

fdenis — Tue, 11 Sep 2018 09:54:00 GMT

is your source full?
is it a one shot process or a dally one?
is oracle db a production db or an archive db?

replie are:
source full --> just use simple select
oracle db is production ! resources are for prod -->no store proc.
daly run --> check process duration
one shot --> think of migration failure

when writing job use tmap. do not use tjava for everything.

good luck.
your question is define global view to chose the best technical solution.
it's your job. I could not give you the perfect solution if I'm not in your place.
I can try to help you.
good luck

Re: How to retrieve historical data using some transformation rules in Talend-DI

Anonymous — Tue, 11 Sep 2018 10:16:26 GMT

Thank You for your prompt responses @fdenis

source full --> just use simple select --> Source is not full but on the process of Data warehousing.
oracle db is production ! resources are for prod -->no store proc. --> OK
daly run --> check process duration --> NO
one shot --> think of migration failure --> PERIODIC ( MONTHLY/WEEKLY/QUARTERLY) BASIS

In tMap, do i need to take the Date field and write the transformation function? If possible, could you give a sample query / expression used for my purpose?

+++

Re: How to retrieve historical data using some transformation rules in Talend-DI

fdenis — Tue, 11 Sep 2018 10:52:14 GMT

so yo are building a data warehouse.
Did you have a staging area? if yes, use it
if no code have to be into Talend, Standardized and Documented.
no sample is possible for me.