BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 
 

Oracle Tips 

by Burleson Consulting

The Data Warehouse Development Life Cycle

Oracle Data Warehouse Design
De-normalization And Data Warehouse Sizing
Now, let’s apply these principles to the analysis we conducted on Guttbaum’s Grocery. The first step is to start with our 3NF model from Figure 4.1 and locate the “facts,” which are always numerical items at the bottom of the entity hierarchy. In our example, quantity and sale_amount in the transaction entity are the facts. Because these appear in the TRANSACTION table in our 3NF model for Guttbaum’s Grocery, we can presume that the transaction table will form the basis for our fact table for our data warehouse.

However, here we return to an important point regarding data aggregation. Our 3NF model for Guttbaum’s assumes that there will be one row for each item on each and every transaction. Because Guttbaum’s Grocery chain has 50 supermarkets, and an average of 3,000 customers each day for each supermarket, with an average of 10 items per transaction, we see that 1.5 million rows are added to Guttbaum’s database each day. Assuming that each transaction row is 20 bytes, we need 30 MBs of disk space per day just to hold the transactions, which translates into 30,000,000 X 365 days = 1,100,000,000,000 bytes, or 1.1 terabytes per year! Even with the price of disk running at the relatively inexpensive rate of $100 per gigabyte or $10,00 per terabyte, we may decide that the cost of disk storage is prohibitively expensive to store this level of transaction detail. One alternative to keeping the lowest level of transaction detail would be to summarize the transaction data, either by total per customer transaction or total by product category. Of course, we may lose some information in this summarization, but we would save a substantial amount of disk storage expense. For details on the issues involved with data aggregation and summarization, refer to Chapter 10, Oracle Data Warehouse Utilities.

So, even if we can keep our transaction table rows to 20 bytes, the additional disk cost for adding another redundant data item to the fact table is rather substantial. Each additional 20 bytes of data will cost Guttbaum’s $10,000 per year in additional disk expense, so we must carefully consider which redundant data items will appear in the fact table and which data items will reside in the adjacent tables of our STAR schema design. Remember the tradeoff--we are sacrificing disk costs for speed of data retrieval. Each attribute added to the fact table reduces the number of table joins that our Oracle warehouse must incur to service a transaction. Hence, only frequently referenced data items are candidates for inclusion in out fact table.


This is an excerpt from "High Performance Data Warehousing". To learn more about Oracle, try "Oracle Tuning: The Definitive Reference", by Donald K. Burleson.  You can buy it direct from the publisher at 30% off here:
http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm
 

 


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.