Midrange Stuff, OS/400 Edition--Ministry Finds Good Use for Business Intelligence Software

The standard business-intelligence case study goes like this: Company A implements data warehousing system B to make higher profits by selling the right product, at the right time, to the right group of people. This case study is a little different. It has to do with how a religious organization, Focus on the Family, is using business intelligence software from Coglin Mill and SPSS to do its job more effectively, which is spreading its message of family values and faith in God.

Focus on the Family started in a small, two-room suite in a Los Angeles suburb 26 years ago in response to Dr. James Dobson's concern for the American family. Dobson, who earned a Ph.D. in child development from the University of Southern California, saw the American household disintegrating as a reaction to massive internal and external pressures, so he started broadcasting a 25-minute weekly radio program.

That weekly radio program caught on, and since then, Focus on the Family has grown into an international organization with more than 82 ministries requiring nearly 1,300 employees. The organization broadcasts 12 separate radio and TV shows, publishes 11 magazine and newsletters that are sent to more than 2.3 million people each month, and also produces books, films and videos. Focus also responds to as many as 55,000 letters a week, offers professional counseling and referrals to a network of 1,500 therapists, and addresses public policy and cultural issues.

Efficiently managing an operation of this size requires a powerful computer, and Focus on the Family relies on iSeries servers for these tasks. The organization runs J.D. Edwards World and OneWorld packages, in coexistence mode, on a four-way iSeries Model 830. The company also has a two-way AS/400 Model 730, on which it runs its business intelligence software.

In 2002, Focus on the Family embarked on an in-house CRM initiative, called "Constituent Connections: Relationships for Eternity." The goal of this CRM project was to analyze the millions of customer and transaction records housed in the JDE software to get a better view of its constituent base, so that it could serve them better.

At the time, Focus on the Family was using the ShowCase product suite from business intelligence software vendor SPSS to analyze the needs of its constituents. The organization's IT department was heavily into SQL and the ShowCase Query tool to pull information from the JDE database. However, Bill Smith, Focus on the Family's data warehouse manager, had qualms about using ShowCase Query with the new Constituent Connections CRM application.

"Before I got here, the group had built a repository with ShowCase, and I commend them," he says. "I don't want to slam the ShowCase warehouse builder. It is good for what we use it for: for transformation, for presenting information. But we were using it in the middle, for loading data. I realized that wasn't going to cut it [with Constituent Connections]."

The drawback in using the SQL-based ShowCase tool in the middle--for organizing the JDE data and loading it into a datamart--was that it didn't allow developers to easily change the business rules, and the business rules were not easily changed, because the tool didn't provide meta data, Smith says. "If I wanted to come up with a flag that shows that these two people are married, it would be like looking for a needle in a haystack" with the ShowCase tool, Smith says. "Going to Constituent Connections, I saw how clumsy it was, because it was missing meta data."

Instead of getting lost in a sea of SQL statements, with the new CRM application, Smith wanted the logic handled with native RPG. But where would he find such a tool?

As luck would have it, a company that develops native OS/400 software for loading and managing data warehouses, Coglin Mill, was making cold calls that day, and Smith was on its list. "They knew we had a '400, and we started talking. It was good timing," he says. "They had a lot of the answers I wanted to hear."

Coglin Mill develops a business intelligence product called RODIN (pronounced row-dan; the software is named after the great 19th Century French artist Auguste Rodin, best known for his famous sculpture The Thinker). RODIN is an OS/400 extract, transform, and load (ETL) tool on steroids. Actually, the product is a code generator that outputs compiled RPG programs for creating and maintaining data warehouses and data marts. Along those lines, it handles much of the underlying grunt work of developing a data warehousing framework, allowing developers to concentrate on their data and their business rules, without getting involved in SQL.

Smith knew he was headed in the right direction. "When I saw that RODIN supported metadata, I said, 'Hey, now I've got a compass that I can use to navigate.' That [metadata support] was the real issue." He wrote a proposal for using RODIN as the basis for the Constituent Connnections CRM project, and to his surprise, management approved it, even though it was submitted as a non-budgeted item. "I was thrilled," Smith says.

After getting approval to use RODIN for the CRM project, Smith brought in a Coglin Mill expert from the company's base in Rochester, Minnesota, for a few days of training at Focus on the Family's Colorado Springs, Colorado, campus. Smith and three programmers learned the RODIN product and its commands, then set out building their first RPG programs.

Five months later, Smith and his programmers had designed 70 data sets and more than 60 RPG applications. Smith could have opted to build a single monolithic application with 30-plus business rules built into it, but for maintenance purposes, he decided a collection of smaller RODIN applications was better. He likened the RODIN development process to building a new screen from an existing one used as a template. "The neat thing is, you don't have to worry about all the basic stuff," he says. "It's like screen display files, you can reuse it. I only had to worry about five things, if I wanted it to do only five things."

Here's how the system works: Once a week, Focus on the Family extracts JDE transactions from the iSeries Model 830 and loads them onto the AS/400 Model 730 for analysis. It uses RODIN for data cleansing and for loading the data into the RODIN repository, where the data is summarized for loading into ShowCase Essbase data marts. More than 12 million constituent records and 22 million transactions are housed in the system, dating back to 1996.

RODIN works behind the scenes to pump the data to the Essbase cubes and ShowCase Query tool. "Marketing wants to do more analysis and wants to add outside help," Smith says. "Yet we still need to feed them the information. They don't want to see millions of transactions. RODIN summarizes the data and always balances to the underlying detail, which makes for more thorough analysis by the third parties." Currently, 30 to 35 people in two departments of the organization have access to five Essbase multidimensional data "cubes" for analysis, and power users in the marketing department still use ShowCase Query to get detail-level data.

Most of Focus on the Family's power users will not see much difference with RODIN managing the data. But for 20 percent of them, RODIN does provide some additional capability they otherwise wouldn't have. It's capability to quickly scan large transaction sets, segment the data, and apply new business rules has given Focus on the Family new views of its constituents, and the capability to develop more efficient target marketing campaigns as a result.

For example, RODIN has enabled the ministry to improve tracking of the various "life stages" of its constituents. Young couples that might have been looking for information on how to raise a two-year-old child in the 1970s could be grandparents now, and they might be receptive to hearing about Focus on the Family resources for grandparents. To this end, the organization is using the software to develop targeted marketing campaigns for young families and single mothers. That level of segmentation of demographic information would not have been possible without RODIN, Smith says.

Data cleanliness is another benefit of RODIN. One of the RPG programs Focus on the Family generated with RODIN illustrates the strength of the software's automated reject management capability. In loading records from the JDE system, Smith is able to program RODIN to automatically set aside any record that has an error in it, such as a missing item code. Then, when the problem has been resolved, RODIN automatically processes the record and loads it into the repository. "There was just no way we could have done that with the ShowCase tool," Smith says. "In many ways, our data quality has just soared as a result of the RODIN tool."

More data cubes and data marts are in the works for Focus on the Family, which is a non-profit, tax-exempt organization. The organization has shown that business intelligence technology doesn't necessarily have to be employed in the pursuit of profit, but can be used to help people, too.