Case Study: Connect

The Problem

The customer, a large organization, had a variety of mainframe reports that certain departments wanted to access with Excel. The process they had put in place to achieve this was:

• Generate the mainframe report to a spool file
• Download the report to a PC, converting it to ASCII
• Import the file into Excel, and
• Manually modify and clean up the data so the imported data was formatted correctly

The first step was performed by the end user, and presented no difficulty. The second step was quite quick and straightforward, but required a technically proficient user. The third step was easy and required no particular expertise. The fourth step was the problem. It always required a significant amount of work to correct the alignment and formatting issues.

The result was too many man hours being spent preparing the data. This created a significant bottleneck. As well, not all the information in the report was needed, so the resulting spreadsheets were not ideal for the intended purpose. Due to the technical nature of the steps, the latter steps were handled on a request basis by the IT group for the end user departments, resulting in variable delays depending on workload.

What was needed was a means of getting access to the data in Excel, while minimizing the manual intervention, eliminating unnecessary information from the spreadsheets and removing IT from the process.

The Solution

The Arbutus technology was chosen after demonstrating the ability to directly read their mainframe reports, select the relevant data from them, and insert the data into a spreadsheet. This allowed “on demand” data access with no manual intervention.

The Process

After reviewing the requirements, the customer decided to maintain the initial spool file, as this was initiated by the end user, and this required the minimum amount of modification to their processes. Arbutus Connect would then be used to read the spool file, select the relevant data, and insert it into the spreadsheet.

Working with Arbutus technology specialists, the organization was able to create table definitions for each of the spooled report files. This process took very little time, since the Data Definition Wizard led them through each step in the process. The customer chose to define all of the report data, as this maximized the opportunities to re-purpose the report for other users. With the Arbutus technology it was trivial to select which data elements to include in each spreadsheet.

The second step was to create an Excel spreadsheet for each report and insert a data link to the table directly within the spreadsheet. Again, this was very straightforward using Excel’s supplied wizards. During this step the customer selected only those rows and columns from the spooled mainframe report file that were relevant to the department. The data link was configured so that it would automatically refresh the data from the mainframe whenever the spreadsheet was loaded.

The final step was to review the completed spreadsheet, to ensure that all relevant information had been included, and to get sign-off from the end users.

The whole process took only a few hours per report, after the initial learning curve.

Results

As a result of this process, the end user departments were able to achieve all of their goals. By merely loading a spreadsheet they were able to get exactly the information from the mainframe reports that they wanted, with no delays at all. Since most of the data specifications are just stored in the spreadsheet, they realized that they could easily update the information in the spreadsheets as their needs changed, without further involving IT.

In the next phase, the client will undertake to remove the initial process of generating a mainframe report to a spool file. Once this process is completed, opening a spreadsheet will automatically apply the business rules on the mainframe, and load the appropriate data directly from the source files. No more generation of the mainframe reports will be required.