Who's driving your business?

Teradata Warehouse 8.0's event-based features let you take your hands off the wheel-without losing control.

by Steve Mazingo & Richard Charucki

Traditionally, data warehousing has focused on strategic decision-making-employing data mining, forecasting and large amounts of historical enterprise data to discover what you don't know about your business. Data warehouse end users included areas such as marketing, strategic planning and finance.

Teradata brings a new dimension to traditional data warehouse functionality by allowing you to apply what you do know to support decision-making throughout the enterprise.

The work mix in the data warehouse still includes complex, strategic discovery queries, but now it has expanded to take on short, tactical queries, background data feeds and event-driven updates all at the same time. Being able to satisfy both types of queries, data feeds and event processing formerly required separate platforms and database instances.

Teradata is unique in that it can easily address all these types of workloads. This concept is commonly called active data warehousing.

Successful active data warehouse implementations can acquire data in near real-time. Furthermore, active data warehouses can trigger tactical, event-driven business processes and applications based on recent activity. As the speed of business intensifies, data, application and business process integration is becoming a business imperative. Active data warehousing with Teradata enables insightful execution of business activities.

Strategic enterprise initiatives, such as increased productivity, reduced cost, improved customer service, increased sell-through and better inventory and supply chain management encourage integration between key enterprise applications and the event-driven active data warehouse. Business drivers also result from merger and acquisition requirements; packaged CRM, ERP or SCM solution deployment; regulatory compliance with initiatives such as Sarbanes-Oxley or Basel II; or IT infrastructure consolidation.

At a tactical level, moving to an active data warehouse requires enabling realtime business decisions by integrating analytical capability into operational business processes. It also requires faster consumption and delivery of information among business systems to support fully automated decision processes.

Recognizing the need for event-based integration, Teradata has developed and partnered with vendors such as TIBCO to provide the appropriate interfaces and adapters in Teradata Warehouse 8.0. Within event-based processing, events are detected by a combination of database, message bus and application functions. These events include timer-based and real-time event recognition, alerts and exceptions that can initiate other automated actions. The following example illustrates this concept.

Getting behind the wheel
Retail store replenishment often requires complicated metrics to ensure that proper inventory levels are set. Carrying too much stock results in high inventory costs and potentially discontinued or expired merchandise. Through the years, retailers have developed and employed these metrics hoping to satisfy customer demand while avoiding storing product for lengthy periods.

Inventory is a large part of a retailer's cost factor, and as such, retailers always are looking for ways to streamline and improve efficiency. Along with tracking sales and product on the shelf, factors such as time of year, calendar holidays or events are frequently taken into consideration.

But what if you could include the weather forecast and use a combination of weather tracking and sales data to help determine reorder points and quantities?

To illustrate this, consider the case of a retailer wanting to automate the consideration and effect of weather on the process of recognizing low inventory levels and replenishing store shelves.

Figure 1 Click to enlarge

Following the steps in figure 1, POS data is collected and continuously loaded into the data warehouse. A database trigger initiates an inventory analysis application, which can detect low stock levels of an item by taking into consideration the typical reorder metrics and the historical effect of certain weather conditions. The inventory analysis application can then publish a replenishment event to the enterprise application integration (EAI) message bus. This event alerts the inventory replenishment system at the distribution center.

This scenario illustrates just one of several ways enterprises can use eventenabled processing with Teradata. The real value in meeting strategic business initiatives is the intelligence that active data warehousing injects into the decision- making process. This reduces the cost of inventory management and optimizes stock levels for maximum customer satisfaction at the lowest inventory carrying cost.

Loaded with options
Teradata Warehouse 8.0 provides building blocks that can be leveraged with existing application and messaging infrastructures for the development and deployment of event-based applications: external stored procedures, triggered stored procedures and queue tables.

External stored procedures-To support event-based processing, Teradata has incorporated new functionality into the existing stored procedures capability. Currently, stored procedures can be used to execute SQL by using Teradata Stored Procedure Language (TD-SPL). In Teradata Database V2R6, stored procedures will be able to execute C/C++ programs. Stored procedures written in C/C++, known as external stored procedures, are an implementation of the ANSI SQL: 1999 standard.

Thus, Teradata's external stored procedures can incorporate C/C++ programs, SQL access in TD-SQL and the ability to "call" freely between TD-SQL and C/C++. This feature brings a whole new level of active data warehousing support for tactical business actions:
> external access via C/C++
> reading and writing from an EAI bus or message queues
> "calling" other stored procedures

In the retail inventory replenishment example in figure 1, Teradata's external stored procedures facilitate automating and integrating the various components of the inventory system.

By using Teradata's external stored procedures with other database features (such as the ability to trigger the external stored procedure), the event definition, the event-detection criteria and the subsequent processing requirements are imposed directly in the database.

Triggered stored procedures-Teradata Database V2R6 supports the invocation of either an external stored procedure or a Teradata SQL stored procedure in response to an action (INSERT/DELETE/ UPDATE). Known as triggered stored procedures, these occur on a Teradata relational table where the SQL trigger is present. The SQL trigger then becomes the action catalyst for instigating an automated business event.

This new capability allows users to define automated business events directly within the Teradata Database via triggers and facilitates event processing through complex business logic within external stored procedures.

The powerful and straightforward combination of triggers and stored procedures enables more sophisticated, event-based strategic and tactical applications.

Figure 2 Click to enlarge

In figure 1, the combination of triggered stored procedures and external stored procedures opens numerous event-based processing opportunities. In figure 2, a trigger in the database fires when there's activity within a table. The trigger invokes an external stored procedure, which sends an event to an application or to the EAI message bus.

Queue tables-Teradata Database V2R6 provides new queue table functionality that is different from competitive implementations because it supports the usual properties of a persistent database table and the special characteristics that enable queue-oriented applications. Teradata queue tables are implemented as Teradata SQL at the database/table level, which provides greater flexibility, power and functionality and leverages the natural performance characteristics of the Teradata Database.

Currently, user applications can implement event processing in the Teradata Database, but the applications must poll an empty table for queued rows. Teradata queue table technology provides a new non-polling solution with queuing mechanisms optimized for the Teradata Database and supported by a new SQL queue table interface.

The queue table feature enables applications that support event processing by providing an interface between the Teradata Database and message-oriented EAI infrastructures. This makes it easier to build applications that leverage products such as TIBCO's Adapter for Teradata.

Functionally, a queue table is a persistent database table with the properties of an asynchronous first-in-first-out (FIFO) queue. However, it differs from a standard database table in that a queue table always contains a user-defined queue table insertion timestamp (QITS) in the first column of the table.

The QITS shows the time the row was inserted into the queue table and is the mechanism for approximate FIFO ordering. Queue tables can be analyzed and managed like any other table, allowing businesses to be more responsive by prioritizing the queue.

Teradata has added a new SQL interface that effectively supports all FIFO queue properties, including "push," "peek" and "pop" operations on Teradata queue tables.
> The FIFO "push" operation is defined as a SQL INSERT operation that stores an entry (row) in the queue table.
> The FIFO "peek" operation is defined as a SQL SELECT operation to retrieve queue entries (rows) in the queue table without deleting them. This is also referred to as Browse Mode.
> The FIFO "pop" operation is defined as a SQL SELECT AND CONSUME operation to retrieve queue entries (rows) in the queue table and delete the selected queue entry upon completion of the read. This is also referred to as Consume Mode.

Teradata queue tables also can employ delayed concepts during queue consumption. When a Consume Mode transaction finds no queue entries in a queue table, the transaction enters a delayed state, effectively waiting for an event to occur.

Subsequently, when a FIFO "push" operation (SQL INSERT) occurs, the waiting transaction immediately "wakes up" and appropriate action is taken. In this case, events are detected immediately and acted upon without user intervention, enabling automated business decision-making.

The result is that any database application that accesses Teradata via SQL can place entries in or consume entries from a queue table. Further, SQL can be leveraged to modify or cleanse entries in the queue.

In retail inventory replenishment, queue tables could be employed to enable events that can be acted upon in an asynchronous manner, providing greater flexibility in tactical active data warehouse application development and deployment.

Figure 3 Click to enlarge

As figure 3 illustrates, when a trigger in a transaction table fires a stored procedure, the stored procedure could insert an entry or event in a queue table. External stored procedures or other functions could then consume the event from the queue and update other database tables (e.g., the customer and offer tables), place messages on a message queue or perform other processing tasks.

Hitting the open road
Building an event-enabled active data warehouse environment is a significant challenge. For any given business problem, a number of different applications across internal and external business entities must work together.

Furthermore, there are many potential technical integration solutions from which to choose-application servers, integration brokers, message brokers, packaged application suites, database systems,Web services, and connector and adapter technologies, to name a few. The numerous options add to the challenge.

Teradata recognizes the complexity of event-enabled data warehousing requirements and is deploying features in the Teradata Warehouse 8.0 release and future releases that will allow for creating and extending event-enabled active data warehouse applications.

It's one less challenge for you and your business. T

Teradata Product Manager Richard Charucki can be contacted via e-mail at richard.charucki@teradata-ncr.com.

Teradata Product Manager Steve Mazingo can be contacted via e-mail at steve.mazingo@teradata-ncr.com.

© Teradata Magazine-September 2004