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

 

 


 

 

 

 

 

 
 

Aggregation, Roll-Ups and STAR Schemas

Oracle Tips by Burleson Consulting

The Data Warehouse Development Life Cycle

Aggregating Data For The Oracle Warehouse

Aggregation, Roll-Ups, And STAR Schemas
Letís illustrate some methods for pre-calculating aggregate tables by looking at a sample STAR schema design. In Figure 6.3, you can see that our base data warehouse consists of a highly de-normalized fact table and numerous dimension tables.

Figure 6.3 A sample STAR schema.

Now, letís assume that we have already defined and populated our STAR schema that contains the Total_cost for each order for each day. While it is now easy to see the total for each order, rarely do the users of a decision support system require this level of detail. Most managers would be more interested in knowing the sum of sales or units sold--aggregated by month, quarter, region, and so on. Even with a STAR schema, these types of aggregations would be hard to compute at runtime from our Oracle warehouse with an acceptable response time.

Essentially, we can either aggregate at runtime using ROLAP technology or pre-aggregate the data offline, thus making the totals available without realtime computation. While realtime data aggregation may provide up-to-the-minute detail, the expense of purchasing a ROLAP engine and the slow response time may make the data warehouse designer look for other methods for providing summary data. One simple alternative to realtime aggregation is to write SQL to pre-aggregate data according to the dimensions that end users may want to see. In our example, letís assume management wants to aggregate total cost by region, state, item type, and salesperson. Because we have four possible dimensions, we can generate a list of the following six aggregate tables to precreate. Assume that all of the tables would have a month_year field as their primary key.


This is an excerpt from "High Performance Data Warehousing".
If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.

 


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.