David Johnstone

I am currently a software developer and lead of the Operations team at Phillips, Hager & North Investment Management - an RBC owned asset management company in Vancouver managing $78Bn for institutional and private investors. The operations environment is an extremely challenging one - I am responsible for seeking out opportunities to automate functions in the team to maximize efficiency, improve issue resolution times and minimize error.

• Designed and built software to automate the following functions in the team:
  • Real-time monitoring of key systems in the company giving Operations analysts a single dashboard application through which to proactively monitor the numerous applications the team is responsible for - everything from checking on network traffic and key services to database anomalies and problem trades. The monitoring system is built in such a way to be easily extensible allowing the team to quickly automate new checks when needed.
  • Report on failures / warnings, and gather performance statistics for the company’s performance and attribution system – Sylvan.
  • Manage resource allocation in the team, manage the support ticket queue and report on team performance. Prioritization and allocation of support tickets is done automatically by the application based on a number of configurable variables. A suite of reports allows managers from all teams across the group to drill into team performance to spot support trends.
• Manage a team of two programmers and three operations analysts to support and enhance the trading, reporting and data integration systems owned by the business services group.
• Regularly liaise with the business on behalf of the technology group at PHN to gather requirements for system changes, discuss issues and report on the performance of the team.

Was the lead developer for a small consultancy specializing in bespoke software development for major pharmaceutical companies including Sanofi Aventis, Johnson & Johnson, Boehringer Ingelheim and Bayer Schering. Most of the applications built supported Siebel CRM systems to perform complex functions such as data integration, migration, and assignment in such a way that they could be easily administered by non-technical users. Regularly liaised with end users and stake-holders during projects often acting as both business analyst and developer.

Fusion Data Management (FDM)

• Designed and lead the development of the core, reusable components of the consultancy’s Fusion Data Management product. The product managed the complicated two way integration of data between the enterprise CRM systems of several leading pharmaceutical companies, and data suppliers Cegedim-Dendrite and Binleys.
• Built using VB.NET 2.0 / 3.0 / 3.5, the Fusion Data Management product comprised a windows service hosting an extendable pool of workflow processes, and a smart client exposing a rich user interface to administrators.
• The data to be integrated described the structure of the ever changing British National Health Service (NHS). Data changes were automatically downloaded, transformed and staged in a SQL Server database, and applied into the CRM system using a number of mechanisms; COM, EIM, EAI or web services. Changes made in the CRM system by users were captured and sent to the data supplier for validation.
• Liaised with key client personnel during FDM implementations to gather requirements for custom processes, report project progress, and provide training.
• Businesses using FDM reported huge benefits in fully automating the exchange of data between data supplier and internal CRM system. In most cases, FDM replaced expensive, user intensive processes prone to human error.

Fusion Assignment Builder (FAB)

• Designed and lead the development of the consultancy’s Fusion Assignment Builder product to support pharmaceutical businesses using Siebel.
• Built using VB.NET 2.0 / 3.0 / 3.5, each interface comprised a windows service hosting an extendable pool of workflow processes, and a smart client exposing a rich user interface to administrators.
• The interfaces extracted data from the target Siebel system into a SQL Server staging database to create a universe from which administrators were able to interactively construct complex assignment rules. Using the tool, administrators could preview rules before releasing them, and schedule them to run periodically.
• Businesses using FAB reported a significant reduction in the cost and effort associated with assignment in their CRM systems, with projects that once took many months to implement and test now taking just hours.

Component Development

• Designed and developed reusable code libraries to reduce the time taken to build robust, reliable and easily maintainable applications.
• Data components eased the interaction with databases in a variety of scenarios. The functionality exposed by these augmented what was available in ADO.NET and could be used to quickly satisfy data access requirements in applications.
• A configuration component exposed an extendable object model reading from, and writing to xml files. The component included an editor allowing users to easily manage application configuration. Plugins implemented in other assemblies were loaded at runtime to guide non-technical users through the steps required to configure objects such as database connections, FTP connections and Siebel data controls. Plugins could be built for any purpose, allowing developers to access configured, ready to go objects with one function call.
• A general use component encapsulated helper functionality for, amongst other things, FTP, SMTP, error logging and file compression.
• By working to increase code reuse, the time taken for the consultancy to implement a typical data interface was reduced from over a year to less than three months.

Was one of a team of four developers tasked with customising the company’s Siebel CRM system.

• Was responsible for the ongoing development of the Siebel application – Change requests, and new areas of configuration.
• Lead the upgrade of the company’s Siebel environment from Siebel 7.5.3 to 7.8.

Was the lead developer in a team of two responsible for the design, development and maintenance of a number of applications spanning a variety of technologies.

Human Resources System

• Was the sole architect and developer for the company human resources management system “Absence”. Built using VB.NET 1.1, the application was used to track attendance, holiday allowance, and working patterns amongst the company’s employees in an Oracle database.

Company Intranet

• Was the lead developer of the company intranet application used by company employees to access marketing and sales data. The intranet application “HOT” was a three-tier solution comprising a business layer and web application built using VB.NET and ASP.NET, and an Oracle database.
• The intranet application allowed administrators to upload data from several different sources into an Oracle OLAP database. PL/SQL stored procedures were used to build data cubes from which sophisticated reports could be generated on request by company employees.
• Role-based security controlled access to sensitive internal company data.
• Reports were made available in a variety of different formats through a third party .NET charting component. Users were able to select time periods and other dimensions when constructing reports.
• An administration suite allowed administrators to manage site access and upload/modify data.

Siebel CRM System

• Was responsible for the ongoing development of the company’s Siebel CRM system.
• Designed and developed many new areas of configuration in the project.
• Have extensive experience developing specialized functionality in Siebel 2000.

Company Data Warehouse

• Was responsible for the development and maintenance of the company’s data warehouse.
• Developed PL/SQL stored procedures to import and manipulate data in the data warehouse’s Oracle OLAP database.
• Designed and developed Business Objects universes and reports.

Gained two years experience working on a variety of VB6 applications as a junior developer.

• Developed bespoke software including an application allowing users to browse and update the company’s lifestyle marketing database, and an application to manage email based marketing campaigns.
• Worked as part of a small development team, and was involved in the full project cycle.