Milestone 8

TECHNICAL DESIGN REPORT

Contents:

A. Executive Summary

B. Introduction

C. Project Proposal

D. Narrative for Normalized ERD

E. Narrative for Modified DFDs

F. Narrative for Design Units

G. Implementation & Post-Implementation

H. Appendices

EXECUTIVE SUMMARY

Planning the changeover of information systems to accept other foreign currencies is not just a matter of dealing with the practical issues and consequences. For the National Bank there will be strategy level issues that warrant attention, issues that will fundamentally affect the way the enterprise conducts its affairs. Changes in the business environment, such as the automation of the foreign currencies, can change the functionality that is expected from information systems. In this document discussion of the strategy level issues is confined to a brief description of how they may influence the preparation of the information systems. The strategic considerations should be taken into account before modifying those systems for the use of these currencies.

This changeover is often compared to the year 2000 problem, probably because both are related to information systems. Systems that use dates, directly or indirectly, can be affected by the year 2000 problem. This means that hardware and software that is not used to process financial information can still be affected by the year 2000 problem.

Since the National Bank's financial information system also uses dates, they must be reviewed for problems associated with both the changeovers: the foreign currencies and the year 2000. We encourage the National Bank to combine preparation for both issues in order to avoid modifying the same information system twice. However, ISM (a subsidiary of IBM Canada) is the company that is responsible for maintaining and managing the National Bank's infrastructure. Therefore, ISM would be already addressing the Year 2000 problem.

There are good reasons for managing the subsequent phases of the projects separately, because:

a. Both projects are fundamentally different. The year 2000 problem is largely a technical problem in information systems. Whereas the proposed changeover requires additional functionality in information systems, and also affects the bank in other areas

b. The combined project could be of unprecedented size and complexity, and may become difficult to manage

c. Deadlines for our project and year 2000 projects are different (a delay in this IT project should not lead to a delay in the year 2000 fix).


In planning for the foreign currencies changeover, the firm needs to address the following five aspects that are essential for a successful changeover:

a.  Define the scope and nature of the changeover problem - Describing the existing systems and determining the quality of those systems is extremely important for determining the changeover strategy.

b.  Determine priorities and strategy - In setting priorities the importance of the information systems and their complexity must both be taken into account. Furthermore, enterprises need to decide which changeover strategy is most appropriate - a 'big bang' changeover, a gradual changeover, or implementation of new information systems.

c.  Dependency on third party software - Enterprises that rely on third party software have little control over the functionality, timing, quality, and price of the new software. Therefore, they must reduce the associated risks to acceptable levels.

d.  Technical preparation deals with the functional and technical problems related to the system changeover. Solutions to some of these problems will be suggested, however, their effectiveness will largely depend on the particular business environment of the enterprise and the way existing information systems are implemented.

All National Bank branches are inter-linked through their information system and they must decide together how and when these systems are changed over to the new system.

Many financial information systems store the same information more than once. Conversion of historical data requires that all instances of the same data are converted in exactly the same way, otherwise unpredictable results and errors may occur. It is impossible to design a utility that can automatically convert the ledger to accept all foreign currencies. Therefore, the preferred option will often be to rebuild the ledger, rather than trying to convert the existing manual ledger electronically.

INTRODUCTION

As an active social and economic force for the past 140 years, the National Bank of Canada is today the sixth largest chartered bank in Canada with assets of $69 billion. The National Bank's head offices are located in Montreal and its Canadian network boasts 642 branches. In all, more than 16,600 employees are responsible for the tradition of excellence that has come to characterize National Bank of Canada.

The National Bank is also active on the international scene (United States, Europe, Asia and Latin America), pursuing its mission of providing the highest quality products and services to a diversified clientele that includes individuals and companies both large and small.

On the road to becoming the dynamic financial institution it is today, the National Bank has taken a series of important steps. The following is a brief overview of the landmark dates in our past:

Converting the system to accepting foreign currencies will have a profound impact on the way the National Bank operates. It will be one of the most important changes in the economic environment of the bank in the next few years. The changeover also has a number of practical consequences for the day-to-day operations of the enterprise. One of those practical consequences is that information systems need to be ready for the use of major foreign currencies. Many people would agree that it is important to be well prepared and that careful planning is essential.

PROJECT PROPOSAL

The scope of the system is to focus on the exchange bureau of one specific branch of the National Bank. Only subsequently to proving its effectiveness will we be employing a bank-wide implementation. Inclusion of the parent departments will be essential to cement the effectiveness of the newly proposed business process. The business processes we will be concentrating on are the International and Exchange bureau.

DEFINITION OF COMPONENTS OF THE IS

Data - We will be implementing the database approach to act as the data stores (e.g., Buy/Sell rate data store transforms to Buy/Sell database table, Ledger data store transforms to Ledger database tables)

Inputs - Rates that go to the International subsystem from the REUTER’s system (e.g., automated electronically with the flexibility of administrative intervention) replaces the hardcopy of the rate sheets

Transaction sheet for deposit/withdrawal (in the form of tangible items treated differently from cash through the Center)

Outputs - Compare ledger to tangible items and a transaction report that is batched daily is generated that documents that there is balance between ledger and tangible items.

Processes - International Subsystem processes (DFD level 1 proposed)

          Exchange Bureau Subsystem processes (DFD level 2 proposed)

Interface - Conversion from test base to GUI interface

WORK PLAN

The project addresses the strategic aspects of the foreign currency system changeover that is important to National Bank. Secondly, a definition of the scope of changeover issues in information systems is presented, and a comparison is made with the year 2000 problem. The section concludes with an analysis of critical success factors.

The following terms will be used with the meanings as defined:

• Information systems - The combination of software and hardware that is used by an enterprise for recording, processing and storing information

• Hardware - The actual physical computer equipment that is used

• Software - The application software that actually deals with the financial information

• Financial information system - Information system that deals with financial information such as invoices, payments, accounts, etc.

• Historical financial information - The term historical financial information is used to indicate all financial information that has been recorded in a financial information system prior to the introduction of the new system. Such financial information can relate to past, present, or future transactions (payments received, accounts receivable, and orders placed), assets and liabilities (inventory levels and mortgages), or financial information on others than the enterprise itself (a bank's creditworthiness files on borrowers). Storing financial information is necessary in order to account for the past, manage the present, and plan for the future.

• General ledger - The general ledger is a financial information system that is used for bookkeeping. It is used to record the assets, liabilities, income and expenses of an enterprise. A subledger is an information system that records transactions in detail. Usually subledgers are linked to the general ledger, which records only part of the details or a summary of the details.

IT environment

The bank also needs to consider the quality, structure, and organization of its IT environment when planning for the introduction of the proposed system. In many cases the existing IT infrastructure of banks is far from perfect, for example:

a. Enterprise A has acquired several other enterprises over the past ten years. Consequently, many different information systems are in use that perform more or less the same tasks. Preparing for such a changeover could mean that this enterprise has to make similar modifications to, for example, five or six information systems that perform the same tasks. Such a duplication of efforts can be both inefficient and expensive.

b. Enterprise B has been using an information system for the past 15 years. As a result of the changing business environment and new functionality demands by users, the system is now becoming obsolete. Normally, the system would be used for an additional four or five years before replacement. However, the prospect of a potentially expensive upgrade to deal with the changeover might be reason to opt for an early replacement.

These examples show that a modification of all existing information systems may well not be an automatic choice as the most attractive changeover strategy. In both examples there is an underlying need to improve the IT environment; the system changeover (in combination with the year 2000 problem) may trigger banks to make more fundamental changes in their IT environments.

These scenarios are certainly not meant as an exhaustive list of possible strategic considerations, which may be fundamental to the future of the bank. However, it is clear that strategic considerations should be taken into account when modifying the present information systems for the incorporation of other foreign currencies.

ISSUES IN INFORMATION SYSTEMS

To prepare an information systems for the introduction of the proposed system it is important to establish which information systems by the changes. The basic rule is that:

From a practical and organizational point of view it may be convenient to deal with the foreign currencies and the year 2000 problem at the same time. However, there is a fundamental distinction between the two:

1. Solving the year 2000 problems means ensuring that the information systems will continue to do what they always did, that is calculate the dates correctly. This makes the year 2000 problem largely a technical problem that needs technical solutions. The users of the information systems must be involved in the process of identifying the potential problems. Furthermore, management support for year 2000 projects is important to ensure that these projects receive sufficient attention from the users and that the business consequences of non-compliance are properly understood. However, most of the actual work on remedying the year 2000 problem will have to be done by the information technology (IT) department of the enterprise
2. Solving the system changeover issues will in many cases mean that functionality must be added to the financial information systems. Adding functionality means that the users must be heavily involved in firstly identifying problem areas and then in finding the solutions for these problems. It also requires management to take decisions on the functionality to be added, because these decisions will affect the daily operations of the enterprise during the transitional period. Therefore, the National Bank's inefficiencies are not primarily IT problems.

Before starting the actual work on planning the changeover, the firm must have a good overview of its financial information systems. This step is rather technical because it requires the enterprise to do the following:

• Make a list of information systems that deal with financial information. Increasingly enterprises are discovering that they use more information systems than they previously realized. Often branches of larger enterprises have implemented information systems that provide functionality beyond that of the standard software used by the enterprise. Many systems, ranging from cash registers to spreadsheets, are often conveniently forgotten when discussing information systems. Underestimating the number of systems that need to be changed can cause enormous problems once the changeover strategy has been determined.
• Document technical details as to the way information systems are implemented:

• Was the software purchased from a third party, or was it custom designed for the enterprise

• What programming language or technique was used to implement the systems. Well documented systems that are programmed in a modern programming language using an underlying (relational) database management system are easiest to modify. However, systems that

1. are programmed using utilities no longer used by the enterprise,
2. are programmed in a spreadsheet,
3. that use a unique data format,
4. that are poorly documented, and
5. that are programmed by an employee who has left the company, may be particularly difficult to modify.

• Is the hardware affected by the changeover. Some software problems are also hardware problems because the software has been embedded into systems as firmware (software that is encoded in hardware, ROM). Such software can only be upgraded by replacing the hardware (for example cash registers).

• Determine dependencies between systems. Dependencies through links or interfaces determine to a large extent whether a gradual approach towards the changeover is feasible. It is important to realize that not all dependencies are internal (within the enterprise), but that external dependencies (for example with customers or suppliers) might exist as well. Additionally, dependencies between systems greatly increase the complexity of the changeover problems.

Conducting a systems inventory has clear commonalties with the approach taken in dealing with the year 2000 problem and if tackled in this light the synergies can be exploited to help reduce overall costs and efforts.

Internal controls

Introducing new information systems or modifying existing ones is not a routine process in most enterprises. This in combination with the proposed system changeover, which by definition is not routine, leads to certain risks for which the bank needs to be prepared:

• Testing new systems - Changes in the information systems should be adequately tested before these systems are put into operation.

• Data conversion - Sufficient controls should be in place to avoid fraudulent insertion, modification, or deletion of financial information during the conversion of data files. Substantial risks exist, especially, where end users need to revise and correct the data files manually.

• Suspense or clearing accounts - A well known procedure for dealing with exceptional or unsettled transactions and differences is to put them in a suspense or clearing account by giving them a special code. The risk exists that the conversion will give rise to a substantial number of these "suspense" or "clearing" items. These items need to be analyzed and resolved in time to ensure that no irregularities have occurred, particularly where users tend for convenience to classify other differences as conversion differences in the new system.

• Unusual transactions - Many users rely on their experience to recognize and investigate unusual amounts and transactions. After the system conversion it will take some time before they regain these skills.

• Access privileges - Some users (administrative users) may need additional access rights to the information systems in order to resolve changeover differences. Granting too much access rights to a single user could expose the enterprise to risks.

B.  Narrative for Modified DFDs

(Please refer to Appendix B)

(Please refer to Appendix B)

Update System

In this system, the market rate is manually keyed into the system when reacting to market volatility. At this point the system calculates the spread (MS VB-OL) and then updates this into the DB2/MS Access databases to be readily available for the exchange bureau’s transaction system.

Transaction Register System

The transaction system uses an MS VB application to calculate the commission charges and to update the appropriate ledgers residing in the database. The rate is utilized in processing all the calculation operations based on the currency that is requested.

Monitor System

The system takes place at the administrative level and its purpose is to keep track of the fund level at each exchange bureau. The CSR initiates the process with a phone order and this triggers the MONITOR FUNDS MS VB module to access real-time ledger balances of each exchange bureau residing in the database(s). This enables the administrator to verify and select the ideal candidate to transfer funds with.

Report System

This system involves the generation of reports. The reports generated are TRANSACTIONS RECEIPTS and BALANCING REPORTS. The receipt reports are generated based on every execution of transactions and these receipts are considered as turn-around output. Later these receipts are used to conduct daily balancing operations. This is the balancing of tangible items against ledger totals. In the event that the CSR cannot come to balance the totals, the CSR will attempt to adjust by identifying the errors. Finally, a balance report is generated and sent to the appropriate bureaus.

IMPLEMENTATION & POST-IMPLEMENTATION

The National Bank Exchange Bureau employs client/server architecture. At the bank level, it uses centralized architecture and at the back office level, the National Bank adheres to the client/server configuration. Inasmuch as the Exchange Bureau features commercial servers at each branch, it abides by this architecture.

Wire speed and average throughput rate: There are different ways to measure performance in shared media networks such as Ethernet. The wire speed is the maximum data transmission rate for the network. For Ethernet, it is 10 Mbps, and equals the speed of the data transmission once the node gets onto the network. But since most networks have more than one user, the average throughput is significantly less for each user. One way to calculate average throughput is to take the wire speed number and dividing it by the number of stations that share the network. For example, for the National Bank to have a fully-loaded Ethernet segment that has 200 users contending for the same 10-Mbps channel, it delivers only 10 Mbps/200 = 0.05 Mbps of average throughput per station. This assumes that all stations generate the same traffic all the time, which in reality is never the case. In addition, this assumes 100 percent efficiency and a 100 percent loading capability, neither of which applies to Ethernet. Due to the chaotic nature of shared Ethernet networks, the average utilization is limited to less than 40 percent, which  means that the average throughput rate is limited to about 4 Mbps for a large Ethernet network. For switched networks, the average throughput can approach wire speed, since a switched network does not share the random nature of a shared network.

• How does one join up the new section of the network with the existing part?
• What happens to the replaced equipment? Can you use it somewhere else in the LAN?

Before the decision to purchase high-speed networking hardware, the systems builders must be sure to understand the capabilities of the National Bank’s cabling plant. The banks owner’s investment in cable, conduits, wiring closets, patch panels, and so on can exceed the cost of the networking hardware itself. It is necessary to make sure that the new high-speed networking gear can run on the bank’s existing wiring. If that's not the case, potentially huge additional costs for upgrading the cabling plant to accommodate the new LAN standard must be factored in. It is recommended that the network managers keep as much of their existing equipment as possible since it's working, proven, and already paid for (i.e., a sunk cost).

Data Conversion:

1. End User dialogs – this component will allow the user to submit existing or new conversion requests. A screen will allow the user to specify the source of the data, the specific data required and the target table (in this case DB2/MS-Access) where the conversion data is to be loaded. The data source is identified by the selection of items found in the list of table and views. The source data and target data do not have to reside on the system where the dialog is executed.
2. Administrative dialogs – this component is used to define and maintain conversion requests, to create views and other data descriptions, and to build information about the DB2/MS-Access tables accessible by the individual users.
3. Relational Extract Manager – is the component that performs the actual data conversion when the source is a DB2/MS-Access database. Thus, it extracts data from DB2/MS-Acess tables in accordance with SQL extract requests generated by the End User dialogs. The request can invoke a batch job to load the data into a DB2/MS-Access table using applicable load utility.
4. User Input Manager – maintains data descriptions and conversion requests for non-relational files and databases. Data descriptions are stored in the data definition table.
5. Data Extract Manager – this component performs the actual data conversion when the source is something other than a DB2/MS-Access database. In other words, it will be the component that would have to deal with the flat-file structure of the old system. This component can process requests in an asynchronous or synchronous fashion. This component can also process request in a single pass and control the priority in which the request are processed.

Hardware Conversion Details:

Hardware Conversion Costs:

Information systems personnel must come up with a solution for moving data between incompatible systems in a reasonable timeline and assure that data integrity controls for the accuracy, completeness and timeliness (or synchronization) is properly maintained.

The risks to system conversion are enormous and include:

- Plummeting productivity during and after the conversion period

- Time and cost overruns because of unexpected problems and last-minute surprises

- Discontented bank customers and employees (if things go terribly wrong)

- Permanent loss of access to critical information and even to the documents themselves

- Hidden problems that continue to crop up and linger long after the conversion is over


The criteria for success include reduced risk, acceptable cost levels, a reasonable elapsed time for the data conversion process, easy setup and maintenance of data files, databases and backup/archival storage, and simplified migration of legacy systems data to and from the new enterprise applications. Interim solutions may be necessary, such as establishing bridges to transfer data from the system in the process of decommissioning to the other bank branche's data center, which will now absorb a greater workload.

System migration or conversions involve a strong project management approach in order to accomplish the objectives of merging or transferring data from one entity into the new platform. There are many issues involved, including negotiating revised licensing agreements with the successful vendor, as well as consummating new maintenance contracts and preparing Request for Proposals for additional hardware or support products. The administrative processes involve the following tasks:

- Assessment of user needs and the criticality of applications

- Impact analysis, scoping and constraints/dependencies, including the degree of binding between bank applications

- Staffing the business and technical teams

- Strategic planning for technical and business roll-out (to coincide with branch openings and closures)

- System selection and cost/performance analysis

- Project management and scheduling

- Methodologies and modeling techniques and tools

- User training and acceptance

- Computer security issues (including implementation of internal controls)

- Support and Help Desk

- Common terminology and standards
 

The technical issues are also challenging. One idea that may captivate IS management in both the  bank and exchange bureau level is to consider a whole new solution, such as client/server system. However, the luxury of time would preclude this option for most cases. On the other hand, network configurations may preclude an easy, immediate solution. However, "open systems" architecture is one of the directions that the new bank might be heading, but the term is one of the most widely used, but least understood IT buzzwords. In computing, open systems seemingly implies compliance with standards, industry adoption of those standards and wide customer acceptance of technology based on those standards. But, open systems actually have nothing specifically to do with computers. "Open systems" is simply a market dynamic that enables banks and companies to build mature commodity industries. Because technical design and implementation is not done in a vacuum, IT must listen to the bankers and conform to the overall merits for merger.

The technical issues involve a panoply of topics, including:

- Hardware and Software Installation

- Upgrades to Core Bank Applications and Compatibility Issues

- Transfer and Reorganization of Databases and Master Files

- Porting Interfaces

- Inventory, Cataloging and Indexing of Software Modules

- Prioritization of Bank Applications

- Timings, Performance Tuning and Capacity Planning Studies

- Use of Automated Tools (i.e., CASE)

- Testing – Test Plan and Scripts, Baseline, Regression, Metrics, Problem Resolution

- Milestones and Certification

- "Data scrubbing" Techniques and Translation

- Availability and Access to Current and Historical Data

- Change Management for Application Software and for the Transfer of Segments of Data from Large Volumes to the New System.


Enterprise data infrastructure tools may be necessary to handle special database problems. One of the dangers of "manual " application conversion projects is that they often result in a badly denormalized database. This alone will spell trouble for the "happy" customers already residing on the existing system. The manual approach, in short, does not provide a stable foundation for the future use of SQL based tool, or database enhancements. For example, the project team managing a general ledger or bank system conversion would need to devise or create a data conversion plan, an application test plan and a detailed task plan. This effort should include the documentation of current system interfaces and data flows. Additional responsibilities include: data testing, data mapping, process flow documentation and the coordination of affiliate conversion and data migration.

Some of the following technical tasks that need to be addressed are, as follows:

- Populate and refresh databases

- Build testing environments

- Data transformation

- Migration of large production databases

- Create data bridge programs for reengineered or new applications

- Customized design and implementation of new modules ("to fill in the gaps")

- Support major migration to new architectures such as client/server or more distributed.


Major migration plans should be customer-related and revenue-generator type applications. The business functions are more important than the actual movement of data to the new environment. That is, the mapping will be mechanical, but the business rules surrounding the use of the data has dire consequences. What happens to a customer’s "bounce protection" if the linked savings account is not moved over concurrently or if the customer is relying on a credit card advance which is weeks or months away from conversion?

Migration or conversion problems are many. They may involve organizational changes, including a tiered management structure with system administrators on one side and business experts on the other. The methodologies used help create a framework for analysis and design of the bank applications, and modeling assists in the implementation of the degisn and links the data structure to the business rules. The business plan will dictate what branches are to close or to be retained in the network. As a result, branches will undergo renovations, including a new teller platform, LAN/WAN interfaces, manuals and documentation, forms and signs. The need for careful coordination is essential to assure the proper transfer of the customer base into the new system in the phases in the project schedule. This is not an overnight event. Also, training and tool evaluations are areas that can hamper a smooth transition. One tactic that should raise the eyebrows of bank examiners is the "fix-on-failure" technique. Finding bugs or problems after they occur rather than before implementation may result in extensive reprocessing of the data as well as the generation of complaints.

The success criteria for migrating data over to the new platform can be measured beyond customer satisfaction. Some benefits include:

- Predictable costs, schedules, and results (actual versus plan)

- Expected translation of essential features

- Minimal down-time during the process

- Few or no irreversible mistakes

- Employee competence in the required new skills and behaviors.

 
Conclusion:

Bank examiners should be concerned about big merger projects. An opinion on whether a conversion project can be deemed "satisfactory" is subjective; nevertheless, the implementation and support issues described above may help to formulate such an opinion. It is easy to blame project failures on incompetent staff, uncooperative users, unsuitable commercial software, and insufficient planning. The real reason for project failures, which entail schedule delays, budget overruns and poor quality, involve the nature of big projects. These factors include: (1) the consequences of a major mistake buried or overlooked; (2) internal communications problems; (3) the demanding and changing business conditions and requirements; (4) the sense of frustration that engulf groups, and (5) staff turnover.

APPENDICES

I.  Appendix A:   Database Design

II.  Appendix B:   Data Flow Diagrams

III.  Appendix C:  Repository Forms

IV.  Appendix D:   Interface Design

V.  Appendix E:   Software Design

Back to main