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.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access