The Power of PrototypingWith small steps focused on gaining and retaining user buy-in, a data warehouse can arrive and thrive
By Chris Lawson Continued from Page 1 DigitalThink also improved performance by disallowing reports with missing or unreasonably broad search criteria (typically accidental reports). Acceptable ranges were established for each search parameter, and noncompliant report submissions were rejected.
"Lumpy" data. In the DigitalThink database, certain customers account for a large proportion of the data. This disparity presented a performance-tuning dilemma. For example, the time to run the most critical report ranged from just a few seconds for most customers to a few minutes for certain customers. How could performance be optimized given this huge variance? One solution was to use an Oracle 8i database feature called "materialized views." Each night, aggregate data is stored in this materialized view — but only for certain customers. Then, whenever these customers submit a report, the Oracle optimizer is clever enough to rewrite the database query to use the special aggregate data. This sophisticated mechanism is called "Query Rewrite." Although aggregate, data could be calculated for all customers after each nightly update, the time to calculate the aggregates would be out of proportion to the benefit received. That is, in order to achieve a 60-second performance gain for just a few customers, an extra 30 minutes of processing might be required every night. TEAMWORKThe critical position of project architect was filled very early in the project. The architect was one of the few team members with extensive data warehouse design experience. He provided the road map on what big steps needed to get done and designed the database star schema. The product manager, who knew the customers' needs very well, identified critical reports to receive top priority. Then, the designers, report-writers, and DBA made it their mission to produce very appealing, rapid reports. A senior designer created all the database queries and acted as the liaison to the Oracle DBA. The designer concentrated on the functionality of the SQL rather than report layout. Once debugged and optimized, the SQL was relayed to a small group of report specialists, who converted the SQL code into a viable Actuate design, including graphs, tables, and so on. Several team members were dedicated to ensuring proper user interface, including useful help menus. Toward this end, developers attended a usability seminar. Also, external customers were invited to preview prototype reports and provide feedback to the project team. An Oracle tuning specialist worked with the designers and report writers to optimize the response time and identify database performance bottlenecks. This frequently meant identifying poorly performing SQL code and suggesting corrections or adding new indexes. HIGH AVAILABILITYThe reporting system needed to be available 2437. This requirement demanded a method of keeping the database available while new data is loaded. The E.piphany tool provided a handy solution to this problem with its duplicate set of database tables. Each table set contains all the data necessary to produce any report. Thus, while the users are querying set A, new data is copied into set B. At the conclusion of the nightly data update, new reports are directed to the most recent set of data. Of course, this feature requires more storage, but the high-availability benefit was deemed a good trade-off. The database and reporting servers were configured for high availability using Veritas Cluster Server. In this system, each server is part of a two-node cluster, so that upon failure of one node, the entire application and database automatically switches to the alternate node. MISSION CRITICALThis project was considered a mission-critical project because the existing legacy system was continually requiring attention, both technical and managerial. There was broad consensus that these problems with the legacy system had to be resolved. Management was especially concerned with poor performance experienced by the firm's largest customers; continuing with the legacy system would have endangered the firm's critical customer base. A successful data warehouse and reporting system was also seen as a crucial factor in maintaining customer satisfaction. In the e-learning business, this is known as providing a complete "learning experience." A customer's perception of this experience determines how the business thrives or falters. Therefore, it was not a question of if the company would build a new data warehouse, but rather when it would be built. The DigitalThink Reporting and Analysis system was successful — but not because of gurus or sophisticated development techniques. Rather, a simple prototyping strategy provided a positive impetus and laid a good groundwork for the more complicated work to follow. Trust and cooperation among team members was a key to the project's success. Although project members were generally not data warehouse gurus, most had significant experience in their field. More important, however, team members trusted their peers and welcomed the expertise of their colleagues. In short, the formula for success was the product of a cooperative blend of competent personnel, small early successes, and big publicity. Chris Lawson [chris_lawson@yahoo.com] consults in the San Francisco Bay Area, where he specializes in Oracle database administration and performance tuning. RESOURCESActuate Corp.: www.actuate.com Data Junction Corp.: www.datajunction.com E.piphany Inc.: www.epiphany.com Oracle: www.oracle.com Sybase Inc.: www.sybase.com
|
 |
Most Popular This Week
IE Weekly Newsletter
Subscribe to the newsletter
| |
| |||||||||||||||||||||||||||||||
