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 2

Enterprise Architecture View

What issues should you consider before running your business intelligence requests directly against an enterprise transactional system?

In part one of this series (see the April issue of DM Review, p. 46), we examined some of the rationale for 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 installment of the series, we will look at some of the shortcomings associated with choosing this information delivery methodology. This series is aimed at providing you with some useful insights and questions that will allow you to examine or maybe re-examine your own information delivery architecture ideas and approaches.

There are various issues to consider when using the transactional system as the business intelligence information delivery store. The following are some of the more significant issues to take into account with this approach.

Processing Burden

Without a data warehouse in the information delivery architecture, the enterprise transactional system is required to support additional processing and data storage requirements beyond its primary business purpose. Enterprise transactional systems are supposed to focus on the processing of business transactions (human resource, financial, supply chain, etc.) in the fastest, most efficient manner possible. In this approach, however, the applications are forced to also carry the increased overhead of historical, audit and statistical records, which detracts from overall performance of the system. Transactions that have been processed and closed, from a business perspective, are required to continue to be stored in the system in order to fulfill information requests. Depending on business, legal and statutory requirements, these closed transactions plus their dependent records may need to be accessible for years, increasing the processing encumbrance.

Interdependency

This architecture approach can also compel a greater dependency between the enterprise transactional system and the business intelligence development. Any changes made to the transactional system database could immediately impact reporting and analytics information delivery components, making them unstable or, at worst, providing inaccurate results. Greater scrutiny in planning and project timelines needs to be maintained with this approach. This co-reliance could delay or require compromises on planned enhancements to either the enterprise transactional or business intelligence component.

Data Latency

Ask any business user of the enterprise transactional system how current information needs to be for reporting and analytic analysis and the answer will always be the "latest updated data." Using the enterprise transactional system as the business intelligence store means the data is always the latest, most current information. It also means that the results from an information query made ten minutes ago may not be repeatable or verifiable due to the constant volatility of the data. Data transactions that go through various stages of update during a business cycle or transaction errors will be included in query results. Depending on your business needs, this real-time data access may be acceptable or required.

Data Model Complexity

Using this architecture approach requires the reporting and analytic solution to deal with the complexities of the enterprise transactional data model. These typically highly normalized data model designs for the enterprise system require increased training, analysis, development and maintenance resources to support reporting needs. Queries with this approach tend to be large and very complex due to the large number of tables, lookups and joins involved to retrieve information. A single query could have dozens or more sub-selects with hundreds of lines of SQL in order to fulfill what would seem to be a simple information request. Enterprise control or setup tables may need to be referenced numerous times in a single information request in order to retrieve all required code values or descriptions. All complex analytic operations, calculations and aggregations must be either processed by the business intelligence engine or by extending the enterprise data store specifically for reporting needs. Users and ongoing support personnel will need to have greater in depth data model and SQL knowledge in order to understand the information they retrieve. Testing and debugging elapsed times for reports and analytics tend to be long due to the intricacies and complexities of the enterprise transactional data model.

Limits Performance Tuning

Running your business intelligence request directly against the enterprise transactional system also limits information delivery performance tuning options. Because the enterprise system's primary purpose is transactional processing, database management system tuning cannot optimally be maximized for information retrieval. Database instance and database parameters such as heap sizes, memory, buffers, data distribution, table spaces and indexes can only be utilized or extended to the limits of transaction performance, not business intelligence.

In the third part of this series, we will explore the value and some shortcomings of the data warehouse architecture method for business information delivery.

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