Teradata

This white paper describes the Teradata® Database Event Architecture primary enabling features of V2R6 that provide the building blocks for the development and deployment of event-based applications such as Queue Tables, invocation (firing) of Stored Procedures from Triggers, and External Stored Procedures (XSP) in support of the active data warehouse (ADW).

Active data warehousing is a centralized, comprehensive, integrated, logically consistent and easily accessible repository of business-critical detailed data – an Enterprise Data Warehouse (EDW). An EDW provides an integrated information foundation to drive strategic and tactical decision support within an organization.

The ADW capabilities provide for, and expand the reach of, information that spans the entire organization. The overall strategy of a firm will be influenced by the traditional use of decision support against the corporate data warehouse. However, the execution of the strategy in an information- driven business will be facilitated through deployment of an ADW. In today's competitive business environment, every successful business is data driven. Information drives decision making based on fact rather than instinct or haphazard guessing.

The data warehouse has been proven to be instrumental in strategic decision making and has evolved to include the realm of tactical decision making as well. Tactical decision support leverages the integrated source of data in the warehouse by deploying timely, cleansed information to decision makers throughout an organization. In conjunction with operational data freshness, the ability to perform demanding strategic decision support and high-performance tactical decision making define Teradata Database as the premier business-critical component of active data warehousing.

However, now is the time to take the active data warehouse into the next phase of its natural evolution. With the advent of Teradata Database V2R6, the functionality of the ADW is extended to enable business decisioning with event-driven triggers to initiate fully automated decision processes. Competition and technology innovations are motivating these advances in decision support. For example, in e-commerce business models, there's no choice but to automate decision-making when a customer interacts with a web site. Through the deployment of event-based applications and functionality, the ADW is all about making decisions to optimize the customer relationship through individualized product offers, pricing, content delivery, and so on.

The larger the role an ADW plays in the operational aspects of decision support, the more incentive the business has to automate the decision processes. The value proposition for the latest evolution of the ADW is the ability to drive operational events through new event propagation functionality and automatically detect and act on business events.

Event processing differs from the traditional strategic and tactical processing because strategic and tactical both imply that an end-user has made a request for information from the database. In the event processing arena, events are detected by a combination of database and application functionality. These events can include timer-based or real-time events, alerts, and exceptions, and are provided to some person or system to take action. The main purpose of event processing is that the database detects an event, and information is pushed to a user or system for action, rather than its being available only when requested.

The primary enabling features of Teradata Database V2R6 that provide the building blocks for the development and deployment of event-based applications are External Stored Procedures, the invocation (firing) of Stored Procedures from Triggers, and Queue Tables. To assist you in better understanding these enabling features a high-level description of each follows.

External Stored Procedures

In support of ADW event-based applications, new functionality has been incorporated into the currently existing capability of Stored Procedures. Today, Stored Procedures can be utilized to execute SQL using Teradata Stored Procedure Language (SPL). In V2R6, Stored Procedures will have the new capability of executing C/C++ programs in addition to retaining the ability to execute native Teradata SQL. Stored Procedures written in C/C++ are known as External Stored Procedures (XSPs) and are an implementation of the ANSI SQL: 1999 standard.

Technical merits of External Stored Procedures include incorporation of C/C++ programs, retaining SQL access in Teradata-SQL and incorporating programmatic algorithms inside the C/C++ programs. Additionally, XSPs provide the ability to 'Call' freely between TD-SQL and C and can be used to provide for external access (via C/C++) to read/write from an EAI bus or message queues and even 'Call' other Stored Procedures.

Powerful and complex logic can be used for writing Stored Procedures. Businesses can now code customized Stored Procedures that update executive dashboards, generate ad hoc marketing notices, automatically replenish inventory, or send out attention alerts. In addition to the new programming power, XSP parameters will support all of the data types supported by SQL language stored procedures and includes the also new Binary Large Object (BLOB) data type.

Triggered Stored Procedures

Triggered Stored Procedures enable the invocation of either an XSP or a Teradata SQL Stored Procedure directly as a result of an action (INSERT/DELETE/UPDATE) occurring on a Teradata Database relational table on which an SQL trigger resides.

The SQL trigger now becomes the action catalyst for instigating an automated business event. The new capability allows the definition of automated business events directly within the Teradata Database (via triggers) and facilitates the processing of the event through complex business logic contained within an XSP. Powerful yet straightforward, the combination of triggers and Stored Procedures enables a whole new level of active data warehouse exploitation for supporting strategic and tactical business actions such as product replenishment notifications, fraud detection alarms, and marketing and sales alerts.

Queue Tables

In support of the Teradata Database Event Architecture, V2R6 provides new Queue Table functionality directly as a Teradata Database feature. Currently, other databases provide queue table capabilities. However, these Queue Table interfaces are implemented at the application level utilizing User-Defined Functions (UDFs) or stored procedures. Since competitors have already implemented database queuing mechanisms outside of SQL, the addition of Queue Table functionality within V2R6 is differentiated from other databases because it's implemented as Teradata SQL at the database/table level. An implementation in this fashion provides the most flexibility and functionality and leverages the natural performance characteristics of the Teradata Database.

Prior to release V2R6, user applications could implement event processing in the Teradata Database, but in a less efficient manner. For example, applications needed to poll an empty table for queued rows. Teradata Queue Table technology provides a new non-polling solution with queuing mechanisms that are optimized for the Teradata Database and supported by a new SQL Queue Table interface.

As such, the Queue Table capability meets the needs for building applications to support event processing, allows customers to interface the Teradata Database to message oriented Enterprise Application Integration (EAI) infrastructures, facilitates the inclusion of a Teradata solution into overall existing enterprise architectures, and allows the use of the data warehouse as a real-time enterprise.

A Teradata Queue Table is defined as a persistent database table with the properties of an asynchronous first-in-first-out (FIFO) queue. The Queue Table differs from a standard database table in that a Queue Table always contains a userdefined Queue Table Insertion TimeStamp (QITS) as the first column of the table. The QITS contains the time the row was actually inserted into the Queue Table and is the mechanism for approximate FIFO ordering.

A new Teradata SQL interface has been added that supports the FIFO queue properties of push, peek, and pop operations on Teradata Queue Tables.

The FIFO push operation is defined as an SQL INSERT operation that stores an entry (row) in the Queue Table.

The FIFO peek operation is defined as an 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 an 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.

The aforementioned operations are further enhanced by employing the delayed and waiting concepts during queue consumption. When a consume mode transaction finds no queue entries in a Queue Table, the transaction enters into 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 on the event. Events are immediately detected and acted upon without user intervention, and automated business decisioning is enabled.

In Teradata Database, event processing is supported in both synchronous and asynchronous modes.When designing event-based applications, a major design decision is whether the integration with other processes or applications is to be conducted synchronously or asynchronously to achieve the appropriate level of automated business decisioning. The following topics provide for the definition of these key terms:

Synchronous Processing

Event-based applications that are composed of synchronous processes are referred to as such because each step in the application/event waits for the next step to complete before it continues. Simply put, no process can proceed until the dependent process has completed. Synchronous processing is associated with the need to provide sequential, coordinated requests between coupled applications/processes. Figure 1 illustrates the concept of synchronous processing.

Synchronous Event Propagation

The following example illustrates the occurrence of an event and the subsequent processing as might occur within the database of an office supply retailer. The business goal of the office supply retailer is to increase the sale of high margin computer consumable goods such as printer paper, cables, CD ROM disks, and backup devices. The target customer for this particular event is anyone who has purchased a computer at any one of their retail outlets. For those customers who purchase a computer and are existing customer loyalty program members, the management team wishes to extend an offer of 10%-30% off computer consumables via an electronic coupon in e-mail form depending on the customer's current status level (Silver, Gold or Platinum). If the computer purchaser is currently not a member of the retailer loyalty program, the management would wish to extend an offer of membership, also via e-mail. It is also a requirement that the offer e-mail be delivered to the customer home e-mail account as soon as possible and ideally by the time the customer returns home from their shopping experience.

The event definition, the event detection criteria, and subsequent processing requirements are imposed directly into the database, utilizing XSPs and the ability to trigger the aforementioned XSP functionality. The event is deemed to be synchronous because there is no inherent latency between the detection of the event and the desired action taking place. The event flow and processing is best illustrated as a simple flowchart as shown in Figure 2.

Asynchronous Processing

Asynchronous processing differs from synchronous processing since it assumes that no process depends on the processes that it calls or invokes, or that call or invoke it. In this case, event-based applications that are composed of asynchronous processes are loosely coupled by design. Figure 3 illustrates the concept of asynchronous processing.

A loosely-coupled solution built primarily on asynchronous processes between applications allows the application to be more available. This is because work can be performed by different processes within the system allowing the observed availability of given services in the system to increase.

Asynchronous Event Propagation

An example of asynchronous event processing is presented in terms of a financial banking application in which the management seeks to identify customers who perform large cash deposits within their institution. These customers are specifically targeted with the express purpose of broadening and deepening the customer relationship through the offering of discount services and other no fee services based on their deposit levels and recent account activity. For this activity, the management has outlined the criteria for the identification of the automated business decisioning event and the subsequent processing actions that would need to occur to support this initiative. The event identification criteria and processing actions are outlined as follows:

During the course of a normal banking day, identify all customers that have performed a large cash deposit transaction at the institution of $10,000 or more.

Evaluate the size of the deposit transaction as compared to the monthly average balance and assess the degree of deviation from the norm.

If the deposit transaction is less than 1.5 times the monthly average balance, use the in-house CRM application to proffer a discounted car loan offer directly to the customer via postal mail contact.

If the deposit transaction is greater than 1.5 times the monthly average balance, employ a more persistent campaign of customer contact and offer delivery through the following vehicles:

• Through e-mail delivery, extend an invitation from the institution's Private Banking organization to attend a private session at which various banking instruments and options would be presented, which would yield financial benefit to the customer.
• If the customer is an ATM user, initiate a "no fee" ATM offer to be delivered through the in-house ATM system and network.
• If the institution is authorized to contact the customer by phone, initiate a mortgage discount offer to be delivered via the in-house call center application.
• If the customer is currently a credit card user, initiate a credit card discount offer to be delivered to the customer via e-mail directly to the customer's inbox.

The aforementioned scenario has more complexity embedded within the event because it's required to provide information to many other in-house applications in addition to performing basic event detection and handling. To facilitate the event automated business decisioning event implementation, database triggers will be used to perform event detection, XSPs will be used to process the various offer delivery options, and Queue Tables will be the primary mechanism for providing offer information to the other in-house applications. The introduction of Queue Tables into the event-based application is an example of asynchronous event propagation. As before, all of the aforementioned features used in the implementation of the event application are imposed directly into the database and a clear demonstration on how the event architecture features of Teradata Database V2R6 can be utilized. For this example, the event flow and processing is best illustrated as a simple flowchart as shown in Figure 4.

The new Event Architecture features of Teradata Database V2R6 support the ADW vision and introduce a whole new level and range of data warehouse exploitation for supporting both tactical and strategic business automation decisioning. The value proposition for the latest evolution of the ADW is the ability to drive operational events through new event propagation functionality and automatically detecting and acting on business events in addition to continuing to support world-class strategic and decision support initiatives.

The new Teradata Database V2R6 Event Architecture features extend the reach of the business community by offering the following benefits:

• External Stored Procedures can now be written in C/C++ facilitating the ability to incorporate complex logic into automated business processes that update Executive Business Dashboards, generate ad-hoc marketing notices, replenish inventory automatically, or create and present attention alerts.
• Events may now be defined directly in the database via Database Triggers. As such, event detection is automatic, and Database Triggers now enable the invocation of Stored Procedures as a result of an action occurring on a Teradata Database relational table. The SQL trigger now becomes the action catalyst for instigating an automated business event.
• Queue Table capability meets the needs for building applications to support event processing, allows customers to interface the Teradata Database to message-oriented Enterprise Application Integration (EAI) infrastructures, facilitates the inclusion of a Teradata solution into overall existing enterprise architectures, and allows the use of the data warehouse as a real-time enterprise.

The Teradata Database Event Architecture features complement the powerful native inherent analytical capabilities of the Teradata Database and support operational automated business decisioning and strategic business automation. These combined capabilities extend the reach and needs of businesses in the natural evolution to an ADW.

Richard "Rich" J. Charucki has been in the information systems field since 1971. In 1986, Rich became an early Teradata customer while working for a major financial institution on Wall Street. In 1991, he joined Teradata and has been designing, implementing, and supporting data warehouses in the retail, insurance, and telecommunication industries. He currently holds the position of CTO/Database Strategy in the Teradata Product Management organization and is a Teradata Certified Master.