Technology Executives Club | Vendors | About Us | Contact Us
Saturday, December 20, 2014 
Home   Subscribe    Webinars   Events   SIGS   Online Education    IT Leaders Circle   Directory   Membership   Vendors

 


Business Intelligence Architectures

By Andy Grohe, Nexus Consulting Group


Introduction

Many companies today find that they are data rich and information poor. They have many transaction systems generating data, but they are unable to turn their most valuable asset, data, into actionable information. A data warehouse infrastructure can provide a company with the means to exploit their data assets. The purpose of a data warehouse environment is to provide efficient information access for corporate decision-making, hence the popularity of the term "Business Intelligence"(BI).

Mistake #5 in The Data Warehouse Institute's "Top Ten Mistakes to Avoid When Building a Data Warehouse" is "Postpone implementing a sound data warehouse infrastructure". A structured data warehouse (DW) solution offloads complex reporting burdens from transaction systems, while providing flexible views of business data. Today, there are several differing architectures available to facilitate corporate reporting. These range from reporting directly from the source systems to a comprehensive data warehouse environment where the data is extracted from the source systems, cleansed and standardized, and loaded into a repository built for efficient reporting. This whitepaper will describe these approaches and the tradeoffs to consider when choosing between them.

Transaction Systems Reporting
Many small organizations and early BI adopters often access data for reporting directly from On-Line Transaction Processing (OLTP) systems. The primary advantage to reporting off of the OLTP systems is speed to information. This type of reporting is often used for reports that need to be up-to-date with transactions occurring within the last 24 hours. The data accessed in the OLTP system is the "raw" transaction data input by data entry clerks and the like.

When accessing the OLTP system directly for inquiries, the system performance for transactions is often adversely affected. The performance of the user inquiries also suffers because they must compete with transaction processing for available system resources.

An additional complexity associated with reporting directly from the source system is that the organization of the data is rarely intuitive to a business user. The data has been stored to optimize data entry, not to optimize data access. Lastly, OLTP systems often do not store historical transactions beyond the past quarter or year. This can make an inquiry about the sales performance of a product over time quite difficult.

Replicated OLTP Reporting
An alternative, one step removed from reporting straight from the OLTP system, is to create an off-line data store that is a replica of the production data store. All user inquiries and reports are routed to the off-line data store.




This approach allows the OLTP systems to continue to process transactions, while another data store services the reporting needs of the end-user. This approach represents an improvement performance-wise over option 1, yet the organization of the data is still not intuitive for the business user and historical transactions are typically not available. The biggest concern for both approaches is the lack of data cleansing that occurs, which means that raw transactions must be relied upon for reporting and analysis. In large organizations with many data entry points, this can lead to a never ending search for a single version of the truth.

Data Mart Reporting

One step further in the maturation of a BI reporting solution is to feed transaction data to a system optimized for reporting, also known as a Data Mart. One technique for optimizing data for reporting is called "Dimensional Data Modeling". This allows the system designers to portray the data in business terms vs. the technical constructs of the OLTP. This type of computing is called On-Line Analytical Processing (OLAP). OLAP is a powerful way to view data across multiple business dimensions. Examples of business dimensions are Customers, Suppliers, Time, Product, and Geography.



In order to transfer the data from the OLTP system(s) to the Data Mart(s), additional mechanisms must be put in place. While these mechanisms can delay user access to the data, they optimize the data for easy reporting. Frequently, the data transferred to the Data Mart is still "raw" and has not been through a thorough cleansing process. Data Marts store historical transactions, allowing business users to evaluate trends over time.

The primary limitation of a Data Mart approach is scalability. In order to keep their intuitive features, Data Marts typically have a singular topical focus such as Sales, Inventory, Financial Analysis, or Production. It is not uncommon for a large organization to have as many as twenty Data Marts for each division. Introducing a Data Mart to your reporting architecture does not solve data consolidation and cleansing issues. These issues arise, for example, when trying to combine order quantities from a Sales Data Mart with sales compensation from an HR Data Mart to help calculate product profitability. Putting all of this information into one Data Mart would overwhelm most users with too much information. Enter the Enterprise Data Warehouse...

Enterprise Data Warehouse Reporting
An Enterprise Data Warehouse (EDW) is designed to combine data from multiple OLTP systems and provide consolidated and cleansed data to an array of Data Marts. The key to this approach is the application of business rules to the source data. The business rules specify consolidation techniques, standardization of codes, cleansing of "dirty" data, and how to track historical transactions. This allows each system (OLTP, EDW, and DM) to focus on what it is that they are designed to do.
 



The data fed into an EDW is consolidated across multiple source systems. After the consolidation, standardization and cleansing rules are applied to the data. This allows the business users to reference the data consistently regardless of source. As changes are made to both OLTP systems and Data Marts over time, the EDW acts as a buffer to minimize rework on the data and reporting infrastructure.

Once the data is clean and has been consolidated, it is stored in a Relational Database Management System (RDBMS). The data is stored in a relational format, allowing the EDW to efficiently store transaction history and modify its design over time. Beyond transaction history, an EDW also stores changes to business entities over time. Sales persons often change regions and sales managers are reassigned new team members. Sales booked under a previous organization should not be restated to reflect a new organizational hierarchy. The transactions should reflect the world as it was when those transactions occurred.

The obvious argument against an Enterprise Data Warehouse is one of cost-to-implement. However, the design and development approach can be structured such that the EDW is built incrementally as new business areas are added, thus deferring much of the cost. Under this approach, the value associated with high quality usable data outweighs the costs of developing a robust reporting architecture.

Summary

The matrix below depicts a summary of the features discussed in this article. As highlighted above, each approach has advantages and disadvantages as a viable reporting architecture.
 

OLTP

Replication

Data Marts

Enterprise

Data Warehouse

Data Quality

D

D

C

C

Ease of Inquiry

D

D

C

C

System Performance

D

D

C

C

History

D

D

C

C

Scalability

D

D

D

C

Speed to Information

C

C

D

D


High quality, flexible reporting solutions are needed now more than ever before. Companies need a reporting architecture that allows them to leverage their data to make complex decisions. While many reporting approaches exist for companies to pursue, an approach that incorporates an Enterprise Data Warehouse provides a solid foundation of data quality and flexibility. As organizations continue to add more data sources to their list of assets, a rock-solid data warehouse infrastructure will optimally support their decision-making efforts


 

Free Webcasts

Free Webcast & Whitepaper of the Week Newsletter!

Register Now

Featured Sponsors









 

 

 

 

 

 

 

 

 

 

 

 

 

 

Free Weekly  |   Events  |   About Us  |   Join  |   Vendor Area  |   Get Leads

Copyright © 2014 Technology Executives Club, Ltd. All rights reserved | Privacy Policy