Introducing the Innovators:
In this month's Reader to Reader, Douglas McDowell, first runner-up in SQL Server Magazine's 2003 Innovator Awards contest, shares his winning solution. McDowell is a principal consultant for Intellinet in Atlanta.
A national network service provider, Cox Communications must deliver high-quality performance for the telecommunications services it supplies to 6.3 million customers. Although Cox's network-management system gave the company's Network Operations Center (NOC) information about its various Internet, cable television, and telephony networks, the system couldn't store historical activity of the more than 2 million daily events and offer adequate response time. The company couldn't track performance data or proactively manage network problems, and the NOC staff didn't have enough information to make business decisions. The NOC receives data about Cox's networks from a variety of data sources: High-speed Internet data comes from a system based on Linux and MySQL, cable television data from a system based on Sun Solaris and Oracle, and telephony data from a system based on Windows and SQL Server.
To solve the problem, Intellinet's business intelligence (BI) team created the Network Monitoring Analysis Solution (NetMon), a custom-built network monitor that uses SQL Server 2000 Enterprise Edition running on Windows Server 2003 and Windows 2000. This 64-bit and 32-bit solution has four tiers, as Figure 1 shows, that store the data that the NOC receives, clean the data, and let the NOC staff create meaningful reports.
Preprocessing. In its first tier, NetMon processes data from all sources and delivers it to a 500GB SQL Server database—featuring multiple-instance SQL Server clustering—distributed across six servers with Itanium2 processors. As Figure 2 illustrates, the system uses Data Transformation Services (DTS) to correlate and cleanse the data, then loads the data into the data warehouse.
Data warehousing. The data-warehousing tier stores enormous amounts of correlated data. The system collects about 640MB of data per day. Its hybrid star-snowflake schema contains 11 fact tables surrounded by 38 dimension tables. The use of clustered indexes on the fact tables greatly improves the system's reporting performance and OLAP cube processing times.
OLAP. In the OLAP tier, NetMon employs Analysis Services to house 14 cubes, the largest of which contains more than 290 million rows. Analysis Services aggregates the data in ways that are meaningful to the company. Then, NOC staff and management customize the NetMon data by using TARGIT AnalysisNET 2K3, a thin-client analysis tool from TARGIT A/S.
Reporting. Using NetMon's Web-based interface for accessing data and running ad hoc reports proved cumbersome for users and overloaded system resources. By using SQL Server 2000 Notification Services for alerts and SQL Server 2000 Reporting Services, NetMon's reporting tier provides integrated report management.
With NetMon, Cox can consolidate its limited, labor-intensive reporting efforts from 18 geographic systems to a small team at the NOC. The NOC can better isolate the causes of network problems and prioritize problem resolution. As a result, the company can deliver a higher level of customer service by focusing resources on problems that affect the most customers.
