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).

--
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).