Portals eNewsletters Web Seminars dataWarehouse.com DM Review Magazine
DM Review | Covering Business Intelligence, Integration & Analytics
   Covering Business Intelligence, Integration & Analytics Advanced Search

View all Portals

Scheduled Events

White Paper Library
Research Papers

View Job Listings
Post a job


DM Review Home
Current Magazine Issue
Magazine Archives
Online Columnists
Ask the Experts
Industry News
Search DM Review

Buyer's Guide
Industry Events Calendar
Monthly Product Guides
Software Demo Lab
Vendor Listings

About Us
Press Releases
Advertising/Media Kit
Magazine Subscriptions
Editorial Calendar
Contact Us
Customer Service

Enterprise Architecture View:
Information Delivery Shortcuts, Part 3

  Column published in DM Review Magazine
June 2005 Issue
  By Michael Jennings

What are some of the pros and cons of implementing a data warehouse solution versus reporting from an enterprise transaction system?

Last month, we examined some of the deficiencies associated with using the transactional system as the primary information delivery data store for the enterprise or business area versus a data warehouse architecture approach. In this last installment of the series, we will look at the pros and cons of implementing a data warehouse solution for information delivery.

There has been a lot of information presented over the last decade or so on the value proposition of implementing and using a data warehouse for enterprise-wide information delivery. Data warehousing today is no longer a business question for corporations, but rather has developed into a recognized instrument to monitor and direct business strategy.

Performance Advantage

An information delivery architecture that includes a data warehouse provides several performance benefits. The data warehouse environment is designed and tuned for information delivery performance, not transactional processing efficiency. The warehouse data model is designed for efficient information retrieval, typically through a dimensional or denormalized design approach. This design provides rapid information retrieval by integrating data and reducing the overall database management processing required to obtain data. This architecture approach also provides other implementation options that are not so obvious when just looking at a data model design. Today's database management systems (DBMSs) offer a variety of data warehousing and analytical performance tuning features that can enhance overall information delivery efficiency beyond the transactional system approach. Indexing, page sizes, buffers, heap sizes, temporary spaces, referential integrity, memory and other features can be tuned specifically for information delivery with the DBMS. A data warehouse database that exists on its own server and/or database instance can take advantage of even more performance tuning options because the isolation prevents degradation of the transactional systems. Further performance can be achieved by analyzing data access usage and distributing data across additional physical disks to increase overall disk input/output processing throughput on your local disk array, storage area network (SAN) and/or network attached storage (NAS).

The data warehouse architecture approach also increases overall performance of the enterprise transactional systems by allowing historical, closed, audit and other data to be offloaded to the data warehouse. This removes not only the data storage burden from the enterprise transactional system, but also transfers the data query burden to the data warehouse. Your overall business intelligence architecture design, including data refresh methods, should allow for all reporting and queries to be run against the data warehouse, eliminating this processing burden from your business systems.


The data warehouse also offers several advantages over the enterprise transactional system from an ongoing support and maintenance perspective. By reducing the number of tables, relationships, business rules and other complexities of the transactional system into a more efficient and understandable dimensional design, ongoing support costs for information requests are reduced while improving turnaround time for these requests. Testing and debugging of information queries is lowered due to less complexity in the data model and supporting structures. Transactional system issues around control, setup and code tables that require the addition of views, aliases, inner/outer joins and other methods to overcome them in queries are eliminated in the data warehouse design.

Another benefit is that business changes to the transactional data model, if needed, can be made without affecting or being held up by the information delivery system. Planned changes to the data warehouse model and supporting reports and queries can be implemented as appropriate, not driven by the needs of the business transactional system.


Depending on the design and architecture, the data warehouse may not be considered the system of record for specific types of business transactions, and thus it would not be usable for adjudication. Data refresh methods and frequency may also prevent its use as the system of record for certain types of business transactions. Processing and budgetary constraints in a company may prevent real or even near real-time (NRT) updates to the data warehouse, also constraining its use for particular business practices. Data integration designs may limit the use of the warehouse for specific business activities.

There is a cost, but also many benefits, to implementing and using a data warehouse in your information delivery architecture. Warehouses typically require months and significant resources in personnel and money to design, implement and support.

The Decision

The short-term gains in time and money of not using a data warehouse in your information architecture need to be carefully weighted against the performance and maintenance realities of utilizing the enterprise transactional system for information delivery plus the impact on daily business processing activity. Business transactions processing and information delivery are two very different activities in your organization that should seldom be combined. Enterprise transaction systems today have simplified many of the data source and integration challenges faced previously but have not removed the true business case and value for the data warehouse.


For more information on related topics visit the following related portals...
DW Administration, Mgmt., Performance, DW Design, Methodology and Enterprise Achitecture.

Michael Jennings is a recognized expert with more than 20 years of information technology experience and speaks frequently on business intelligence/architecture issues at major industry conferences and has been an instructor at the University of Chicago's Graham School. He is a co-author of the book Universal Meta Data Models and a contributing author of the book Building and Managing the Meta Data Repository. He works for EWSolutions, a GSA schedule and Chicago-headquartered strategic partner and systems integrator dedicated to providing companies and large government agencies with best-in-class business intelligence solutions using data warehousing, enterprise architecture and managed meta data environment technologies (www.EWSolutions.com). He may be reached directly via e-mail at MJennings@EWSolutions.com.

Solutions Marketplace
Provided by IndustryBrains

Verify Data at the Point of Collection: Free Trial
Protect against fraud, waste and excess marketing costs by cleaning your customer database of inaccurate, incomplete or undeliverable addresses. Add on phone check, name parsing and geo-coding as needed. FREE trial of Data Quality dev tools here.

Design Databases with ER/Studio: Free Trial
ER/Studio delivers next-generation data modeling. Multiple, distinct physical models based on a single logical model give you the tools you need to manage complex database environments and critical metadata in an intuitive user interface.

Free EII Buyer's Guide
Understand EII - Trends. Tech. Apps. Calculate ROI. Download Now.

Data Mining: Levels I, II & III
Learn how experts build and deploy predictive models by attending The Modeling Agency's vendor-neutral courses. Leverage valuable information hidden within your data through predictive analytics. Click through to view upcoming events.

Click here to advertise in this space

View Full Issue View Full Magazine Issue
E-mail This Column E-Mail This Column
Printer Friendly Version Printer-Friendly Version
Related Content Related Content
Request Reprints Request Reprints
Site Map Terms of Use Privacy Policy
SourceMedia (c) 2006 DM Review and SourceMedia, Inc. All rights reserved.
SourceMedia is an Investcorp company.
Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.