Customer Solution Case Study
/ / Telecom Firm Cuts Costs, Enhances View into Business with 5-Terabyte Data Warehouse
Overview
Country:United States
Industry:Telecommunications
Customer Profile
Based in Allen, Texas, Sage Telecom is the third largest telephone company in Texas, with over a half-million customers in 11 states.
Business Situation
Sage Telecom needed a better source for operational data to help it control costs, and business intelligence to help it better serve customers.
Solution
Sage Telecom created two data warehouses using versions of Microsoft® SQL Server™ 2000 and the Microsoft Windows Server™ 2003 operating systems.
Benefits
Ability to validate inter-carrier billing
Information to negotiate better inter-carrier contracts
Better visibility into customer needs
Easy-to-use reporting / “The goal is to serve our customers and build loyalty to reduce turnover. The data marts we’ve created using Analysis Services are giving us the information we need to do this.”
Richard Reynolds, Data Warehouse Architect, Sage Telecom
Sage Telecom, which has operations in 11 southwestern states, has enjoyed rapid growth since its founding in 1996. With growth comes the need for better data. Sage Telecom identified two areas where additional information was especially needed—data for reducing operational costs, and to enhance customer service. To reduce operational costs, the company deployed its Decision Support System data warehouse with 5 terabytes of call detail records on a single instance of Microsoft® SQL Server™ 2000 Enterprise Edition (64-bit) running on Microsoft Windows Server™ 2003 Datacenter Edition. It created a business intelligence data warehouse on a single instance of SQL Server 2000 Enterprise Edition running on Microsoft Windows Server 2003 Enterprise Edition. The data warehouses help Sage Telecom save on inter-carrier charges, while giving the company a better view into customer needs.
Situation
Sage Telecom, which was founded by industry veterans in 1996 to serve areas in the north of Texas, proved so popular that it has since extended its full range of local and long distance telephone services into 11 states. One of the fastest growing competitive local exchange carriers in the United States, Sage Telecom has more than 500,000 billable lines.
As the company continues to grow, so does its need for operational data and business intelligence from its information systems (IS) infrastructure. Telecommunications involves complex billing structures as the telecommunications company supporting the origination of a call makes use of resources owned by other telecom entities to complete the call. The accounting becomes more complex with long distance calls and “pay-per-use” features. The company needed an improved solution for revenue assurance, to ensure that it wasn’t being overcharged by other carriers, and to verify that its own billings were correct.
Telecommunications has become increasingly competitive in recent years as consumers have been offered more choice in selecting a carrier. This has been good for consumers and has forced carriers to focus more on customer satisfaction to avoid customers moving to another carrier, a problem referred to in the industry as “churn.”
Sage Telecom needed a set of business intelligence solutions—a data warehouse to help it control operational costs, and a business intelligence (BI) data warehouse to help it better serve its customers.
The Need for Operational Data
Sage Telecom needed a data warehouse to help control operational costs because its current system for monitoring payments to and receipts from other carriers was very cumbersome. It required vast amounts of manual intervention since the company lacked automated means of processing extensive, complex inter-carrier billing checks and balances
The Need for Customer BI
Sage Telecom needed a customer BI data warehouse to help it ensure that its customers had the proper calling plan—meaning the one that would save the customer the most money, even if it reduced monthly payments to the company. Sage Telecom realizes that in the long run the best way to grow revenue is by keeping customers happy to build loyalty and reduce churn.
The company wanted to use business intelligence to get a better idea of what new features its customers might be interested in, and to help it structure better inter-carrier contracts with other telecom companies by providing more precise data on usage patterns.
Without customer BI, Sage Telecom had no means of spotting trends and anomalies with respect to calling plans versus customer calling usage behavior, all of which is required to perform accurate marketing analysis.
Considering its need for the two data warehouses, Sage Telecom determined it needed to create a solution that would:
Support bill validation
Ensure proper customer fit for calling plans
Help determine future offerings
Generate management reports
Solution
The company created two data warehouses, an operational costs data warehouse, called the Decision Support System (DSS), to validate data from call detail records, and a customer BI data warehouse feeding several data marts to provide analytics from the company’s customer service online transaction processing (OLTP) system.
Decision Support System Data Warehouse
Sage Telecom’s DSS data warehouse stores 5 terabytes of call detail records, which include more than 12.7 billion rows of data. The call detail records are stored on a single instance of Microsoft® SQL Server™ 2000 Enterprise Edition (64-bit) database software running on the Microsoft Windows Server™ 2003 Datacenter Edition operating system. Both Windows Server 2003 and SQL Server 2000 are part of Microsoft Windows Server System™ integrated server software. The DSS data warehouse is hosted on an HP 9000 Superdome server with 20 processors and 40 gigabytes (GB) of RAM.
The DSS data warehouse has a tiered architecture, with all three tiers hosted on the HP 9000 Superdome. The DSS data warehouse tiers include:
ETL Tier. Every day, Sage Telecom’s billing department receives call detail records from other carriers in flat-file format. From there, the files are bulk inserted into a TEMPDB database using two separate processes, so extract, transform, and load (ETL) processes can be performed in parallel to validate and cleanse the data. All of the ETL is performed using SQL Server stored procedures written using Transact-SQL (T-SQL). Additional ETL is performed on the call detail records as they are normalized into 140 dimensions and then loaded into the DSS data warehouse each night. T-SQL is also used to perform ETL on 300 GB of data that is brought in each night from the customer call center OLTP database. Only new distinct records are loaded, with average database growth between 330 MB and 550 MB per nightly load.
Data Tier. The 5 terabytes of data is loaded across 11 databases, with 10 of the databases holding data according to financial quarters, with each database holding two quarters. The eleventh database holds telecom switching data. Nightly downloads can involve inserts to multiple quarters because call detail records are frequently 90 days old, and sometimes several months older than that when received from carriers. Stored procedures determine which database the record should be inserted to. The new distinct data is then used to update dimensions via additional T-SQL ETL processes.
Reporting Tier. A third-party relational online analytical processing (ROLAP) application provides analytics, gaining a performance boost from working across the 11 databases simultaneously.
Business Intelligence System
Sage Telecom’s customer BI data warehouse stores 300 GB of customer service records on the same single instance of Microsoft SQL Server 2000 Enterprise Edition (64-bit) and Windows Server 2003 Enterprise Edition as the DSS data warehouse.
The customer BI warehouse has a multi-tier architecture, with all tiers hosted on the same HP 9000 Superdome server as the DSS data Warehouse. The customer BI data warehouse tiers include:
ETL Tier. Throughout the day the Customer Service OLTP database uses the Transactional Replication feature of SQL Server 2000 to keep the subscribed database on the customer BI system current. The Customer Service OLTP system utilizes a Unisys ES 7000 server with 12 processors and 28 GB of RAM on a single instance of SQL Server 2000 Enterprise Edition and Microsoft Windows Server 2003 Datacenter Edition. Each night, the T-SQL stored procedures validate and cleanse the data, load it into the staging tier, and apply appropriate business rules according to the data mart the information will be sent to.
Staging Tier. The staging tier inserts new distinct records into the dimensions in the star schema database.
Star Schema. The star schema database consists of multiple fact tables linked via shared and private dimension tables, containing hundreds of levels and millions of members. The average fact table will contain 20+ dimension keys and over 1 million rows.
Data Marts. The data marts, created using SQL Server 2000 Analysis Services to meet business-driven initiatives, are updated with the new data snapshots.
Reporting and Analysis Tier. ProClarity is used to run a unique set of reports against each data mart as well as various data analysis initiatives.
Benefits
Creating the DSS and customer BI data warehouses has produced a number of benefits for Sage Telecom, including the ability to validate inter-carrier billing, information to negotiate better inter-carrier contracts, better visibility into customer needs, and easy-to-use reporting.
Ability to Validate Inter-Carrier Billing
Bringing all call detail records together into the 5-terrabyte, 20-processor DSS data warehouse has given Sage Telecom the ability to monitor much more closely what it is being charged by other carriers. “Before creating our DSS data warehouse with SQL Server 2000, we had to search through terabytes of COBOL flat-file data to verify that inter-carrier charges were correct,” says Kevin Brooks, Lead Data Warehouse Architect at Sage Telecom. “In fact, effective monitoring wasn’t really possible before we created the DSS data warehouse. Our ability to host a database that is now nearly 5 terabytes on SQL Server, combined with our ROLAP application and ProClarity reporting, has given us the ability to precisely monitor and validate our inter-carrier charges.”
Monitoring inter-carrier charges—whether being over charged, or under compensated—is essential because small errors multiply across an operation. “Inter-carrier billing errors happen, and when you multiply that times 500,000 customers, and dealing with eight or nine phone companies, the numbers get big in a hurry,” says Rowdy Atkins, Manager, Database Systems at Sage Telecom. “Without our SQL Server 2000 DSS data warehouse, we did not have a good way of telling whether we were over billed from a carrier, or underpaid for our services. We just received our bills and paid them. Now we have the ability to reduce our cost by finding billing mistakes and filing disputes to resolve the errors with the other phone companies. This helps us lower costs to our customers.”
Billing errors can add up to significant amounts. “The errors we have already caught by using the DSS data warehouse will provide significant savings when the errors are resolved,” says Richard Reynolds, Data Warehouse Architect at Sage Telecom. “Without the visibility that we have gained from pulling call detail records into our SQL Server 2000 database, these errors could have gone unnoticed. Our deployment has more than paid for itself through identifying these inter-carrier billing errors.”
Information to Negotiate Better Inter-carrier Contracts
Having call detail records in the easily searchable DSS data warehouse is helping Sage Telecom identify customer calling patterns and to use that information to negotiate better contracts for inter-carrier services.
“With our SQL Server data warehouse, we have easily accessible data now that we simply didn’t have before,” says Atkins. “This information gives us negotiating power. We can go to a carrier, point to the evening hours in which we have heavy customer usage, and ask for price adjustments. With our old system it would have taken months to have developed a data set that we now get automatically.”
Better Visibility into Customer Needs
The customer BI data warehouse provides Sage Telecom with deep insights into customer usage patterns, which helps the company better meet customer needs. In the deregulated telecommunications environment of the United States and many other countries, customers have the ability to easily change carriers. The need to avoid customer churn has placed a premium on the ability to fit customers into the best calling plan to reduce the customer’s monthly bill.
The customer BI data warehouse includes a cube to help ensure that a customer isn’t spending more than they need to. The customer BI data warehouse also includes a Friendly Call cube that is used in conjunction with this data. A friendly call or direct-mail message is triggered when Sage Telecom determines that a customer could save money by switching to a different plan.
“We want to make sure that the customer is in the most beneficial plan for them,” says Reynolds. “If a customer signed up for a plan that provides 60 minutes of free long distance calls per month and is actually using 300 minutes, we want to place them in another plan that will save them money—while increasing their satisfaction with and loyalty to Sage Telecom. Similarly, if a customer signed up for a plan offering more minutes than they use, we can lower their costs by placing them in a plan that provides fewer free long distance minutes.”
The same kind of analysis can be done for services. “One customer might have loaded up on additional services they don’t use, while another might save money because they are using services they could get at a discounted price if they had another service plan,” says Reynolds. “The goal is to serve our customers and build loyalty to reduce turnover. The data marts we’ve created using Analysis Services are giving us the information we need to do this.”
The company uses an Initiative Analysis cube to track promotional efforts. “When we identify a group of customers that would benefit from an upsell to a plan that better fits their needs, we can offer the upsell to 80 percent of the group and then track churn, comparing the 80 percent that were contacted with a friendly call, with the 20 percent who weren’t,” says Cindy Kelley, IS Data Warehouse Analyst at Sage Telecom. “Analysis Services gives us the analytics we need to precisely measure and fine-tune our marketing initiatives.”
Easy-to-Use Reporting
Sage Telecom is enjoying the ease with which reporting solutions can be created using SQL Server Analysis Services to create multi-dimensional cubes and ProClarity for reporting. “Analysis Services is extremely easy to use,” says Reynolds. “It provides drag-and-drop functionality that makes it easy for our Database Architects to create and populate cubes. The Analysis Services Usage-Based Optimization Wizard makes it easy to create aggregations to speed query processing enabling database architects to pinpoint where users are looking and create additional aggregations.”
Wherever a Sage Telecom business process owner (BPO) sees a need for data, the IS team can quickly respond with reporting. “We just used Analysis Services to create a Trouble Ticket cube because we had a BPO who wanted to track and analyze how long it takes for customer problems to be resolved,” says Kelley. “This helps us create a customer-focused business.”
Sage Telecom IS appreciates the ease of creating reports with Analysis Services and ProClarity because initial successes have led to increasing demands. “As the BPOs see how reports are helping other groups, they identify new ways in which they can use data for their own organizations,” Reynolds says. “There has been an avalanche of requests for new reports. This is good because that is what we are here to do. Fortunately, Analysis Services makes it easy to respond.”
Planning for SQL Server 2005
The company is already making plans for an early upgrade to SQL Server 2005 to take advantage of new features, including SQL Server Integration Services, which replaces DTS; Online Indexing, which supports concurrent modifications (updates, deletes, and inserts) to the underlying table or index data; Database Mirroring, for disaster recovery; and Database Snapshots for incrementally updating cubes.
“We are eager to move to SQL Server 2005, and our first deployment will be for our Decision Support System,” says Reynolds. “We want to use SQL Server Integration Services because it has great error handling capabilities, provides a truly enterprise-grade ETL solution, and is easy to use.”
The company plans to use SQL Server 2005 Online Indexing with its customer OLTP data. “We like the fact that SQL Server 2005 gives us robust online re-indexing capabilities,” says Reynolds. “We will no longer have to perform extensive re-indexing during the maintenance window, thanks to the new Online Indexing functionality. We can form it during the day with limited to zero impact on the call center.”
Online Indexing is expected to help ensure data availability to customer service staff. “We run a 24-hour call center, and using Online Indexing will free us from having to block users when we re-index a 17-million row table,” says Reynolds. “As a typical OLTP system, we may have 30 joins in one statement to bring up a customer’s data. If re-indexing is blocking one table, our customer service has to put the customer on hold, explaining, ‘I’m sorry, my system is running slow.’ This could take 30 minutes, and you can’t keep the customer on the phone that long.”