RE: Warehouse design: snowflake vs star schemas
Peter - I wouldn't even presume to pass judgements on two such titans of their respective industries, but taking it down a notch, here is what I would make of their discussion. The relational model has been extremely powerful, based as it is on a firm mathematical foundation. I would count Chris Date (and I have copies on nearly all his books) as a foremost leader of this field. Along comes these data warehousing people, storing their data in a relational database. But they are deliberately flouting the carefully worked out "Rules of Data Normalization" (reading the chart on my wall). Claiming this as a necessary extenuating situation. Opportunities for confusion abound. If you think this disagreement is difficult to understand, just try comprehending the disagreements between the two data warehousing leaders, Bill Inmon and Ralph. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 14, 2002 10:39 AM To: Multiple recipients of list ORACLE-L No no - it wasn't to do with the specifics of snowstorms or cosmic radiation, rather a further discussion of the issues of database design, which ultimately has to be the reference point in any discussion of star schemas etc. peter > -Original Message- > From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] > Sent: 14 October 2002 15:09 > To: Multiple recipients of list ORACLE-L > Subject: RE: Warehouse design: snowflake vs star schemas > > > Peter - What does this article have to do with snowflake vs. > star schema? I > didn't see where Chris Date was advocating snowflaking. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Monday, October 14, 2002 4:59 AM > To: Multiple recipients of list ORACLE-L > > > Just for the record (and perhaps to confirm that there are > always two sides > to a story). Readers may like to see the article Chris Date > wrote to Ralph > Kemball on the subject of business rules and integrity constraints: > > http://www.dbdebunk.com/kimball1.htm > > peter > edinburgh > > > > -Original Message- > > From: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]] > > Sent: 09 October 2002 15:04 > > To: Multiple recipients of list ORACLE-L > > Subject: RE: Warehouse design: snowflake vs star schemas > > > > > > Dennis, > > > > Thanks for the link. It's a great DW resource. > > There are not only fundamentals, but some valuable real-life > > stuff, which is > > what I need. Live and learn, learn,learn... > > > > Regards, > > Alex > > > > -Original Message- > > WILLIAMS > > Sent: Tuesday, October 08, 2002 7:39 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Alexandre > >Stephane gave you an excellent reply. If you want to learn > > more about > > these DW design issues, I would start by visiting > > http://www.ralphkimball.com. He is one of the leading figures > > in the DW > > field, and has copies of all his articles posted on his site. > > I would start > > with the oldest articles because those are where he discusses the > > fundamentals of data warehousing. > > > > > > Dennis Williams > > DBA > > Lifetouch, Inc. > > [EMAIL PROTECTED] > > > > -Original Message- > > Sent: Tuesday, October 08, 2002 3:29 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Stéphane, > > > > Thanks for response. > > > > > I have always discplined myself to use star schema and > > > never snowflake. > > Would you mind asking why? > > > > > The "Which one is easier to implement and easier ETL > > > ?" is not a good question as your data model should > > > not be design for the ETL procecess but only for the > > > querying. > > I mean ETL to load data _INTO_ data warehouse. Of course, > > complexity is > > mostly determined by sources, but still I'd like to know if > > there is any > > general influence by DW's data model. > > > > Another question. Is it feasible to make date dimension or > > just use date > > column? For example, Oracle Discoverer can work with date > > columns using > > hierarchies Y->M->D and similar. > > What is faster: separate table for date dimension or date column? > > If I go with date dimension table should I use date column as > > a foreign key > > in fact table or use some artificial key? > > > > TIA, > > Alexandre > > > &
RE: Warehouse design: snowflake vs star schemas
No no - it wasn't to do with the specifics of snowstorms or cosmic radiation, rather a further discussion of the issues of database design, which ultimately has to be the reference point in any discussion of star schemas etc. peter > -Original Message- > From: DENNIS WILLIAMS [mailto:[EMAIL PROTECTED]] > Sent: 14 October 2002 15:09 > To: Multiple recipients of list ORACLE-L > Subject: RE: Warehouse design: snowflake vs star schemas > > > Peter - What does this article have to do with snowflake vs. > star schema? I > didn't see where Chris Date was advocating snowflaking. > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Monday, October 14, 2002 4:59 AM > To: Multiple recipients of list ORACLE-L > > > Just for the record (and perhaps to confirm that there are > always two sides > to a story). Readers may like to see the article Chris Date > wrote to Ralph > Kemball on the subject of business rules and integrity constraints: > > http://www.dbdebunk.com/kimball1.htm > > peter > edinburgh > > > > -Original Message- > > From: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]] > > Sent: 09 October 2002 15:04 > > To: Multiple recipients of list ORACLE-L > > Subject: RE: Warehouse design: snowflake vs star schemas > > > > > > Dennis, > > > > Thanks for the link. It's a great DW resource. > > There are not only fundamentals, but some valuable real-life > > stuff, which is > > what I need. Live and learn, learn,learn... > > > > Regards, > > Alex > > > > -Original Message- > > WILLIAMS > > Sent: Tuesday, October 08, 2002 7:39 PM > > To: Multiple recipients of list ORACLE-L > > > > > > Alexandre > >Stephane gave you an excellent reply. If you want to learn > > more about > > these DW design issues, I would start by visiting > > http://www.ralphkimball.com. He is one of the leading figures > > in the DW > > field, and has copies of all his articles posted on his site. > > I would start > > with the oldest articles because those are where he discusses the > > fundamentals of data warehousing. > > > > > > Dennis Williams > > DBA > > Lifetouch, Inc. > > [EMAIL PROTECTED] > > > > -Original Message- > > Sent: Tuesday, October 08, 2002 3:29 AM > > To: Multiple recipients of list ORACLE-L > > > > > > Stéphane, > > > > Thanks for response. > > > > > I have always discplined myself to use star schema and > > > never snowflake. > > Would you mind asking why? > > > > > The "Which one is easier to implement and easier ETL > > > ?" is not a good question as your data model should > > > not be design for the ETL procecess but only for the > > > querying. > > I mean ETL to load data _INTO_ data warehouse. Of course, > > complexity is > > mostly determined by sources, but still I'd like to know if > > there is any > > general influence by DW's data model. > > > > Another question. Is it feasible to make date dimension or > > just use date > > column? For example, Oracle Discoverer can work with date > > columns using > > hierarchies Y->M->D and similar. > > What is faster: separate table for date dimension or date column? > > If I go with date dimension table should I use date column as > > a foreign key > > in fact table or use some artificial key? > > > > TIA, > > Alexandre > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: Alexandre Gorbatchev > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > > San Diego, California-- Mailing list and web > hosting services > > > - > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (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). > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.com > > -- > > Author: DENNIS WILLIAMS > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services
RE: Warehouse design: snowflake vs star schemas
Peter - What does this article have to do with snowflake vs. star schema? I didn't see where Chris Date was advocating snowflaking. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Monday, October 14, 2002 4:59 AM To: Multiple recipients of list ORACLE-L Just for the record (and perhaps to confirm that there are always two sides to a story). Readers may like to see the article Chris Date wrote to Ralph Kemball on the subject of business rules and integrity constraints: http://www.dbdebunk.com/kimball1.htm peter edinburgh > -Original Message- > From: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]] > Sent: 09 October 2002 15:04 > To: Multiple recipients of list ORACLE-L > Subject: RE: Warehouse design: snowflake vs star schemas > > > Dennis, > > Thanks for the link. It's a great DW resource. > There are not only fundamentals, but some valuable real-life > stuff, which is > what I need. Live and learn, learn,learn... > > Regards, > Alex > > -Original Message- > WILLIAMS > Sent: Tuesday, October 08, 2002 7:39 PM > To: Multiple recipients of list ORACLE-L > > > Alexandre >Stephane gave you an excellent reply. If you want to learn > more about > these DW design issues, I would start by visiting > http://www.ralphkimball.com. He is one of the leading figures > in the DW > field, and has copies of all his articles posted on his site. > I would start > with the oldest articles because those are where he discusses the > fundamentals of data warehousing. > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Tuesday, October 08, 2002 3:29 AM > To: Multiple recipients of list ORACLE-L > > > Stéphane, > > Thanks for response. > > > I have always discplined myself to use star schema and > > never snowflake. > Would you mind asking why? > > > The "Which one is easier to implement and easier ETL > > ?" is not a good question as your data model should > > not be design for the ETL procecess but only for the > > querying. > I mean ETL to load data _INTO_ data warehouse. Of course, > complexity is > mostly determined by sources, but still I'd like to know if > there is any > general influence by DW's data model. > > Another question. Is it feasible to make date dimension or > just use date > column? For example, Oracle Discoverer can work with date > columns using > hierarchies Y->M->D and similar. > What is faster: separate table for date dimension or date column? > If I go with date dimension table should I use date column as > a foreign key > in fact table or use some artificial key? > > TIA, > Alexandre > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY
RE: Warehouse design: snowflake vs star schemas
Just for the record (and perhaps to confirm that there are always two sides to a story). Readers may like to see the article Chris Date wrote to Ralph Kemball on the subject of business rules and integrity constraints: http://www.dbdebunk.com/kimball1.htm peter edinburgh > -Original Message- > From: Alexandre Gorbatchev [mailto:[EMAIL PROTECTED]] > Sent: 09 October 2002 15:04 > To: Multiple recipients of list ORACLE-L > Subject: RE: Warehouse design: snowflake vs star schemas > > > Dennis, > > Thanks for the link. It's a great DW resource. > There are not only fundamentals, but some valuable real-life > stuff, which is > what I need. Live and learn, learn,learn... > > Regards, > Alex > > -Original Message- > WILLIAMS > Sent: Tuesday, October 08, 2002 7:39 PM > To: Multiple recipients of list ORACLE-L > > > Alexandre >Stephane gave you an excellent reply. If you want to learn > more about > these DW design issues, I would start by visiting > http://www.ralphkimball.com. He is one of the leading figures > in the DW > field, and has copies of all his articles posted on his site. > I would start > with the oldest articles because those are where he discusses the > fundamentals of data warehousing. > > > Dennis Williams > DBA > Lifetouch, Inc. > [EMAIL PROTECTED] > > -Original Message- > Sent: Tuesday, October 08, 2002 3:29 AM > To: Multiple recipients of list ORACLE-L > > > Stéphane, > > Thanks for response. > > > I have always discplined myself to use star schema and > > never snowflake. > Would you mind asking why? > > > The "Which one is easier to implement and easier ETL > > ?" is not a good question as your data model should > > not be design for the ETL procecess but only for the > > querying. > I mean ETL to load data _INTO_ data warehouse. Of course, > complexity is > mostly determined by sources, but still I'd like to know if > there is any > general influence by DW's data model. > > Another question. Is it feasible to make date dimension or > just use date > column? For example, Oracle Discoverer can work with date > columns using > hierarchies Y->M->D and similar. > What is faster: separate table for date dimension or date column? > If I go with date dimension table should I use date column as > a foreign key > in fact table or use some artificial key? > > TIA, > Alexandre > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: DENNIS WILLIAMS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 http://www.fatcity.com > San Diego, California-- Mailing list and web hosting services > - > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > * This e-mail message, and any f
RE: Warehouse design: snowflake vs star schemas
Stéphane, Thanks for good points! Alex -Original Message- stephane Sent: Tuesday, October 08, 2002 4:59 PM To: Multiple recipients of list ORACLE-L 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. --- Alexandre Gorbatchev <[EMAIL PROTECTED]> a écrit : > Stéphane, > > Thanks for response. > > > I have always discplined myself to use star schema > and > > never snowflake. > Would you mind asking why? > > > The "Which one is easier to implement and easier > ETL > > ?" is not a good question as your data model > should > > not be design for the ETL procecess but only for > the > > querying. > I mean ETL to load data _INTO_ data warehouse. Of > course, complexity is > mostly determined by sources, but still I'd like to > know if there is any > general influence by DW's data model. > > Another question. Is it feasible to make date > dimension or just use date > column? For example, Oracle Discoverer can work with > date columns using > hierarchies Y->M->D and similar. > What is faster: separate table for date dimension or > date column? > If I go with date dimension table should I use date > column as a foreign key > in fact table or use some artificial key? > > TIA, > Alexandre > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Warehouse design: snowflake vs star schemas
Dennis, Thanks for the link. It's a great DW resource. There are not only fundamentals, but some valuable real-life stuff, which is what I need. Live and learn, learn,learn... Regards, Alex -Original Message- WILLIAMS Sent: Tuesday, October 08, 2002 7:39 PM To: Multiple recipients of list ORACLE-L Alexandre Stephane gave you an excellent reply. If you want to learn more about these DW design issues, I would start by visiting http://www.ralphkimball.com. He is one of the leading figures in the DW field, and has copies of all his articles posted on his site. I would start with the oldest articles because those are where he discusses the fundamentals of data warehousing. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, October 08, 2002 3:29 AM To: Multiple recipients of list ORACLE-L Stéphane, Thanks for response. > I have always discplined myself to use star schema and > never snowflake. Would you mind asking why? > The "Which one is easier to implement and easier ETL > ?" is not a good question as your data model should > not be design for the ETL procecess but only for the > querying. I mean ETL to load data _INTO_ data warehouse. Of course, complexity is mostly determined by sources, but still I'd like to know if there is any general influence by DW's data model. Another question. Is it feasible to make date dimension or just use date column? For example, Oracle Discoverer can work with date columns using hierarchies Y->M->D and similar. What is faster: separate table for date dimension or date column? If I go with date dimension table should I use date column as a foreign key in fact table or use some artificial key? TIA, Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Warehouse design: snowflake vs star schemas
Alexandre Stephane gave you an excellent reply. If you want to learn more about these DW design issues, I would start by visiting http://www.ralphkimball.com. He is one of the leading figures in the DW field, and has copies of all his articles posted on his site. I would start with the oldest articles because those are where he discusses the fundamentals of data warehousing. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, October 08, 2002 3:29 AM To: Multiple recipients of list ORACLE-L Stéphane, Thanks for response. > I have always discplined myself to use star schema and > never snowflake. Would you mind asking why? > The "Which one is easier to implement and easier ETL > ?" is not a good question as your data model should > not be design for the ETL procecess but only for the > querying. I mean ETL to load data _INTO_ data warehouse. Of course, complexity is mostly determined by sources, but still I'd like to know if there is any general influence by DW's data model. Another question. Is it feasible to make date dimension or just use date column? For example, Oracle Discoverer can work with date columns using hierarchies Y->M->D and similar. What is faster: separate table for date dimension or date column? If I go with date dimension table should I use date column as a foreign key in fact table or use some artificial key? TIA, Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Warehouse design: snowflake vs star schemas
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. --- Alexandre Gorbatchev <[EMAIL PROTECTED]> a écrit : > Stéphane, > > Thanks for response. > > > I have always discplined myself to use star schema > and > > never snowflake. > Would you mind asking why? > > > The "Which one is easier to implement and easier > ETL > > ?" is not a good question as your data model > should > > not be design for the ETL procecess but only for > the > > querying. > I mean ETL to load data _INTO_ data warehouse. Of > course, complexity is > mostly determined by sources, but still I'd like to > know if there is any > general influence by DW's data model. > > Another question. Is it feasible to make date > dimension or just use date > column? For example, Oracle Discoverer can work with > date columns using > hierarchies Y->M->D and similar. > What is faster: separate table for date dimension or > date column? > If I go with date dimension table should I use date > column as a foreign key > in fact table or use some artificial key? > > TIA, > Alexandre > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
RE: Warehouse design: snowflake vs star schemas
Stéphane, Thanks for response. > I have always discplined myself to use star schema and > never snowflake. Would you mind asking why? > The "Which one is easier to implement and easier ETL > ?" is not a good question as your data model should > not be design for the ETL procecess but only for the > querying. I mean ETL to load data _INTO_ data warehouse. Of course, complexity is mostly determined by sources, but still I'd like to know if there is any general influence by DW's data model. Another question. Is it feasible to make date dimension or just use date column? For example, Oracle Discoverer can work with date columns using hierarchies Y->M->D and similar. What is faster: separate table for date dimension or date column? If I go with date dimension table should I use date column as a foreign key in fact table or use some artificial key? TIA, Alexandre -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Alexandre Gorbatchev INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: Warehouse design: snowflake vs star schemas
Data modeling in a datawarehouse is there to ease and make querying faster. I have always discplined myself to use star schema and never snowflake. The "Which one is easier to implement and easier ETL ?" is not a good question as your data model should not be design for the ETL procecess but only for the querying. Oracle star transformation join technique is designed to handle star schema. HTH --- Alexandre Gorbatchev <[EMAIL PROTECTED]> a écrit : > Dear Data Warehouse Experts, > > Could you please share you experience with snowflake > and star data models. > How do you choose between them? > What problems may arise? > Is star schema preferred for Oracle? > What is users' experience with those schemas? Which > one they like more and > why? > Which one is easier to implement and easier ETL? > Are there other patterns for DW? > > I would like to check/confirm/change my possibly > subjective point of view to > more objective perspective. I would appreciate your > thoughts or links where > I can review practical conclusions. > > TIA, > Alexandre > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Alexandre Gorbatchev > INET: [EMAIL PROTECTED] > > Fat City Network Services-- 858-538-5051 > http://www.fatcity.com > San Diego, California-- Mailing list and web > hosting services > - > To REMOVE yourself from this mailing list, send an > E-Mail message > to: [EMAIL PROTECTED] (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). = Stéphane Paquette DBA Oracle, consultant entrepôt de données Oracle DBA, datawarehouse consultant [EMAIL PROTECTED] ___ Do You Yahoo!? -- Une adresse @yahoo.fr gratuite et en français ! Yahoo! Mail : http://fr.mail.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?paquette=20stephane?= INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
