Does Teradata provide an application development advantage?

by Deborah J. Smith

Introduction
We frequently participate in discussions that center on a typical Teradata DBA's day and how it differs distinctly from another DBA's workday. That difference provides the Teradata DBA with opportunities to expand the typical DBA role into one that may overlap with application development or business analyst roles. The effort of explaining the DBA differences has frequently caused us to overlook the impact of ease of application development on Teradata for the application group. The purpose of this paper is to highlight some of the application development advantages. To better understand these advantages, we have enlisted the help of our customers to provide us with specifics. We then categorize those specific aspects into cost avoidance, releveraging/reusability, data quality, time to market and future capability.

Cost avoidance
Cost avoidance is the goal of each department within an organization. For the IT department, cost avoidance opportunities are often hidden in the day-to-day operations. When building an enterprise data warehouse, the initial cost reduction is in the form of reduced servers, reduced software requirements for multiple data stores as data marts are consolidated. After the initial implementation, as those very obvious kinds of cost reductions are realized, the question becomes: Where do other cost reductions come from?

Data transformation
One area where cost avoidance can be realized is data transformation. Customers who have taken advantage of a normalized data model in Teradata have realized an ETL development and maintenance cost avoidance. Existing ETL tools are typically built to be used with the normalized data models of transactional systems. While Teradata will execute well with any well-defined data model, customers who have opted for a normalized data model have realized an ETL development and maintenance cost avoidance. Some customers have opted to perform the transformation inside Teradata, allowing them to take advantage of Teradata's parallelism to quickly address transformation needs.

During a recent proof of concept, a travel corporation identified some major differences in its ETL process for a single application. The existing ETL process was estimated to have taken 32 hours to develop, and then another 70 hours to optimize and improve performance. Once developed and optimized, the process was executed in 30-50 minutes a day. During proof of concept testing, the customer was able to develop an ETL process in just under 3 hours—2 hours 41 minutes—and it executed in 21 seconds. Just the development of the ETL process provided them a savings of 100 hours, with a daily execution time savings of 29.5-49.5 minutes.

Schema changes
Even the most well thought out data schemas occasionally require changes to the underlying data schema, and the load processes and/or ETL processes that support that schema. Within Teradata, the addition of a column to a table is as easy as executing an alter table statement. The existing data structures remain in place.

A major North American retailer found that by using Teradata it was able to accelerate the addition of new data, improving productivity by 25%. By using a normalized model, this same retailer found it was able to reduce maintenance costs. By centralizing and adopting a single normalized model, the retailer saved an estimated 10% of the time spent on different data models and another 40% of the time spent on the firefighting those different models inherently provide.

Data load
Teradata has different load utilities designed to fulfill different requirements, from continual load requirements to bulk data loads. All are easy to code and able to automatically take full advantage of Teradata's parallelism.

During the travel customer's proof of concept, an initial application load of data using one of Teradata's load utilities took 16 minutes and 6 seconds. That same data load in the existing architecture took 33 hours and 30 minutes. This amounts to an initial data load savings of over 33 hours. Since the initial load process was so extreme, the customer opted not to test the daily load process. However, on its existing architecture, that daily process required 4-6 hours. With a requirement of 4-6 hours for incremental data loads, the customer's ability to take advantage of near real-time data feeds was nonexistent. The expectation is that Teradata will allow the customer to address its need for near real-time data updates.

Data centralization
Imagine determining your data architecture on a blank canvas, free from different business areas claiming control over the data or clutter from other internal political issues, and free from any technical constraints. How would you draw your ideal architecture on that blank canvas? Would you draw multiple pods to hold and process data for different requirements for those different political issues or business areas? Each pod requires its own infrastructure for support and hardware/software. Since those pods would most likely hold some of the same data elements, how do you re-create or duplicate those elements for each pod? The technical issues, such as high concurrency, multiple workload types, response time requirements, query complexity, query data volume, data volume, query freedom and schema complexity that typically reinforce the multiple pods of data don't exist with Teradata. While it doesn't solve the potential internal political issues, Teradata does provide the technology that enables a centralized single view of the enterprise.

An international retailer that has been a Teradata customer for some time was struggling with being able to centralize its information until it began its journey with Teradata. Now that the retailer has been able to centralize, it is beginning to see some of the additional advantages that Teradata brings. For example, as an international retailer it needs an architecture that enables greater centralized control while still providing for local variances. Besides being able to address local variances, the retailer has found that one of its greatest abilities is developing sophisticated MIS applications at a low cost.

Usage
While data centralization, data transformation, schema changes and data loads provide some significant cost avoidance, real cost avoidance is achieved as a result of using the data warehouse. This cost avoidance comes in the form of taking advantage of a centralized architecture that provides a platform for cross-functional data usage and enables both query freedom and ease of application development that is only limited by the users' and developers' creative thought processes. The goal is to be able to create an infrastructure that is able to support speed of thought. When an improvement opportunity is identified, or trends in the marketplace indicate changes, the speed in which you are able to respond to those changes establishes your place in the marketplace.

One of the state governments was able to generate $28 million within the first 3 years of implementation. Ongoing revenue gain has been $10 million per year.

An international bank had an existing process to detect third party fraud patterns. This entailed regular examination of credit card transaction data. Being able to identify fraud in a timely manner was absolutely crucial for the bank. Performance on its existing architecture was not as optimal as it wanted. In a like-for-like benchmark comparison with its existing architecture and Teradata, performance on Teradata was consistently better. The greatest discrepancy revolved around a query that was executing for 5.5 hours and still failing to return an answer set. On Teradata, that query executed for 4 minutes and returned the correct answer set. This effort substantially improved detection rates and fraudulent transactions were detected sooner, leading to a multi-million-dollar return.

Releverage/reusability
When building a high-quality environment, one of the best practices and considerations is to build an environment that provides for the careful development of database objects, processes and procedures that can be reused for different needs. Integrating data and developing a schema that enhances that reusability is just one way. Developing processes and procedures that also enable reuse and releverage is another critical development. Identifying and developing data load processes is a critical reuse and releverage strategy that proves its merit when new data sources need to be quickly integrated. Developing views to facilitate end user ease or to incorporate standard corporate metrics or best coding practices is another reuse and releverage consideration. One of those reuse and releverage concepts is storing the data once and then using it for multiple purposes.

During the proof of concept executed by the travel industry customer, one reuse and releverage practice brought to light was to develop multiple views to be used in varying ways and to answer multiple types of questions easily. One view was developed that aggregated data rather than developing the aggregate summary tables to address specific processing needs. This allowed the same data to be used in place rather than developing the strategy to create and maintain a summary table. While it may become necessary to instantiate the summary table due to multiple needs and business requirements, developing a view allows them to first see the value in doing so and to quickly begin using it.

For an international travel customer, the data warehouse has made all the difference in the world. Before building a data warehouse, the company had only one daily performance report with no way to tie financials back to it. The data was siloed in each operational area, with no support for query freedom or history for detailed analysis. There truly was limited access to the data. The company has since moved to a single corporate source of data that is flexible, responsive and in near real time. It has gone from analyzing only a segment of the customer experience to analyzing the entire customer experience from beginning to end—including customer history, customer schedules and bookings—along with the different travel-booking interfaces available to the customer. This ability to fully understand customer value allows the company to provide customers with the personal attention they deserve while working toward keeping customer costs down by identifying and preventing costly fraud. By using the same data, this customer is able to develop profiles, rules and flags that follow fraudulent patterns. Within the first 3 years, it was able to eliminate $30 million worth of fraud.

Data quality
Data quality is frequently a top reason for enterprises to begin investigating building a data warehouse. Executives find they have reports generated from different systems for different areas of the business, with what is expected or anticipated to be the same data elements. But each of those different areas of the business requires a slightly different calculation or includes different data elements. Data sources may differ, and frequently the data freshness for each of those different business areas is slightly different. These differing aspects result in irreconcilable reports and report numbers. This inability to identify the correct numbers puts management and business leaders in the position of having to guess or take an average of all the report numbers. Being able to create a centralized single view data warehouse eliminates these differing aspects. When all reports come from the same data using the same calculations and the same data, executives gain confidence in the quality of their data.

Travel companies are frequently confined to reviewing just one segment of a customer trip. One travel customer has taken advantage of its data warehouse to integrate related data such as ticket revenue, travel agency information, historical customer information and future information on schedules and bookings. This allows the company to perform full revenue analysis based on a complete customer itinerary, extending data quality into process quality to truly identify high-value customers.

For a U.S. manufacturer, the data warehouse meant the ability to understand the quality of their customer records. Prior to the data warehouse, 40% of the company's U.S. customer records had invalid addresses. Now, those customer addresses are 90% correct, saving the company $200,000 per year.

For an international communications company, data quality meant being able to provide customer service with information pertaining to a customer and integrating that with order status information. Prior to the data warehouse, this was a very labor-intensive effort: capturing information from the customer service representatives and integrating it with order status information. Timeliness of information was also an issue. Primarily, this data resided in and was reviewed by management through Excel spreadsheets. By building a Web front end, managers were able to easily capture customer service representative information and merge it with order status information. Building the Web front end was completed in 2 weeks, and it facilitated substantially improved data quality and improved order commitment performance, as well as allowing product management to more appropriately manage this particular high-growth product area.

Time to market
In this fast-paced, instant-gratification world, how long it takes an organization to respond to changes in the marketplace is a key factor in that organization's ability to prosper and grow. How long it takes an organization to respond and address marketplace changes will establish that organization's rank in the marketplace. The technology that enables the Internet, e-mail, cell phones and PDAs has helped to generate a community that wants and expects information at its fingertips.

The travel corporation that recognized the ability to avoid some of the ETL costs in its existing architecture also recognized a huge improvement in its ability to address time-to-market concerns. The ability to eliminate 100 hours from the ETL development process and then another hour of processing time for each execution goes a long way in being able to use new applications to bring new products and services to market. The same is true of the schema changes. When it takes 300 hours to make a schema change in the existing architecture, the actual change is then prolonged, grouped together with other changes, examined for requirement to the nth degree, minimizing the ability to bring new applications to market quickly. However, when that same change takes only 3 hours to develop and execute, the ability to quickly bring new applications to market sharply increases.

One U.S. transportation company had two equal-sized applications to deliver to the business. One application was developed on the existing architecture, the other on Teradata. The existing architecture application took 1,800 hours and 6 months to complete. The Teradata application took 800 hours and 2.5 months to complete. With this variance in development cycle, the company's ability to deliver applications to the business in a timely manner is significantly improved with Teradata.

The state government that generated $28 million within the first 3 years of implementation was able to begin that tax gap compliance project within the same year that it acquired Teradata. The system was operational within 5 months. The sooner you are able to begin using new applications, the sooner they will prove their worth.

A foundation for future capability
When building a data warehouse, a great deal of thought and effort goes into making the decisions that will eventually establish the infrastructure. Establishing the right technology takes the burden off by not forcing a niche architecture to do what it is not prepared to do. This is best said by a transportation customer who attempted to build an enterprise data warehouse three times before it felt confident in moving forward.

The first time around, the customer built the data warehouse with a DBMS that would only allow 60 queries per week, none of which could be complex. This limited analysis capability and user base. While at the same time, that data warehouse was considered a success, having accomplished what the initial effort required, there was no ability to grow and take greater advantage of the environment. The customer tried the data warehouse a second time and built it with a DBMS that would allow 200 queries. The ability to execute complex queries was a little improved; however, users were still unable to perform marketing analysis without separate data marts. Data volumes and number of users were limited, including no concurrent user access. The customer tried a third time with Teradata. Immediately it was able to execute 5000 queries per week, and this has grown considerably since installation. Query complexity was no longer a concern, concurrent users became unlimited and user growth grew exponentially, all with exponential performance improvement. For this transportation customer, data warehouse future capability is no longer something to worry about.

Conclusion
Clearly, if the data warehouse platform enables ongoing cost avoidance, promotes reuse and releverage of work accomplished, enables data quality, facilitates time to market advantages and provides for future abilities, there is an application development advantage, both for current efforts and future needs. Teradata provides the power of technology; you provide the power and knowledge to imagine how to distinguish your organization in today's and tomorrow's landscapes. T

An example of the advantage at work:

A large North American retailer used its Teradata data warehouse to identify 22.4 million duplicate customers in its customer file. Once these duplicate customers were identified, the company was able to correct 3.5 million non-deliverable addresses, thereby saving campaign management costs for those undeliverable mail items, and ensuring that marketing products reached customers. This ability has been credited with generating first-year incremental sales of more than $12 million. By improving the quality of the data and ensuring correct customer addresses, the data could now be used to perform statistical modeling that identified profitable customers. This resulted in an ROI that paid for the initial database implementation in the first year of operation. By continuing to expand that effort across other channels, the retailer will enhance cross-channel awareness. When talking about data warehouse success, this retailer measures that success by being able to increase retail transaction size by $1.03, translating to $42 million per year. Increasing customer visits by 1 per year translates to $24 million per year. Increasing retail margin in just 3 departments translates to $19 million per year. Improving efficiency and accurately reporting sales saved another $2.5 million per year. This retailer has a normalized data model and found it could easily integrate additional data elements. Altering the schema to include the additional data elements and updating those elements to include the available data within Teradata took 3 hours and 5 minutes. The retailer estimated the same changes would have taken over 300 hours to develop and implement on their existing architecture. By being able to accelerate the addition of new data, a 25% improvement in productivity has been realized. It improves the speed of application deployment to new acquisitions. When the retailer realized that its policy of accepting returns without receipt was a wide security hole for fraud, it wanted to implement consumer product return without receipt. If a customer paid by check or credit card, the transaction could be looked up and verified even if the customer had not brought their receipt. The RFP'd solution came back with a price tag of more than $1 million, while the transactional IT group said it would take a minimum of 6 months just to find the data, then they would let the business know how long it would take to code and implement. The data warehouse group was able to identify that the data had been integrated into the data warehouse and then code a couple of stored procedures within a couple of hours. The application was available and ready for production in a week. The turnaround on this particular application was so quick because the data had already been integrated into the data warehouse for marketing purposes. Some of that same data was then used to develop a solution that enabled price lookups to occur dynamically in the data warehouse when the cashier at a store scanned an item whose price point had not made it into the store system. This eliminated the need for the cashier to call the department and describe the item or wait for a department representative to identify the correct sell price. Since the round trip from store to data warehouse and back again occurred in less than 4 seconds, it all happened without inconvenience to the customer. The business unofficially said this improved cashier throughput by 20%. As the data warehouse matures and the data of the enterprise becomes available within it, the speed of developing applications increases. When the data is already available in the data warehouse, you are able to concentrate on the development of the application rather than including the research and sourcing of that data for the application. This integration of data can be expanded to facilitate information integration during merger situations. By using the data warehouse to create a common culture and language between merger parties, the ability is then transferred to being able to accelerate the benefits of the merger itself. All of this, possible with a Teradata data warehouse.

Deborah J. Smith, senior data warehouse consultant, Teradata, a division of NCR, has spent more than 14 years developing and supporting mixed-workload Teradata warehouses. You can reach her at deborah.j.smith@teradata-ncr.com.