Case Study: Medium Size Project, Telecommunications & Financial The Problem Our client's existing system was old, slow, cumbersome, and unreliable, and no longer met the client's needs. Our client needed to migrate their existing data and billing systems, upgrade their bespoke solution and add new functionality to their bespoke applications that handled new business requirements. The client had a website and internet enabled aspects that needed to be integrated and bridged with their billing system, to provide new automation and better IS infrastructure. The client wanted to keep the new billing system as a bespoke solution that was not web-enabled. The new system would need to automate more business logic, exhibit greater operational flexibility and produce more output documents. The new system would need to work with different data sources and integrate into automated office documents and spreadsheets. There would need to be a parallel rollout of the new system. The solution would need to cater to this. A critical part of the remit for this project was that the solution should be multi-user, secure, robust and scalable. The Solution This problem required a full end-to-end solution - from feasibility and gathering requirements, through design & development, to UAT, deployment and final handover. A multi-tier solution was built with Microsoft Visual Basic 6. It consisted of a software application, software components, the design & development of a SQL Server database, and output via Microsoft Word, Microsoft Excel, HTML, XML and Crystal Reports. Data was analysed and a new data model created. In tandem, we went through a business analysis phase, as a result of which new business processes were introduced and older ones were re-engineered or replaced. This was reflected in the new system in by new automated procedures and functionality. Integration with their existing internet-enabled components was achieved through an interface control software application, some automation components, and XML. Integration into different data sources was achieved by utilizing a multi-tier approach: This established approach also meant that the system was more scalable, as it would enable the client to upgrade their database if they so needed much more easily. As you can see from the diagram, the client application and business rules can both work and be maintained virtually irrespective of the data source. This architecture also helped to ease the parallel rollout of the system, as legacy data and data from existing sources could also be accessed via the new system. In addition, the solution came with security management for users and user groups; user-specific, data-driven functionality and menus; and protection of sensitive data by the use of mathematical encryption algorithms. The new software also produced on-screen and printable management reporting facilities, including graphs and charts, and a bespoke graphical interface for viewing and searching a historical view of invoices using Direct Manipulation. This data could also be exported for use in other systems or applications. A well-designed architecture and system, coupled with verifiable QA, made for a robust and scalable solution. The project was finished and deployed successfully, followed by a completion handover. More About the Project Benefits:      • More users could access the system at any one time, meaning much more efficient use of staff and resources • Repetitive time-consuming tasks were automated - saving time, resources and money • The billing cycle was improved and further automated to achieve the goal of maximizing income from paid invoices • Management had up-to-date and historical data and reports at hand on various key performance indicators (KPIs) and financial information • Diary, Actions and Scheduling systems were introduced • The client saved money on unpaid invoices, lost invoices and other inefficiencies • Savings were made on inefficiencies that had previously existed, as the old billing system was not integrated with other information systems • Workflow and processes that had previously not been automated had changed and improved - resulting in better efficiency, customer relations and financial savings Integration Part of the new solution fed data into Microsoft Access databases that were used in conjunction with some financial analysis functions that were used by finance and business analysis. The financial and statistical functions resided in Microsoft Excel spreadsheets, and used VBA to access data from the database. As well as routine financial analysis, this data was used as part of management decision support for monitoring product-billing effectiveness and strategy, calculating product-specific profit & loss and targeting customers. What we used: Multi-user n-tier Solution Use Case Technique & UML OOA, OOD, OOP Data Modelling & SSADM Visual Basic 6.0 VBA - with Excel & Word MS Access SQL Server SQL DTS DAO, ODBC, ADO, OLEDB COM & ActiveX DLLs MAPI XML & HTML SourceSafe Crystal Reports    Case Study: Medium Project - Migration, Financial Sector The Problem Our client had a large existing database system that had been developed with Visual Basic 6.0 and SQL Server 7. Due to some internal re-structuring, the client had in part moved to the .Net platform and required significant aspects of the existing systems to run under .Net, taking advantage of the .Net platform and features. In addition, some small, yet important, features were needed to be added to accommodate changes in the business. The Solution The initial phase of the solution was to clean and validate the existing data. Once this process had been completed and checked, the data was then ported from SQL Server 7 to SQL Server 2000. This was done using DTS packages, and a small software application designed to help automate, control and verify the data migration process. The VB6 application was then migrated to VB.Net. The system was then tested thoroughly at this point before new functionality was added. The new features then went through UAT and the new solution subsequently went live. What we used: .Net VB.Net SQL Server ADO.Net SQL SourceSafe HTML SSADM OOA, OOD, OOP Case Study: Medium Size Project, bezier creative print (Retail Media Sector) The Problem Our client needed a multi-user solution for a POS retail media business requirement with one of their end-clients, a major high street retailer and household name. The primary requirements were to manage and control multiple promotions. The solution would need to log, manage and process promotions specifications from the end-client, and from these to then generate and produce the printing requirements. Other requirement aspects included distribution planning, allocation and management. The end product in our client's process was that of retail media in the form in-store promotions - for example posters and banners - for the client's client. An example of a print run would involve producing posters and banners for a particular promotion that would be implemented out across some or all of end client's high street stores. The requirements specified and needed by the end-client were complex, store-specific and promotion-specific. The promotion specifications also had to conform to other business rules, multiple types of exceptions and other constraints and conditions. The Solution Their existing PC based desktop database system was redeveloped to allow the users to easily enter the requirements for each promotion as they came in from the end-client. A combination of a new software application, a database and powerful reporting then enabled the full automation of the process of generating the printing requirements. The business logic was encapsulated in software application and reporting. An object-oriented approach was used for the solution. The system produced reports and output that the printers would use at production. This included what the print consisted of, how it would be printed, colour requirements, paper requirements, media requirements, and quantity and distribution requirements. The system also contained routines for importing data from external data sources, and integrated backup and repair functionality. What we used: Multi-user Solution Visual Basic 6.0 MS Access DAO, ODBC, ADO, OLEDB COM & ActiveX DLLs SQL SourceSafe SSADM Use Case Technique OOA, OOD, OOP Crystal Reports Case Study: Recording Studio, Medium Size Project, Music Industry The Problem Our client, a recording studio, required a system to automate the day-to-day operation and functions of their business. Their current IS solutions consisted of Word documents, Excel spreadsheets and other file-based solutions. The business was suffering from a lack and an inefficient use of I.T. and automation, which was being reflected in studio bookings and turnover. The client needed to manage customer details, studio bookings, customer invoicing, purchase accounts, studio time and other business-specific functions. Our client also needed to automate output documentation (invoices, letters, reports etc). The Solution Following meetings with the client and a feasibility study and cost-benefit analysis, it was decided that the best solution for the client, that would be within budget, cost-effective, meet their business goals and specific requirements, was a bespoke client/server system. The solution was an end-to-end project. After completing and signing-off the Requirements Analysis stage and Requirements Specification, we designed and developed their new database and system. The system used a SQL Server database. This was necessary as our client's data requirements were significant, and it was determined that our client's needs and usage would very quickly outgrow a desktop database, such as MS Access. A bespoke application was also developed that handled customer enquiries, customer records, appointments, studio bookings, invoicing & payment, studio time management, event scheduling, marketing, accounts, inventory & stock, equipment hire, standard letters, actions, financial analysis and management reports.  Most output from the system was generated using Crystal Reports, MS Word and MS Excel. Some data was exported (CSV, XML) for use elsewhere. As part of the handover, the new system went through a UAT stage and was supplied with full technical and user documentation, and user training. Integration Our client needed to automatically supply & pump data from the new computer system for use in a marketing IT project they were involved with in conjunction with a 3rd-party company. The solution consisted of a bespoke application that allowed the user to create "mapping templates" that bridged the gap between my client's SQL Server database, and the destination data structures, which could be vastly different. The solution also provided exports in CSV and XML, the formats of which could be changed via the same bespoke application. Benefits: Eradicated double-bookings Increased turnover Increased repeat bookings More new customers More efficient use of resources Provide & Manage new income streams Management Reporting enabled managers to make more informed decisions that meant the company could save money in many areas, improve cost-benefit ratios, and cut-out or minimize on unnecessary costs. What we used: Client/Server Solution Visual Basic 6.0 SQL Server ADO, OLEDB COM & ActiveX DLLs SQL SourceSafe SSADM XML VBA (Excel & Word) Crystal Reports OOA, OOD, OOP Case Study: Recording Studio, Small Project, Music Industry The Problem Our client required a new database system, and a Windows compatible Object-Oriented API and translation layer to allow them to control and manage data with one of their digital recording studios. The Solution Several layers were needed in order to achieve the objectives. A low-level comms layers was created that would communicate with the recording studio equipment, which was connected via USB. A more friendly API then sat on top of this, providing a subset of needed functionality, control and access. On top of this, a generic Object-Oriented(OO) high-level layer was designed and developed, along with two COM DLLs. A newly designed bespoke application was created, with a SQL Server back end. The top OO layer was then used by the new bespoke application, and other systems, to allow the user to transfer digital songs between PC and recording studio, perform backups, create and manage song & patch libraries, edit studio music data, and control operations on the studio from the PC software. What we used: Visual Basic 6.0 SQL Server ADO, OLEDB COM & ActiveX DLLs SQL C USB SourceSafe SSADM OOA, OOD, OOP Case Study: Medium Size Project, Software Company The Problem Our client was redeveloping an existing product for the Microsoft Windows platform. The system was a Library Management System with many complicated features. This was originally an MS-DOS system that had been written in Clipper. It had to be totally rewritten for Windows, and to make it Year 2000 compliant. The Solution Working from the original Clipper source code, all the programs were redesigned and re-written to run on Windows using Microsoft Visual Basic. This also meant the system had a new architecture. Reverse-engineering was needed for some aspects of the original software. The database employed was Microsoft Access. What we used: Data Modelling SSADM Use Case Technique Visual Basic MS Access SQL DAO, ODBC OOA, OOD, OOP Crystal Reports Clipper BPR Reverse Engineering Case Study: Large Size Project, Software Company The Problem Our client was contracted by one of their clients to provide a full end-to-end solution for a requirement they had. The new requirement was a Multi-Currency Accounting System for a client in the timber trade. We were sub-contracted to do all the work on the design, development and testing of the new system. The Solution Working with requirement and system specifications, the new system was written using Microsoft Visual Basic. The database used was Microsoft Access. This was a complex system, involving several postings per transaction, with either credits, debits or both being in a currency other than GBP Sterling. Other aspects of the new system included: Cash Received/Payments Made Allocation to outstanding postings Letters of Credit Bank Reconciliation FX Deals Management Accounts This system is still live today and is under discussion for redevelopment to add new features, and take advantage of more up-to-date technologies and platforms, though core code will be retained. What was used: Data Modelling SSADM Use Case Technique Prototyping Visual Basic MS Access SQL DAO, ODBC OOA, OOD, OOP Crystal Reports BPR Case Study: Small Project, Finance & I.T, Visa The Problem Our client was sub-contracted to work on a major product development. The client needed a flexible back-office strategy and system for creating e-catalogues that related to the end-client product. The Solution Since catalogues were set up via a number of different means, the solution needed to be flexible and able to handle different data sources. It was agreed that a RAD approach was required. Design of this system included meeting with the users to produce a User-Requirements Specification, followed by System Design, Object Modelling, Client/Server design, database design, ActiveX control development & COM, coding and testing - all with full documentation. A bespoke system was developed that used ODBC and ANSI SQL to connect to different RDBMS and other data sources. We also advised my client on their technical architecture, back-office strategy and the use of different databases and development tools, such as Visual Basic. What we used: Visual Basic 5.0 SQL Server MS Access Oracle ADO, OLEDB, RDO, DAO ODBC ANSI SQL COM & ActiveX DLLs SourceSafe SSADM RAD HTML & DHTML Java JavaScript TCP/IP OOA, OOD, OOP Case Study: Medium-Large Size Project, GE Capital GCF(Global Consumer Finance) The Problem The client was in the process of a large Y2K upgrade and migration programme that involved moving systems and data away from the mainframe and legacy systems to run on the Microsoft Windows platform, with Client/Server solutions using Oracle. One key aspect of this was a suite of requirements for extracting and collecting business-critical business and financial data from different sources, including the mainframe. The data from this was information essential for the running of the company. Accountants, managers and business analysts use this data in accounting systems, for financial planning, for risk analysis and in the future for data mining. Therefore, this system was a business-critical requirement. The Solution A suite of new and upgraded applications and solutions were designed and developed. A solution was developed to extract data from any given source that was in use. This include different RDBMS, an e-mail server and desktop MS Access databases. This data would then be analysed by users either from another new bespoke application, or via an Excel spreadsheet and add-in.  These systems were used for the production of support information to end-clients, and the production of business, financial and management reports. We were responsible for all aspects of the porting of any existing 16-bit systems to 32-bit. The port included developing a new bespoke application, migrating existing applications, databases, reports and spreadsheets. There were several new features added in the new solution, including new software and routines for analysing data. From 16 to 32 bit In the move from 16-bit to 32-bit, a number of 16-bit 3rd party controls were re-developed in-house. This involved reverse-engineering, object modelling with UML, using Use Case Technique, specification building and refinement, and modelling objects across multiple tiers. The project also involved the in-house redesign of the 16-bit version's use of global memory and windows messaging to share data between applications. Performance In redeveloping this system, another part of the remit of the work was to increase performance. This was done by reducing the time taken for importing data and recalculating query result values. The response times of other data queries were reduced by the use of pre-calculated values.  Error Management The solution featured a complete error management and debugging sub-system that was customizable and included automated error logging and resolution. What was used: n-tier, Multiple Solutions Client/Server Solutions PRINCE2 BPR Visual Basic 5.0 VBA, with Excel & Word SQL Server, SQL Oracle, MS Access DAO, OLEDB, ADO, ODBC, RDO COM & ActiveX DLLs SourceSafe SNA Server SSADM Crystal Reports UML, OOA, OOD, OOP Case Study: Large Size Project, Finance, Credit Reference & Automotive, Experian The Problem Due to market competitiveness and a market-gap that had been identified in the specific area of vehicle history and credit-scoring, our client needed to realise a solution to capitalise on this, whilst also updating their current systems and providing new services to their end-clients. The client's end-clients, and other 3rd parties, would need remote, secure and fast access to up-to-date decision-support information. Use Case Example: 1. Customer enters a car dealership showroom to upgrade their current car and purchase a new car. 2. Upon applying for credit to make the purchase, a check is needed via Experian to ensure the customer's suitability, eligibility and credit-worthiness. 3. If this is successful, a further check carried out via Experian to ensure that the car to be part-exchanged has not been involved in a major accident, stolen or is subject to outstanding finance. 4. If the car had outstanding finance, for example, the dealer would have avoided taking the vehicle in part-exchange. The dealer could look into this further and offer options to the customer (e.g., if the car had outstanding finance from a previous owner). The Solution A new vehicle information system was developed, which would be one of the largest of its kind in the UK. The new system was a vehicle registration and credit-scoring database checking system for the motor industry. Typical users of the new system would include retail car dealers, finance companies, banks, building societies, and motor-reference companies. The end-user would have their own "front-end" software - or software supplied from my client - that would access the Experian data via a comms engine and multi-tier architecture. Go with the flow... A critical aspect of the project was the "Flow Controller". This is an OLE Server that interfaces between a client's application and a "requester", effectively controlling the flow of valid and verified data. The requester communicates with a 'comms engine, which in turn communicates with the security on the mainframe before data is eventually passed to the relevant CICS application region on the mainframe (3270). Data is subsequently passed back via the same architecture.  Test the water... A front-end application and a full test-harness were also developed, which was necessary to fully test the flow-controller in-house, and used SQL-Server and MS Access to store data for the many Use Case Scenarios. What was used: n-tier Solution Visual Basic 4.0 (32 bit) Visual C++ MS Access SQL Server DAO, ODBC, RDO COM & ActiveX DLLs SQL SourceSafe SNA Server SSADM OMT OOA, OOD, OOP