HIREDBRAINS
KILLING TIME: REDUCING OPERATIONAL LATENCY TO BOOST PRODUCTIVITY By Neil Raden
ABOUT THE AUTHOR Neil Raden is the founder of Hired Brains, Inc., http://www.hiredbrains.com. Hired Brains provides consulting, systems integration and implementation services in Business Intelligence, Data Warehousing and Performance Management for clients worldwide. Hired Brains Research provides consulting, market research and advisory services to the Business Intelligence, Data Warehousing and Decision Support industry. Based in Santa Barbara, CA, Raden is an active consultant and widely published author and speaker on data warehousing, business intelligence and information technology strategy. He welcomes your comments at nraden@hiredbrains.com.
Copyright © 2009, Hired Brains, Inc. No portion of this report may be reproduced or stored in any form without prior written permission.
Killing Time:
Reducing Operational Latency
Page 1
Killing Time:
Reducing Operational Latency
Page 2
EXECUTIVE SUMMARY Operational systems, traditionally known as OLTP (Online Transaction Processing), and analytical systems, alternately known as data warehousing or BI (Business Intelligence), are on a convergent path. Partly a result of the effects of Moore’s Law, and partly the result of 1 technological innovation, the artificial divide between operations and analysis is closing rapidly . This promising development is leading to real reductions in latency of operations and the creation of time itself for decision-makers. Almost unheard of a few years ago, the term Operational Business Intelligence (Operational BI) is now a well-used phrase in the industry. But what is Operational BI? Early attempts at building Operational BI systems merely replicated operational data, without integration into the larger data warehouse. This allowed the BI infrastructure to produce reports, off-loading the process from the operational systems. Though a step in the right direction, the capability to make operational decisions based on fast analysis of the data is what distinctly makes Operational BI most useful. While reporting and informing provide value, Operational BI fosters better decision making by providing not only real-time information, but suggestions on the best course of action that can yield better decisions at an operational level, such as call center, inventory or point-of-sale. With the inclusion of rules/reasoning engines, certain types of high-volume operational decisions can be automated completely. Operational BI can and should manage data beyond that typically seen in data warehouses. Process events, for example, or streaming data from external sources are captured and stored because they are important, but modeling this type of data for analytics is not typical in existing BI approaches. Operational BI is different from traditional BI, which is largely an after-the-fact process of reports and analysis. Operational BI is more concerned with immediate, important issues such as comparing the results of different reactions, creation of time and elimination of latency. For example, the simple decision to pay an invoice depends on many factors, such as the following: • The trade-off between a discount not taken and rejecting the invoice • Penalties for late payment • The type of contract or invoice • Whether previous invoices for the vendor had required purchase orders • The relationship with the vendor • The likely result of not paying the invoice To a large extent today, these decisions are made manually, often by staff that is capable of more valuable work. This is the type of decision-making that an Operational BI system can help with. An Operational BI system, however, requires a different architecture and methodology than current BI practice. Traditional systems are more likely to contain historical data about the invoice and vendor, but may not have the latest cash balances to check against. Going a step further, the payment of an invoice may involve external factors, such as other systems impacting available cash. Operational systems typically work within the confines of their own data models, but an Operational BI system can and should have access to other relevant data. Currently, most Operational BI discussions center on speeding up data warehousing processing to allow for the somewhat watery terms “right time” or “near real time,” updating and integrating data from operational systems at shorter intervals. But these approaches overlook the other crucial components of Operational BI – outstanding performance at satisfying analytical queries with massive amounts of data, exploding user populations (including unattended processes), and growing complexity in the data models and queries. 1
For example, Service-Oriented Architecture enables the creation of hybrid applications that can be composed to combine operational and analytical functions, but more importantly, the adoption of enterprise software, such as ERP and CRM, heightens the need for analytics of operational data.
Killing Time:
Reducing Operational Latency
Page 3
The driver positioned to enable these capabilities is speed. But is speed itself really sufficient? Technically, speed is just a measure, not a result. I may be driving 55mph, but where am I going? The assembly line may be moving 40ft/sec, but what is the output? When it comes to Operational BI, what really matters is the results, the creation of time and the elimination of latency.
Time Creation and Latency How does one create time? It may seem like a violation of the fundamental principles of physics, and it is, but the perception of creating time is very real. When systems run faster and more smoothly, time is created for doing other things. The most precious and latent resource in any organization is the productive effort of people who cannot operate at their optimal level due to external constraints. This is not to imply that everyone in an organization is a peak performer just waiting for the opportunity to excel, but there is no question that everyone spends a substantial amount of time in activities with less value than those for which the person is trained. Alleviating someone of these efforts frees them to focus their attention on the efforts that match their level of skill and training. It has the effect of adding hours to the workday. That is creating time. But how do you do it? Handoffs—or more formally, latency, the waiting time between steps in a process—contribute to total cycle time in most systems today. The reasons for latency are, of course, a lack of speed in steps that need to synchronize. Even processes designed carefully with Business Process Management (BPM) tools rely heavily on human intervention at many crucial decision points. The latency in those instances, between machine, human and machine, can be substantial. For example, data flowing in and being positioned for the next step can be stalled waiting for storage in intermediate, persistent structures to save CPU cycles rather than to process the data in flight.
Decisions, Not Reports…in Practice The whole point of Operational BI is to improve the speed and effectiveness of operational decisions. “Decisions” is the key word. Fast operational reports do not translate into Operational BI. Unless the decision is part of the process, it amounts to only faster reporting. Operational decisions are not strategic, that is the domain for strategists. Operational decisions are small and plentiful, but occupy an inordinate amount of decision makers’ time. To be effective in creating time and reducing latency, a system designed to support operational decision-making must be precise, agile, consistent, fast, and cost-effective. Many leading-edge organizations already apply operational BI principles to their business to help make immediate analytical decisions. Some examples are: • • • • • • • •
Health Insurance Underwriting Airline Rebooking Priority Call Center Routing Funds Availability Manufacturing Quality Pharmacy Benefits Pricing Decisions Warranty Claims
All of these examples involve complicated decision processes that depend not only on current policy but on quick analysis of historical and current data to arrive at a decision. The addition of an analytical element distinguishes Operational Business Intelligence from operational systems.
Killing Time:
Reducing Operational Latency
Page 4
Magazine Publisher A U.S. magazine publisher was unable to satisfy pricing requests on either a timely or a consistent basis. The pricing process was complex (for a human) and required specialists with specific knowledge and the use of detailed spreadsheets. The calculations involved both direct variables, such as color, time of year and creative development, as well as indirect data, such as available space, client history and corporate discounts. To improve the operational decision making process, the publisher developed a solution which involved a decision engine coupled with a real-time analytical query processor, a relational database designed for extreme processing of analytical queries. The result was that analysts freed up 30% of their time for more important tasks such as account management. Salespeople are able to provide what-if analysis with customers and generate price quotes in real time, improving all around satisfaction.
Automobile Manufacturer A major headache for automobile manufacturers is handling warranty queries from dealers. There isn’t time for analysts to pore through the details of a claim including the date of purchase, model, year, part, circumstances and previous maintenance work. Customers are waiting and dealer service managers need answers fast. A solution was implemented to process all warranty claims for validity, applying a complicated (but easily modified) set of rules to not only evaluate the claims but to explain why rejected claims were rejected. Service managers could get coverage answers quickly, without having to manually look up and reconcile customer and manufacturer records. The result was lower maintenance costs and a much higher rate of automatic processing, with rules controlled by warranty experts rather than programmers who didn’t understand the rules.
Challenges of Operational BI Storing data and retrieving it for another purpose creates latency. Loading data into structures optimized for analytical queries, such as data warehouses constructed from general purpose relational databases, adds waiting time. Is it possible to avoid this step? Can the stream of transactional data be split before being committed to operational systems to serve more than one purpose? In this way, the data can be managed simultaneously with its storage in the operational systems’ databases. For true Operational BI, some data can be acted upon in the stream itself, and for other applications it can be committed to extremely fast, scalable databases for use as needed, modeled for the purpose (data warehouses are typically not modeled for operational analysis). One inescapable fact about data is that it can be read without being lost. It doesn’t matter how many processes are watching a stream, the stream doesn’t lose its message. Consider a baseball game. The manager in the dugout and the umpire behind the plate both witness the same event, but they process the information with different models, so to speak. The umpire evaluates the trajectory in real time and even though it is very close to the edge of the strike zone, makes the decision at the instant the ball hits the catcher’s mitt that it’s a third strike. The manager also views the trajectory in real-time and determines the pitch as a ball and, of course, leaps from the dugout to argue the call. While it is the same data, the different models employed by the different actors are used differently, but the manager does need to wait for the umpire to commit the event to his database only to extract it and store it another. In an OLTP system, an event causes data to be generated and stored, such as a scan of an item at a checkout counter. The data is captured in real-time, chronicling the event. However, items may be scanned twice or a defect is observed in the product, causing the scan to be backed out. Only when the checkout is complete are the scans batched together with header information, such as the time and date, aisle number, method of payment, etc. This represents another transaction and is also stored in real-time (somewhere, though not necessarily the permanent location). This constant stream of data can be, in many cases, captured in stream and directed to additional locations, including all of the in process steps, not just the final results.
Killing Time:
Reducing Operational Latency
Page 5
So the steps for avoiding latency in Operational BI systems are to eliminate unnecessary data transformation and to invoke a simultaneous identical stream of data for analytic processing, if possible. However, if we are able to stream data into an analytical structure in real-time, will we be able to use it at nearly the same pace? There are very promising alternatives available, especially those that stretch the relational database idea to perform extremely fast processing on extremely large collections of fine-grained data.
Relational Performance and Analytical Queries Classic relational databases, generally optimized for transaction processing, are defined by their columns, not their rows. Operations happen on a column-wise basis, whether it joins, sorts, filters or calculates. Rows are mostly irrelevant. But transactions are defined by combinations of columns, one row at a time, so the meaning of a row is implicit. When inserting a single record to represent a scan or an ATM withdrawal, relational databases have been designed and refined over time to be incredibly efficient, scalable and secure. They can also be used to scan tables searching for matches and performing certain operations, such as summing numeric values. But operational decisions are often sequential or even branching, such as with decision trees. Classic relational databases are generally quite poor at performing these queries unless they have been physically tuned for exactly the queries presented, which, of course, have to be crafted in advance. Interestingly, it is not the actual engineering of a relational database system that is “relational” by definition, it is the system’s ability to use the standard language for databases, SQL (and, of course, support all of the operations that can be defined with SQL). In other words, two relational database management systems, with completely different engineering should, with the same physical schema and data, return the same results using the same SQL, in principle. There may be differences in indexing and how the optimizer works, but query results should not differ. As a result, a database engine can be designed for a certain kind of work, but can still be addressed by the same language, which is the reason relational databases have endured . The generality of SQL can contribute to performance problems. It is easy to craft SQL queries to retrieve or insert some records, but devilishly difficult to create queries to do analytical work. Relational databases that were designed for OLTP have been noticeably deficient in providing decent response times to these complicated queries, though they have been improving. In contrast, new relational database technologies, using column-based storage models with massively parallel processing, achieve exponential performance gains for analytic processing when compared to their traditional OLTP sisters. The best performance for analytical queries has always been and will continue to be from relational databases designed specifically for analytical queries, such as the ParAccel Analytic Database. Response times can be improved by a factor of 10, 100 or even 1,000, which is not only possible, it is available now. Using a traditional data warehouse and a general purpose relational database for the analysis needed in Operational BI makes no sense as it hinders the decision making process.
Purpose-Built for Analytical Speed Tuning a traditional relational database for analytical work is possible up to a point. The first step is usually to create aggregate data. In traditional BI, most queries search for higher-level, aggregated data, not the fine-grained transactional data. This is the first and most common approach. It doesn’t work for Operational BI because the analysis almost always starts with the transactional detail. The next step is to create indexes, because it is much faster to manipulate records in memory than to spool them from disk, and indexing is, basically, a compression of the record. There are three problems with indexes. The first is that they slow down the update process, sometimes to a crawl. The second is that they have to, in most cases; anticipate how the data will be searched. And third, though they reduce the number of records that are ultimately brought into memory, the entire record is still read using valuable compute cycles.
Killing Time:
Reducing Operational Latency
Page 6
There are very attractive alternatives for fast analytical work with linear scalability and massively parallel computing available today. These solutions marry standard hardware with specialized analytical DBMS software. Commodity pricing for servers and memory are always a good bet if the price-performance is right and the TCO (total cost of ownership) compares well. However, good software is required to deliver analytic performance from good hardware. Good software, such as the ParAccel Analytic Database, that physically arranges the tables in a columnar fashion so that each field or attribute in a record becomes, in effect, a separate table provides a compelling alternative to OLTP databases. This way, only those columns that are needed for a particular query are read, vastly reducing the work that needs to be done. In addition, this design promotes the use of wider (logical) tables and minimizes joins that are very time consuming. It has been well understood in the data warehousing industry that for analytics relational databases designed for analytical work consistently outperform those that are general purpose. They usually have a lower TCO as well, because they are easier to set up and maintain for their targeted purpose than one that has to be tuned and coddled to squeak out better performance. The ability to satisfy complicated analytical queries with lightning-fast performance is an absolute requirement for real Operational BI. The creation of time and elimination of latency are essential.
CONCLUSION In summary, creating time is like money in the bank. While it is difficult to measure the financial benefit of relieving knowledge workers of routine work, few knowledge workers today have time on their hands and creating some by automating decisions for higher value work will give them some relief. Doing this requires removing latency from Operational BI by removing unnecessary intermediate steps and employing the right tools that can provide the extreme performance required for Operational BI. Existing data warehouse tools and methodologies are too slow and too rigid. Specialized analytic databases with massive parallelism and a columnar orientation provide query and load performance and attractive deployment options, are well-suited for Operational BI.
Killing Time:
Reducing Operational Latency
Page 7
BI Thought Leader™ Sponsored by
For more information about ParAccel and the ParAccel Analytic Database™, please visit www.paraccel.com or send e-mail to info@paraccel.com.
Killing Time:
Reducing Operational Latency
Page 8