Datamation content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.
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.
|
|
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.”
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.”
|
|
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.
Counterpoint
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.
No small task
|
The $1 Million solution Training users to abandon paper for online reporst can save a huge amount of money. Between 60% and 80% of paper reports can be eliminated if the systems and users are properly tuned, according to Gartner Group studies. The consulting firm estimates an average cost of six cents per page for hard copy reports. (Current print costs per month) Source:Gartner Group |
Now, Allegiance continues to refine reporting through a two-pronged approach: constantly monitoring report-query activity within the data warehouse in order to identify bottlenecks, and interacting with users to optimize their data-gathering methods and activities. The latter is no small task, since about 2,600 workers now compile reports on the system.
For monitoring, the company uses Oracle Enterprise Manager, a database management tool that lets Allegiance take snapshots of activities inside the system. That helps it find long-running queries and track the steps the query is following. “We found that there were a lot of bad queries,” Ciekutis notes. For example, workers with a regional interest might run a report pulling national data and then extract their regional information manually when reading the report. Others might construct queries in ways that required the Business Objects reporting system to perform many lookups. “They were doing a lot of table scans, so a query that could run in five minutes was taking three hours,” Ciekutis notes.
Allegiance plans to refine its report-workload monitoring and is looking at tools from Pinecone Systems Inc., of Englewood, Colo., that will allow it to continuously monitor query activities. But even constant, as opposed to hourly monitoring, still requires the legwork of user interaction. For example, a user may request information using a name rather than a code — substituting the region name “west” for the database code, “WE.” That will cause the system to refer back to the name repeatedly. On the other hand, if the user enters a code instead of the full name, the system takes advantage of indices Allegiance has attached to each code within the data warehouse. With the help of the indices, the system is able to more efficiently find information related to the code.
But the legwork is worth it. As an indication of the program’s success so far, Allegiance now measures about 30 to 40 report queries running at any point in time, compared with 100 simultaneous queries last year. It’s not that the system is producing fewer reports now; users are simply getting on and off faster, and not clogging the system.
“Once you enlighten users on ways to do things more efficiently, they’re all for it, because they’re usually waiting on data, too,” Ciekutis says. “The issue is getting a handle on it when it happens, so you can get in touch with people.”
Microsoft’s hybrid approach
One company that seems to be swimming against the current is Microsoft Corp., of Redmond, Wash. It is installing a reporting system that will pull records directly from its R/3 system. But it will do so only on a limited and selective basis for data that can’t be found in the company’s data warehouse.
Called Mars, the data warehouse has been in operation since 1995, compiling information from an instance of Microsoft’s SAP financial module. Mars takes the same summary records that the financial system automatically prepares for handing off to other R/3 modules. “We could extract all the records into a big data warehouse, but if you don’t need all those records, that’s not very efficient,” relates Eric Hanson, Microsoft’s senior manager of SAP Finance.
Because the information in the data warehouse is limited, report writing is faster. Still, the arrangement meets only about 80% of the company’s field reporting needs, the manager estimates. To let its personnel access more data, the software giant is installing a drill-down system based on reporting software from Information Builders. When a user wants more detailed information than Mars provides, his query is automatically converted into SAP’s ABAP programming language and transferred to the operational R/3 system. For instance, a manager reviewing monthly costs might want to see who is responsible for a particular expense item. There, the system retrieves only the in-depth data not available in Mars.
By working in ABAP programming code, the query mechanism operates in the application layer of SAP. “That lets you control how many people are using it,” explains Hanson. Currently, Information Builders is programmed to admit only six reporting users at a time into Microsoft’s operational system. The seventh gets bumped from the system. But Microsoft expects to install upcoming software from Information Builders that will automatically queues excess reports. “We are very interested in protecting our SAP system,” says Hanson. “We don’t want any more than six concurrent users from our reporting system.”
But at the same time, the company recognizes the importance of giving those users prompt and efficient access to ERP data. “Reporting is a very important business need,” Hanson concurs.
Jeffrey Zygmont is a freelance writer who focuses on business and technology. He can be reached at jzygmont@concentric.net.
