RE: Design question DW

I consider the skillset I acquired as an extension of the OLTP ones I already had.  Some lessons I learned:

think big (lots of data, parallelize queries and object creation/maintenance),

move to SAFE tablespace layouts (famous paper 711) to reduce object sizing and defrag work,

bone up on partitioning features and caveats,

ditto for locally-managed tablespaces,

denormalize tables to speed query response times,

don't denormalize to make it easier for developing SQL,

live without database-enforced constraints that slow down DML (enforce it on the load programs)

don't run in archivelog mode,

replace traditional cold backups with transportable tablespaces (will keep your DW in read-only mode while backup executes)

wide-striping performed better that manual object/datafile/device I/O balancing,

and I can't emphasize this enough, get design help from the professionals.  Our 1st through 4th attempt at deploying a DW were driven by OLTP programmers.  In the words of an independent audit (read $$$) conducted on our DW "It's an OLTP programmer's view of a DW.  Keep the named of the DW and trash the rest."  Heads rolled, money was misspent and I lost some more hair.

If you haven't already done so I suggest reading almost everything in http://www.billinmon.com/  http://www.ralphkimball.com/ to get up to speed on DW concepts.  I'm not totally sure but I think your case is referred to a multi-values source system fields and has been addressed in one of the two sites.  I learned that for starters the approach I would take is to gather typical questions that are asked of the OLTP system (i.e.. foreign cars that used p240 tires, foreign cars that take 2 or more tire sizes, all cars that use p250 and p200).  Each one of these implies that the multi-values column should be parsed out into separate tables (one dimension table for cars, one for tire sizes, and another helper table for car/tire size rows).  You'll find many of these terms in the two websites.  The 3 tables could answer the examples as follows (excuse my syntax errors):

Foreign cars that used p240 tires:

Select

  car,count(*)

from

  car_table, car_tire_table

where

  car_table.domestic=false

and  

  car_table.car = car_tire_table.car

and  

  car_tire_table.tire_size = 'p240';

Foreign cars that take 2 or more tire sizes:

Select

  car,count(*)

from

  car_table, car_tire_table

where

  car_table.domestic=false

and  

  car_table.car = car_tire_table.car

group by

  car

having

  count(*) > 1;

All cars that use p250 and p200 tires

Select

  car

from

  car_tire_table

where

  car_tire_table.tire_size in ('p250','p200');

My point here is to show that gathering few common questions coupled with a little knowledge of DW concepts can jumpstart your thinking in DW design terms (or scare you enough to seek out people that do it for a living).

HTH Tony Aponte

-----Original Message-----

From: adba_at_eurosport.com [mailto:adba_at_eurosport.com]

Sent: Friday, October 19, 2001 10:35 AM

To: Multiple recipients of list ORACLE-L

Subject: Design question DW

Hello List,

     I have a design question for a datawarehouse

application.  I have done OLTP design, but no DW

design before.  The issue is that this warehouse is

being fed from an existing system that allows

multi-values.  What I mean by multi-values is a

column that has comma delimited values in it.  So for

example, if there is a car table with a column that

says tire_size, if the car can handle tire sizes of

p200, p240, and p250 then for a select on the one row

with that car in it, under the tire column you would

have p200,p240,p250. 

I would never even think about letting this thru in

an OLTP system, and if I didn't have access to the

list I wouldn't allow it in a data warehouse either.

But, since I have the advantage of your expertise, I

thought I might as well use it.  There are about 40

of these columns in 5 tables in the proposed design

by the developer.  The developer is trying to say

that they won't use these columns in the where

clauses on most of them and that they are just

detailed data for display.  I have been around long

enough to know how fast that changes, and all of a

sudden the database is a dog and everyone is blaming

the DBA. 

     So my question is, am I just an OLTP DBA in new

territory, or the same principles apply, and

multi-values are still one of the biggest relational

design disasters regardless of OLTP or DW.

Any help is appreciated,

Mike

You too can have your own email address from Eurosport.

http://www.eurosport.com

-- 

Please see the official ORACLE-L FAQ: http://www.orafaq.com

-- 

Author: 



  INET: adba_at_eurosport.com



Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

San Diego, California        -- Public Internet access / Mailing Lists

--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

the message BODY, include a line containing: UNSUB ORACLE-L

(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).