Development Tool Key to Data Warehouse Success

Outsourcing its core business systems made sense for awhile, but when Paragon Cable decided to move them to an in house AS/400 it was like moving from the frying pan to the fire.

Until two years ago Minneapolis-based Paragon Cable, a cable TV provider serving 180,000 subscribers in the Twin Cities area, relied on an outsourcer for its subscriber and network maintenance billing systems.

"It was a way for a smaller organization to utilize a big mainframe and let the outsourcer take care of it," says Mark Webb, Paragon Cable's MIS supervisor. "We had terminals and PCs with terminal emulation that connected to their mainframe."

The problem was that the outsourcer basically owned the information. Any time Paragon needed reports there would be a charge for them. "We had grown to the point where we decided we wanted more control over our business and our information," Webb explains.

Paragon installed an AS/400 Model 310 (since upgraded to a RISC-based Model 530), acquired it's data on tape from the outsourcer and converted to the Cable Master billing system from Cincinnati Bell Information Systems (CBIS). But while the move in-house solved some problems, it created some new ones.

"One thing we didn't consider, is that the outsourcer had a lot of canned reports that we ran daily, weekly or monthly,” Webb observes. “The new product claimed to have the same information and we took it for granted that our accounting department, for instance, would get the information it was used to getting. But the reports weren't there."

It was clear that it would take CBIS a long time to create the reports and write them into the program, he says, and it would be at an additional cost. Plus, there was the chance that end users running queries against the data on the AS/400 would slow the system down.

"We decided that a better idea was to do the reporting on a completely different platform so that the AS/400 could be used just for the billing system," Webb says. "We decided to do a data warehouse and chose the Windows NT platform."

The first attempt at a data warehouse was using an Windows NT version of a mainframe warehouse application. "It ran on an NT server with a module on the AS/400," Webb explains. "It was to be a three tiered reporting tool. Some of the data was kept on the PC, some on the NT server, and you would write a program on the PC that would tell the NT server to retrieve certain information from the AS/400."

After working with this product for a year, Webb says, Paragon wound up with the beginnings of a very complicated data warehouse and a lot more development work to do. "We needed someone who knew SQL well enough to write the programs that would make the AS/400 and the NT product work together. We didn't have anyone who could do that." After a year, he says, it was time to move on.

"What I was looking for was a piece to run between the AS/400 and the NT server to schedule the data. I was looking for something we didn't need to hire a programmer to create and maintain," Webb says.

Last year, after exploring the top available tools, Webb found a free demo of Montreal-based Open Universal Software's Rapid Data data warehouse development tool on Open Universal's Web site (www.universal.com). He downloaded it, called their support and, he says, had the product installed and up and running in about four hours.

According to Webb, one of the problems encountered in the first data warehouse lay in managing changes in cable customer subscriptions. If two or more changes were made in a single account only the final change would be available. Paragon needed the ability to track multiple changes to a file, and to append those changes to an existing SQL table on the Windows NT server.

"Some of the Cable Master databases are set up so that if a change is made to a customer account the old record is overwritten," Webb explains. "There is no way to track how many times that record has been changed. All we see is whatever is current."

"We found that Rapid Data has a feature that can be used as an audit trail," he continues. "When a record gets changed on the AS/400 it will replicate that to the NT server in near real time and append it to a file rather than overwrite the record. Now we have a complete master file with an audit history on the NT server."

This is important to Paragon's marketing and training departments, and would have required programming changes in Cable Master. "Sometimes you need to find out why certain changes were made and who made them," Webb says. "It has a time stamp and indicates the person who made the change. We know who is making what changes and when. We didn't need to hire a programmer to do that."

Another benefit of running the data warehouse on the Windows NT server, according to Webb, is that it will allow Paragon to keep its data longer. "As time goes on we are going to be purging data from the AS/400 but we'll be able to retain that on the NT server. The marketing department will find that very valuable. In about another year we will be able to start doing some history and trend analysis."

Not only was the product easy to install, Webb says, but it has exceeded his hopes. "Since we have started using the product we've found features we hadn't planned on using. But we've come up with reasons for using them."