E-Commerce: Data Preparation and Data Processing

Our first success story comes from a multinational corporation and e-commerce company that provides consumer-to-consumer and business-to-consumer sales services via the Internet. This corporation leverages in-database processing to prepare the data and increase end-to-end performance.

With a very sophisticated and large data management system, there is also an extensive analytic ecosystem analyzing the petabytes of data stored in the data platform. Their data platform is used to discover, explore, analyze, and report the data using various analytics tools. Figure 2.3 shows data integration, data platform, and data analytics.

The data platform is where in-database processing will be executed. In Figure 2.3, the data platform builds a strong foundation for analytics and has structured and semi-structured data consisting of transactions and web logs of 36 petabytes of customer and behavior data. Analyzing millions of rows and hundreds of columns of data within a finite time frame with the traditional approach presented many constraints. These included transferring large amounts of data, managing massive storage space with duplicated data, and administering system resources. In addition, the volume and frequency of data to be processed has opened up several challenges around data preparation and transformation, data transfer between the data platform and data access and analytics, data set storage, and optimum system resource utilization, leading to increased data set processing costs and scalability bottlenecks. Due to the limitations with the traditional approach and infrastructure, the customer decided to adopt in-database technologies for the flexibility and efficient way in which to analyze the increasing amounts of data being collected. By integrating analytics into the database or data warehouse and applying the analytics to where the data reside, it is able to scale and provide better performance compared to the current approach and infrastructure. By moving data preparation, analytics, and reporting tasks to where the data are offers many benefits such as increasing speed and performance, reducing data movement, and promoting enhanced data governance for the IT department. For decision makers, the in-database approach provides faster access to the analytical results, which lead to more agile and accurate data-driven decisions. Let's examine the before-and-after picture and the benefits of in-database processing.

Figure 2.4 provides a look at the traditional approach to analyzing customer behavior from summarized tables processed on a daily basis. The process involves creating a data set from an underlying summarized table from the data warehouse. It consists of four major steps, all triggered by a wrapper script. In the first step, the parameters necessary for analysis are entered into a text file (parameter source file) as macro variables. Then, a sequence of SQLs on the database is executed, which yields a summarized table in the data warehouse. This table acts as the source data for all the subsequent processes. In the next step, a program uses fast-export process with multiple sessions to transfer the table from the data warehouse onto the analytics server and a data set created. The analysis is then performed on this data set to provide test analysis at different dimensions based on multiple aggregations. The output after analysis of each dimension is appended to the final output data set. The final step transfers the results back from the analytical data set to table in the data warehouse using fast-load process. This result or output from the data set is visualized on different dashboards by using a visualization or business intelligence tool for end-user consumption.

Table 2.1 illustrates the process and time for each step in the traditional approach. A typical test analysis usually consists of over 200 million records with over 100 variables of customer behavior.

Table 2.1 clearly shows that the SQL step takes around 30 minutes to create a summarized table from the data warehouse. Data transfer from the data warehouse to be used in the analytics server usually takes almost an hour to transfer the data. Looking forward, as the data volume increases, the processing time will increase proportionally and is scalable for this purpose. The analysis is provided for different dimensions of the data set. The data set is aggregated multiple times, and analysis is performed on the summary data set created after aggregation. This part of statistical computation takes around four hours, which is the bulk of the total processing time. The final transfer from output data set to the data warehouse is about six minutes. On the whole, the standard process consumed around five hours, which is too time consuming and resource intensive to maintain. In addition, any “tweaks” or changes to the process will take several business days to test and implement.

There are several challenges with the traditional process. First and foremost, this process uses fast export approach with multiple sessions, and this might take a lot of bandwidth on the server. In addition, the data transfer may fail if the volume of data is massive and the process has to be restarted. More system resources are needed to process such volume of data, so there is a limitation to execute only one or two processes at a time. This will put restrictions on the whole process to perform the analysis in a tight time frame.

Telecommunication: Developing a Data Model Using In-Database Technology

In our next success story and use case, we have a customer based in Europe. This company is one of the largest telecommunication carriers in Central Europe. With the company earnings under pressure due to falling prices, declining customer loyalty, and competitive nature, there is an enormous need to understand customer behavior in order to diminish the rising churn rates.

Churn analysis is important in every industry, but particularly in the telecommunication sector. Churn analysis is the process of identifying potential defectors and determining the causal factors, so that the company can try to prevent the attrition from happening in the future. Every telecommunication provider uses churn prediction to segment early warning for customer retention and marketing analysis. Segment early warning would be an application of churn analysis in campaign management. It basically is a combination of customer churn segmentation and customer value segmentation. The resulting subsegments (e.g., highest-value customer with neutral churn risk between 30% and 70%) are used for differentiated retention offers to the customer base and help to reduce overall costs of customer retention. Building an effective churn analysis relies heavily on granular customer data, which consists of large amounts of data and a variety of data sources.

One of the key aspects in churn analysis is to minimize modeling latency so that timely reactions can be taken due to market changes. To complete the end-to-end analysis, an automated scoring process should be included in order to have churn scores available on an hourly, daily, or weekly data. At the same time, to take advantage of granular and large amounts usage data in the form of call detail records and in order to optimize this churn model, it needs to have a well and tightly integrated data warehousing and analytics environment to minimize any type of latency between the environments, which historically have been only loosely integrated for this organization.

Financial: In-Database Model Development and Deployment

A major European financial organization identified that its cycle time from model initiation to model deployment was unsatisfactory with the changes in a global, competitive economy. The process was manual, error-prone, and resource-intensive. The process had little or no monitoring to identify model degradation.

This organization developed a flexible and efficient infrastructure for flexible data management, model development, and model deployment using in-database technology. The platform harnesses the power of the database environment for data preparation and model scoring and uses the power of analytics to build the complex and comprehensive analytical data models. By leveraging the massively parallel power of the database, over 55 million records can be scored within the database many times during the day. This could not have been accomplished with the older and traditional process. Figure 2.5 shows the process that this customer has implemented within its organization.

By adopting the in-database processing, the numerous months that it usually took for a model to be promoted to a production environment were dramatically reduced down to days. There was a 40 percent reduction in data preparation, and analysts are 50 percent more productive doing more strategic initiatives. This financial company was able to increase performance, economics, and governance.

Let's examine how this financial institution was able to apply analytics and the challenges they encountered.

Background

According to a Gartner report titled “Measuring the Business Value of Data Quality”, 40% of business initiatives fail due to poor data quality. Big data warehousing and analytics projects have failed due to data quality issues. The Data Warehousing Institute reports that billions and billions of dollars are lost because of poor data quality.² The cost accounts only for U.S. business losses in postage, printing, and staffing overhead. Frighteningly, the real cost of poor data quality is much higher. Beyond wasted resources, there are disgruntled customers, decreases in sales revenues, erosion of credibility, and the inability to make sound business decisions. Sometimes the effects of bad data cause enough damage for complete business failure.

Organizations depend on data. Regardless of the industry, revenue size, or the market it serves, every organization relies on its data to produce useful information for effective business decision making. Unfortunately, with so much emphasis on such information, data quality rarely gets the attention it deserves. No one wants to consciously admit that business decisions are based on inaccurate or incomplete data. TDWI revealed that 75 percent of organizations have no data quality processes in place.³ It appears that the majority of the businesses have taken no steps to determine the severity of data quality issues and its impact on the bottom line.

Companies invest hundreds of thousands of dollars and significantly large portions of information technology (IT) budgets on building sophisticated databases and data warehouses. In the quest for successful business intelligence, various applications and systems will be deployed and information gathering processes will be created. However, many overlook the essential fact that it is the underlying data that matters. All of the fantastic screens and reports in the world will not make a positive difference if the data that supports the system is inconsistent, redundant, and full of errors.

There are many reasons why the quality of the data that companies collect and analyze is so poor. The reasons vary everything from the very ambiguous nature of the data itself to the reliance on data entry perfection. But none is more compelling than the simple fact that companies rely on so many different data sources to obtain a holistic view of the business.

Information collection is increasing more than tenfold each year, with the Internet a major driver in this trend. As more and more data are collected, the reality of a multi-channel world that includes e-business, direct sales, call centers, and existing systems sets in. Bad data (i.e., inconsistent, incomplete, duplicate, or redundant data) are affecting companies at an alarming rate, and the dilemma is how to optimize the use of corporate data within every application, system, and database throughout the enterprise.

Take into consideration the director of data warehousing at a major electronic component manufacturer who realized there was a problem linking information between an inventory database and a customer order database. The inventory database had data fields that represented product numbers differently than the customer order database. Nothing was done about it. There were hundreds of thousands of records that would have to be reviewed and changed but there were no resources available to take on this tremendous, time-consuming task. As a result, more than 500 customer orders were unfulfilled. At an average order per customer of $5000, the resulting loss of US$2.5 million in revenue was significant.

Data Quality Defined

Data quality is often defined as a process of arranging information so that individual records are accurate, updated, and consistently represented. Accurate information relies on clean and consistent data that usually includes names, addresses, email addresses, phone numbers, and so on. Good data quality means that an organization's data are accurate, complete, consistent, timely, unique, and valid. The better the data, the more clearly it presents an accurate, consolidated view of the organization, across systems, departments, and line of businesses.

Technological advancements that use data pattern analysis, smart clustering, numerous data algorithms, and a host of other sophisticated capabilities ensure that data gathered throughout the organization is accurate, usable, and consistent. By intelligently identifying, standardizing, correcting, matching, and consolidating data, software solutions offer much needed relief to organizational data quality headaches.

Today organizations are looking for a wide range of features in data quality tools. According to the report by TDWI, standardization and verification top the list of desired capabilities, followed by tools that define and validate business rules. Other important features include matching, consolidation, and integration with other enterprise applications such as analytics.

Business Challenges

Bad data originates from a variety of sources—errors in data entry, erroneous data received from Internet forms, faulty data purchased or acquired from an outside source, or simply combining good data with outdated data and not having the ability to distinguish between the two.

One obstacle to creating good data is simply examining what is available and developing a plan for how it will be used. You will need to determine which data are good, which are bad, and how bad the bad data are. Most organizations have little or no idea about the quality of the data residing in their systems and applications.

According to TDWI, almost half (44 percent) of the respondents said the quality of the data within their companies was “worse than everyone thinks.”⁴ This same report chronicles examples of costs and missed opportunities due to inaccurate or incomplete data:

• A telecommunications firm lost $8 million a month because data entry errors incorrectly coded accounts, preventing bills from being sent out.
• An insurance company lost hundreds of thousands of dollars annually in mailing costs (postage, returns, collateral, and staff to process returns) due to duplicate customer and prospect records.
• A global chemical company discovered it was losing millions of dollars in volume discounts in procuring supplies because it could not correctly identify and reconcile suppliers on a global basis.

Sadly, most companies are oblivious to the true business impact of poor quality data. The simple truth is that poor data quality absolutely affects the bottom line. Accurate, complete data reduces costs in a number of ways, from the simple and obvious marketing savings (postage and production costs on a direct marketing piece, for example) to the less obvious organizational efficiency savings.

According to the same Data Quality Survey (mentioned earlier), almost half of the surveyed companies suffered losses, problems, or costs due to poor quality data. Companies also cited extra costs due to duplicate mailings, excess inventory, inaccurate billing, and lost discounts, as well as customer dissatisfaction, delays in deploying new systems, and loss of revenue.

Data as a Strategic Asset

Data should be treated as a key strategic asset, so ensuring its quality is imperative. Organizations collect data from various sources: legacy, databases, external providers, the Web, and so on. Due to the tremendous amount of data variety and sources, quality is often compromised. It is a common problem that many organizations are reluctant to admit and address. The single most challenging aspect for companies is to recognize and determine the severity of their data quality issues and face the problem head-on to obtain resolution. Spending the money, time, and resources to collect massive volumes of data without ensuring the quality of the data is futile and only leads to disappointment.

Cleansing data at the source is a significant way to enhance the success of a data warehouse and analytics. Thus, it becomes a proactive rather than reactive model. Simply collecting data is no longer sufficient. It is more important to make proper sense of the data and to ensure its accuracy. As the amount of data escalates, so does the amount of inaccurate information obtained from the data. Data should be cleansed at the source in order to detect and address problems early in the process so that quality issues are prevented further down the line.

Information is all about integration and interaction of data points. Inaccuracies in a single data column can ultimately affect the results and may directly affect the cost of doing business and the quality of business decisions. Preventive measures to ensure data quality usually are more economical and less painful. Delaying the inevitable data cleansing dramatically increases the cost of doing so, as well as increases how long the cleansing process takes to complete.

Improved synergy between the extraction, loading, and transformation (ETL) warehousing process and data quality offers the ability to more easily manage complex data integration. By applying data quality in the ETL process, data integrity and accuracy are assured. Much of the data warehousing effort is concentrated in the ETL process with the extraction of records and fields from various data sources, conversion of the data to new formats, and the loading of data to other target destinations such as a warehouse or a data mart. The purpose of the ETL process is to load the warehouse with integrated and cleansed data. Data quality focuses on the contents of the individual records to ensure the data loaded into the target destination are accurate, reliable, and consistent.

The Value of In-Database Data Quality

The technology for in-database data quality has only been on the market for about three years. Not many vendors offer this capability. This technology is neither as mature nor as advanced as in-database analytics. Enabling in-database data quality provides value to both business analysts and IT professionals. Many of the IT professionals focus heavily on the ETL process since its purpose is to load the data warehouse with integrated and cleansed data. However, data quality is a key component in the preparation for entry into the data warehouse. The best place to clean the data is in the source system so the defects cannot extend to the data warehouse and other interrelated systems. There are several methods and processes to achieve integrated and cleansed data.

Data Auditing and Standardization

One of the most popular data quality processes is standardizing the data. Now, you can standardize the data in-database. Data in a database or data store typically are inconsistent and lack conformity. There are many ways to say the same thing. As Table 2.4 illustrates, a title may be expressed in various ways.

If a direct mailing campaign plans to extract 5,000 “VP of Sales” out of a database by writing a query, and the query does not include every possible way that “vice president of sales” is represented, then the search will miss some of the target names. Inconsistently represented data are more difficult to aggregate or query. If the data are not represented consistently, it is even more difficult to perform any meaningful analysis of the data.

The first step in the cleansing process is data auditing. Data auditing provides counts and frequencies for data fields. It also identifies unique values and range reports with maximum and minimum values. In this phase, you should also define your business and cleansing rules. Once you have analyzed the values, then you can standardize the title for consistency.

Data Consolidation and Matching Analysis

Another important data-quality process is matching and consolidating records. Anyone who gets two or three of the same advertisement or magazine instead of a single copy to which you subscribed is likely experiencing a perfect example of a “duplicate record” problem in the customer database. In some cases, there are probably small variations in the way the subscriber's name or address appear in the database. Table 2.5 illustrates the example.

William Doe may have several contact points and enter various kinds of contact information. A human being would look at these variations and instantly recognize that these names represent the same person, but a computer would store these as different records—hence multiple copies of the same advertisement or magazine. In this case, resources are allocated on inaccurate information extracted and loaded into the data warehouse with duplicate or redundant data. This problem can obviously be very costly for any organization that routinely mails against a large customer database.

Data consolidation and matching analysis are fundamental to the data management and data quality process. These capabilities link data from multiple sources and identify records that represent the same individual, company, or entity. The process of consolidation combines the elements of identifying matching records into a single, complete record (often called the golden or master record).

Matching and standardizing routines are necessary to analyze, cleanse, and consolidate data from a variety of platforms and sources. It includes defect analysis and corrections for invalid data and data authentication processes.

Other In-Database Data Quality Functions

Standardizing and matching of data are the most prevalent data quality functions, and they are most useful in-database. Other in-database functions include:

• Casing: Ensures context-appropriate casing is used in a data column.
• Extraction: Extracts context-specific entities or attributes from text string.
• Gender analysis: Determines the gender of a name.
• Identification analysis: Determines the type of data represented by a text string.
• Parsing: Segments a string into separate, discrete entities.
• Pattern analysis: Shows simple representation of a text string's character pattern. It's useful to determine if further data quality processing is necessary.

As in-database data quality evolves and customers are demanding more in-database data-quality functions, I truly believe it is an essential element to conquering big data and analytics.

Value of In-Database Data Quality

Like in-database analytics, in-database data quality offers valuable benefits and advantages. Imagine what you can accomplish when data quality processes you once considered too complex are not only possible, but can be done quickly and with minimal effort. You will get your big data under control in no time—which leads to big ideas. It has big potential and possibly an even bigger payoff.

With in-database data quality, you can:

• Become more agile, reacting with lightning speed to new opportunities and challenges.
• Improve productivity by minimizing time spent on moving and manipulating data.
• Boost security by keeping data intact and in a single, highly governed environment.
• Seamlessly manage the flow of cleansed data throughout the enterprise.
• Keep costs in check by using an integrated solution instead of piecemeal technology.

It is a fact that ignoring data quality is costly, and it affects every industry that relies on accurate and consistent information. Data quality is a continuous process and effort. By addressing data quality at the source, data cleansing becomes a proactive rather than a reactive process. The integration of data quality with the database minimizes the risk of failure, cost, and number of resources to manage the data. The synergy between ETL and data quality provides integrated and cleansed data for your warehouse, mart, or other repository. Data are a vital resource and should be treated as a key strategic asset in order to obtain reliable and accurate view of your organization.