Challenge
An International Payment Processor engaged DecisivEdge to assist with the implementation of an electronic payment reconciliation process for one of the largest hotel chains in the world. The project entailed creating an ETL process by evaluating input files and their corresponding output files to reverse engineer the business logic to generate the same aggregated information as the current process. The output files are consumed by a 3rd-Party on behalf of the hotel chain. There was also a need to match and reconcile transactions for specific corporate customers for purposes of expense management. All of these files were related to transactions initiated at hotel properties in the United States. The services included the following:
- Project Management: Coordinate activities across multiple companies and teams to identify and document requirements, conduct project meetings, and provide periodic updates to key Project Stakeholders.
- Analysis: Analyze the current file specs and sample files to understand content, timing of data, and lookup required to map & aggregate input data to output data.
- Define: Define specifications for database structure, ETL processes, integration with other data sets, validation and logging.
- Build: Develop the required structures and processes to provide an exact replica of known output files along with the appropriate error and process logging leveraging Microsoft SQL Server and SSIS for ETL.
- Test: Create and execute test plans and document results.
- Deploy: Work with the Operations teams to deploy the solution across multiple environments and verify configuration in each environment.
- Document: Create documentation to support the hand-off of the source code and operational process to the appropriate teams.
Solution
DecisivEdge assembled a team of Business Analysts, Project Managers, Technical Architects, Developers, and QA Leads and Testers with the appropriate experience in Financial Services and Payment Processing to perform the services required by the client.
The solution included several ETL packages to monitor input folders for new files at specific times of the day, import the files, cleanse, validate and aggregate the data, and export files to multiple folder locations based on final destination. For security purposes, the input files processed contained an alternate key in place of any credit card numbers.
The business logic to aggregate the information was based on a point in time hierarchy and designated start and end times that identify each transaction date. Multiple dates were included in the input file and the specifications were not sufficient to determine which date was used to group transactions, so manual analysis of transactions from the input file were compared to the aggregated records to determine the aggregation process.
In addition to reverse engineering the aggregation logic, the DecisivEdge team also drove the following project activities:
- Designed a database to capture input, export and reference data as well as information about steps, warning and errors encountered during processing.
- Designed ETL processes to complete all steps of two types of reconciliations.
- Performed testing and validation of generated output files to the known sample output files.
- Prepared and assisted with deployment to pre-Production and Production environments.
- Provided documentation for the entire process including the logic required to cleanse and aggregate the data.
- Integrated process logging including error handling into a central operational repository to allow the Operations team to monitor and manage the process.
Results
The DecisivEdge team completed all required services within expected time frames as outlined by the client. After completing validation of the output files generated by the DecisivEdge process compared to the sample files provided, the process was deployed to a production environment where it was executed in parallel with the current process. After a short time, the process was deemed ready for production and fully deployed.
The client subsequently engaged DecisivEdge to complete a similar process for its properties in Europe as well as an additional engagement to build a custom operations monitoring portal that could be configured for each production process and to accept input via an API.