Rethink reporting: Page 3

(Page 3 of 3)

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

Comment and Contribute


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