Re: Datawarehouse with Oracle

On Tue, 02 Jul 2002 12:22:39 GMT, willjamu_at_mindspring.com (James Williams) wrote:

>On 2 Jul 2002 00:32:45 -0700, xtanto_at_hotmail.com (Krist) wrote:
>
>There are many products out there. Since you just mention Oracle.
>
>Oracle Warehouse Builder - need an ETL tool unless you RYOC plus
>metadata.

I'd take a look around at other products before settling on OWB. We have it here for close to 2 years and we have all but abandoned it. It is somewhat limitied in it's abilitites. For example, it cannot join multiple tables when creating the ETL packages, for us a big downside. Also, the code it generates is not the most efficient (or for that matter, readable). Exception handling is worthless - all you get is a when others with a dbms_output.put_line. For our latest datamart and warehouse, I just ended up coding the whole thing myself and will do subsequent datamarts in this manner.

>Oracle Discoverer - AD hoc query tool. Have the admin build EUL and
>pass off to user.

Again, I'd recommend looking around at other options, depending on your requirements. Discoverer is good for simple lists, graphs, etc., but that is about the limit of it's abilities. You can't do much OLAP with it and certainly no detailed if-then analysis. We're taking a hard look at Oracle Express as an additional reporting tool.
>
>More and more of the capability of Oracle Express (OLAP) is being
>added to the RDBMS especially in 9i. You have CUBE, ROLLUP, functions
>even in 8i so you have a lot of what you need.

We are using both 8i and 9i on our datamarts and warehouse with the warehouse on 9i. But keep in mind that Discoverer does not play well with 9i yet. We just got the 9.0.2 release in and have not had a chance to check the documentation or to test with that version yet so this may have changed.

>
>Also, you need good diminsional modeling skills. Fact tables and
>dimensions are the norm in warehouses. So is denormalization. Also,
>read the Oracle data warehousing guide. You need to set your hardware
>and database up appropriately.

These points cannot be understated. One of the biggest issues I ran across was getting other developers to even consider standard warehouse design principles as a valid design option. Most of them basically stated if it was not relational, it was crap. It seems to be a concept the schools have been very sucessful at drilling into students heads over the last 10 years. Some people can get past that roadblock, others possibly cannot. Our first datamart was assembled by Oracle consulting and they seem to not be able to get out of a relational mode. We ended up with a snowflake schema that is more like an iceball :)

>
>>Hi All,
>>
>>If we want to implement Datawarehouse and OLAP with Oracle, besides
>>Oracle RDBMS Server, what are other Oracle products that I need to
>>purchase ?
>>
>>Thanks a lot for the info,
>>Krist
Received on Tue Jul 02 2002 - 14:19:08 CDT