Most Teradata clients who want to activate their enterprise data warehouse (EDW) start with active load. Fortunately, active load is getting easier as Teradata TPump matures and more best-practices guides become available. Some sites start out simple; some leap straight into continuous data loading. Respondents to a recent survey by The Data Warehousing Institute cited numerous techniques as being important to their operational business intelligence (BI) strategy, including accelerating the frequency of batch loads (38%) and trickle feeding data into the EDW as events occur (24%).

But how do these front-runners who have already blazed the path to active load do it? What architectures are they using? Here we examine four of the many ways data gets from the front line into the EDW and back to the operational front-line users.

Simple extract, load and transform (ELT)
Active loading can be implemented using very simple ELT methods. One telecommunications company wanted to give its 4,000-plus call center customer service representatives (CSRs) more accurate and timely information to solve customer problems faster. When the company realized the EDW had 80% of the data needed, the goal became to load the other 20% into the EDW.

Every day as CSRs handle calls, rows are written into the call center database. Similarly, self-service interactive voice response records are captured showing customer menu choices. Since only 1,000 of these records are loaded every five minutes, the company decided to use BTEQ mini-batches into staging tables. iWay software is used for change-data capture to extract data from the source database. A BTEQ job then inserts these rows into a Teradata staging table every five minutes to begin the transformation process, which includes:

	Account and phone number matching
	Call event categorization
	Accommodating data from new customer channels
	Moving the data into the final target base tables
	Loading several hundred thousand records daily

enlarge

Using the fresh data, a BEA WebLogic portal delivers recent contact history to the CSRs via Web services calls to the iWay integration server, which issues SQL requests to the Teradata Database. The application has a two-second response time service level agreement (SLA) for the active accesses. Averaging 50,000 inquiries a day with peak loads of 10,000 tactical queries an hour, the Teradata system easily supports concurrent call center BTEQ loading, MultiLoad jobs and hundreds of concurrent tactical queries.

TPump straight-through loading
In a second example, a major retailer wanted up-to-the-minute reports for supply chain managers and store managers. Operational managers use the reports to detect stores selling items faster or slower than expected. They wanted to duplicate the tactics of the stores that sell beyond expectations in the stores that are lagging behind. So, the retailer's goal was to produce near real-time reports and scorecards in fewer than five minutes—from printing the consumer's register receipt to operational reporting. With point-of-sale data already loading into the mainframe, they decided to pass the purchase receipts data to TPump via IBM WebSphere MQ. (See figure 1 above.)

Since a transaction receipt has many complex line items—stock keeping units (SKUs), payment type, price, coupon discounts, taxes, etc.—the programmers use an access module for transformations. The access module is a program written in C that is linked to TPump. The programmers need TPump to insert records into as many as 40 tables from one purchase transaction. For each transaction, the access module calls one of 40 subroutines to properly reformat and transform the incoming record types.

Because of the company's database design, all data is loaded via inserts, switching to an upsert only if a "record exists" error occurs. This approach doubles the loading throughput versus using only upserts. The access module routine also includes the "Notify Exit," which captures errors and sends them back to the access module to be processed in real time. The application then fixes the most common errors, thus increasing data accuracy while reducing downtime.

During the busy seasons, the system loads up to 4 million transactions per day with an average of 10 to 12 records per transaction (40 million to 50 million records per day). The CPU overhead for the entire process on the Teradata server is only 1% to 2%. Unfortunately, on peak sales days, the overloaded mainframe becomes a performance bottleneck. But the designers were smart and built the TPump processes and scripts to run on either UNIX or mainframe servers.

After the design was put into production in 2005, two obvious benefits developed:

	The data is loaded continuously and only once into the Teradata system.
	All of the maintenance is contained in a single access module so new subroutines can be added.

TPump into staging tables
Because of the unpleasant reality that their competitor is only two clicks away, dot-com companies have a tendency to think of everything in real time. Consequently, one travel company had so many real-time load requests that it had to prioritize projects by return on investment (ROI). To cope with the flood of data, the company designed an architecture using TPump that could be replicated to handle different data streams with simple scripting changes. The variety of source data—click-streams, supplier prices, Web page graphics, offers and deals, sales bookings—and service level goals demanded a robust design.

enlarge

The design uses two TPump jobs, each servicing one WebSphere MQ end point. One of the two TPumps is in sleep mode while the other actively loads staging tables. The mini-batch "end-of-file" condition is forced every two minutes or after a specified number of rows have been loaded. Regardless of which happens first, the TPump job stops taking in new rows and starts transformations on its mini-batch via stored procedures. Meanwhile, the sleeping TPump job wakes up and starts accepting rows from WebSphere MQ into a second staging table. After the first TPump job finishes emptying the staging table into the base tables, it goes to sleep and waits for its next turn. (See figure 2.)

In 2005, the company started running eight TPumps, loading more than 1 million rows a day in near real time. The TPump jobs were also designed to do catch-up after a planned outage. In this case, the TPump job will read large flat files of saved updates and blast them in once the Teradata system is operational again. Today, the number of TPump load streams has expanded for data when latency must be short. When the latency does not have to be measured in minutes, the programmers use MultiLoad and FastLoad mini-batches intra-day followed by extract, transform and load (ETL) late at night.

This is the quintessential mixed workload application for a data warehouse. Consequently, Teradata Active System Management plays a vital role by managing service levels for the widely varied user communities and tasks. Using priority management techniques, the consumer Web site typically achieves one- to two-second performance, reports run normally and the load jobs run in spurts as the data arrives.

Ultimately, the company was able to feed its Web site with fresh data on price changes, offers, advertising graphics and consumer campaigns in real time using its Teradata system. While loading up to a million records a day with TPump, the company also concurrently serves 400,000 decision support queries daily and more than 1 million Web site requests to its Teradata system.

enlarge

Replication and ELT
Another dot-com company—this one an e-tailer—also needed multiple load strategies running at the same time. One of the biggest concerns—after order bookings, of course—is marketing campaigns to online users. Like an air traffic control tower, this company has dozens of marketing people cross-selling to customers to close added business, adjust ongoing deals and reach out to customers every day. Campaigns as well as deals could start one day and end the next. Marketing staff must invent campaigns, assess profitability, recruit customers to visit the Web site and sell products—all within 48 hours. With the help of the Teradata Relationship Manager and Teradata Value Analyzer, this stress was alleviated. (See figure 3.)

Because of SLAs on data latencies, more than 70% of the e-tailer's data has to be loaded intra-day, leaving mostly ERP data for nightly batch ETL. The company turned to three products for fast deployment of their load strategies: GoldenGate TDM (Transactional Data Management) for replication, Business Objects (an SAP company) Data Integrator for nightly batch ETL and Oracle's Sunopsis data integration tools for ELT transformations. With GoldenGate TDM, the company can grab Oracle database "redo logs" from the Web site and load them into Teradata staging tables at the rate of 5 million to 20 million a day.

More than 650 Oracle tables are monitored for updates that are replicated to the Teradata system each day. Because GoldenGate TDM supports many data sources, it is also used to capture "redo logs" from Microsoft SQL Server applications. To feed the Business Objects virtual Rapid Marts inside the EDW, the company relies heavily on Sunopsis to run 180,000 mini-batch loads per day. Once the batches of data are in the staging tables, Sunopsis, coupled with a job-scheduling product, executes around 180 transformation jobs per hour throughout the day. A few BTEQ jobs are also run for lower-priority transformations.

This company's compelling results provide its management with full Business Objects dashboards that have hourly profit-and-loss summaries. Along with the expected merchandising reports, campaign results and return customer analysis, the company has push-button consumer metrics showing new customers, retention rates, repeat buys, average revenue by customer segment and product line and, of course, profit measures.

Common themes
Each of these companies shares at least three outcomes from its use of active load: First, the results dispel the myth that near real-time loading has to be expensive. One company said it cost 2% of its Teradata server capacity to do active loading. While each company spent the typical project costs in labor, programming and operational tasks, nearly all said no hardware upgrade was required.

Second, each company relies on Teradata Active System Management to deliver on front-line user service levels while maintaining good load speed.

Last, each of these sites was continuously loading all day, every day—way back in 2005. These front-runners have done the debugging and established best practices for other companies to follow. That's good news for the EDW sites that will be joining the other front-runners who do intra-day, active loading already. T

Dan Graham has more than 35 years in IT and leads Active Data Warehouse Technical Marketing at Teradata.

Teradata Magazine-June 2008