Core principles of data warehouse design

The following is an excerpt from Chapter 8 of Database Modeling and Design: Logical Design, Fourth Edition by Toby Teorey, published by Elsevier in 2006. Toby Teorey is a professor in the Electrical Engineering and Computer Science Department at the University of Michigan, Ann Arbor. Professor Teorey's current research focuses on database design and data warehousing, OLAP, advanced database systems and performance of computer networks.

Click here to read the full chapter.

A data warehouse is a large repository of historical data that can be integrated for decision support. The use of a data warehouse is markedly different from the use of operational systems. Operational systems contain the data required for the day-to-day operations of an organization. This operational data tends to change quickly and constantly. The table sizes in operational systems are kept manageably small by periodically purging old data. The data warehouse, by contrast, periodically receives historical data in batches, and grows over time. The vast size of data warehouses can run to hundreds of gigabytes, or even terabytes. The problem that drives data warehouse design is the need for quick results to queries posed against huge amounts of data. The contrasting aspects of data warehouses and operational systems result in a distinctive design approach for data warehousing.

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Oracle data warehousing Tip: Exposing logical attributes with virtual columns Top five data management buzzwords A bit wiser with Oracle technology Oracle vs. SAP Saving lives with Oracle The BI application consolidation challenge MetaBase scripting for the Oracle data warehousing DBA The case for data stewardship Why OLAP deserves more attention Beyond reporting: Getting value out of your data Oracle business intelligence and analytics Will Sun help Oracle eclipse IBM? Customizing reports in Oracle CRM On Demand Oracle New Year's resolutions, part 2: GRC tips and customer resolutions revealed Oracle's business intelligence unit scores a win at the polls, signs Gallup Oracle's Fusion Applications: Way behind or right on track? Oracle updates its enterprise performance management system, details Hyperion integration Enterprise search a key component of Oracle portal project Data warehouse appliances demand diligence Will Oracle outbid SAP for Business Objects? Oracle unveils BI suite for small companies Oracle business intelligence and analytics Research Oracle data warehousing Exadata: A first look at Oracle's entry into the appliance market Oracle New Year's resolutions, part 2: GRC tips and customer resolutions revealed Oracle's Top 8 stories of 2008 IOUG chief discusses OpenWorld, DBA pay and security Oracle enters the appliance market with Exadata Tip: Exposing logical attributes with virtual columns Oracle users prepare for MDM Data modeling tools no substitute for hard work eHarmony spurns Microsoft, finds match with Oracle 10g Separate database for each client in Oracle 10g

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary bioinformatics  (SearchOracle.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

A data warehouse contains a collection of tools for decision support associated with very large historical databases, which enables the end user to make quick and sound decisions. Data warehousing grew out of the technology for decision support systems (DSS) and executive information systems (EIS). DSSs are used to analyze data from commonly available databases with multiple sources, and to create reports. The report data is not time critical in the sense that a real-time system is, but it must be timely for decision making. EISs are like DSSs, but more powerful, easier to use, and more business specific. EISs were designed to provide an alternative to the classical online transaction processing (OLTP) systems common to most commercially available database systems. OLTP systems are often used to create common applications, including those with mission-critical deadlines or response times.

Let us now take a look at the core requirements and principles that guide the design of data warehouses (DWs) [Simon, 1995; Barquin and Edelstein, 1997; Chaudhuri and Dayal, 1997; Gray and Watson, 1998]:

Printed with permission from Morgan Kaufmann, a division of Elsevier. Copyright 2006. Database Modeling and Design: Logical Design, Fourth Edition by Toby Teorey. For more information about this title and other similar books, please visit elsevier.com.