Implemention Technology
Business Intelligence
Warehouse Engine
  Infomix Red Brick Warehouse

We've all heard amazing data warehousing success stories where companies received a 400 percent return on investment within three years. We've also all heard stories where companies worked on a warehousing project for two years, invested millions of dollars and labeled the project a failure when the finished product didn't meet expectations. At ALLTEL, we went through the warehouse development process and put our system into production, only to find out it did not completely or efficiently fulfill the intended purpose. The temptation was great to keep the system limping along, shoring it up when necessary. Instead, we analyzed our problems, sought out new technology and learned from our mistakes to produce a new highly effective warehouse.

ALLTEL is a customer-focused information technology company that provides wire line and wireless communications and information services. We had three goals for our initial project: first, to reduce the cost of ad hoc reporting done on a third-party billing vendor's mainframe; second, to reduce report turnaround time by giving ad hoc report requestors a simple environment to do their own queries in their own time frame rather than submitting requests to dedicated staff; and third, to allow for analysis of customer calling patterns, churn and easy identification of the profitable and nonprofitable customers through access to more than three months of data.

Process ­ The First Time Around

Many data warehousing projects fail because they're technology driven rather than business driven. To avoid that mistake, we hired consultants to do the database design. They interviewed between 70 and 100 end users of the current ad hoc reporting process. From these interviews came a requirements matrix which they then translated into a database design. Many products were evaluated for all stages of the data warehousing process. An HP T500 was the machine selected. We purchased PRISM Warehouse Manager to generate COBOL source code and target SQL code as well as to serve as a meta data management tool. BusinessObjects was selected as the desktop DSS tool, and a traditional RDBMS was chosen for the data warehouse DBMS which was then constructed in normalized form.

Results ­ The First Time Around

From the start of design it took six months to get a successful load of our operational data into the warehouse database. We weren't really too bothered by that statistic, but somewhat more troubling was the additional six months it took to get change control procedures in place to manage our loads. There were 17 loads per month for two million customers, each taking four to six hours to load summary billing and usage information.

Unfortunately, we tried to deliver everything too soon. We wound up having to drastically scale back the number of tables that would be delivered to get anything delivered at all. Worse yet, we moved quality assurance testing to the end of the process, instead of doing it from the beginning. Referential integrity was not enforced, and we had many problems with the data quality. These problems were partly attributed to some of our technology choices not living up to our original expectations. In short, everything took longer than we advertised because we tried to do too much too soon, and the data we had loaded was not completely correct. Both cost us user confidence ­ something that took a long time to restore.

Although end users were relatively pleased with the BusinessObjects desktop tool, ad hoc query performance was totally unacceptable, ranging anywhere from an hour to "it never came back" against our 80GB database. These performance numbers were directly attributable to the normalized DBMS design. We tried indexing and disk striping at all levels to improve performance but got more administrative overhead and negligible performance gain.

Needless to say, our users were very disappointed with the product and, therefore, preferred to use the mainframe ad hoc reporting process. Fortunately, instead of merely labeling the project a failure, we were allowed to learn from our mistakes and fix the problems.

Process ­ The Second Time

This time we used our own people who had experience with the current ad hoc environment and our current DBMS system. Again, we began with end-user interviews, talking to 20 to 40 users. We compared this to a list of strategic business questions from a company business plan. After researching data warehousing, we recognized that the major flaw was our normalized database design. We sent the design staff to a dimensional modeling class.

In addition, we revisited our DBMS vendor decision due to performance and administration issues. A decision was made to move toward a more "data-warehouse centric" DBMS that was simpler to manage, and Informix Red Brick Warehouse was purchased. Finally, our problem analysis showed that PRISM's warehouse manager was simply not meeting our needs, so a decision was made to replace it with transformation and load utilities coded by the billing vendor. We upgraded our machine for more performance gains as well.

Results - The Second Time

This "re-invention" of our warehouse took only four months from start of the database design phase to the first successful data load. Loads, which were taking four to six hours, now took 30 minutes. This allowed us to add 30 daily loads to the 17 monthly loads we were previously performing. We even added a lower level of detail for billing, which increased the size of that data 10 times. Most of the ad hoc queries now ran in minutes instead of hours, and even our most complex queries were completed in a reasonable amount of time ­ no more going off into "never-never land"! In addition we were able to solve the data quality, granularity and referential integrity problems encountered in the first implementation. In summation, our users were happy, our problems were solved and we had a useful tool to help us run our business more effectively.

Lessons Learned

In many ways we were blessed ­ not all organizations get the second chance that we were given. So if you've got to get it right the first time, our advice is outlined below. You may have already heard many of these recommendations, but it just goes to show that the rules of thumb you hear about actually do apply in the real world:

  • Work personally with your end users to develop requirements (do interviews, form focus teams, etc.). Don't depend on outside contractors to know your business better than you do.
  • Let technology people make technology decisions, and let end users select toolsets (within reason).
  • Design the database for fastest information retrieval performance, i.e., dimensional modeling. Remember that the purpose of the warehouse is to deliver information efficiently and accurately.
  • Meta data management is critical for users to keep a handle on what's in the warehouse.
  • Deliver the warehouse in small pieces with the most important piece first, i.e., try to get that "quick win." Make sure your support staff understands and can use each piece that's delivered.
  • Find one technical consulting company that services all your products and use them exclusively to build loyalty and reduce consulting time.
  • Plan to feed the results of your warehouse analysis back to your operational systems to enhance your business.
  • Plan to re-evaluate and possibly overhaul your warehouse every 15 to 24 months.

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

Don't have an account? Register for Free Unlimited Access