Data Mining / Ad Hoc Reporting Solution for RMS Flat File Systems
Background / Client Challenge
AutoTrakk, LLC is an auto leasing company specializing in providing late model vehicle financing for credit challenged customers. AutoTrakk had been in operation for approximately 10 years utilizing a leased software application designed specifically for the leasing industry. The software application operates only on an Open VMS platform on HP Itanium servers owned by AutoTrakk.
AutoTrakk’s IT challenge evolved from their need to closely examine the customer application data of current and past customers to create a profile matrix which could be used to score future credit applications. These data points included income level, debt to income and payments to income ratios as well as length of residency and employment and employment type in addition to the standard customer payment history and credit scores.
The data from their leased program is stored in a non-relational RMS flat file system. Over the past decade AutoTrakk’s customer base had grown to include more than 25,000 individual records and $100 million in leases. Now with over 1,000,000 data points on file the nonrelational aspect of the existing data storage system presented an almost insurmountable challenge to any successful form of comparison and made “what if” scenario analysis virtually impossible.
The obvious solutions immediately available to AutoTrakk were to contract the original software application developer to write a multitude of scripts as needed to export data from the legacy system with each script containing specific inclusion/exclusion criteria. AutoTrakk would then need to contract with another firm to compare and analyze the data using multiple combinations of conditions. Only then could the resulting data be compared by knowledgeable internal staff to known past performance or industry standards.
Instead AutoTrakk contracted the SGCO Group to perform a complete system analysis with the eventual goal of making data stored within the existing VMS system available in a standardized format for quantitative and qualitative analysis by internal staff.
Accepting Legacy System Limitations
Accepting the reality that the overall cost as well as client’s investment in, and commitment to, the existing legacy system precluded the option of replacing the entire system SGCO researched the most cost effective alternatives. The goal clearly was to eventually house the data generated by the legacy system in a relational database which would provide the client with the flexibility necessary to analyze the data, in various combinations and conditions as it relates to past performance and industry standards without re-coding the leased application.
SGCO engineers used the legacy system’s documentation to meticulously map each of the hundreds of file tables and all the data locations within those table files for each data item stored by the legacy system. At the completion of this stage SGCO now had a data map of all data stored by the legacy system.
The next step was to create a data warehouse where the imported data would reside. SGCO & Young added a modestly priced DELL server to the client’s network and installed Microsoft SQL Server 2010 at the client’s request.
The task of duplicating the data in real time on the data warehouse’s SQL server was accomplished with the installation of a CONNX high-performance high-function data access engine that provides secure real-time read/ write access to the RMS files on Open VMS without disruption of existing systems or extensive programming. For this particular installation, the CONNX engine was configured to read data from the RMS files but not to write data to the RMS files thus preserving the integrity of the leased software application.
To further assist the client, SGCO personnel created template “views” within MS SQL for use by the client’s internal staff. These views included the fields and data relationships common to the most urgent research projects. SGCO personnel then provided the client’s staff with preliminary training in creating SQL queries utilizing MS SQL empowering the client with the ability to create custom data exports and web reports
Client Benefits resulting from the Completed Project
Upon completion of the project the Client was able to successfully query their new data warehouse for the most current information as well as nearly a decade of historical data which provided key insight into which socioeconomic indicators were relative to actual portfolio performance. These findings were used to re-structure the company’s scoring matrix which now takes into account more than 40 factors in scoring incoming lease applications.
In addition to accomplishing the initial goals, the completion of this project also empowered AutoTrakk to create a real-time report generation system delivered via the company intranet. The client is also currently in the process of creating intranet hosted Management Dashboards highlighting Key Performance Indicators (KPI) from real time data comparing them to previously unavailable historical figures.
Summary and Conclusions
By conducting extensive research on the client’s behalf SGCO was able to provide a solution that a) enabled the client to continue utilization of their legacy system, b) was most cost efficient of all possible scenarios c) provided a data export to fit the initial criteria d) provided a flexible system for future data analysis and e) provided a real-time data source for ongoing projects such as ad hoc reporting and web enabled management dashboards.
With this project SGCO successfully delivered a technical solution that not only met the client’s immediate needs but which also provided additional data export flexibility via a data warehouse thus empowering the client’s internal IT staff to implement information delivery systems that would not have been possible previously.
SGCO are specialists in the technical aspects of Interoperability which enables us to keep our clients’ needs and best interests as our paramount goal independent of platforms, operating systems, hardware or software based solutions.
About SGCO
SGCO an Information Technology consulting firm that delivers innovative, scalable business solutions to help our clients reduce costs, increase revenue and gain competitive advantage through technology. Since 1995 the SGCO Group has provided advice, consultancy, design, implementation, training, network support, internal/external business application solutions and digital media communications solutions to a wide range of companies and organizations in more than 12 countries including USA, Canada, United Kingdom, France, India, Australia, South Africa, Serbia, and the Netherlands.
