Here is my 2 cents -- yes, any tool can get in the way, any tool can be useful -- it is 100% a two way street there. Taken to illogical extremes, anything can be bad like that.

On of the important things in a data warehouse is documentation, "where did this bit of data come from", "what was done to this data over time", "why do you give me X as the answer when this other thing gives me Y as the answer". In order to answer those questions AND keep all of the books as to what comes from where and when - I think you'll find OWB pretty good at that. It is to the point where you can be sitting looking at a report and because our stuff knows about OWB -- you can click on a data element in the report and trace its lineage back through the system to see where it came from. So, I can see this data is actually merged data from two systems where system1 was added to system2 and system1 was my mainfram VSAM files and system2 was some relational data from my new web interface on the internet. This is more then just a "neat" function -- this will save the analyst hours when asked to justify their numbers. This will save days verifying the system is getting stuff from where it should be. In short -- one of the predominant "features" of OWB is the repository -- the bookkeeping it does. It automates many of the mundane tasks you would otherwise.

By "ETL" engine we mean much of what you actually said. We have the gateways to everything (part of iAS). That gives us great "E" capability. Inside the database with things like merge, multi-table inserts, external tables, pipelined functions, transportable tablespaces and the like -- we have the "TL" parts, the transform and load. Whereas most ETL tools take a pure "dump", "transform", "load" approach -- hitting the file system 2, 3, 4 times along the way -- we just extract, tranform and load in as few steps as we can - you even have the ability to do the ETL in one continous step -- no intermediate files, no intermediate steps.


And I asked Derrick Cameron, our resident BI/DW/Tools guru to respond as well and he says:

Firstly, I think it is important to recognize there are a great many
successful data warehouse implementation projects that did not use an
ETL tool. Not surprising considering ETL tools are still early in the
evolution of this technology. I totally agree about the use of
sqlloader (actually, external tables now), sql, plsql as the optimal way
to load and process data in an Oracle database, as well as your other
comments about database features. I too have worked on data warehousing
projects without the use of an ETL tool so your comments have merit.

So what does an ETL tool, and in particular OWB, bring to a data
warehousing project? Many good things.

1. Automated code generation. OK, we're coders. We can do that.
There's more to it than that. In a team environment you varying levels
of expertise, techniques, and approaches, etc. OWB generates all this
code (sql, plsql, tcl scripts, etc.) in a standardized way that so
implementation and maintenance is consistent, and much easier to
maintain, than trying to figure out how someone else coded the
transformations. Some projects may have only semi technical staff and
coding may be difficult for them. A non-issue with OWB. It is also
quite common for development staff to continue to do things the way they
know how rather than adopting the latest best practices, such as
leveraging the 9i ETL functions such as external tables, merge
processing, pipelined transformations, etc. To address one of the
questions about OWB and the 9i ETL engine - the point is OWB generates
code that takes advantage of database ETL features such as merge, multi
table insert, external tables, etc. The OWB development group work VERY
closely with the server technologies group to ensure optimal techniques
are used. Its a very collaborative development environment. They are
right up to date with the latest server features, leveraging all the
things you raise in your comments, but implemented consistently in a
standardized way.

2. Utilities. In our custom development project we built a whole
infrastructure around process management and control. I'm talking about
auditing the load jobs, monitoring, reporting, tracking, etc. jobs and
processes. This is part of what a tool brings to a project. It's one
thing to code a transformation and another to log these and know when
the jobs were run, how long they took, what the results were, how the
exceptions were handled, etc. In a large production warehouse these
become important.

3. Documentation. Ever used Excel spreadsheets to document flows from
source to target? What a pain. I have. Dreary work. Never
maintained. OWB provides extensive reporting of all the metadata in the
repository, including lineage tracking. Better yet, end users can drill
from a workbook in Discoverer (end user ad hoc query tool) back to the
source - target mapping to determine where the data came from. This is
not developed for each workbook but come as a result of building the
mappings in the tools and exploiting the tool integration.

4. This brings me to inter-tool integration. ETL is not the end goal.
It's getting information to end users. Without an ETL tool you start
from scratch in reporting. OWB allow you to build your reporting End
User metadata layer (EUL) as you develop ETL processes. In fact,
updates to the EUL are now (OK, maybe in a month) real time. The saves
a lot of effort building the business metadata reporting layer. This
also extends to OLAP. OWB generates multidimensional OLAP Cubes in
Oracle 9i, and provides the support for cube loading and OLAP metadata.
Again, you're building from scratch there without the ETL tool. Same
goes for Designer. We're really just scratching the surface here. The
bottom line - Datawarehousing is a lot more than just ETL, and tools
(those that are integrated that is), save a lot of development and
maintenance time and effort down stream.

Maybe two or three years ago a lot of this was not there. Its come a
long way and should now be a serious consideration for anyone building a
data warehouse.

see

</code> http://otn.oracle.com/products/warehouse/content.html <code>

Yes it can, you have gateways to pretty much all relational and many non-relational data stores. OWB works with them all. If you have OWB as part of iAS, you have the gateways as well

good question for support -- i've never run win98, no experience with it.

support says......


O/S Information:
Certification of this product on MS Windows 98 is not planned.

in 10g, it'll be.

in 9i timeframe, it is part of iAS licensing

hmm, doh, yes -- ids, sorry about that.

in 10g, DB
in 9i and before ids

OWB -> builds the warehouse that
end users query with Disco
power users use jdev with data mining/bi beans wizards to mine
developers build olap apps against with jdev/bi beans

please contact support for installation and configuration issues (but good luck with really old software on really new os's -- if you want a bit of "old" you have to go "all old" like windows NT as both 8i and 8 predate XP by many many years)

it is saying "you need a EE database (not much of a data warehouse without EE)" and the developer suite, that looks correct.

windows98 is a single user, desktop operating system. it is not a server platform.

You might have been able to get personal edition to load way back when (no server capabilities really), but given that MS itself doesn't support this, neither do we.

I believe it would have worked for personal edition, but I never used that OS so I cannot verify. As a server -- no, not at all.

otn.oracle.com -> discussion forums

Forums Home
Technologies
Data Warehousing and Business Intelligence

I'd ask the makers of datastage what products they support?

exceptions propagate up to the client - as all good code should let them do.

It is very bad to catch exceptions you are NOT expecting! You let unhandled (un-expected) exceptions cause the process to fail. Errors go to top level - where they can be reported on.

Catching them at the low level would only cause them to be "hidden" from the client - the most important thing to not withhold them from.

that paper predates the release of 10gR2 by a long long long time.

OWB predominantly creates plsql - which is in the database.

the main use of a tool to do ETL is in the documentation...

it is all about maintainability, understanding, getting up to speed quickly.

you can do whatever you like, but OWB will consistently output the code it does - without your modifications.

(so it would not be a really good idea to modify it...)

... Now problem is that we
have to choose between Sybase IQ and OWB to be installed in our systems. ...


Interesting.

OWB is an 'extract transform load' (ETL) tool.
OWB is not a query tool, it is not a database.

Sybase IQ is not an ETL tool.
Sybase IQ is a query tool.


So, I'm utterly at a loss to compare the two. They are not even remotely comparable.


and what "achievement" are you talking of?

Forum: Oracle BI Suite Enterprise Edition

http://forums.oracle.com/forums/forum.jspa?forumID=378

... Through OWB we cannot expect the same result always. Some times it gives
different results for the same mapping after couple of runs.
...

I'm sorry, but the software is deterministic.


I hate slow by slow processing as does anyone that has worked with a database of more than a few records. Informatica epitomizes slow by slow processing - every single time I encounter it - it is the cause of slowness (true, I tend these days to only encounter things when they are giving a problem - but still...)

... I believe that OWB would use straight SQL/PL SQL to accomplish
the same task....


correct, in the ETL process flow, OWB tries to skip the unnecessary E and L steps altogether.


... They sell the products as if you will no longer need an ETL programmer. ...

I hope not, when I talk about them - it is from the perspective that this is for your large development team - it is a book-keeper plain and simple. If you have one guy that does the ETL in their spare time on wednesdays - you don't want this. If you have a team of more than a few people that will be doing long term work that has to be maintained over the course of years - then this is a tool you want to look at.


Writing code by hand - given a competent developer - you'll get something fast. It won't be flexible probably, it'll be harder to enhance/to change, it'll probably come with minimal (if any) documentation.

That is what ETL tools are good for - being the book-keeper, helping with understanding what has taken plan, being flexible.


See my first followup for a more in depth statement of this...