Re: data warehousing desing - to denormalize or not to denormalize -
-- Gurelei [EMAIL PROTECTED] on 05/10/02 10:43:23 -0800 Hi. We are designing a small database using a data warehousing desing. We have created a 3rd normal form and are now debating whether and how to denormalize it. I see the pluses of denormalization - easier queries creation and tuning. What are the disadvantages that we should be aware of? Wasted space is not an issue because the tables a pretty small. What else should we consider as a potential issue? Oracle was designed (and still is) an OLTP tool. It doesn't do very well with fully denormalized data. Wasted space is only one problem, bloated indexes are another. You can also end up with indexes that lack enough entropy for general use due to repatition, leaving you better off with table scans in most cases. For a small(ish) database there may not be any real difference between a snowflake and star schema, but databases tend to grow. Every time I've dealt with any denormalized schemas in Oracle they have performed poorly (or crashed). -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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: data warehousing desing - to denormalize or not to denormalize - that
Jared, Thanks for the answer. I must admit my ignorance in terminology as for me data warehouse and data mart a pretty much the same thing except for size. I understand that data mart is smaller. The database I'm referring to could probably be described as data mart as it is going to be rather small - a gig or so maybe. --- [EMAIL PROTECTED] wrote: One philosophy of DW states that you build a DW that is fairly normalized, much like an OLTP database, albeit one with a temporal component and complete logging of transactions within the data. This is then used as a warehouse. The data from the DW is used to assemble data marts. These data marts are queried by users. They never look at the data warehouse. So to answer your question: It depends. * On how much time you have * do you want the ability to create new data marts without adding to the ETL system ? ( it should already be getting everything you need ) Since you already have something that looks like a DW, why not use that to build data marts that employ star schemas and bitmap indexes? They are easier to query, and faster. I believe both Kimball and Inmon subscribe to this philosphy. Jared Gurelei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/10/2002 11:43 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:data warehousing desing - to denormalize or not to denormalize - that is the question Hi. We are designing a small database using a data warehousing desing. We have created a 3rd normal form and are now debating whether and how to denormalize it. I see the pluses of denormalization - easier queries creation and tuning. What are the disadvantages that we should be aware of? Wasted space is not an issue because the tables a pretty small. What else should we consider as a potential issue? thank you __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] 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: [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: INET: [EMAIL PROTECTED] 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: [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). __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] 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: [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: data warehousing desing - to denormalize or not to denormalize - that
One philosophy of DW states that you build a DW that is fairly normalized, much like an OLTP database, albeit one with a temporal component and complete logging of transactions within the data. This is then used as a warehouse. The data from the DW is used to assemble data marts. These data marts are queried by users. They never look at the data warehouse. So to answer your question: It depends. * On how much time you have * do you want the ability to create new data marts without adding to the ETL system ? ( it should already be getting everything you need ) Since you already have something that looks like a DW, why not use that to build data marts that employ star schemas and bitmap indexes? They are easier to query, and faster. I believe both Kimball and Inmon subscribe to this philosphy. Jared Gurelei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/10/2002 11:43 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:data warehousing desing - to denormalize or not to denormalize - that is the question Hi. We are designing a small database using a data warehousing desing. We have created a 3rd normal form and are now debating whether and how to denormalize it. I see the pluses of denormalization - easier queries creation and tuning. What are the disadvantages that we should be aware of? Wasted space is not an issue because the tables a pretty small. What else should we consider as a potential issue? thank you __ Do You Yahoo!? Yahoo! Shopping - Mother's Day is May 12th! http://shopping.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gurelei INET: [EMAIL PROTECTED] 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: [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: INET: [EMAIL PROTECTED] 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: [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: data warehousing desing - to denormalize or not to denormalize -
-- Gurelei [EMAIL PROTECTED] on 05/10/02 12:13:27 -0800 Jared, Thanks for the answer. I must admit my ignorance in terminology as for me data warehouse and data mart a pretty much the same thing except for size. I understand that data mart is smaller. The database I'm referring to could probably be described as data mart as it is going to be rather small - a gig or so maybe. Data Mart == summarized data from a Warehouse used to speed up query times. Main point to a mart is that by pre-aggregating the data the volume (and keyspace) are reduced. Mart's acutally increase total storage becuase they store the data more than once (Warehouse + agg'd into the Mart). Advantage is speed for the 90% of all queries that use agg'd data in the first place. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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: data warehousing desing - to denormalize or not to denormalize - that
The distinction between a data mart and a data warehouse is not size (Size doesn't matter). It just works out that way usually... The distinction between a DM and a DW is its function within the entire decision-support strategy. A data warehouse is the consolidation point for all data from all sources. Data marts are downstream breakouts, subsets of that consolidated data for specific business functions, optimized for end-user access. There a situations where a data mart can exceed the data warehouse in size (usually due to different data retention requirements), but a data mart is by definition focused on one specific business topic or area whereas the contents of a data warehouse encompass the entire enterprise, across all business areas. Usually, this means the DW is larger than any of the many possible DMs, but it is not a requirement that this be so. Another common distinction between a data mart and a data warehouse is political. If the accounting department ponies up money for a decision-support strategy but the marketing department does not, then you are unlikely to have anything that can be referred to as a data warehouse. Hence, the popularity of data marts... The third major component of a decision-support strategy is the operational data store (ODS), which is largely understood to be a staging area during extraction, transformation, and loading into the data warehouse from the operational source systems. But, depending on requirements, an ODS can also be utilized as a consolidation point for data for unified tactical reporting, possibly to offload the operational systems. For example, consider the example of a company that runs Peoplesoft financials that acquires another one company that runs Lawson financials, another company that runs Oracle financials, and a third company that runs Quickbooks for financials. How is these folks going to get consolidated tactical (not strategic) financial reporting? One option is to utilize the first step of the decision-support strategy. As the data is staged on it's way to the data warehouse and the financial data mart after that, why not allow the consolidated data to be reported upon from the staging area in the ODS? Yet another use of the ODS (besides staging for transformation from operational to DSS data models and offloading tactical reporting) is possible archival of transactional data, when the source system does not support archival. For example, most legacy systems and quite a few modern software packages never dealt with the issue of archival. Rather than trying to modify each individual source system for archival, why not archive at the consolidation point, in the ODS? Sorry for the long-winded insertion into this thread, but I just wanted to add that little change of perspective... - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, May 10, 2002 2:13 PM denormalize - that Jared, Thanks for the answer. I must admit my ignorance in terminology as for me data warehouse and data mart a pretty much the same thing except for size. I understand that data mart is smaller. The database I'm referring to could probably be described as data mart as it is going to be rather small - a gig or so maybe. --- [EMAIL PROTECTED] wrote: One philosophy of DW states that you build a DW that is fairly normalized, much like an OLTP database, albeit one with a temporal component and complete logging of transactions within the data. This is then used as a warehouse. The data from the DW is used to assemble data marts. These data marts are queried by users. They never look at the data warehouse. So to answer your question: It depends. * On how much time you have * do you want the ability to create new data marts without adding to the ETL system ? ( it should already be getting everything you need ) Since you already have something that looks like a DW, why not use that to build data marts that employ star schemas and bitmap indexes? They are easier to query, and faster. I believe both Kimball and Inmon subscribe to this philosphy. Jared Gurelei [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/10/2002 11:43 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:data warehousing desing - to denormalize or not to denormalize - that is the question Hi. We are designing a small database using a data warehousing desing. We have created a 3rd normal form and are now debating whether and how to denormalize it. I see the pluses of denormalization - easier queries creation and tuning. What are the disadvantages that we should be aware of? Wasted space is not an issue because the tables a pretty small. What else should we consider as a potential issue? thank you
Re: data warehousing desing - to denormalize or not to denormalize -
A DW stores data in a denormalised fashion, that's one point that every body knows but it is also subject oriented. It is also developped one subject at a time. A DW is multi-subjects as a datamart is on one subject. From Bill Inmon From the data warehouse data flows to various departments from their customized DSS usage. These departmental DSS data bases are called data marts. A data mart is a body of DSS data for a department that has an architectural foundation of a data warehouse. The data that resides in the data warehouse is at a very granular level and the data in the data mart is at a refined level. The different data marts contain different combinations and selections of the same detailed data found at the data warehouse. In some cases data warehouse detailed data is added differently across the different data marts. Yet in other cases a data mart will structure detailed data differently from other data marts. But in every case the data warehouse provides the granular foundation for all of the data found in all of the data marts. Because of the singular data warehouse foundation that all data marts have, all of the data marts have a common heritage and are able to be reconciled at the most basic level. --- [EMAIL PROTECTED] a écrit : -- Gurelei [EMAIL PROTECTED] on 05/10/02 12:13:27 -0800 Jared, Thanks for the answer. I must admit my ignorance in terminology as for me data warehouse and data mart a pretty much the same thing except for size. I understand that data mart is smaller. The database I'm referring to could probably be described as data mart as it is going to be rather small - a gig or so maybe. Data Mart == summarized data from a Warehouse used to speed up query times. Main point to a mart is that by pre-aggregating the data the volume (and keyspace) are reduced. Mart's acutally increase total storage becuase they store the data more than once (Warehouse + agg'd into the Mart). Advantage is speed for the 90% of all queries that use agg'd data in the first place. -- Steven Lembark 2930 W. Palmer Workhorse Computing Chicago, IL 60647 +1 800 762 1582 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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 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: [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: data warehousing desing - to denormalize or not to denormalize - that
Tim, Don't *ever* apologize for long threads when they are full of useful content. :) Jared Tim Gorman [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 05/10/2002 02:19 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: data warehousing desing - to denormalize or not to denormalize - that The distinction between a data mart and a data warehouse is not size (Size doesn't matter). It just works out that way usually... The distinction between a DM and a DW is its function within the entire decision-support strategy. A data warehouse is the consolidation point for all data from all sources. Data marts are downstream breakouts, subsets of that consolidated data for specific business functions, optimized for end-user access. There a situations where a data mart can exceed the data warehouse in size (usually due to different data retention requirements), but a data mart is by definition focused on one specific business topic or area whereas the contents of a data warehouse encompass the entire enterprise, across all business areas. Usually, this means the DW is larger than any of the many possible DMs, but it is not a requirement that this be so. Another common distinction between a data mart and a data warehouse is political. If the accounting department ponies up money for a decision-support strategy but the marketing department does not, then you are unlikely to have anything that can be referred to as a data warehouse. Hence, the popularity of data marts... ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] 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: [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).
