Case Study: AT&T Builds a Data Warehouse

For the past three years, AT&T has been using a stand-alone credit and risk management data warehouse of critical data for its consumer sector. Sandy Hall, division manager for Credit & Accounts Receivable Management, and Ron Parks, Credit & Accounts Receivable Management MIS district manager, explain what led AT&T to implement the data warehouse, the challenges in building it, how it is used, and where it is going.

A Need for Daily Data

In 1996 AT&T decided to return to direct consumer billing, instead of bundling its long-distance charges with those of local exchange carriers as it had done after divestiture. As a result, the company formed its Credit & Accounts Receivable Management (CARM) group to handle billing and collections.

As a direct-bill organization, CARM supports the consumer sector, not business customers. Whereas the business side emphasizes managing relationships, in the consumer world the focus is on making sure the customer pays the bill. “We look at how long someone has been a customer, their revenue, credit score, etc., and make [strategic] decisions based on these parameters,” says Hall. An accounts receivable (AR) system was established for results reporting, but as the organization started to grow and mature, so did its need to evaluate policies.

The need for CARM’s business analysts to have daily data was driven by its write-off cycle, which can range from 60 to 120 days, says Hall. Responsible for collection management, the business cannot wait two to four months to analyze the data. If it is not on track, the whole year can be written off. Because of constant financial pressures, the business “had to become more proactive in changing and tweaking policy,” she says. “In order to do that, we needed to understand what was happening and the simulation of what would happen.”

The result was the decision to build a data warehouse, providing more realistic data for profitability models. “In the past, we would summarize on a monthly basis, so by the time we got to the summarization process, we could see what we did in the past, but couldn’t really make decisions based upon results for that month,” says Parks. “With the data warehouse, we can still key off history, but the concentration is on the types of information required daily.”

AT&T had first contracted with NCR to develop AR reporting, which is separate from the warehouse (and now managed in-house). The warehouse incorporates NCR’s Teradata database manager hardware, as well as MicroStrategy’s OLAP (on-line analytical processing) tool for multidimensional database functionality. The warehouse’s DSS (decision support system) was built in-house but uses Oracle’s metadata definitions. Oracle technology is also used to bring in data from the collections center, serving as kind of a data mart.

Through this combination of technologies, the warehouse can support different types of data analysis on a single engine.“There aren’t a lot [of warehouses] out there that can support ad hoc queries, reporting, DSS and OLAP all in one architecture,” Hall says. “If you choose a mainframe architecture for a data warehouse, you won’t have much flexibility.”

The daily information feed for the warehouse comes from AT&T’s billing and collections systems, as well as credit bureau and marketing data from the collections decision engine. Types of data include unbilled charges, payments, adjustments and balances, all loaded nightly into Teradata, where it is available the following day via AT&T’s intranet.

Operations data such as call detail records (CDRs) are not integrated into the feed. MIS should not be used to run systems on a call-by-call basis, says Hall-that is the job of the operations system. “We get a lot of push back because we have a system that can do certain things, but should we give users access to run the business? … The answer is no, it’s not built for that,” she says. “It runs week-by-week, month-by-month and day-by-day to fine-tune the business, but not for minute-by-minute situations” such as those used by customer service and call centers.

As a CARM-specific data repository, the warehouse holds the “master” MIS copy of data. However, as Hall stresses, it is not the actual master data-that is in the operations system. “The philosophy ... was to have one central MIS copy of the data-true data,” she says. “If data is farmed out, others can use it, but when doing any official reporting, they have to come back to the warehouse’s master copy.”

According to Parks, the initial build-out of the warehouse took three to four months, and the push to get more timely results took a few more months. This involved upgrading the NCR hardware and adding daily summarization while his team was still taking in requirements for business needs.

Nontraditional Development

While putting together an operational system involves a long, drawn-out requirements process, MIS requires more instantaneous reporting. Since development process times are cut and requirements change frequently, developers need to be flexible. “With a data warehouse, you should not and we do not put the system through a traditional development process,” says Hall. Users request specific queries or reports that are usually needed in a matter of days, or they are actually in the warehouse manipulating the data themselves.

Hall refers to the warehouse as a “victim system,” since it must take data from many different systems, making it a “slave” to a master data source elsewhere. This created challenges in setting up the initial feeds. For example, an analyst would need a report with data elements not in the warehouse. Therefore, the added data elements had to be integrated into the traditional development process. This happens less frequently now, says Parks, but was common in the beginning when the system focused on results reporting. Taking data from different systems also entailed synchronizing and product stability issues. The MIS team needs to make sure data is updated when it is supposed to be-that the feed didn’t somehow get turned off. Analysts have actually discovered problems in MIS that the operations systems didn’t know about, such as large billing problems or large problems with an accounts receivable section.

As Hall observes, funding is always a challenge. “It is critical to running the business, but it’s not always intuitive that it would be,” she says. “In any corporation the demands for capital and development are pretty severe, and this is a sophisticated, mature type of tool.” According to Hall, the team is fortunate to have had executive sponsorship by Brent Bostick, CARM vice president, who knew the value of daily information from his work in the banking industry.

Data Warehouse Usage

Four CARM divisions use the data warehouse: Strategy and Results; Acquisition Process, Policy and Operations; Fraud and Usage Management Process, Policy and Operations; and Collections Process, Policy and Operations. It is also used extensively to prepare for executive presentations.

Users request both canned reports, which are pulled on a daily basis, as well as ad-hoc reports for deeper analysis. Analysts can easily drill down for data with the MicroStrategy OLAP tool’s multidimensional view of the database. People can save the queries used to produce ad-hoc reports and have them run at specific intervals. There is currently no central repository for storing and sharing past queries, but it is in the works, says Parks. Analysts now share queries via e-mail. The way they access data in the warehouse is moving toward the Web, which will mean less effort maintaining the client-server environment and less worry about software distribution.

Ongoing Challenges and Applications

Through the use of metadata-data about data-the CARM MIS team is starting to define business rules for data elements within the warehouse. Still in the early stages, it is a more sophisticated control mechanism for ensuring data cleanliness. Depending on the query, there can be thousands of business rules, and every time a report is run, specific measurements need to be determined.

Hall gives an example of the value that can be provided by metadata: A collector may want to compare the performance of a group of collectors against that of the group’s key performer, but first the key performer needs to be defined. Is the collector looking for the key performer across all elements, or the top performer by a specific element, such as promises paid/promises kept? What is the rule-how can the collector measure his or her performance? If the key performer collects $400 per hour and the inquiring collector collects $300 per hour, then the business rule is that the collector’s performance is at 75 percent of the key performer’s.

Another ongoing challenge is maintaining data quality; the MIS team cannot just assume that the data coming from the billing system is correct. They are constantly monitoring and checking against past performance as possible indicators of problems. Whereas analysts usually find missing data or billing problems, Parks and his team focus on system-type data quality, such as data retrieval and location. The end result is a partnership between the analysts and the production team.

The CARM MIS team focuses daily quality efforts in four main areas:
Timeliness-Guaranteeing data is there when it should be.
Completeness-Ensuring the correct number of data elements.
Cleanliness-Using edits and controls for data cleansing, with the choice of loading data, or putting it into an error (holding) file.
Availability/access-Ensuring that data is available to users with the best tools for retrieval.
As a nontraditional use of the data warehouse, CARM was given the challenge of minimizing subscription fraud by tracking write-offs by country and product. This type of fraud cannot be caught by traditional detection systems, since information is needed that the fraud system doesn’t have. However, by manipulating the data in the warehouse, CARM was able to analyze and segment the uncollectible history to determine patterns of write-offs for “never pays” scenarios.

Now working with NCR, Parks’s team is beginning to use data mining tools to increase the value of the warehouse by finding additional patterns and relationships between the data. Automatically predicting the data correlations the analysts will need, it is mature to the level of where artificial intelligence was 10 years ago, says Hall.

Managing myriad state regulatory policies is another continuous challenge. The warehouse needs to know service balances and regulatory rules for each state when developing new policies. Since certain policies only apply for certain states, the MIS team needs to know each state’s profile-how the policy will be affected there, what the risk is, etc. “Some states have very little leverage in getting customers to pay their bill,” says Hall.

The Benefits

What has AT&T gained by implementing a data warehouse? According to Hall, CARM commits to multimillion-dollar savings each year in supporting the data warehouse. “We make our goal every year, which is why we continue to get funding,” she says. Ultimately the savings come from better control of uncollectibles and organization expenses. Certain production problems can cost the company millions of dollars per day, so the benefits are significant if the problem is caught right away. CARM estimates an investment to savings ratio of 5-6 to 1, says Hall. There is also a cost avoidance savings, which can’t really be measured, Parks adds.

Hall also notes that, although data warehouses can be difficult to justify, once they are in place they can help justify other types of infrastructure for future cost savings. “It becomes easier to say, ‘If we had the capability to do X, it would have saved us $4 million last year-and by the way, these are the types of customers it would apply to,’ ” she says.

Future Challenges

Hall expects a continuing need to integrate new AT&T acquisitions into the warehouse, which raises a whole new level of complexity regarding appropriate business rules for data elements. According to Hall, the data warehouse is actually sometimes the first area integrated after a merger, even before any operations systems. “It should be one of the easier systems [to integrate], but since it is hooked into all different places, it keeps us hopping.” Newly captured data is stored in a learning lab table until the MIS team understands how it can be used. It is then integrated into the warehouse, and queries are developed.

Since the warehouse is currently geared toward information delivery, Hall regards it as still growing. “In a stable environment,” she says, “we would focus more on product cleansing (which should happen automatically), production and new interfaces with data, … and less on information delivery.” Most organizations typically shrink after they mature and fully deploy information delivery, but Hall expects the CARM environment to remain stable.

comments powered by Disqus