DM Direct: Online Newsletter for Business = Intelligence & Data Warehousing Professionals

***** ISSUE : MARCH 15, 2002 *****

Issue=20 Introduction | Article: 1=20 2=20 3=20 4 =20

3D"Reporting

Many companies today find that they are data rich and = information poor.=20 They have many transaction systems generating data, but they are = unable to=20 turn their most valuable asset, data, into actionable information. = A data=20 warehouse infrastructure can provide a company with the means to = exploit=20 their data assets.

In the "Top Ten Mistakes to Avoid When Building a Data = Warehouse"=20 published by The Data Warehouse Institute, number 5 is Postpone = implementing a sound data warehouse infrastructure. A = structured data=20 warehouse (DW) solution offloads the reporting capabilities from=20 transaction systems, while providing flexible views of business = data. The=20 purpose of a data warehouse environment is to provide efficient=20 information access for corporate decision making, hence the = popularity of=20 the term, business intelligence (BI). Today, there are several = differing=20 architectures available to facilitate corporate reporting. These = range=20 from reporting directly from the source systems to a comprehensive = data=20 warehouse environment where the data is extracted from the source = systems,=20 cleansed and standardized, and loaded into a repository built for=20 efficient reporting. This article will describe the most common = approaches=20 for corporate reporting and the tradeoffs to consider when = choosing=20 between approaches.

Transaction Systems Reporting

Many small organizations and early BI adopters often access = data for=20 reporting directly from online transaction processing (OLTP) = systems. This=20 approach typically utilizes a tool that has native connectivity to = the=20 OLTP database or the use of a generic data access protocol such as = ODBC,=20 JDBC or OLE DB to name a few.

Figure=20 1: Transaction Systems Reporting

The primary advantage to reporting from OLTP is speed to = information.=20 You only need to wait until the transaction has been entered = before making=20 your inquiry. This type of reporting is often needed for = time-sensitive=20 reporting. Time-sensitive reports are those that need to be up to = date=20 with transactions occurring within the last 24 hours. A training=20 administrator looking for open seats during a training session = would want=20 the most recent transactions. These types of reports often provide = a=20 listing of transactions as they have occurred in the system.

The data accessed in the OLTP system is the "raw" transaction = data=20 input by data entry clerks and the like. This data is often = "dirty,"=20 meaning that it does not accurately capture what it is meant to = capture.=20 For example, customer names are often input in varying formats =96 = Bob=20 O=92Neill, Robert O=92Neill and Bob O=92Neal all refer to the same = person. To=20 make matters worse, these customer names can be input from a = number of=20 sources throughout a large company. With each division using a = separate=20 system, many reports must be created and consolidated to get = answers to=20 basic questions such as who are our top customers?

An additional complexity associated with reporting directly = from the=20 source system is that the organization of the data is rarely = intuitive to=20 a business user. The data has been stored to optimize data = entry,=20 not to optimize data access. This often leads to = specialized=20 personnel devoted to producing reports for others in the = organization.=20 These team members are increasingly relied upon to provide new and = modified reports, preventing them from completing their assigned = duties or=20 forcing a department to dedicate staff to producing reports.

When accessing the OLTP system directly for inquiries, the = system=20 performance for transactions is adversely affected. In a large=20 organization with many users making inquiries of the data, this = can=20 quickly drag the system to a halt. The performance of the user = inquiries=20 also suffers because it must compete with transaction processing = for=20 available system resources.

Lastly, OLTP systems often do not store historical transactions = beyond=20 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=20 system is to create an offline data store that is a replica of the = production data store. The replicated data store can be refreshed = as=20 needed and often makes use of the same reporting tools. All user = inquiries=20 and reports are routed to the offline data store. This offline = data store=20 can take a number of forms, ranging from flat files extracted from = the=20 OLTP systems to replicated database tables.

Figure=20 2: Replicated OLTP Reporting

This approach allows the OLTP to continue to process = transactions,=20 while another data store services the reporting needs of the end = user.=20 This benefits both equally. The system resources of the OLTP = server are=20 dedicated to quickly processing transactions, while the offline = data store=20 is dedicated to the reporting needs of the organization. However, = many of=20 the problems associated with OLTP reporting remain. The data is = still=20 dirty, the organization of the data is still not intuitive for the = business user and historical transactions are typically not = available.

Replicated data is often transferred from the OLTP systems via=20 transaction logs, database replication or batch files. Each = approach has=20 benefits and drawbacks as outlined below.

The preferred means to replicate data is to make use of = database=20 transaction logs. Transaction logs take advantage of the built-in=20 abilities of the OLTP system to track database changes. = Transaction logs=20 are commonly used for database backup and recovery. They store all = data=20 structure and value changes occurring in the database. These = changes are=20 saved to a file, copied and applied to another database. This = approach=20 works very well for both sides while not impacting = performance.

Establishing database replication quickly transports the = changed data=20 to the replicated data store. The user only needs to wait for the=20 transaction to clear the replication queue (typically 10 =96 15 = minutes).=20 However, there are several drawbacks to using database replication = for=20 corporate reporting. The OLTP database must spend system resources = on=20 processing replication requests one at a time. This is in addition = to=20 transaction logs that are already being generated. Database = replication is=20 also timely to set up and maintain for the many OLTP systems that=20 typically need to feed enterprise-level reports.

The final approach of using batch programs to replicate the = data is=20 both time-consuming to set up and maintain. It also does not take=20 advantage of any of the built-in features of the database vendors, = such as=20 transaction logs or database replication.

Data Mart Reporting

One step further in the maturation of a BI reporting solution = is to=20 feed transaction data to a system optimized for reporting, also = known as a=20 data mart. While OLTP systems are optimized for processing = transactions,=20 data marts are optimized for efficient reporting. The technique = for=20 optimizing data for reporting is called dimensional data modeling. = This=20 allows the system designers to portray the data in business terms = vs. the=20 technical constructs of the OLTP. The average business user should = be able=20 to browse a data mart and obtain moderately complex information = with=20 little or no training. This type of computing is called online = analytical=20 processing (OLAP). OLAP is a powerful way to view data across = multiple=20 business dimensions. Examples of business dimensions are = customers,=20 suppliers, time, product and geography. OLAP can answer such = questions as=20 "How many widgets were sold by which sales person in each region = last=20 month?" and "How do those numbers compare to the prior month and = the same=20 period last year?"

Figure=20 3: Data Mart Reporting

In order to transfer the data from the OLTP system(s) to the = data=20 mart(s), additional mechanisms must be put in place. While these=20 mechanisms can delay user access to the data, they optimize the = data for=20 easy reporting. Frequently, the data transferred to the data mart = is still=20 raw and has not been through a thorough cleansing process. In this = approach, the users are accessing a separate system from the OLTP = system.=20 This allows both systems to dedicate resources to a single purpose = and=20 work as efficiently as possible. An additional advantage with a = data mart=20 is the ability to store historical transactions. This allows = business=20 users to evaluate trends over time that would not be possible from = OLTP=20 systems.

The primary limitation of a data mart approach is scalability. = In order=20 to keep their intuitive features, data marts typically have a = singular=20 focus such as sales, inventory, financial analysis or production. = It is=20 not uncommon for a large organization to have as many as 20 data = marts for=20 each division. Introducing a data mart to your reporting = architecture does=20 not solve data consolidation and cleansing issues. These issues = arise, for=20 example, when trying to combine revenue numbers from the sales = data mart=20 with headcount figures from the HR data mart. Putting all of this=20 information into one data mart would overwhelm most users with too = much=20 information. Enter the enterprise data warehouse.

Enterprise Data Warehouse Reporting

An enterprise data warehouse (EDW) is designed to combine data = from=20 multiple OLTP systems and provide consolidated and cleansed data = to an=20 array of data marts. The key to this approach is the application = of=20 business rules to the source data. The business rules specify=20 consolidation techniques, standardization of codes, cleansing of = dirty=20 data and tracking historical transactions. This allows each system = (OLTP,=20 EDW and DM) to focus on what it is that they are designed to = do.

Figure=20 4: Enterprise Data Warehouse Reporting

The data is often fed into an EDW in a standard format. = Specific file=20 layouts or XML schema apply to each type of data being fed into = the EDW.=20 For example, when sending sales information to the EDW, it is = consolidated=20 across multiple source systems. Despite the number of divisions = tracking=20 customers, each must be consolidated into a master list.

After the consolidation, standardization and cleansing rules = are=20 applied to the data. This allows the business users to reference = the data=20 consistently regardless of source. Those records that do not meet = quality=20 specifications can be placed "on hold" until a predefined action = is taken=20 to remedy the situation. This data can also be mapped to a = "default" value=20 according to business rules.

Once the data is clean and has been consolidated, it is stored = in a=20 relational database management system (RDBMS). Quite often, this = RDBMS=20 uses the same technology as the OLTP systems, e.g., Oracle, IBM, = SQL=20 Server, etc. The data is stored in a relational format, allowing = the EDW=20 to efficiently store transaction history and modify its design = over time.=20 Beyond transaction history, an EDW also stores changes to business = entities over time. Sales people often change regions and sales = managers=20 are reassigned new team members. Sales booked under a previous=20 organization should not be restated to reflect a new = organizational=20 hierarchy. The transactions should reflect the world as it was = when those=20 transactions occurred. Consequently, the EDW is designed to = account for=20 these co-called slowly changing dimensions.

Since the data has been cleansed and consolidated, the EDW can = serve as=20 the single source for corporate data marts. The data mart is still = the=20 preferred means to allow user access to data, with the EDW acting = as the=20 single source of regularly updated data.

As changes are made to both OLTP and data marts over time, the = EDW acts=20 as a buffer to minimize rework on the data and reporting = infrastructure.=20 An EDW directly supports companies who are planning to migrate = from legacy=20 systems to an ERP system. The EDW is designed to process on = standard=20 transaction formats based on the target subject area. Less = emphasis is=20 placed on manipulating each source system; rather the focus is on = how to=20 massage the source system to match the standard format. Once the = data is=20 in a standard format, it can be processed in a standard way across = data=20 sources.

This applies equally to companies in a merger and acquisition = scenario.=20 The output of an EDW is targeted for a dimensional data mart. = These data=20 marts use standard data structures, and star and snowflake schemas = to=20 store data for display to the user through a front-end tool. These = days,=20 the major business intelligence vendors can access these standard = formats=20 and allow the user to easily navigate their business data. When = the=20 company purchases a new tool, as always seems to happen, the tool = will=20 attach to the relevant data mart to provide reporting capability. = All of=20 this stability is provided by the EDW architecture.

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

Summary

The diagram in Figure 5 depicts a summary of the features = discussed in=20 this article. As highlighted previously, each approach has = advantages and=20 disadvantages as a viable reporting architecture.

Figure=20 5: Features of Reporting Architectures

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

Andrew Grohe is a practice director at Nexus Consulting Group = where he=20 helps clients plan, manage and implement robust data warehousing=20 solutions. Grohe has 10 years of experience in business = intelligence,=20 enterprise resource planning and custom systems implementation. He = may be=20 reached at (312) 782-3500, ext. 253 or via e-mail at agrohe@nexcongroup.com. =

^=20 top

About DM Direct
DM Direct is a weekly, HTML-based = e-mail=20 newsletter distributed to more than 26,000 subscribers in = the IT=20 industry. The editorial focus of DM Direct parallels that of DM = Review magazine, and the featured articles are only available = in the=20 newsletter. Past Issues:	To subscribe: Send an email to DMDirect@tfn.com and specify = 'DM=20 Direct' in the subject line. To unsubscribe: Send an e-mail = with=20 "SIGNOFF DMDIRECT" in the first line of the message body to mailto:%20RemovesDMDirect@tfn.= com