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.
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.
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.
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.
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
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access