Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: data warehousing desing - to denormalize or not to denormaliz
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
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).
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). Received on Fri May 10 2002 - 14:48:36 CDT