NSGB has successfully migrated all its Core Banking Solutions (Retail & Corporate) from separate Legacy Applications to one International Integrated System (Delta). eT3’s role in more than 2 years projects includes Testing & User Acceptance, Specific & Trade Finance Data Migration, and Reporting Solutions

Introduction

    During the last three years National Société Générale Bank (NSGB), as the leading foreign bank in Egypt, has completely renewed every single aspect of its Information Technology Platforms and Applications, with the main objective of equipping the bank with the latest State of the Art in the IT arena, and to standardize all Société Générale overseas branches (BHFM) with a standard IT infrastructure. In this aspect, NSGB has switched all its Core Banking Solutions (Retail & Corporate) from separate Legacy Applications to one International Integrated System (DELTA). This project has been called the “HARPE” project (Harmonisation des Applications Retail Pour l’Etranger)

   Usually, when a huge organization such as NSGB decides to switch all its core applications, two implementation methods can be adopted. The first, a phase by phase method, divides the project into phases which are implemented serially until the end of project. Alternatively, in a “Big Bang” approach,  all the old data are migrated and automatically transferred to the new platform and uploaded into the new applications over a weekend.  On the next business day, the new targeted applications will be up and running, supporting the entire bank.  NSGB, to achieve HARPE project, decided to apply the second method (Everything will Go Live on the cut-off date).

    The success of the “Big Bang” approach depends, among other factors, on the Professionalism  and Accuracy of Testing and Data Migration Procedures.  NSGB Management then decided to utilize eT3 expertise to accomplish these two tasks

eT3 Role in NSGB HARPE Project

   eT3’s  participation in more than 2 years projects, started mid-year 2005, and completed with the successful Go Live of NSGB Applications (10 June, 2007) and merged MIBank applications (11 Nov., 2007), included    :

      Testing and User Acceptance

     Data Migration

§  Specific Data Migration

§  Trade Finance Applications Migration

     Reporting Solutions (On-going)

     Satellite Application (Hewalty)

       Miscellaneous Services

Testing and User Acceptance

      It was imperative, within the “Big Bang” approach, adopted by NSGB, to fully test the new targeted applications by specialized business users, and get their acceptance before the go live date. The problem with this approach is the fact that selected business lines users are usually experts in their area of specialization but without any IT or Testing applications experience. HARPE project’s management utilized eT3’s expertise in user’s acceptance testing field to accomplish this task.

    eT3 team, composed of three Banking Consultants, were the first consultants joining Harpe project. Meanwhile Harpe Project management has decided that “Test Director” from Mercury will be used as a main testing tool.  Therefore, eT3 consultants started accordingly to parameterize “Test Director” and acted, all over the project life, as Administrator of this testing tool.

    The following chart is summarizing eT3 adopted methodology in order to accomplish their mission:  

 

  

  

  

  

  

  

  

  

  

  

  

       Perimeter Definition

     The beginning of testing, sub-project managed by eT3 started by not only defining all applications required to be implemented at NSGB within HARPE project scope, but also by identifying each function which should be tested in each module of each applications.

    Identified Functions were grouped by Module, accordingly business line, and modules by application. eT3 used it’s long experience in banking industry to categorize each function using one of the following categories;

·         Mandatory Tests, meaning that this function will be a hundred percent covered with more than one test case.

·         Nice to Test, sixty percent of the function will be tested, while if we still have time the remaining part will be tested.

·         No Tests, function is not applicable in NSGB. No need to spend time to test it.

Testing sub-project perimeter was defined, testing scope and objective too.

Business Lines Initiation

    As the project was divided into twenty seven business line team covering every single business aspect of the bank, and, as previously stated before, the fact that none of the users composing these business line teams has a previous experience in application testing, eT3 developed a complete course to initiate the users on how to design test cases, explaining test categories such as passing and non passing tests, regression tests etc …, how to build test scenarios, how to execute a test campaign, in addition to explain in details the detected defects handling flow. The following chart represents the defects handling flow;

 

Test Director Familiarization and usage was also occupying a major part of Business Line initiation, the philosophy behind parameterize the tool the way it has been done was explained in full details.

    When a business line team was launched, eT3 was spending one complete day to initiate the members of this business line. At the end of the initiation class, users were receiving a complete user guide, also developed by eT3, in addition to the perimeter of tests for this specific business line.

Unit Tests Campaigns

    Each business line team composing HARPE project team, was completely assisted by one of eT3 consultant, together the business line users and eT3 consultant designed and developed series of test cases covering specifically all required function of this business line.

    Banking experience of eT3 consultants played a major role defining the several cases to be tested inside each business line in a way leading to never leaving even a small business case to the hazard and without being tested.

    The following picture is illustrating a very simple example of the mechanism used to develop test cases;

 

Three execution waves were performed in Unit Test phase as following;

·         First execution to test every function of the business line.

·         Second execution to test only the corrections added to the application(s) to fix defects detected during the previous run.

·         Third execution to test overall the module before going live.

Integration Tests Campaigns

    Integration tests campaigns started immediately after the first unit tests campaigns, the purpose of these campaigns was to perform inter module, and inter applications tests. 

    Once again           banking experience of eT3 consultants played a major role defining the several cases to be tested and building testing scenarios to be run between the several business lines. eT3 also was guaranteeing synchronization between the different involved parties, without banking experience these type of tests could not be achieved.

Two executions campaigns were managed and conducted by eT3 consultants;

·         The first one was planned to be executed after successfully executing the major unit tests campaign.

·         The second one took place after successfully executed the first migration campaign, and was executed on migrated live data.

Migration Tests Campaigns

The main objective of migration test campaign was set as following:- 

·         Mass control. Controlling the totals on old application(s), and on the targeted new one(s) e.g. 250,000,000 customers are existing on the old application, now on the new one only 218,000,000 are existing.

·         Business tests. To take a sample data and check if the balance is ok, limits have been migrated properly, transaction history is there etc….

·         Insurance Tests. To be sure that we can add, update, new data on migrated records.

 Three migration test campaigns were executed all of them during week-ends.

General Rehearsal 

A general rehearsal is a complete live simulation of what will happen on the go-live date. It is always starting with migration while every user in the bank including bank’s branches will test connectivity and control and test migrated data, and it will always end by comparing financial results on both the two environments.

eT3 has successfully planned and managed three general rehearsals before going live.

Test Director Management

      eT3 team was responsible for managing Test Director, as main testing tool used in HARPE,  during all over the project life, this responsibility was including the following activities; 

1.    Test Director Parameterization.

This Task was completely performed by eT3 consultants with the main objective of customizing the tool to fit HARPE project requirements. The bulk of parameters was performed at the beginning of the project but according to the several conditions occurring during the project life, eT3 was there to adapt the tool.

2.    Building Test Requirement.

    Testing Requirements was identified immediately with the project management and has been developed into Test Director upon finishing the Perimeter Definition Phase.

3.    Managing Test Plans Design.

In order to save design time and taking into consideration the fact that all business lines users were much more familiar with excel than Test Director eT3 consultants designed a template excel sheet permitting the users to write their developed test plans, the excel sheet was imported automatically into Test Director using an automated process designed by eT3.

4.    Managing Test Labs

Test cases were transferred from Test Plan part  to the Test Lab eT3 consultants were building test sets where test cases were logically and chronologically attached (e.g. the case 2 will not be executed if case 1 was not successfully executed while case 3 and 4 will be executed anyhow). Sets were ready for execution in Test Director’s lab by the business lines users.

5.    Managing Defects.

This was an on going effort performed by eT3 resources, several times a day detected defects were reviewed with business lines users and application provider’s resident resources.

6.    Build and generates follow-up and statistical reports and graphs.  

eT3 consultants automated an extraction process from Test Directory to a specially designed Excel Sheets in order to produce daily, weekly and monthly reports and graphics showing the testing sub-project progress to HARPE Management and the Project Steering Committee.

 

DATA MIGRATION

    The first data migration was for NSGB old applications (Go Live 10 June, 2007).  The second for MIBank (merged with NSGB) old applications (Go Live 11 Nov., 2007).   Before actual Go Live dates, several Go Live Rehearsals where scheduled to ensure the timely and correct results of the Migration Procedures

    From the beginning, it was necessary for eT3 specialists to use different methodology and procedures in the Migration of Trade Finance Applications, than other Specific Data Migration due to different platforms and environment.

Specific Data Migration

1.   NSGB Applications

o   From COBOL files to Delta on Oracle 9i DB (both on UNIX)

o   Included the following :

1.1 The Migration of NSGB IT enhancements of the MIE Standard Core Banking application; which represents  more than 100 % of the Original Standard Functions.

Standard + Specific  modules include:

-   Customers

-   Accounts

-   Loans

-   Deposits

-   Historical data

1.2 Also the migration of additional complete modules that were specific to NSGB (Egypt). The migration procedures here included data analysis and updating the mapping document, as a guide for development and testing.

Additional Specific Modules include :

-   Referential (Joint Customers, Third Party Customers, Local and Foreign Banks)

-   Certificates

-   Letters of Guarantee (LGs)

-   Back Office Applications.

1.3 Extract data from NSGB files for Certificates, Deposits, Loans, Commitment accounts and excel sheets and relate them together to build Guarantors and Commitments data for Delta Collateral System.

2.   Merged MIBank Applications

o   From Legacy AS400 Applications to Delta on Oracle 9i DB

o   Included the following :

2.1 Building Collateral Data from migrated database data.

2.2 Signatures Migration, different from other migrated modules in Extraction and Loading due to Image Handling.  eT3 conversion programs here generate XML files containing extracted data. Arabic Comment fields were also appropriately converted

2.3 Handling Arabic data  in ALL the migrated modules that include Arabic texts.

Trade Finance Migration

Special Procedures were adopted by eT3 specialists in the Specific Migration of Trade Finance Applications due to the following : 

o   Neither source code nor documentation were available for main applications; (VISION TF on Oracle V7) covering all Letters of Credit and Export Documentary Collections. VISION had been referred to by NSGB as a “Black Box”

o   Import Documentary Collections were available either on EXCEL sheets (before year 2006) or a simple Oracle Application (Docu-Station). Both contained minimal or no validations on existing data causing large duplications, rejections …

The Trade Finance migration procedures include

o  Comprehension of non-documented legacy applications

o  Setting migration methodology

o  Data Analysis

o  Developing Mapping Tables (Field and Code mapping)

o  Designing Migration Environment

o  Migration Programs Specifications, Programming & Testing

        Special routines for handling duplications, empty fields …

o  Data Cleaning Procedures

o  Developing Post Migration Go Live Scripts (GLS)

o  Developing Data Mass Control Queries (Quantity Control)

o  Developing Consistency Check Scripts (Customer Commitment & Cover Accounts + Bank Commitment) to compare totals of Migrated LCs with balances migrated by other Procedures

o  Functional & Technical  Documentation

Extracted Data Migrated to Delta Tables includes:

o  Letter of Credit Files, related Payments, Documents, File Situation & Discrepancies …

o  Documentary Collections Files with related Documents, Bills, File Situation ….

o  Correspondent / Foreign Banks

o  Account Numbers linked to Customer & GL Accounts

o  Generation of Customer Transit Current Accounts (for non-existing customer accounts)

o  Generation of  Contra Accounts non-existing in MIE Legacy system (Customers’ and Banks’ Accounts). These are for non-confirmed export LCs and  non-avalized DCs

o  Links of Correspondent Banks’ to their Contra Accounts

o  Post Finance Loans

 

Reporting Solutions

This on-going service, provides NSGB with Reporting Solutions that are built on latest Technologies in the field

Scope of Services

      Discussing User Requirements

      Reports Analysis.

      Development & Testing

Business Scope

      Types of Reports Developed

           - Central Bank of Egypt (CBE) Reports

           - NSGB Internal Reports (that are not included as Standard Delta)

           - Société Générale Affiliate (BHFM) Reports.

      Reports cover the following Departments:

-       Call Centre

-       Cash Centre

-       Certificate of Deposit

-       Cheques under Collection

-       Cheques on Us

-       Compliance

-       Corporate Loans

-       Documentary Collections

-       Financial

-       Front Office

-       IB Management

-       IBG Loans

-       Letters of Credit

-       Letters of Guarantee

-       Market

-       Professional Offering

-       RISK

-       Time Deposits

-       Transfers and Foreign  Currency Cheques

-       Statistics

  

Environment and Tools

• Reports Developed use:

-       Crystal Reports XI Release 2

-       Excel VBA Macros.

• Reports are published on Business objects server.
• Users are viewing reports through InfoView.

Satellite Application (Hewalty)

“Hewalty” is a Web application developed to handle incoming cash transfers from the Bank Correspondents via SWIFT gateway to one of the following:- 

-          The Account; if beneficiary is a Bank Customer (Core Banking Integration)

-          A Smart Pre-Paid Card (Card Management System Integration).

-          Cash at any Bank’s Branch teller station (Core Banking Integration).

-          Door To Door Cash Delivery (New Application Development + Integration with Core Banking).

In addition, the solution is completely integrated with the Bank SWIFT gateway in order to capture all incoming financial messages, transform them to XML, store them on a DB, and re-route the non-cash transfer messages to their final appropriate destinations, while dealing directly with the cash ones.

The solution also includes Migration of old MIBank Cash Transfers from the old platform (IBM – AS400) to the new one (based in MicroSoft) 

Environments and Tools

·          Developed using MS VB.net

·          Running on

-     MS SQL Server  2005 Data Base

-          Biz Talk middle ware

-          MS A4SWIFT

 

Miscellaneous Services

Formatting / Calculations in Customer Account Statement

Objective:

    The objective of this user requirement is to format a text file of Customer Account Statements data for input to Xerox Printing

Description :

      Reading a flat file generated from Delta Data Base tables, using VB6 program, to extract the Customer and Account data and reformatting it in a text file that can be read by Xerox for direct Account Statement Printing.

      Calculating the Balance after each Transaction

      Splitting the resulting file into pages