Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Warehouse design: snowflake vs star schemas

RE: Warehouse design: snowflake vs star schemas

From: paquette stephane <>
Date: Tue, 08 Oct 2002 06:58:32 -0800
Message-ID: <>

Snowflake is often used because people still want to normalize (and save some disk space !) which is not the way to go to ease query.

If you do an hybrid data model, your loading will be easier as you will have less problems to solve. I agrre with you, the complexity comes from the number of sources and their quality. But, from experience, at my last job, the DW has designed an hybrid data model to ease the ETL processes. Let me tell you that the querying and reporting was painful and slow. We have redesign it in a more formal star schema and we had some real challenges to load the DW. On the current project, we have 15 sources (excel, cobol, Oracle, Clipper, DB2/MVS, Nomad,... ) we are doing a prototype with an ETL, we will have fun !

It is feasible just to have a date column in the fact table. That's what they had done at the previous job. I do not recommend that. If you carefully do the analysis, you'll see that the users want to manage all kind of special events like season, national day, F1 racing (in Montreal, a beer company is checking if beer is more sold during the week-end Grand Prix). Also, often the fiscal year do not match the calendar year. So there is plenty stuff you may want to track with the time dimension.

Using a generated key or the date value as the key is a good question. On the theoritical side you should use a generated key. I've used a date field without problem.

Stéphane Paquette
DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant

Do You Yahoo!? -- Une adresse gratuite et en français ! Yahoo! Mail :
Please see the official ORACLE-L FAQ:
Author: =?iso-8859-1?q?paquette=20stephane?=

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (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).
Received on Tue Oct 08 2002 - 09:58:32 CDT

Original text of this message