Rethink reporting: Page 2

(Page 2 of 3)

Mirror systems

Structural Dynamics Research Corp. (SDRC), of Milford, Ohio, decided it couldn't take that risk. It opted instead for a duplicate reporting instance of its SAP R/3 system. A supplier of computer-aided design and engineering applications, SDRC supports international operations on a single instance of R/3, which it brought online in early 1997. The system operates around the clock, supporting users on three continents. "This is an enterprise system to the max," says Craig Berry, enterprise systems manager. Accordingly, "we have a very narrow window for batch reporting."

"If someone sets the selection parameters of a query injudiciously, they could cause the system to slow down during critical periods," notes Craig Berry, enterprise systems manager at SDRC.

What's more, the company's growing reporting demands threatened the system's primary mission, transaction processing. For example, SDRC recently purchased an additional 75 R/3 licenses, but 50 of them are read-only. "That's 50 more people who are interested in extracting data," says Berry. "If someone sets the selection parameters of a query injudiciously, they're going to make the system think for a while. That could cause the system to slow down during critical periods."

To prevent that, SDRC is currently installing EMC storage hardware and software to create two mirror instances of its R/3 data, in addition to the live, transactional data on the main system. One of the instances will be used primarily for backup, and the other is primarily to support reporting. Each instance uses its own server from Sun Microsystems, Inc., of Palo Alto, Calif., running the Unix operating system. Software from Information Builders is being used as the end-user query writer.


Although the system isn't yet fully operational, Berry expresses satisfaction with the arrangement so far. But at least one consultant advises against this concept of running a separate, or mirror reporting instance of an ERP system. It may take the load off the operational system, but "you'll get more users on your system with a data warehouse than you will on an operational system serving as a data warehouse," says Howard Dresner, vice president and research director at Gartner Group, a consultancy in Stamford, Conn. That's because the mirror ERP system isn't optimized to support reporting the way a data warehouse is. It still contains normalized tables and operational code that can get in the way. On the other hand, says Dresner, "a warehouse takes the data and flattens them out, so that more of the information you need to conduct analysis are in a single table. That's a far more streamlined, high-performance process." Therefore, "if you can get 10 active users on a (mirrored-instance) server, maybe you'll get 100 users on a data-warehouse server," he estimates.

Thus, although the price tag for setting up a data warehouse may be "extreme," as Dresner terms it, "the outcome for the less tangible advantages, like user performance and user productivity, are enhanced quite a bit."

But at SDRC, Berry says expectations for a data warehouse can run too high, particularly when a single warehouse is expected to serve a variety of goals, like archiving, answering common report queries, and supporting the in-depth analysis and reporting of OLAP applications. In fact, if SDRC does construct a data warehouse in the future, it won't necessarily replace the mirrored, reporting instance of R/3 that's now being implemented, Berry says. Instead, it will most likely be used to support executive decision-making.

Fine-tuning the warehouse

Even with a data warehouse specially designed to support reporting needs, a company isn't necessarily in the clear. Allegiance Healthcare recognized that. By polling colleagues at meetings and conferences, the company learned that reporting volume can overwhelm even a well-designed data warehouse.

Therefore, it planned from the start to mount a persistent attack on reporting inefficiencies, to keep its warehouse from suffering the same problems an ERP system would have handling the burden of corporate information demands. "We had a pretty good handle on our reporting needs going in, but everything we had heard said that once people started using our data warehouse, things would change," attests Ciekutis.

The company's data warehouse came on line at the start of 1997, supported on two Model 8400 Alpha Servers running Unix from Digital Equipment Corp., of Maynard, Mass. (now Compaq Computer Corp., of Houston, Texas). The ERP-sourced data resides in databases from Oracle. At the same time, the company started using the financial module of R/3. With reporting limited to financial analysis, the warehouse handled the volume without any hitches.

But the system started to feel the load from report queries as Allegiance's distribution centers came online with SAP's sales and distribution and materials management modules -- a phased implementation spanning from October 1997 to the end of 1998. "As more and more people started using the warehouse, we had too many people at month's end running reports," Ciekutis says.

One of the early remedies was to process reports in batches when possible, instead of letting users start queries at will. That helps balance the load on the system. "We were able to identify about 40% of the reports that people didn't really need to see right away," says Ciekutis. Those now get reported through Business Objects Document Agent, a report scheduling and distribution tool from Business Objects. The tool holds report queries until scheduled times and then distributes the results to multiple users. That helps prevent people from running different queries to get the same information.

To handle its month-end crunch, Allegiance also surveyed users to identify high-priority reports. "IT owns those and queues them up before we open our data warehouse for general use," Ciekutis explains.

Page 2 of 3

Previous Page
1 2 3
Next Page

Comment and Contribute


(Maximum characters: 1200). You have characters left.



IT Management Daily
Don't miss an article. Subscribe to our newsletter below.

By submitting your information, you agree that datamation.com may send you Datamation offers via email, phone and text message, as well as email offers about other products and services that Datamation believes may be of interest to you. Datamation will process your information in accordance with the Quinstreet Privacy Policy.