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" <ORACLE-L_at_fatcity.com> 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.
>
>
> --- Jared.Still_at_radisys.com 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 <gurelei_at_yahoo.com>
> > Sent by: root_at_fatcity.com
> > 05/10/2002 11:43 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > 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: gurelei_at_yahoo.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).
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author:
> > INET: Jared.Still_at_radisys.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).
>
>
> __________________________________________________
> 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: gurelei_at_yahoo.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.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).