Getting support from the Active Data Warehouse

Take a good look at the questions you're asking of your data.

by Randi Zeehandelaar and Pepper Lawrence

There's a growing trend in the business world toward real-time data warehousing and analytics. In the past, data warehouses handled mostly strategic applications, which didn't require instant response time, direct customer interaction or integration with operational systems. Today's information is used increasingly in the moment to drive real-time customer interaction.

Companies with an active data warehouse will be able to appropriately interact with a customer to provide superior customer service and increased revenue opportunities for the business.

For example, Fred calls into a customer service center complaining about the number of dropped calls. Through the call center application, the customer service representative (CSR) can access specific details about all of Fred's interactions with the company—regardless of product line or sales channel—along with his customer profitability score, because it is stored in an active data warehouse.

The CSR knows more about Fred's relationship and history with the company and how much contact he has had with the company over the past year. Based upon this information the CSR can now provide the appropriate information to Fred to resolve his phone service issue to his satisfaction. Additionally, the CSR will have the insight to cross-sell or up-sell additional services based on the details in Fred's profile and company interaction information.

This move to active data warehousing has given rise to an entirely new class of database queries in addition to the strategic queries currently used by decision support or analysis application developers. These are known as tactical queries. Figure 1 illustrates the differences between strategic and tactical queries.

As more companies evolve to this information model, some data warehouse managers will find that implementing an active data warehouse introduces additional factors to be analyzed and managed, such as:

• Impact of increased network traffic introduced by application-to-application communication
• Resource contingencies of mixing long-running strategic queries with short-running tactical queries
• Working with business users to determine requirements and set service-level goals (SLGs) and service-level agreements (SLAs)
• Monitoring and analyzing workloads to determine compliance to SLGs
• Security, encryption and authentication
• Data quality
• Integration with enterprise system management tools like BMC Patrol, Tivoli or CA-Unicenter
• System and data availability
• Batch window requirements
• System agility to respond to performance bottlenecks

Early warning system
Workload management becomes especially important when you mix long-running strategic queries with short tactical queries.

"The average tactical application in Teradata today consumes maybe one percent or less of the platform resources—that's just a whisper," says Carrie Ballinger, a senior technical consultant in Teradata's Active Data Warehouse Center of Expertise. "You can give that kind of work a very high priority, and it's not going to really slow down anything else in the system."

She adds that the kinds of work that consume large amounts of resources are almost always moderate- or low-priority. These might be long-running decision support or analysis queries. "Those are very valuable business questions, but people are not usually sitting at the screen holding up their work and waiting for an answer, like they might be at a call center."

However, when you mix a significant number of short-running, high-priority queries with long-running, lower-priority queries, resource contention is increasingly likely to occur, which could impact the ability to deliver results within the timeframes required by the business. That makes workload management an important factor in the success of the active data warehouse.

Think of workload management as an early warning system. Don't wait for a crisis to happen if there are signs that can avert it—instead, look at what your workload is telling you and try to stay in front of any problems. (See accompanying story, "Introducing Teradata Active System Management.")

"Combine your monitoring with an understanding of trends, patterns, and your particular platform," says Ballinger. "Then try to put some fences around things so everything runs in a more orderly and predictable way."

Focus on service
As data warehouses add new tactical applications, their end users will come to expect the same response time they currently receive from their operational systems. If you're transitioning to an active data warehouse with performance capabilities measured in seconds rather than minutes, both IT and line organizations may want to consider creating a service-level agreement or service-level goals spelling out target response times, arrival rates and percentage of on-time service (among other factors).

But consider the goals carefully and make sure that they are complete and realistic, because some companies may enact monetary penalties on the IT department if service-level agreements or goals are not met. So the impact of unmet agreements isn't just on resources but also on an organization's bottom line.

There are also cultural issues to consider in effectively managing an active data warehouse. Data warehousing organizations are sometimes established as a small island within the larger IT organization. All too often, such isolation creates problems.

For example, system outages may not be visible to a data warehouse because it's considered to be a different part of the organization. Systems programmers may overlook Teradata Warehouses as a resource to be monitored through enterprise systems management tools like Tivoli or CA-Unicenter.

A move toward working in real time usually requires tighter integration between the data warehouse, the data warehouse team and the rest of the IT organization. It may involve simple remedies, such as data warehouse team members being invited to meetings or included on an e-mail list, or it may involve expanding enterprise systems management processes to include the data warehouse.

Best practices
Security and data quality issues also become more important with tactical applications, and best practices should be developed to cover them. Solutions for managing active data warehouses are often grouped as "best practices." It's a generalized description but accurate, since these strategies could benefit almost any data warehouse. At the same time, they are especially valuable if you're attempting to improve response times.

Security and authentication issues also loom large. In the past, a typical data warehouse might have fewer than 100 users. Today, some Teradata customers are running applications that support 6,000 or more users. As the number of users increases, data warehouse managers need to consider whether all the users are supposed to have access to the same volume of data. The latest Teradata releases allow encryption of information in the data warehouse. For example, a company may decide to limit access to some of the data in the employee file by encrypting the name or salary.

With an active data warehouse, data quality is even more crucial than before, because the real-time or near real-time information interacts more closely with operational systems. Similarly, a company can use Teradata Warehouse Miner to analyze the information in the data warehouse to determine if it's in sync with operational systems.

To analyze the performance of the queries and workloads, data warehouse managers need performance information. Teradata Statistics Wizard can determine which information from the query log should be saved for further analysis.

Of course, allowing the company to integrate all the information in the enterprise is also critical to successful management of an active data warehouse. At Gartner's 2005 Application Integration and Web Services Summit, Paulo Malinverno, research vice president of Gartner Research, presented the concept of an "integration competency center," which would serve as the center of expertise for things like metadata management, data modeling, integration architecture and system management. One of the points stressed at the conference was that the data warehousing environment must be part of this center of expertise to achieve the desired effect.

Looking ahead
Teradata is aggressively addressing key issues for organizations seeking active data warehousing. Teradata Warehouse 8.1 introduces three new or enhanced products under the umbrella of Teradata Active System Management (TASM). (For more on TASM, please see Ask the Expert.) TASM advances our ability to manage the active data warehouse by:

• Helping control resource allocation to enable a comprehensive classification of all the queries that are coming in. So right from the start, the queries will run at the correct priority level.
• Providing automated exception handling. Queries that run in an inappropriate manner can be automatically detected and corrected on the fly.
• Graphically visualizing real-time systems performance and longer-term trends.

Figure 2 outlines the Teradata products that fall under the TASM umbrella.

As end users demand faster response times, data warehouse managers will need to refine strategies for staying ahead of the curve. Making use of Teradata resources can benefit customers and fellow employees. Cycles won't be wasted, and data warehouse managers won't run into delays getting answers that are essential to business profitability. T

A closer look at the newly introduced group of monitoring utilities: Teradata Active System Management

THE NEED FOR EVER FASTER response times is putting increased pressure on data warehouse managers to make their systems more reliable. Service-level agreements can define the framework for providing consistent and reliable service, but you'll need automated tools to monitor, visualize and adjust those service-level agreements. To meet those needs, Teradata Warehouse 8.1 introduces a new generation of workload management known as Teradata Active System Management (TASM). TASM is a grouping of products to assist in the monitoring and management of the varying mixed workload of an active data warehouse. TASM currently consists of three products: Teradata Workload Analyzer, Teradata Dynamic Workload Manager and Teradata Manager. Teradata Workload Analyzer taps into the database query log and system tables. It analyzes information and recommends a set of workload definitions. It also splits up resources among the various workloads and determines how they should be allocated to meet service-level goals (SLGs). You may have a workload definition for tactical queries, saying that to run these queries in three seconds or less, certain types of resources need to be applied. See Figure 3 for an example of SLGs recommended by Teradata Workload Analyzer. You might also have a workload definition for low-priority queries. These might be queries that run in the background. They wouldn't need an excessive amount of resources, and you don't care if they take one or two hours to complete. Let's say you want your queries to behave in a certain way. What happens if they don't? That's the role of the Teradata Dynamic Workload Manager. Using it, data warehouse managers can create rules that help maintain service-level goals. If there is a workload definition that's full of longer-running queries, the data warehouse manager can input a rule that virtually states to the system, "Once you've reached 30 queries of a particular type of workload, the next one will be delayed." The result? A change in processing that results in minimal disruption despite the workload traffic. If, for example, the 31st query normally runs in one hour, and it takes an hour and five minutes, the end user probably won't be affected as much as he or she might be by query gridlock. Once the work level goes below 30, the delayed query is automatically put back into the system. Feedback is essential, and that brings us to the Teradata Manager. We've analyzed the workloads and applied rules for their regulation. Now we need to monitor their progress and display that progress visually. That's done through the Teradata Manager dashboard. You'll be able to see the workloads displayed in near real time. How many queries are active in the workload definition? How are they performing? You'll see a line that shows if you're meeting your service-level goals. Are you falling behind? What are the expected arrival rates? Are you near the arrival rates you projected? If you're not meeting your service-level goals, you can use the feedback to fine-tune your definitions and rules or the queries themselves. (See Figure 4.) We've spoken with companies that have written in-house tools to monitor these types of activities. Such automation frees employees to focus on other issues and opportunities. With the new TASM tools, data warehouse managers see workloads change as the day progresses and receive automatic reports instead of having to generate them manually. Incidents are resolved quickly and in some cases even before the incidents are visible. These tools provide you with insight and direction on how to make your Teradata system more efficient and in doing so, increase the value of your original investment. T Figure 3 (click to enlarge) Figure 4 (click to enlarge)

Pepper Lawrence joined Teradata in 1988. He has served in many roles surrounding the Teradata Warehouse and is currently the senior product manager for the Teradata Database Management toolset.

Randi Zeehandelaar, enterprise integration marketing manager, has more than 25 years in the computer industry. She has supported database, middleware and BI pre-sales efforts at Business Objects, Information Builders (iWay Software) and Sybase. Her current responsibilities include product marketing for Teradata Tools and Utilities and TASM initiatives.