FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Information Delivery Shortcuts, Part 3

Enterprise Architecture View

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.

Maintenance

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.

Limits

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.


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.

For more information on related topics, visit the following channels:



Industry Vendors