Comcast Cellular Communications, a division of Comcast Corporation, is a regional wireless telecommunications company offering cellular service to approximately one million customers in the Northeast. Comcast is among the nation's largest wireless service providers. They were the first U.S. cellular carrier to provide 100 percent digital coverage throughout their entire network. Comcast sells high-end telecommunications products including phones, pagers, accessories and service. Sales consist of several channels including numerous resellers and more than 60 retail stores. These outlets are served by a centralized inventory procurement and warehouse distribution center.
The business units for marketing, finance and customer service were determined to improve customer satisfaction and retention. The goal was to build a data warehouse that would help the business make informed and timely decisions. Comcast partnered with Approach Inc., a professional services firm, to provide the technology and expertise for an integrated business solution.
Initial exploration revealed that important data was not being captured or it existed only in operational systems where it could not be effectively leveraged for business intelligence. Problems existed in both inventory and marketing systems. Items were sometimes overstocked or were not being restocked quickly enough. The marketing team lacked the ability to track the effectiveness of promotions and advertising campaigns because sales and customer information was only available though a proprietary third-party system that had a one-month lag in data delivery. Additionally, the marketing department did not have the flexibility to do its job effectively. For example, they could not easily track campaigns by retail store, geographical boundaries or specific customer demographics over time.
Data for the warehouse was gathered from various disparate systems including a mainframe billing system that contained customer information, service plans and statistics on minutes of use, calls and dollars spent. There was a proprietary system that held only current inventory information and operational systems that recorded retail sales. We used the techniques popularized by Ralph Kimball to develop eight star schemas in an iterative manner. These data marts track inventory movements, inventory snapshot levels, customer events (such as activations, deactivations and changing features or service plans), customer phone usage and retail sales data. Each star schema uses common notation and several identical dimension tables for customers, subscriptions and time. We used slowly changing dimensions to track all metrics and changes over time.
In addition, a comprehensive set of reference data was gathered to make the dimension tables information rich, with descriptive attributes to support analysis and reporting. For example, we used descriptive interpretations of codes used by the business for phone features, service plans and customer feedback. We used customer information including demographic information that was purchased from a third party and lists of service plans, markets and products.
Approach Inc. implemented a bottom-up distributed data warehousing solution consisting of eight dependent data marts. We used relatively inexpensive Compaq Proliant Servers and became early adopters of Microsoft SQL Server 7.0 for our data warehouse engine. This was a good fit because Comcast runs nearly all production systems under Windows NT and has used SQL Server almost exclusively in the past. The skills and knowledge within their MIS and data services departments is most compatible with SQL Server, therefore development and maintenance costs would be minimized. The start-up, implementation and maintenance costs were very low compared to alternative hardware and software solutions. In addition, the Microsoft suite of products is tightly integrated and comes with the promise of long-term support and continuous improvement.
Using the distributed approach, a single, low-cost NT machine supported the initial business needs, and additional servers were utilized as the warehouse expanded. SQL Server 7.0 comes with excellent tools for data extraction and transformation (data transformation services or DTS) and has promising data analysis potential in future releases through its Plato tool and the Office 2000 version of Excel, which acts as an interface to Plato for graphical analysis and data presentation.
However, to meet immediate reporting, querying and analysis needs, Plato and Excel were inadequate and did not scale well. The multidimensional approach to building data cubes resulted in unacceptable levels of "data explosion," and the Excel tool for end-user analysis was not yet completed. Other proprietary query and report writing tools were considered cost prohibitive given the organization's Microsoft SQL Server strategy, competency and infrastructure.
BrioQuery Designer, part of the Brio suite, is an end-user analysis and report writing tool that is powerful, yet easy for less technical individuals to learn. Business users can graphically access our data marts, select the desired fact and dimension tables, choose columns of interest and then specify limits or querying criteria. Using a simple interface, the user can analyze the result set by drilling up and down on pivot tables and charts. The tool can also be used to quickly create new reports. Several key reports were developed and scheduled (using Brio Enterprise Server) to run and publish daily to the Web for easy access and viewing by the interested business units.
With the data marts and OLAP tools in place, Comcast can now easily answer an endless line of strategic business questions. Marketing efforts can become more focused by determining what customer demographics fit certain categories of subscription usage (market segments). The possibilities are endless. End users will experiment, mine useful information and drive improvement and production of the data marts that have ultimately helped streamline business activity and improve customer service.
Never underestimate the importance of up-front planning and analysis. Make sure your input data has been validated by the business unit before any initial data loading. This can be done in parallel with design activity or requirements gathering.
When developing a distributed data warehouse, you will invariably be setting standards and practices that will be repeated in future work. It is beneficial to include as many developers and end users in the initial design in order to promote integrated and consistent dimensions. For example, you should agree upon a single customer or product dimension that will appear in all appropriate star schemas. Strive for more useful designs rather than focusing only on what is "right" or "wrong."
Finally, with any DBMS or hardware platform, it is essential to perform a rigorous proof of concept before making an investment in a particular solution.
The Comcast Cellular Data Services Team includes Bob Hyland, Ed Lindenhofen, Mohammed Maseeh, Darren Gordon, Andrea Durlak, Rachel Richmond, Stephen DiAcetis and Bill Brown.
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access