Executives and employees at a major housing authority required regular access to mainframe data from the comfort of their Windows notebooks and desktops. The mainframe data reports delivered to the end-users for manipulating in Excel or Access also had to be relevant to the individual departments of the organization, so spreadsheet columns not only had to be formatted and scrubbed, they had to be department-specific. Finally, the organization also wanted to remove IT from the process entirely by giving end-users the ability to generate up-to-date mainframe reports for use in Excel or Access whenever they wanted.
Sounds reasonable enough, but to anyone who has ever tried to access mainframe legacy data in Windows, the process can be frustrating and time-consuming, as well as prone to error. One large organization that used Arbutus technology presented the following challenges.
Previously, when mainframe reports for use in Excel or Access were required at the housing authority, a mainframe report would be generated to a spool file, downloaded to a PC, converted to ASCII, then imported into Excel or Access. These steps didn't take long, but executives and employees at the housing authority couldn't use the data just yet. One final step remained, and it was this one that always caused the biggest problem, and created the longest delays:
All the data had to be aligned, formatted and scrubbed.
Due to its technical nature, this last step was handled on a request basis by the IT department. It always seemed to take far longer than expected, creating a significant bottleneck, and then, by the time the data was finally delivered to the end-user, it was often stale or useless. Finally, the reports contained columns of information that would be only relevant to certain departments, which created security issues, or additional work at the very least.
The problem was clear: what this organization needed was a solution that would help them:
- Access mainframe data with Excel or Access
- Automate processes to minimize manual intervention
- Eliminate unneeded data from spreadsheets
- Remove IT from the process
Arbutus offered LegacyLink™, a solution that could directly read mainframe reports, select the relevant data, and insert it into an Excel spreadsheet. This allowed "on demand" data access with no manual intervention and no IT support.
End-users could open a spreadsheet to access mainframe data and create customized reports for Excel or Access, with only the information they required, whenever they wanted. Also, because it provided read-only access to mainframe data, it offered the security demanded by IT and management.
LegacyLink™ was first used to read the spool file generated from the mainframe, select the relevant data, and insert it into the spreadsheet. Working with Arbutus consultants, the housing authority 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 repurpose the report cross-departmentally. With Arbutus, it was very easy to select which data elements to include in each spreadsheet.
The second step was to create an Excel spreadsheet for each report that included a data link to the table that would automatically refresh the data from the mainframe whenever the spreadsheet was loaded. Next, each spreadsheet was reviewed to ensure that relevant data - and nothing more - was included, then sign-off was obtained from the end-users.
With Arbutus LegacyLink™, the organization was able to empower its executives and employees with relevant, on-demand mainframe reports accessible for immediate use on Windows notebooks and desktops. Further, following the initial set-up, involvement by the IT department was practically eliminated, while overall workflow increased dramatically.
Phase Two of the project at the housing authority will see the initial step of generating a mainframe report to a spool file completely eliminated. Although LegacyLink™ could have been used from the start, the customer decided to maintain their use of the spool file during Phase One, as it required minimal disruption to their existing processes. With LegacyLink™ fully implemented, end-users will be able to open a spreadsheet that will automatically apply the business rules on the mainframe, then load the appropriate data directly from the source files, without actually touching the data on the mainframe, thereby keeping it secure.
Visit the Connect product page to find out more.