Rethink reporting

Reporting can kill an ERP system if it isn't designed right. Invest the time and money upfront in a data warehouse if you want to get the information you need out later.
(Page 1 of 3)

When Allegiance Healthcare Corp. began implementing an R/3 enterprise resource planning system from SAP AG of Walldorf, Germany in early 1997, it simultaneously embarked on a data warehouse project. No, the $21 billion supplier of medical products isn't crazy - it just recognized early on that a data warehouse was the only way to meet its reporting needs without compromising ERP performance.

Allegiance's Mark Ciekutis says it's tough to get what you need from R/3
Of course, the centerpiece of Allegiance's $100 million Horizon Project is the R/3 system itself. A main goal of the system is to speed order fulfillment by providing well-integrated links within the company's sales, distribution and materials management processes. But at the time of its ERP implementation, "a lot of companies were learning that it is tough to get what you need out of R/3," for reporting purposes, says Mark Ciekutis, data warehouse manager for Allegiance, of McGaw, Ill. As a very high transaction-volume user - at 300,000 transactions a day -- Allegiance couldn't risk hanging the system and preventing it from processing orders.

So that's why the company set aside $10 million of its ERP budget for a data warehouse. "One of the ways to lower our risk was to move all the reporting out of the OLTP environment into a separate environment," Ciekutis explains.

Allegiance was unusual in its foresight. Typically, it takes a while for users to form the same conclusion. The usual lifecycle goes like this: The user first accepts the reporting tools and templates provided by the ERP vendor. The honeymoon ends shortly after implementation, when workers become comfortable enough with a new system to begin making demands, says Mimi Spier, marketing manager of packaged applications and data warehousing at Business Objects S.A., a supplier of software for compiling and managing reports, headquartered in Paris and San Jose, Calif. "There comes a time when there are too many end users and too many queries made against operational data," she says. At that point, she adds, "ad-hoc querying starts to affect system performance."

"If you're in a high-transaction environment, you don't want to be banging on those (ERP) databases with any kind of reporting tools," agrees Sam Clark, an analyst at Meta Group, an information technology adviser in Stamford, Conn. He explains that reporting tools generally work by disguising queries as transactions within an ERP system. "They make the operational system think there's a transaction going on, when actually all you're doing is asking for information," Clark states. The increased activity can bog down both the reporting activities and the operational transactions.

The conflict shows up at times like 9 a.m. Monday mornings, when a lot of staffers kick off the week by running reports. In a lot of companies, "the people doing input hate that time of day, because the system is so slow," observes Tim Lang, ERP business development manager at Seagate Software, of Vancouver, British Columbia, Canada. Seagate sells a suite of reporting management tools for gathering information from ERP systems and from data warehouses linked to ERP systems.

A data warehouse is one way to avoid this bottleneck. These systems take the data compiled during ERP transactions off-line and store it in a repository that's specially structured to be friendly to report queries. Even ERP vendors are getting in on the action. For example, last fall SAP brought out its own data warehouse option for R/3, the Business Information Warehouse.

But companies are also using other approaches to protect system performance from an overload of report queries. The least sophisticated approach is to employ reporting tools that help manage and control the reporting load against an operational ERP system. A more advanced method is to take the query data offline, not with a data warehouse but with a separate, duplicate instance of the production system. EMC Corp., a storage system vendor in Marlborough, Mass., supports that approach with its Time Finder software package, which creates a duplicate database on a duplicate server for such purposes as backup and query support.

Let the tools do the work

Whatever the method for getting the data off-line, consensus holds that a company shouldn't rely for long on the usual default practice: letting users query the production ERP system itself, sometimes even making tweaks and adjustments to accommodate them. That's because ERP systems are optimized for rapid, efficient transactional processing, not for compiling report data.

One way to get around those limitations is to use reporting tools that feature load-balancing capabilities. Meta's Clark cites products from Information Builders Inc., in New York. That vendor's SNAPpack line for SAP R/3, for instance, converts report queries to ABAP code, which is SAP's own programming language. In native format, queries can be interpreted by R/3's Basis module - its primary logic engine -- and assigned for processing according to the load on the overall system. In effect, it can assign a higher priority to operational transactions, holding report queries until they can run without affecting overall system performance. Information Builders sells similar products for ERP suites from J. D. Edwards & Co., of Denver, and PeopleSoft Inc., of Pleasanton, Calif.

Another Information Builder's product, called Focus, uses a knowledge base to determine how long a query will need to run. If the runtime exceeds pre-set parameters, Focus sends the query back for restructuring. "It's designed to prevent what we call runaway queries, because they retrieve far too many records, access far too many fields, and use far too many CPU cycles," says Jan Brown, vice president of Information Builders' Integrated Solutions Division.

Even so, the structure of data within ERP systems generally prevents efficient reporting. Data tables are very normalized -- containing minimal cross-references, so that transactions move rapidly. "They get rid of any redundant data, so if you need additional data, you have to look it up in an additional table," explains Brown. "But that means to get reports, you have to hit up against many different tables. That eats a lot of CPU cycles."

Page 1 of 3

1 2 3
Next Page

Comment and Contribute


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