Spool-Explorer & EZ-Pickin's Case Study

Prospecting for Data and Report Distribution Is EZ-Pickin's with BCD

The iSeries is a rich depository of information waiting to be mined to unveil money-making facts. How users get to that information can be a thorny problem, however. Should you spend tens of thousands of dollars in software costs, hardware upgrades, and manpower to create a data warehouse, or is there a rich vein of data just waiting for prospectors to glean?

FASTNet, the moniker of the accounting information systems section of the finance department of the Metropolitan Government of Nashville and Davidson County, Tennessee, pans for information riches using two business intelligence (BI) tools marketed by Business Computer Design Int'l, Inc. (BCD): Spool-Explorer and EZ-Pickin's. FASTNet had two major problems it needed to address. First, it needed a simple way to allow more than 200 users to print iSeries reports on their local PC printers, rather than having to use centrally located iSeries printers. Second, FASTNet wanted to provide new reports to management without having to involve the IS staff. Spool-Explorer solved the first problem. Both products used in tandem solved the second.

Spool-Explorer

Spool-Explorer is a Windows application that provides users with instant access to iSeries spool files, using a familiar windows interface. The user can connect to multiple iSeries and filter spool file lists by user, job, output queue, spool file name or type, or user data. Reports may be downloaded by the user to their PC, or sent via email as a text file, as an Adobe PDF document, to a web site as HTML, or as rich text format (RTF). In addition using EZ-Pickin's along with Spool-Explorer, data from reports can also be extracted and sent as email attachments in MS Excel, Access and a multitude of other formats.

According to Joe Palmer, IS analyst at FASTNet, one of the greatest benefits of using Spool-Explorer is that his users can easily print iSeries reports on their desktop printers, rather than having to send them to a centralized iSeries printer in some other area in the county. Nashville and Davidson County have more than 550 users, and more than 200 of them have the Spool-Explorer client software installed and running on their PCs.

Because Spool-Explorer downloads iSeries spool files directly to the PC, you can print any report directly as if it were a PC file. Spool-Explorer includes flexible print options, such as embedding the correct blank lines to format the report as it would appear on an iSeries printer; font control; and options to cut and paste segments of reports into a 'print area' to save paper when you want to cull relevant information from a huge report. Another benefit for FASTNet is that the IS staff doesn't have to get involved whenever a user wants to print a report.

Dealing with Security Issues
FASTNet used the tried-and-true method of reading the trade magazines to find a BI solution. The department requested information on Spool-Explorer and EZ-Pickin's and received a CD with full working versions of both software products on it. Dan Pursley, director of IS at FASTNet, liked what he saw, but was concerned about security issues with Spool-Explorer. Because the software allows the user to select almost any spool file, (by using the criteria of user ID, job, spool file name, forms type, and user data) he was concerned that users would now have free access to certain reports with sensitive information. "We needed to have users see only their files, unless we say otherwise," Pursley said. He expressed his concern to BCD. In response, the team responsible for developing Spool-Explorer added a security feature that allows Pursley to define on the iSeries access restrictions by user to the spool file selection options in the PC user interface. These restrictions control whether or not a user is allowed to select lists of spool files by any of the available criteria, or whether a default value for items such as output queue or user name is automatically provided for the user. With this feature added, Pursley is able to centrally manage access to spool files from client PCs. This feature is now included in the current version of Spool-Explorer.

Mining Reports
While Spool-Explorer is useful as a standalone tool, it also shines as a portal to EZ-Pickin's. You can think of EZ-Pickin's as Excel on steroids. You construct a model that forms a mask over a report layout to reorganize or summarize data from the report. This makes it easy to extract data that may be scattered over multiple line formats and organize it into a uniform table. EZ-Pickin's includes powerful filtering functions to subset report information, in addition to the ability to create extensions such as new calculations, sorting, and 3-D graphing. After you have constructed the model, you can apply it repeatedly to different instances of the same report. From here, the data can be exported to almost any other PC application.

Joe Cimino, assistant fiscal manager for the Nashville Police Department, uses Spool-Explorer and EZ-Pickin's to produce summary financials in Microsoft PowerPoint presentations for upper management. He uses Spool-Explorer's seamless interface to EZ-Pickin's to transform his data. First, he selects a list of spool files to work with. Next, he simply clicks on the file he wants, and then clicks the EZ-Pickin's launch button. (See Figure 1 for an example of a list of spool files in Spool-Explorer). EZ-Pickin's is then automatically loaded, and Cimino sees the data transformed by a previously built EZ-Pickin's model. The original source of the reports comes from a J.D. Edwards application. In this case, it is an operating budget of year-to-date (YTD) revenues and expenses. From this, EZ-Pickin's produces a formatted table (Figure 3), which Cimino then directly exports to Excel or PowerPoint. Figure 4 shows the results of loading the formatted table into a spreadsheet.

For Cimino, the EZ-Pickin's solution works well as opposed to other options that involve file transfers and labor-intensive cut-and-paste operations. In addition, this approach reduces the opportunity for human error. Prior to acquiring Spool-Explorer and EZ-Pickin's Cimino was limited to using the text import function of Excel to get information into spreadsheets. This meant repeating the same steps every time he wanted to extract data from similar reports. In contrast, an EZ-Pickin's model only needs to be created once. It can be used repeatedly with different instances of the report it was built on.

Every Day vs. Three Times a Year
When asked what the major benefit of using Spool-Explorer and EZ-Pickin's has been, Cimino said, "The products have allowed us to provide timely information to management. We used to have a mainframe. Each time we wanted a report, we had to contact the division of accounts to get them to generate it. Generally, it was not what we wanted the first time, so we had to go back and request it again, with revisions.

At that time, I asked for special reports three times a year at most. Now, using Spool-Explorer and EZ-Pickin's, I run custom reports every day, and I provide a financial presentation every month to management.

"So far, the reports I'm producing are fundamental, such as budgets, YTD expenditures and budget balances. In the future, I'd like to be able to expand these to include projection modeling and more statistical information. I'm also looking to shift from using PowerPoint to Access as the final target for the information," Cimino said.

There are other areas of the products that Cimino has not fully explored yet. While the level of automation used to produce reports has helped him greatly, Cimino would like to streamline the process even further. Cimino said, "I'd also like to learn how to use the auto-scripting feature of EZ-Pickin's so I can automatically transform the report with no manual intervention." EZ-Pickin's auto scripting feature lets the user run a series of Windows tasks at once, using a batch file.

Using products such as Spool-Explorer and EZ-Pickin's has freed up IT staff resources at the Metropolitan Government of Nashville and Davidson County and empowered users like Cimino to unlock the riches of information buried deep in the belly of their iSeries, without having to build another infrastructure on top of existing applications. In addition, the cost of this solution is inexpensive compared to typical data warehouse solutions.

EZ-Pickin's is sold as either; Standalone copies or Concurrent seats. To receive a PRICE QUOTE on EZ-Pickin's, Spool-Explorer and/or Catapult please call us at 630-986-0800 or email at sales@bcdsoftware.com

Click to go to these product pages: EZ-Pickin's, Spool-Explorer or Catapult.