Re: data warehousing desing - to denormalize or not to denormaliz

>From what I remember, one of the main advantages of a normalized database is
a certain level of data integrity and flexibility. The integrity comes from foreign keys, lack of duplicate data, and generally a data design based on characteristics of the data. The flexibility comes from the ablility to query in many different ways (you are not restricted as you are in a heirarchical db). Though I have never designed a data warehouse, I always thought that the integrety piece would be less of an issue. Since there are very few writes to a warehouse, there is less chance to fowl up the data integrity as long as there are good controls on the load. Flexibility on querying a warehouse seems to also be possible using other design methods (fact tables, snowflake, ...)

Henry

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, May 10, 2002 3:48 PM

> Let's see . . . disadvantages . . . hmmm. scorn of anyone that understands
> data warehousing? On your resume putting "created a completely normalized
> DW" and wondering why everyone giggles when they read it.
>
> Actually, a normalized schema will probably use less space than
> denormalized.
> The key point is your users. A typical normalized design means a lot of
> tables. These tables must be joined in specific manners. Non-I.S. users
find
> this intimidating. So they don't use it. So you've spent a lot of time
> creating a DW only to have nobody use it.
> Normalized schemas are optimized for inserts and updates, not
> generating reports. To generate a report from an OLTP normalized schema,
you
> usually start by interviewing the developers. And they usually have to do
> some research. A DW is a "write mostly" schema. Our DW is only refreshed
> weekly. Sunday is spent loading it and Monday the users charge in and run
> reports all day.
> Study star schema. Go to http://www.ralphkimball.com and read
> articles he has written, starting with the oldest ones and working
forward.
> Read Ralph Kimball's book "The Data Warehouse Toolkit: Practical
Techniques
> for Building Dimensional Data Warehouses". Classic work, excellent
starting
> point.
> A beginning star schema DW should have only a central fact table and
> 4-6 dimension tables. Non-computer-geeks actually have a hope of
> understanding how to navigate that. How many tables did your 3nf schema
> produce?
> Or go ahead, build a normalized one, then study and build the second
> version incorporating the hard-won lessons others have learned.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
>
> -----Original Message-----
> Sent: Friday, May 10, 2002 1:43 PM
> To: Multiple recipients of list ORACLE-L
> - 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: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.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: Henry Poras
  INET: hporas_at_attbi.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).