Rants. Difficulty to learn ETL tools?

[Database Administrator]

Dear Experts,

I've worked with Oracle since 1995. I have gone
very deep into many of the Oracle features, including
sqlloader, and export/import. And I've done data
modelling even longer.


At the same time, I have done ETL since 1995.
Although, at the Proc, and PLSQL, sqlloader, level.
Map the data. Take data from the source, do any
modifications/transformations that are required,
and insert or update. Easy. The hard part is
determining the mappings.


But I have not been given the opportunity to
work much with ETL tools such as:
Informatica, DataStage, Ascential, Ab Initio

The little bit of experience that I do have,
showed that Informatica was -incredibly- easy to use.
It's a GUI. It's SUPPOSED to be simple!!!


I have missed out on a number of opportunities
because I didn't have a few YEARS of experience
with ETL tools. Which seems odd, because the
tool looks like it requires a max of a week to learn!


Just how difficult are these ETL tools to learn?
Especially if you have already been working with
Oracle, and doing data mappings and loads since 1995?
I'm thinking that it can't be difficult at all.

When you search for newsgroups, there are no newsgroups
for these tools. Although the companies who make
them, might have their own newsgroup.


There are also not many books on these tools.
If you search for books on Oracle, or SQL Server,
you will find a lot of very big, and detailed
books. But there is next to nothing specifically
on the various ETL tools.

Any certifications for any of these tools look
to be much simpler than Oracle's.


To me, the real issue is the mappings. If you
come into a new environment, the data mappings
are completely esoteric to those systems.
No amount of experience with an ETL tool, or any
tool, is going to tell you what the mappings should be.


Questions:
- Just how difficult are these ETL tools to learn
for an experience Oracle pro like myself?

- Other than a GUI, making everything simple to use,
just what are the advantages of using ETL tools?

- what built in functionality do ETL tools
have, that can't be done in PLSQL?


Thanks a lot!

[Database Administrator]

I have used Data Junction, NEON (EDI/ETL tool from Sybase) and DTS/SSIS.
ETL tools in my experience, GUI or not, are NOT easy to master. They may be
easy to slap together a simple load. But once you get into more than that
it definitely takes time and study and 'practice' to become really
useful/productive with them. Which is why companies want to see a few years
experience.

--
TheSQLGuru
President
Indicium Resources, Inc.

wrote in message
news:1176659255.762245.187960@n59g2000hsh.googlegr oups.com...
> Dear Experts,
>
> I've worked with Oracle since 1995. I have gone
> very deep into many of the Oracle features, including
> sqlloader, and export/import. And I've done data
> modelling even longer.
>
>
> At the same time, I have done ETL since 1995.
> Although, at the Proc, and PLSQL, sqlloader, level.
> Map the data. Take data from the source, do any
> modifications/transformations that are required,
> and insert or update. Easy. The hard part is
> determining the mappings.
>
>
> But I have not been given the opportunity to
> work much with ETL tools such as:
> Informatica, DataStage, Ascential, Ab Initio
>
> The little bit of experience that I do have,
> showed that Informatica was -incredibly- easy to use.
> It's a GUI. It's SUPPOSED to be simple!!!
>
>
> I have missed out on a number of opportunities
> because I didn't have a few YEARS of experience
> with ETL tools. Which seems odd, because the
> tool looks like it requires a max of a week to learn!
>
>
> Just how difficult are these ETL tools to learn?
> Especially if you have already been working with
> Oracle, and doing data mappings and loads since 1995?
> I'm thinking that it can't be difficult at all.
>
> When you search for newsgroups, there are no newsgroups
> for these tools. Although the companies who make
> them, might have their own newsgroup.
>
>
> There are also not many books on these tools.
> If you search for books on Oracle, or SQL Server,
> you will find a lot of very big, and detailed
> books. But there is next to nothing specifically
> on the various ETL tools.
>
> Any certifications for any of these tools look
> to be much simpler than Oracle's.
>
>
> To me, the real issue is the mappings. If you
> come into a new environment, the data mappings
> are completely esoteric to those systems.
> No amount of experience with an ETL tool, or any
> tool, is going to tell you what the mappings should be.
>
>
> Questions:
> - Just how difficult are these ETL tools to learn
> for an experience Oracle pro like myself?
>
> - Other than a GUI, making everything simple to use,
> just what are the advantages of using ETL tools?
>
> - what built in functionality do ETL tools
> have, that can't be done in PLSQL?
>
>
> Thanks a lot!
>

[Database Administrator]

Hi,
even Oracle have some GUI tools :P

Have you take a look at Oracle Warehouse Builder (I think this one is
free now) or the new Oracle Data Integrator (ex: Sunopsis)?

pedro.


dba_222@yahoo.com wrote:
> Dear Experts,
>
> I've worked with Oracle since 1995. I have gone
> very deep into many of the Oracle features, including
> sqlloader, and export/import. And I've done data
> modelling even longer.
>
>
> At the same time, I have done ETL since 1995.
> Although, at the Proc, and PLSQL, sqlloader, level.
> Map the data. Take data from the source, do any
> modifications/transformations that are required,
> and insert or update. Easy. The hard part is
> determining the mappings.
>
>
> But I have not been given the opportunity to
> work much with ETL tools such as:
> Informatica, DataStage, Ascential, Ab Initio
>
> The little bit of experience that I do have,
> showed that Informatica was -incredibly- easy to use.
> It's a GUI. It's SUPPOSED to be simple!!!
>
>
> I have missed out on a number of opportunities
> because I didn't have a few YEARS of experience
> with ETL tools. Which seems odd, because the
> tool looks like it requires a max of a week to learn!
>
>
> Just how difficult are these ETL tools to learn?
> Especially if you have already been working with
> Oracle, and doing data mappings and loads since 1995?
> I'm thinking that it can't be difficult at all.
>
> When you search for newsgroups, there are no newsgroups
> for these tools. Although the companies who make
> them, might have their own newsgroup.
>
>
> There are also not many books on these tools.
> If you search for books on Oracle, or SQL Server,
> you will find a lot of very big, and detailed
> books. But there is next to nothing specifically
> on the various ETL tools.
>
> Any certifications for any of these tools look
> to be much simpler than Oracle's.
>
>
> To me, the real issue is the mappings. If you
> come into a new environment, the data mappings
> are completely esoteric to those systems.
> No amount of experience with an ETL tool, or any
> tool, is going to tell you what the mappings should be.
>
>
> Questions:
> - Just how difficult are these ETL tools to learn
> for an experience Oracle pro like myself?
>
> - Other than a GUI, making everything simple to use,
> just what are the advantages of using ETL tools?
>
> - what built in functionality do ETL tools
> have, that can't be done in PLSQL?
>
>
> Thanks a lot!
>

[Database Administrator]

OK. But I still have questions.


Is it because there are 3000 built in functions
and commands specific to the ETL tool, and a number
of new paradigms, that you first have to master?
Since I don't see big thick books on the tools,
if any, I don't believe this is the case.


Or, is it that the new esoteric mappings themselves are
difficult? They always are. In which case, I conclude
that it doesn't really matter whether you use an ETL tool, or
hard code it, the mappings will be difficult either way.

Actually, I can imagine that an ETL tool may actually
slow you down, because it's more limited in functionality
than hard coding. In which case, I can understand that
you will need to spend time with the tool, trying to
get it to do the things that it wasn't designed to do.
Is this the case?

The idea that I'm getting at is transferable skill sets.
If someone knows C++, they can learn java much easier
than one without the OO knowledge. If one knows databases
and SQL in depth, and can hard code ETL, this is also
a transferable skill set.



Again, Questions:
- Just how difficult are these ETL tools to learn
for an experienced Oracle pro like myself?

- Other than a GUI, making everything simple to use,
just what are the advantages of using ETL tools?

- what built in functionality do ETL tools
have, that can't be done in PLSQL?

[Database Administrator]

1) The kajillion functions and their syntax/calling mechanisms is one
'masterization' impediment. I will add that you don't see books on these
tools simply because there isn't enough licensed copies of them in existence
for publishers/authors to make any money.

2) Yes, EDI mappings can be extremely difficult/complex. Some tools handle
differing flavors of these better than others. I doubt any one of them does
well at all the different spec's out there.

3) I agree that an ETL tool will actually be an IMPEDIMENT to productivity
IF you are not INTIMATELY FAMILIAR with that tools interface, flow logic,
conditional logic, and functional capabilities.

4) As far as the tools go, I do not believe there is such a thing as
'transferrable skills', unless several of them use VBScript (DataJunction
does this) or some such. Your comparison to C++/Java or SQL is flawed, in
that those are VERY limited, pretty much fixed sets of
keywords/logic/processing.

To answer your reiterated questions:

A) I don't think the ETL tools are necessarily difficult to learn, they will
just take time/practice with them. Still, if "I" were a recruiter, I
wouldn't give you the time of day for anything other than an entry level
ETL-tool job unless you already had significant experience and /or training
on the product my company used.

B) The primary advantages to ETL tools are that that provide a (reusable)
framework for logic/flow/parsing and built in capabilities for data/file
movement and error handling. I would NOT want to hand-craft a system to be
able to parse and process an ANSI X12 document!!

C) I don't know much about PLSQL, but if it is a robust G3/4 language with
very good data handling, conditional, procedural and I/O capabilities then
you probably could roll your own ETL with it. Good luck with that if you
try it. :-)

--
TheSQLGuru
President
Indicium Resources, Inc.

wrote in message
news:1176995593.936269.18150@y80g2000hsf.googlegro ups.com...
> OK. But I still have questions.
>
>
> Is it because there are 3000 built in functions
> and commands specific to the ETL tool, and a number
> of new paradigms, that you first have to master?
> Since I don't see big thick books on the tools,
> if any, I don't believe this is the case.
>
>
> Or, is it that the new esoteric mappings themselves are
> difficult? They always are. In which case, I conclude
> that it doesn't really matter whether you use an ETL tool, or
> hard code it, the mappings will be difficult either way.
>
> Actually, I can imagine that an ETL tool may actually
> slow you down, because it's more limited in functionality
> than hard coding. In which case, I can understand that
> you will need to spend time with the tool, trying to
> get it to do the things that it wasn't designed to do.
> Is this the case?
>
> The idea that I'm getting at is transferable skill sets.
> If someone knows C++, they can learn java much easier
> than one without the OO knowledge. If one knows databases
> and SQL in depth, and can hard code ETL, this is also
> a transferable skill set.
>
>
>
> Again, Questions:
> - Just how difficult are these ETL tools to learn
> for an experienced Oracle pro like myself?
>
> - Other than a GUI, making everything simple to use,
> just what are the advantages of using ETL tools?
>
> - what built in functionality do ETL tools
> have, that can't be done in PLSQL?
>
>
>
>
>

[Database Administrator]

1) The kajillion functions and their syntax/calling mechanisms is one
'masterization' impediment. I will add that you don't see books on these
tools simply because there isn't enough licensed copies of them in existence
for publishers/authors to make any money.

2) Yes, EDI mappings can be extremely difficult/complex. Some tools handle
differing flavors of these better than others. I doubt any one of them does
well at all the different spec's out there.

3) I agree that an ETL tool will actually be an IMPEDIMENT to productivity
IF you are not INTIMATELY FAMILIAR with that tools interface, flow logic,
conditional logic, and functional capabilities.

4) As far as the tools go, I do not believe there is such a thing as
'transferrable skills', unless several of them use VBScript (DataJunction
does this) or some such. Your comparison to C++/Java or SQL is flawed, in
that those are VERY limited, pretty much fixed sets of
keywords/logic/processing.

To answer your reiterated questions:

A) I don't think the ETL tools are necessarily difficult to learn, they will
just take time/practice with them. Still, if "I" were a recruiter, I
wouldn't give you the time of day for anything other than an entry level
ETL-tool job unless you already had significant experience and /or training
on the product my company used.

B) The primary advantages to ETL tools are that that provide a (reusable)
framework for logic/flow/parsing and built in capabilities for data/file
movement and error handling. I would NOT want to hand-craft a system to be
able to parse and process an ANSI X12 document!!

C) I don't know much about PLSQL, but if it is a robust G3/4 language with
very good data handling, conditional, procedural and I/O capabilities then
you probably could roll your own ETL with it. Good luck with that if you
try it. :-)

--
TheSQLGuru
President
Indicium Resources, Inc.

wrote in message
news:1176995593.936269.18150@y80g2000hsf.googlegro ups.com...
> OK. But I still have questions.
>
>
> Is it because there are 3000 built in functions
> and commands specific to the ETL tool, and a number
> of new paradigms, that you first have to master?
> Since I don't see big thick books on the tools,
> if any, I don't believe this is the case.
>
>
> Or, is it that the new esoteric mappings themselves are
> difficult? They always are. In which case, I conclude
> that it doesn't really matter whether you use an ETL tool, or
> hard code it, the mappings will be difficult either way.
>
> Actually, I can imagine that an ETL tool may actually
> slow you down, because it's more limited in functionality
> than hard coding. In which case, I can understand that
> you will need to spend time with the tool, trying to
> get it to do the things that it wasn't designed to do.
> Is this the case?
>
> The idea that I'm getting at is transferable skill sets.
> If someone knows C++, they can learn java much easier
> than one without the OO knowledge. If one knows databases
> and SQL in depth, and can hard code ETL, this is also
> a transferable skill set.
>
>
>
> Again, Questions:
> - Just how difficult are these ETL tools to learn
> for an experienced Oracle pro like myself?
>
> - Other than a GUI, making everything simple to use,
> just what are the advantages of using ETL tools?
>
> - what built in functionality do ETL tools
> have, that can't be done in PLSQL?
>
>
>
>
>

[Database Administrator]

Hi DBA...
your append is exactly what I have been talking about since the mid
90s as well....making ETL easier......

We have invented the future and the future of ETL is 'generated ETL
from the data mapping workbook'. (www.instantbi.com)

You have to do your data mapping somehow, and excel is how most people
do it, the laggards are still using word......

Since you already have to do your data mapping, and if you are
sensible you do it in excel, it makes the most sense to generate the
etl subsystem directly from the workbook as well as publish the
workbook via the web so that authorised people can see any and all
details of the ETL subsystem.

No ETL subsystem will ever be any easier to develop and deploy than
what we have invented because no ETL subsystem will ever be easier to
build than a direct generate from the mapping workbook.....this is the
'end game' for development of ETL subsystems.

Why use such a tool rather than PL/SQL.....well, because it is
generated directly from the workbook we have 'done away with' the ETL
programmer.....and that is a good thing. I have done far too much ETL
programming over the years and I want to get rid of that complete
waste of time....

What can it do that you can't do in PL/SQL? Well, some nice things are
we can parallelise the processing of large numbers of fact records and
we can put the dimension tables in memory mapped IO and access them in
a shared fashion using binary search......this is 10x faster than
doing the same in PL/SQL at runtime....

Also, we have intelligence built into it that means you can do things
like add new summaries without any code changes, you can add new keys
to fact tables without any code changes, you can make lookups into
dimension tables to get new keys FAR more complex than possible than
via normal sql statements.

In short, we have eliminated all the 'coding' effort that is required
when writing you ETL subsystem no matter what the tool.....and we have
done it in such a way that it is as scalable as the operating system
underneath....

Another BIG feature is that the ETL subsystem is portable across
databases and operating systems....something that PL/SQL and SSIS are
obvioulsy not.....this means that if some better/faster database comes
out we can move to it......not something that MSFT would like to her
and this is their newsgroup.....but it has always been a belief of
mine that the ETL subsystem should be fully portable across OS and
database.....and surprisingly, this is NOT the case with any of the
ETL tools that I have seen....they all require quite some effort to
move them.....thereby creating a cost to adopt a faster/cheaper/better
database.

If you are keenly interested, feel free to check my personal site
www.peternolan.com where I have published vast amounts of code and
documentation on ETL subsystems.

Best Regards

Peter
www.peternolan.com

[Database Administrator]

Hi DBA,
a couple of other comments....

1. Skills are not particularly transferrable between the leading ETL
tools like Informatica and DataStage....and worse, when people learn
one or the other they become 'religiously convinced' that the one they
learned is the best and tend to be worse than a newbie if they are
forced to transfer to the other.....just human nature...

2. The ETL tools are evolving rapidly and so there is significant need
to stay up to date.....just like any other piece of complex software.

3. ETL tools make the 'simple things simpler' and the 'harder things
just about impossible'. A few years back I was working on a project
where the ETL tool forced changes to the data model because the tool
could not load the data model as designed. If that is not the cart
before the horse I do now know what is.

4. By making the 'simple things simpler' it has been possible to
outsouce more development to lower skilled people. And a lot of that
has gone to India. There are some things workable and some unworkable
about this.....if you are outsourcing your ETL development then you
need at least one good ETL person on site as an employee so you know
you are getting 'good stuff' back.

5. The ETL tools are generic general purpose tools that are then in
some way 'coded'. They exhibit the exact same properties of other
source code environments...it makes no difference that the 'source
code' is presented as squares joined by lines in a GUI...it is still
source code of a fashion...it still needs to be tested every time
something changes....


And just as an amusing aside....I recently did a project where we used
our ETL tools to build the prototype and then recoded everything into
Informatica. We hired one of the leading Indian IT companies to do the
conversion of our prototype ETL to Informatica.....their lead person
looked at what we were giving him and the first question out of his
mouth was 'Why do you need us? You already have a working ETL
subsystem?' to which the answer was 'Well, the corporate standard is
Informatica, and your job is to convert this into Informatica. And you
get paid for doing so.'....and they were happy campers.... ;-)

Best Regards

Peter

www.peternolan.com

[Database Administrator]

Peter Nolan wrote:
>
> Also, we have intelligence built into it
>
> Peter
> www.peternolan.com

Too bad that "intelligence" is insufficient to read a usenet
group's charter and not self-label its creator as a spammer.

Angering your potential customers is rarely a successful strategy.

An apology at comp.databases.oracle.tools would be appropriate.
The only group allowed for postings such as yours is c.d.o.marketplace.
--
Daniel A. Morgan
Puget Sound Oracle Users Group
www.psoug.org

[Database Administrator]

what you can't run multiple PL/SQL statements at the same time in Oracle?

ROFL


"Peter Nolan" wrote in message
news:1177319493.688345.26010@o5g2000hsb.googlegrou ps.com...
> Hi DBA...
> your append is exactly what I have been talking about since the mid
> 90s as well....making ETL easier......
>
> We have invented the future and the future of ETL is 'generated ETL
> from the data mapping workbook'. (www.instantbi.com)
>
> You have to do your data mapping somehow, and excel is how most people
> do it, the laggards are still using word......
>
> Since you already have to do your data mapping, and if you are
> sensible you do it in excel, it makes the most sense to generate the
> etl subsystem directly from the workbook as well as publish the
> workbook via the web so that authorised people can see any and all
> details of the ETL subsystem.
>
> No ETL subsystem will ever be any easier to develop and deploy than
> what we have invented because no ETL subsystem will ever be easier to
> build than a direct generate from the mapping workbook.....this is the
> 'end game' for development of ETL subsystems.
>
> Why use such a tool rather than PL/SQL.....well, because it is
> generated directly from the workbook we have 'done away with' the ETL
> programmer.....and that is a good thing. I have done far too much ETL
> programming over the years and I want to get rid of that complete
> waste of time....
>
> What can it do that you can't do in PL/SQL? Well, some nice things are
> we can parallelise the processing of large numbers of fact records and
> we can put the dimension tables in memory mapped IO and access them in
> a shared fashion using binary search......this is 10x faster than
> doing the same in PL/SQL at runtime....
>
> Also, we have intelligence built into it that means you can do things
> like add new summaries without any code changes, you can add new keys
> to fact tables without any code changes, you can make lookups into
> dimension tables to get new keys FAR more complex than possible than
> via normal sql statements.
>
> In short, we have eliminated all the 'coding' effort that is required
> when writing you ETL subsystem no matter what the tool.....and we have
> done it in such a way that it is as scalable as the operating system
> underneath....
>
> Another BIG feature is that the ETL subsystem is portable across
> databases and operating systems....something that PL/SQL and SSIS are
> obvioulsy not.....this means that if some better/faster database comes
> out we can move to it......not something that MSFT would like to her
> and this is their newsgroup.....but it has always been a belief of
> mine that the ETL subsystem should be fully portable across OS and
> database.....and surprisingly, this is NOT the case with any of the
> ETL tools that I have seen....they all require quite some effort to
> move them.....thereby creating a cost to adopt a faster/cheaper/better
> database.
>
> If you are keenly interested, feel free to check my personal site
> www.peternolan.com where I have published vast amounts of code and
> documentation on ETL subsystems.
>
> Best Regards
>
> Peter
> www.peternolan.com
>