We help IT Professionals succeed at work.

New to ETL

royjayd
royjayd asked
on
hi guys

I am working in an environment where i am new. The following is the scenario we have

We have two projects: Peoplesoft project and java project( java UI with backend oracle)
We have data coming from PeopleSoft project to java project (in oracle database).
Data is exported from oracle (using Java UI) to Peoplesoft.

The above process is done using ETL and Autosys.


Any idea what ETL and Autosys is and how the above tasks are performed using ETL and autosys?

thanks
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
ETL is a generic term meaning: Extract Transform and Load.

I believe autosys is a generic job scheduling tool.

Author

Commented:
so is ETL  performed using sql queries ?
ETL is typically performed by SQL queries, some custom programs or tools available on the market.

Author

Commented:
ok..I have Oracle 9.x client and Oracle SQL Developer installed on my windows system. Can i run ETLs ?
I have worked with SQLs and store procedures but not ETL.

can you show me a simple sample ETL?

thanks
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
There is no 'set' tools for etl.  It is a concept.

Anything that will allow you to extraxt data, massage it if neccessary, and load it is 'etl.

I believe sql developer has a way to extract data to a CSV file or at the least I think the sqlworksheet will allow the sqlplus spool command.

That works as the 'E' in etl.
Extract:
create table staging_tab as
select * from test_table;

Transform:
update staging_tab set col1 = col1*2;

Load:
insert into final_table (select * from staging_tab);

I know that it's simple and it might not give you the good feeling about ETL, but without specific question it's difficult to help you.

Moreover typically Extract happens on the other systems that the Transform and Load. You typically extract data from online systems, them move them to data warehouse and there you transform and load.

Author

Commented:
thanks
How are Peoplesoft, oracle , ETL , Autosys related ?
Is there a good read on ETL which will help?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
They are not related.  Peoplesoft and Oracle are software products.

ETL is a concept not a 'thing' and like I mentioned, I believe autosys is a Job Scheduling product.

Have you Googled around on these topics?
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
ETL:
http://en.wikipedia.org/wiki/Extract,_transform,_load

Not sure what 'autosys' reference you are using but try this one:
http://en.wikipedia.org/wiki/CA_Workload_Automation_AE

Your team lead should be able to tell you what it is.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.