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!
19  4153 
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.
<db*****@yahoo.comwrote in message
news:11**********************@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!
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?
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.
<db*****@yahoo.comwrote in message
news:11*********************@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?
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.
<db*****@yahoo.comwrote in message
news:11*********************@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?
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
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
what you can't run multiple PL/SQL statements at the same time in Oracle?
ROFL
"Peter Nolan" <pe***@peternolan.comwrote in message
news:11*********************@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
On May 5, 2:13 am, "Aaron Kempf" <ake...@dol.wa.govwrote:
what you can't run multiple PL/SQL statements at the same time in Oracle?
ROFL
"Peter Nolan" <p...@peternolan.comwrote in message
news:11*********************@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.comwhere I have published vast amounts of code and
documentation on ETL subsystems.
Best Regards
Peter
www.peternolan.com- Hide quoted text -
- Show quoted text -
How about instead of ETL we do ELT? Actually this is the line we are
going to take for one of our projects using SSIS for loading. I didn't
know about ETL tools until this post.
Hi Araron,
"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.... "
The most cpu intensive task in building a dimensional model is the
tranlsation of real keys into integer keys...the attribution process.
Doing this work in PL/SQL or inside the database as ELT type tools do
consumes much more (10x or more) processing power than is required if
you do it more efficiently.....and since this is the most expensive
bit, it's worth speeding up in large accounts...
Yes, of course, you can run multiple pl/sql statements at the same
time to do this work if you would like.
However, one thing we have been doing for about 7 years how is putting
a unique key on the front of fact table records. And when processing a
single set of transactions that have been split into different files
to enable parallelism you have to somehow allow for the allocation of
keys for these unqiue ids on the front of the fact records...this
means a semaphor at the file level, locks etc, so the pl/sql to handle
that becomes more difficult...especially when you consider you cannot
just allocate one key at a time and do the update because it slows
things down too much. You must be able to allocate blocks of
keys.......the ETL tools have these features in place because they are
so necessary.
Many people say to me, well, just let the database allocate a sequence
number....but in most databases these are slow....and they also give
you trouble if you want to delete and reload data because it is often
not so easy to reset the sequence number so that the delete/reloaded
records get the same sequence number they used to have......
We also do things like...when we need to update records it is often
faster to delete them and then to use the loader to load the record
rather than do an update. In these cases the sequence numbers must be
respected so using the database to allocate them does not work if you
want to do a delete/load rather than an update.
And yes, PL/SQL can do a good part of all this if you want to write
it...but it is far from trivial......and it will run 10x or more
slower than doing it properly....and that is well worth the effort in
most sizable accounts.....plenty of DW projects fail because the ETL
subsystem is too slow, too expensive, and too difficult to change.
Best Regards
Peter
On May 4, 10:13 pm, "Aaron Kempf" <ake...@dol.wa.govwrote:
what you can't run multiple PL/SQL statements at the same time in Oracle?
ROFL
"Peter Nolan" <p...@peternolan.comwrote in message
news:11*********************@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.comwhere I have published vast amounts of code and
documentation on ETL subsystems.
Best Regards
Peter
www.peternolan.com- Hide quoted text -
- Show quoted text -
Peter Nolan wrote:
Hi Araron,
"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.... "
The most cpu intensive task in building a dimensional model is the
tranlsation of real keys into integer keys...the attribution process.
Doing this work in PL/SQL or inside the database as ELT type tools do
consumes much more (10x or more) processing power than is required if
you do it more efficiently.....and since this is the most expensive
bit, it's worth speeding up in large accounts...
Yes, of course, you can run multiple pl/sql statements at the same
time to do this work if you would like.
However, one thing we have been doing for about 7 years how is putting
a unique key on the front of fact table records. And when processing a
single set of transactions that have been split into different files
to enable parallelism you have to somehow allow for the allocation of
keys for these unqiue ids on the front of the fact records...this
means a semaphor at the file level, locks etc, so the pl/sql to handle
that becomes more difficult...especially when you consider you cannot
just allocate one key at a time and do the update because it slows
things down too much. You must be able to allocate blocks of
keys.......the ETL tools have these features in place because they are
so necessary.
Many people say to me, well, just let the database allocate a sequence
number....but in most databases these are slow....and they also give
you trouble if you want to delete and reload data because it is often
not so easy to reset the sequence number so that the delete/reloaded
records get the same sequence number they used to have......
We also do things like...when we need to update records it is often
faster to delete them and then to use the loader to load the record
rather than do an update. In these cases the sequence numbers must be
respected so using the database to allocate them does not work if you
want to do a delete/load rather than an update.
And yes, PL/SQL can do a good part of all this if you want to write
it...but it is far from trivial......and it will run 10x or more
slower than doing it properly....and that is well worth the effort in
most sizable accounts.....plenty of DW projects fail because the ETL
subsystem is too slow, too expensive, and too difficult to change.
Best Regards
Peter
On May 4, 10:13 pm, "Aaron Kempf" <ake...@dol.wa.govwrote:
>what you can't run multiple PL/SQL statements at the same time in Oracle?
ROFL
"Peter Nolan" <p...@peternolan.comwrote in message
news:11*********************@o5g2000hsb.googlegro ups.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.comwhere I have published vast amounts of code and
documentation on ETL subsystems.
Best Regards
Peter
www.peternolan.com- Hide quoted text -
- Show quoted text -
Your last actual experience with Oracle was how many versions ago?
--
Daniel A. Morgan
University of Washington da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
you're full of crap
you can't do any of this IN ORACLE
it's all a seperate tool you have to purchase
Oracles just plain stupid; I wish that MS had some real competition
as it is; MS won the olap war - www.olapreport.com and you're just
plain fucking stupid if you're not using Analysis Services
On May 16, 9:00 am, Peter Nolan <p...@peternolan.comwrote:
Hi Araron,
"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.... "
The most cpu intensive task in building a dimensional model is the
tranlsation of real keys into integer keys...the attribution process.
Doing this work in PL/SQL or inside the database as ELT type tools do
consumes much more (10x or more) processing power than is required if
you do it more efficiently.....and since this is the most expensive
bit, it's worth speeding up in large accounts...
Yes, of course, you can run multiple pl/sql statements at the same
time to do this work if you would like.
However, one thing we have been doing for about 7 years how is putting
a unique key on the front of fact table records. And when processing a
single set of transactions that have been split into different files
to enable parallelism you have to somehow allow for the allocation of
keys for these unqiue ids on the front of the fact records...this
means a semaphor at the file level, locks etc, so the pl/sql to handle
that becomes more difficult...especially when you consider you cannot
just allocate one key at a time and do the update because it slows
things down too much. You must be able to allocate blocks of
keys.......the ETL tools have these features in place because they are
so necessary.
Many people say to me, well, just let the database allocate a sequence
number....but in most databases these are slow....and they also give
you trouble if you want to delete and reload data because it is often
not so easy to reset the sequence number so that the delete/reloaded
records get the same sequence number they used to have......
We also do things like...when we need to update records it is often
faster to delete them and then to use the loader to load the record
rather than do an update. In these cases the sequence numbers must be
respected so using the database to allocate them does not work if you
want to do a delete/load rather than an update.
And yes, PL/SQL can do a good part of all this if you want to write
it...but it is far from trivial......and it will run 10x or more
slower than doing it properly....and that is well worth the effort in
most sizable accounts.....plenty of DW projects fail because the ETL
subsystem is too slow, too expensive, and too difficult to change.
Best Regards
Peter
On May 4, 10:13 pm, "Aaron Kempf" <ake...@dol.wa.govwrote:
what you can't run multiple PL/SQL statements at the same time in Oracle?
ROFL
"Peter Nolan" <p...@peternolan.comwrote in message
news:11*********************@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.comwhereI have published vast amounts of code and
documentation on ETL subsystems.
Best Regards
Peter
>www.peternolan.com-Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -
Susie DBA [MSFT] wrote:
you're full of crap
Well nothing like advertising your professionalism to the industry.
you can't do any of this IN ORACLE
Of course you can't. Fortune 500 companies always purchase products
that don't work.
it's all a seperate tool you have to purchase
And the name of that tool is?
Oracles just plain stupid; I wish that MS had some real competition
Of course you do. Whatever happened to Lotus 123?
you're just
plain fucking stupid if you're not using Analysis Services
Another impressive example of your professionalism.
You are a credit to Microsoft. When are they going to award you
your well deserved Evangelist of the Year award? I've no doubt
Ballmer is wishing there were thousands more like you to promote
his company and its products. Keep up the good work. It is always
wonderful to see a teenager so engaged in industry.
--
Daniel A. Morgan
University of Washington da******@x.washington.edu
(replace x with u to respond)
On May 24, 8:30 am, DA Morgan <damor...@psoug.orgwrote:
Susie DBA [MSFT] wrote:
you're full of crap
Well nothing like advertising your professionalism to the industry.
you can't do any of this IN ORACLE
Of course you can't. Fortune 500 companies always purchase products
that don't work.
it's all a seperate tool you have to purchase
And the name of that tool is?
Oracles just plain stupid; I wish that MS had some real competition
Of course you do. Whatever happened to Lotus 123?
you're just
plain fucking stupid if you're not using Analysis Services
Another impressive example of your professionalism.
You are a credit to Microsoft. When are they going to award you
your well deserved Evangelist of the Year award? I've no doubt
Ballmer is wishing there were thousands more like you to promote
his company and its products. Keep up the good work. It is always
wonderful to see a teenager so engaged in industry.
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Wow, I've watched this ridiculous post go on and on and on and on and
I just can't take it anymore.
To Mr. 'DBA' self proclaimed Oracle 'expert'. I have seen this same
post on several other groups, as well as other posts by yourself. You
are nothing but ignorant, arrogant and argumentative. If you really
want to validate your 'argument', stop your incessant WHINING and
learn the damn ETL tools you are so critical of. Either way, if you
refuse to be versatile, you may survive in this business, but you will
go nowhere. You're just one of those poor slobs that is going to
continue to beat your head up against the wall and wail about the
unfairness of it all until you're out of a job, or a decent one
anyway. I think the only reason you've taken this absurd rant, as you
so aptly put it, to the internet is most likely because your coworkers
can't stand your whining anymore.
Good GOD man, shut the hell up and learn something. Ignorance never
makes a good argument. But you don't WANT to learn anything do you.
You just want to whine and complain and stomp your little Oracle-
Expert-DBA-freaking-feet.
WAH! Some call a WAH-mbulance for this moron. su***********@gmail.com wrote:
>>it's all a seperate tool you have to purchase
>And the name of that tool is?
Good GOD man, shut the hell up and learn something. Ignorance never
makes a good argument.
But obscenities do? Enlightening.
So, for the second time, what is the name of that tool that must
be purchased separately?
--
Daniel A. Morgan
University of Washington da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
On May 24, 12:57 pm, DA Morgan <damor...@psoug.orgwrote:
susansands...@gmail.com wrote:
>it's all a seperate tool you have to purchase
And the name of that tool is?
Good GOD man, shut the hell up and learn something. Ignorance never
makes a good argument.
But obscenities do? Enlightening.
So, for the second time, what is the name of that tool that must
be purchased separately?
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
Do your own research. You don't want to be enlightened or you would
have done it yourself instead of kicking back and expecting the world
at large to give you the answer.
There are many - our agency uses the new SSIS on SQL2005. You don't have
to be using SQL - it's merely a free ETL tool you get with SQL. Very, very
powerful.
<su***********@gmail.comwrote in message
news:11**********************@x35g2000prf.googlegr oups.com...
On May 24, 12:57 pm, DA Morgan <damor...@psoug.orgwrote:
>susansands...@gmail.com wrote:
>>it's all a seperate tool you have to purchase
And the name of that tool is?
Good GOD man, shut the hell up and learn something. Ignorance never
makes a good argument.
But obscenities do? Enlightening.
So, for the second time, what is the name of that tool that must
be purchased separately?
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
Do your own research. You don't want to be enlightened or you would
have done it yourself instead of kicking back and expecting the world
at large to give you the answer.
su***********@gmail.com wrote:
On May 24, 12:57 pm, DA Morgan <damor...@psoug.orgwrote:
>susansands...@gmail.com wrote:
>>>>it's all a seperate tool you have to purchase
And the name of that tool is?
Good GOD man, shut the hell up and learn something. Ignorance never
makes a good argument.
But obscenities do? Enlightening.
So, for the second time, what is the name of that tool that must
be purchased separately?
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
Do your own research. You don't want to be enlightened or you would
have done it yourself instead of kicking back and expecting the world
at large to give you the answer.
Here's who I am: http://www.oracle.com/technology/com...e2.html#morgan http://www.outreach.washington.edu/e...ad/oad_ins.asp http://www.psoug.org/about.html
A somewhat public figure and slightly knowledgeable with respect to
Oracle and databases in general.
Try something different. Instead of throwing around your schoolyard
obscenities try stretching for a bit of integrity. Try answering the
question. No doubt everyone else in all these usenet groups will want
to benefit from your knowledge and wisdom. And, as you said I am a
stupid person so I couldn't possibly know what you know.
We will draw our own conclusions based on your response or lack thereof.
--
Daniel A. Morgan
University of Washington da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org
On May 24, 4:58 pm, DA Morgan <damor...@psoug.orgwrote:
susansands...@gmail.com wrote:
On May 24, 12:57 pm, DA Morgan <damor...@psoug.orgwrote:
susansands...@gmail.com wrote:
it's all a seperate tool you have to purchase
And the name of that tool is?
Good GOD man, shut the hell up and learn something. Ignorance never
makes a good argument.
But obscenities do? Enlightening.
So, for the second time, what is the name of that tool that must
be purchased separately?
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
Do your own research. You don't want to be enlightened or you would
have done it yourself instead of kicking back and expecting the world
at large to give you the answer.
Here's who I am:http://www.oracle.com/technology/com...org/about.html
A somewhat public figure and slightly knowledgeable with respect to
Oracle and databases in general.
Try something different. Instead of throwing around your schoolyard
obscenities try stretching for a bit of integrity. Try answering the
question. No doubt everyone else in all these usenet groups will want
to benefit from your knowledge and wisdom. And, as you said I am a
stupid person so I couldn't possibly know what you know.
We will draw our own conclusions based on your response or lack thereof.
--
Daniel A. Morgan
University of Washington
damor...@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Groupwww.psoug.org
A. I have seen your posts on this and other groups and they are far
from dignified. You belittle others who ask questions about
applications you feel are inferior (which is anything but Oracle).
You criticize and demean. You ask for advice and then you insult the
person who gave it to you.
B. I, unlike yourself, do not claim to be an expert. I suspect that
you do not want to benefit from others' knowledge and experience or
you would have taken some of the good advice you have been given and
tried it by now.
C. Credentials are meaningless. How you treat people, and how you
weather adversity is a much better indicator of a person's character.
Whining is not a verb.
D. Don't think of what I said as schoolyard obscenities, think of it
more as tough love. If your employment hinges on whether or not you
can or will learn something other than Oracle, then I suggest you do
it. As the previous post says, SSIS is a very good ETL tool.
E. I did not say you were stupid, I said you were ignorant. Someone
as smart and worldly as yourself should know there is a huge
difference between the two.
F. Do you have a mouse in your pocket? You just caught me on a bad
day. I'm sure the vast majority of usenet users are as unimpressed
and/or annoyed as I am, they're just better at controlling the urge to
reply to your silliness.
That's all I have to say about that. I will not respond again, but I
have a feeling you will carry on. I've seen enough to know it's the
banter you're after, not the knowledge. su***********@gmail.com wrote:
A. I have seen your posts on this and other groups and they are far
from dignified.
Lacking, I presume, in the classy touch of schoolyard obscenities. I'll
work on that.
You belittle others who ask questions about
applications you feel are inferior (which is anything but Oracle).
Unlike you who show respect for other applications vendor's products
and their customers. I am so ashamed.
B. I, unlike yourself, do not claim to be an expert.
Good.
C. Credentials are meaningless.
Tell that to your physician, your dentist, and your accountant. I know
they will be as impressed as I am.
D. Don't think of what I said as schoolyard obscenities, think of it
more as tough love.
Try reading for comprehension.
E. I did not say you were stupid, I said you were ignorant. Someone
as smart and worldly as yourself should know there is a huge
difference between the two.
So now I am smart and worldly? Please make up your mind. I was just
getting used to being the dullest knife in the drawer.
F. Do you have a mouse in your pocket? You just caught me on a bad
day.
Then consider a sincere apology.
That's all I have to say about that. I will not respond again,....
Which pointedly demonstrates that your statement about Oracle and its
products was pure nonsense. Thank you for acknowledging that fact by,
yet again, not answering the question.
You can put the shovel down now.
--
Daniel A. Morgan
University of Washington da******@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group www.psoug.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Elijah |
last post by:
I have read many of the topics on learning C++ or Java first.
It seems like everyone says something different.
I would like to know if I should learn C++ or Java.
First a little about myself.
I...
|
by: TAM |
last post by:
Hi,
I read that ASP.NET uses VB.NET instead of VBScript. I also read that
ASP.NET is a subset of VB.NET. So if I learn VB.NET first then do I have the
knowledge for programming ASP.NET...
|
by: patricia59 |
last post by:
Can someone suggest a really elementary learning forum
for vb.net? I am trying to learn VB.net web in
conjunction with MS Sql data access. My previous
experience has been with desktop databases,...
|
by: SStory |
last post by:
Hello Group.
I am a VB/VB.NET programmer. I have done some C/C++ years ago.
I am considering a job opportunity which is unfortunately in C# and not
VB.NET.
What is the group's opinion on...
|
by: CoreyWhite |
last post by:
C++ is different from C, because C was built to execute algorithms and
solve mathematical problems quickly. C++ is designed to work with
special data types that hold strings of abstract...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |
