Sql Server ETL Azure AWS DMS SSIS SSAS
Resume:
f
Allan Fox
Phone:
*****.***@******.***
ALLAN FOX
Mr. Allan Fox is a seasoned SQL Server Consultant and Developer with over 30 years of experience in designing and developing database solutions in the Financial Services, Health Care and Industrial sectors. He has hands on experience designing, administering and supporting large MS SQL Server databases and related data warehouses and has developed and tuned ETL processes created to load data into them using SSIS. Allan is also proficient at building and maintaining reporting databases and systems using SSAS and SSRS, and creating reports using T-SQL, stored procedures and query languages. In addition to his SQL Server skills, he has recent experience on the MS Azure and Amazon AWS cloud platforms and Postgres and Mysql databases. He is a detail-oriented individual with solid communication, problem solving and time management skills.
PROFESSIONAL EXPERIENCE
Company
Position
Location
Dates
Munich Reinsurance
Tech Lead / Project Manager
Toronto, Ontario
Sept. 2020 – Present
Datavail Corporation
Migration Specialist
Toronto, Ontario
March 2023 - Present
Capital Markets Inc. (CAPCO)
ETL Technical Lead
Toronto, Ontario
Oct 2019 – Sept. 2020
Bank of Montreal
Database Developer
Toronto, Ontario
Mar. 2019 – Oct. 2019
Prophix Inc.
ETL Specialist
Toronto, Ontario
February 2019
Capital Markets Inc. (CAPCO)
ETL Technical Lead
Toronto, Ontario
Oct 2015 – Jan. 2019
Adastra Corporation
SQL Server Consultant
Markham, Ontario
May 2013 – Oct 2015
Novo Nordisk Inc
SQL Specialist
Toronto, Ontario
Mar 2013 – May 2013
Navantis Inc
SQL Server BI Developer
Toronto, Ontario
Nov 2011 – Mar 2013
Royal Bank of Canada
SQL Server Production DBA
Toronto, Ontario
Jan 2011 – Oct 2011
Davis + Henderson
SQL Server Operational DBA
Toronto, Ontario
May 2010 – Dec 2010
Hyundai Canada
BI Developer/Data
Toronto, Ontario
Jan 2010 – Mar 2010
Warehouse/DBA
Ontario Medical Association
SQL Developer
Toronto, Ontario
Oct 2009 – Apr 2010
Accenture Canada
DBA/Developer
Toronto, Ontario
Apr 2008 – Sep 2009
Insurance Bureau of Canada
DBA
Toronto, Ontario
Sep 2007 – Apr 2008
Accenture Canada
DBA
Toronto, Ontario
Sep 2005 – Sep 2007
Imperial Oil Ltd
Developer/Support Analyst
Toronto, Ontario
Jun 2001 – Sep 2005
Ministry of Health & Long
Developer
Toronto, Ontario
Feb 2004 – Mar 2005
Term Care
The Toronto Sun
Developer
Toronto, Ontario
Sep 2000 – Jun 2001
DataMirror Corporation
Senior Programmer
Toronto, Ontario
May 2000 – Sep 2000
Manulife Financial
Developer
Toronto, Ontario
Nov 1999 – Mar 2000
Imperial Oil Ltd
SQL Server Developer/DBA
Toronto, Ontario
Sep 1989 – Sep 1999
Ministry of Health
Senior Programmer/Analyst
Toronto, Ontario
Sep 1985 – Sep 1989
Consultancy Projects:
OANHSS
Data Warehouse Architect
Toronto, Ontario
Prophix Inc.
ETL Developer
Toronto, Ontario
Rapido Trains
Consultant / Full-Stack Developer
Toronto, Ontario
PROJECT HIGHLIGHTS
Munich Reinsurance (MunichRe) – Toronto, Ontario
Project Manager – MunichRe IFRS17 DWH, MRNV Claims Processing System
Sept 2020 – Present (Contract)
Responsibilities:
IFRS17 DWH:
Developed the IFRS17 Data Warehouse and support databases
Designed and implemented a general-purpose data-driven framework for executing sets of ETL processes. Each process step can be an SQL procedure or an SSIS (sub)-package.
Completed the development of the IFRS17 data warehouse and all import and export ETL processes.
Managed the Go-Live production deployment and parallel processing quarterly data export feeds.
Designed Azure DevOps CI/CD pipeline for deployments to QA, UAT, and Production environments
Managed a team of 3-5 developers and testers
MRNV:
Implemented a database system to import Premium, Claims, and Commission data from multiple sources.
Implemented a monthly summary and export feed for transmission to the Canadian accounting system.
Managed a team of 3 developers and testers
FWA:
Migrated data from SQL Server to Postgres AWS database
Developed views and permission structures to restrict access to sensitive (PII and PHI) information.
FinRe
Designed ER Model and Database objects.
Assisted the BA in authoring design documents (BRD and SRS)
Developed ETL processes for ingesting complex multi-tab Excel workbooks into SQL Server Azure system.
Environment:
SQL Server Azure, SSIS, Powershell, Visual Studio, TFS
Datavail – Toronto, Ontario
Migration Specialist – CritiCall System Modernization Project
March 2023 – October 2023 (Contract)
Responsibilities:
CritiCall:
Translated legacy SQL Server stored procedures to AWS Mysql / Aurora.
Designed and implemented a message-queue based auditing / logging sysem in AWS (SQS and Lambda functions)
Developed DMS tasks for migrating 4 SQL Server databases to Mysql in the AWS cloud
Developed runbooks and processes for migrating the application databases to AWS.
Capital Markets Inc (CAPCO) – Toronto, Ontario
ETL Technical Lead – MunichRe IFRS17 DWH
Oct. 2019 – September 2020 (Contract)
Responsibilities:
Led a team of two developers in developing new data feeds for client Munich Re-insurance (MunichRe) for loading data into an international data repository. The data feeds include financial and insurance policy data that are being collected for use in implementing the new IFRS17 insurance reporting standards. A secondary goal of the project is to lay the foundation of a North American regional data warehouse.
Environment:
SQL Server Azure, SSIS, Visual Studio, TFS
Bank of Montreal – Toronto, Ontario
Sr. Database developer – Enterprise Risk data warehouse
March. 2019 – Oct. 2019 (Contract)
Responsibilities:
Supported and maintained an existing data warehouse implemented on IBM Netezza appliances.
Developed tables and loading procedures to support new dimension and facts according to client specifications.
Prepared run books for weekly and monthly code deployments and performed non-production code deployments.
Performed peer reviews on other team members’ code.
Migrated Netezza code to Pivotal’s Greenplum database (a Postgres implementation).
Environment:
Netezza, Greenplum (Pivotal)
Prophix Inc. – Toronto, Ontario
ETL Specialist
February 2019 (4-week Contract)
Responsibilities:
Developed ETL processes and staging database to extract data from Bullhorn’s web-based CRM using Bullhorn’s REST API library.
Identified the entities and attributes to be extracted.
Created a normalized SQL Server database into which the data would be staged.
Developed routines in C# to log in to the Bullhorn website and extract the required entities from the BullHorn cloud database.
Implemented data-driven ‘full’ and ‘incremental’ data loading procedures to populate and synchronize the staging database with the cloud (Cloud-to-local extract).
Staging database was used by other Prophix team members to populate the Prophix database and produce reports and dashboards for the end client.
Environment:
SQL Server 2014, SSIS, C#, Bullhorn CRM
Prophix Inc. – Toronto, Ontario
ETL Specialist
August – September 2018 (8-week Project)
Responsibilities:
Developed an integrated Excel / SQL Server project estimation utility. The tool would generate Excel spreadsheets containing company-approved formula in protected zones. Users could populate the sheets with customer and product data extracted from a SQL Server repository, and could continue to work on the Excel sheets while off-line or at client sites. Data could them be uploaded back to the central repository upon return to the office.
Environment:
Excel, Excel VBA, SQL Server
Capital Markets Inc (CAPCO) – Toronto, Ontario
ETL Technical Lead
May 2017 – January 2019 (Contract)
Responsibilities:
Led a team of two developers in migrating the customer information of BMO / Harris Bank on-line (mobile / web) banking to a newly developed platform.
Architected data migration from legacy system into multiple (7) existing and new bank systems
Developed and implemented procedures to transfer 860,000 customers from the old on-line system to the new platform.
Administered the development database server
Prepared deployment scripts and run books for Production deployments
Input was received in a set of comma-delimited text files. It was loaded into a SQL Server database, validated, cleansed, and transformed into a set of outputs to be consumed and verified by the target systems. File formats and data encoding (ASCII, Unicode, UTF8) had to be used to match each target system’s requirements. In some cases, direct access to target databases was granted, allowing direct data transfers to the target system in SSIS.
Environment:
SQL Server 2016 Enterprise, SSIS, C#, Oracle
Oct 2015 – April 2017 (Contract)
Responsibilities:
Led a team of three developers in creating ETL processes for loading daily data feeds into ODS database of CAPCO client Sainsbury Bank
Architected process control and daily load routines for multiple (9) source systems
Authored Code Generator tool in C# to generate SSIS packages from table metadata
Data was parsed from a set of flat files delivered to the ODS environment on a daily basis. The ETL process converted the data from ASCII and UTF8 format to Unicode, then merged the data into both an ODS and historical database.
Environment:
SQL Server 2012 Enterprise, SSIS, C#
Prophix Inc – Mississauga, Ontario
ETL Developer – Multiple projects
Oct 2016 – Sept 2018 (Contract, in parallel with CAPCO)
Responsibilities:
Designed and implemented ETL process for extracting financial and budgeting data from Prophix system deployed by client, CBRE.
ERP Budget and Actual data were summarized on an hourly basis and exported to a set of files representing fact and dimension entities.
The export files were encrypted using a custom software module supplied by Microsoft, and transmitted to the Microsoft data warehouse landing zone. (Microsoft Corp was consuming the output from the CBRE system).
Implemented ETL process to consume Master Data files exported by Microsoft and load them into CBRE’s Prophix financial data warehouse.
Debugged and improved existing CDC solution deployed in client’s database.
Developed multiple data loading ETL packages for Prophix clients. Financial transaction data was transformed and summarized by month an budget for input into the Prophix ERP system.
Implemented data refresh a loading system from Sage50 accounting to Prophix using Excel and Powershell
Implemented Excel front-end / SQL Server back-end Sales estimation tools with integration to Prophix ERP.
Environment:
SQL Server 2012 Enterprise, SSIS, C#, Powershell, Excel
Adastra Corporation – Markham, Ontario
SQL Server Consultant
May 2013 – Oct 2015
Projects:
Rogers Communications Inc – ETL Developer
Developed ETL packages in SSIS for multiple client applications
Implemented control systems for daily data import and export, using SSIS and C#
Environment:
MS SQL Server, Oracle, Teradata, SSIS, C#
Bank of Montreal, ERPM IT Strategy - Credit Risk Reporting – SQL Reporting Specialist
Analyzed and investigated data sources for data quality and completeness
Developed and optimized T-SQL solutions (tables, views, indexes, queries, functions) needed to produce the reports specified in the system requirements documents (SQL Server development)
Developed Tabular Data Model reporting database for use by the Corporate Risk Reporting team.
Developed SSRS reports as to system specifications in DAX and T-SQL
Environment:
SSRS, DAX, TDM, SQL Server, Visual Studio 2006, Composite v6.4, Excel
Rapido Trains Inc.
Developer
July 2015 – ongoing project support
Responsibilities:
Integrated components of the client’s e-commerce and accounting system
automated the import of orders from the client website into the accounting system (QuickBooks)
automated credit card payment processing from Moneris and integrated payments with QuickBooks
Developed inventory management utilities (Stock-taking, product picking / shipping)
Environment:
SQL Server, VB.Net
OANHSS / AdvantAGE Ontario
Data Warehouse Architect
Oct 2014 – Jan. 2015, project support to present
Responsibilities:
Designed and implemented a data warehouse for the Ontario Association of Non-Profit Homes and Services for Seniors (OAHNHSS). . It aggregates member and financial data from member homes across the province.
The warehouse was implemented using SQL Server (2008 R2) database and SSAS cubes
Excel Power BI used for additional analysis.
Novo Nordisk Inc – Toronto, Ontario
SQL Specialist
Mar 2013 – May 2013
Responsibilities:
Developed and maintained SSIS packages to load data from Excel spreadsheets into the corporate data warehouse
Extended existing cubes in the warehouse and developed an automated system for comparing and verifying data warehouse cubes in the Development and Production environments
Environment:
SSIS
Navantis Inc – Toronto, Ontario
SQL Server BI Developer
Nov 2011 – Mar 2013
Responsibilities:
Created an enterprise data warehouse using SSAS to monitor province-wide bed and resource utilization
Coded ETL in SSIS to load the data warehouse
Developed MDX and T-SQL queries for reports and authored reports using SSRS for use within the website
Developed auditing sub-system for the CritiCall Ontario Provincial Hospital Resource website using SQL Server 2012 CDC features, triggers, and Service Broker
Worked on multiple BI and Reporting projects for Navantis customers (Hospital for Sick Children, LV Lomas Inc.)
Tuned and re-factored queries to improve performance and eliminate deadlocks
Environment:
SSIS, SSAS, SSRS, T-SQL, SQL Server 2012
Royal Bank of Canada – Toronto, Ontario
SQL Server Production DBA
Jan 2011 – Oct 2011
Responsibilities:
Provided DBA services for 20 application groups within the bank and its associated companies
Maintained MS SQL Server 2000, 2005, and 2008 database instances
Performed T-SQL code and index tuning, often improving I/O performance by 50-90%
Maintained SQL logins and user accounts, deployed databases and maintenance (SQLAgent) jobs to development, QA, and Production environments
Consulted with application groups to improve stored procedure and SSIS package performance
Instituted source code management practices for database schema changes for all mid-range
database technologies in the bank’s SCON environment
Environment:
SSIS, T-SQL, MS SQL Server 2008/2005/2000, SQL
Davis+Henderson – Toronto, Ontario
SQL Server Operational DBA
May 2010 – Dec 2010
Responsibilities:
Analyzed and tuned loan payment database system and instituted index and maintenance changes that reduced batch execution times from eight hours to four and a half hours
Diagnosed SQL connectivity issues to stabilize a multi-server environment
Provided scheduled and on-call DBA support for Production and UAT systems
Performed code and schema deployments to Production and UAT systems
Environment:
SQL Server 2008 Enterprise
Hyundai Canada – Toronto, Ontario
BI Developer/Data Warehouse/DBA
Jan 2010 – Mar 2010
Responsibilities:
Re-built the SQL Server-based national data warehouse, with emphasis on improving data quality and accuracy of national sales and leasing data
Introduced proper data cleansing and de-duplication procedures written in T-SQL and used to improve data quality of feeds from AS400 systems
Created interfaces with Oracle-based Siebel CRM system and maintained existing internal reports developed using Crystal Reports
Created SSIS system and stored procedures for processing data captured daily at all 2010 Auto
Shows and reconciled it with sales data from the data warehouse
Environment:
SSIS, SQL Server 2005, T-SQL, SSRS, Crystal Reports
Ontario Medical Association – Toronto, Ontario
SQL Developer
Oct 2009 – Apr 2010
Responsibilities:
Implemented the payment-processing module using MS SQL Server 2005
The module interfaced with MS Dynamics 4.0 to extract data, perform payment processing, and then updated the CRM database
Designed the payment database in MS SQL Server, coded payment processing in T-SQL, and developed SOAP routines in C# to interface with MS Dynamics via web service calls
Developed reports with SSRS and integrated them with the CRM application
Designed the database and server maintenance regimen
Environment:
MS SQL Server 2005, T-SQL, SSRS, C#, MS Dynamics
Accenture Canada – Toronto, Ontario
DBA/Developer
Apr 2008 – Sep 2009
Responsibilities:
Project: Cancer Care Ontario, Ontario Wait Times Information System
Performed database and server tuning using Microsoft tools (Perfmon, Profiler)
Identified and optimized poorly-performing T-SQL queries
Maintained Production ERD model and performed code and schema deployments
Prepared and tested ‘build-books’ and documentation for deployments performed by client
Migrated system to MS SQL Server 2008
Environment:
MS SQL Server 2008, T-SQL
Insurance Bureau of Canada – Toronto, Ontario
DBA
Sep 2007 – Apr 2008
Responsibilities:
Responsible for database administration in a VLDB MS SQL Server 2005 environment
Managed code and data deployments to Development, SIT, UAT, and Production systems
Maintained database model integrity across all systems and monitored and administered terabyte databases
Built and supported ETL packages using MS SSIS and BIDS, T-SQL and stored procedures
Built and deployed datamarts and cubes using MS SSAS and BIDS
Developed automated database and server monitoring tools using VBScript and T-SQL
Assisted in managing legacy Oracle and DB2 databases (Rel. 9.2.0.6 to Rel 9.2.0.8)
Environment:
MS SSIS, MS SQL Server 3005, Oracle, DB2, SSAS, T-SQL
Accenture Canada – Toronto, Ontario
DBA
Sep 2005 – Sep 2007
Project:
Cancer Care Ontario
Served as a DBA on a Rapid Application Development team and maintained Development, Testing, and Pre-Production (SIT and UAT) databases for the team of approximately 10 Developers
Worked with the Data Architect to ensure that all referential integrity rules were in place and that all company naming standards were being followed
Maintained the database models using ERWin AllFusion
Automated the build process using utilities developed in C# and VBScript
Maintained schedule of automated and manual database and server backups
Developed stored procedures and participated in peer T-SQL code reviews throughout the development cycle
Responsible for optimizing all stored procedures and underlying databases
Developed DTS packages for data loading and migration
Produced deployment scripts and documentation for the client
Performed database schema and code promotions
Developed and deployed a replicated reporting database and reporting suite implemented via stored procedures and Crystal Reports
Provided post-production support and training to client
Environment:
T-SQL, Crystal Reports, Erwin, C#, VBScript
Imperial Oil Ltd – Toronto, Ontario
Developer/Support Analyst
Jun 2001 – Sep 2005
Responsibilities:
Part of a team of support analysts maintaining a network of MS SQL Server installations distributed across Canada
Implemented a roll-out of approximately 40 additional sites and converted database schema and stored procedures from MS SQL Server 6.5 to MS SQL Server 2000
Converted NT-based processes and services to Windows Server 2003
Designed and implemented a TCP/IP-based database replication and synchronization package
Developed and maintained a library of 600+ stored procedures
Environment:
MS SQL Server 2003/2000/6.5
Ministry of Health & Long Term Care – Toronto, Ontario
Developer
Feb 2004 – Mar 2005
Responsibilities:
Converted Clipper-based application to Client/Server system using MS SQL Server 2000 and Visual Basic 6
Designed and implemented database, client forms and reports to client’s specifications
Built interfaces to downstream financial system (IFIS) and database maintenance functions
Environment:
MS SQL Server 2000, Visual Basic 6
The Toronto Sun – Toronto, Ontario
Developer
Sep 2000 – Jun 2001
Responsibilities:
Developed the client-side application for the StreetPerfect mail list management application, using VB6
This involved coding routines for importing and converting mail list data from several external data formats including text files, Excel spreadsheets, and Sybase databases
It also included a label editor and generator as well as job queue management facilities
Environment:
VB6
DataMirror Corporation – Toronto, Ontario
Senior Programmer/Analyst
May 2000 – Sep 2000
Responsibilities:
Acted as the Senior Programmer/Analyst on the NT/MS SQL Server team for DataMirror’s ‘Transformation Server’ product
System components were implemented in C and C++, using the ODBC interface to MS SQL Server 7.0/6.5
Developed product support for NT Clustering and rewrote low-level data manipulation routines to stabilize the production release of the product
Environment:
MS SQL Server 7.0/6.5, C/C++,
Manulife Financial – Toronto, Ontario
Developer
Nov 1999 – Mar 2000
Responsibilities:
Created the tools and management framework needed to measure and analyze the contents of application software, including Java, C/C++, HTML, and Lotus Notes
The system included a GUI interface, data storage, reporting, and extraction components
Environment:
MS Visual Studio (VC 6.0), Lotus Notes C-API, AWK
Imperial Oil Ltd – Toronto, Ontario
MS SQL Server Developer/DBA
Sep 1989 – Sep 1999
Project:
Esso's 'BETA' System
Involved in the entire lifecycle of the project, from the early development phases to maintenance and support of the mature system
Performed many functions, including:
Database designer and Database administrator (SQL Server 2000/6.5/4.2/1.1)
Software developer (SQL Server and System-level)
Technical and User Support
Optimized, tuned, and maintained up to 96 distributed SQL Server installations
Managed many multi-server, coordinated code and schema promotions
Built and configured NT Servers and SQL Server databases
Performed database loading, backup, restoration, and repairs
Developed and implemented disaster recovery strategies
Provided first and second level support for OS/2, NT, SQL Server, and the coded Open Data Services extensions (DLLs) for SQL Server v6.5 and 2000
Created a MS SQL Server based general-purpose report writer
Developed custom replication technology using TCP/IP sockets
Developed: I/O drivers co-processor boards and NT systems, SQL code parsers and translators,
Serial communications packages, and I/O device simulators
Environment:
SQL Server 2000/6.5/4.2/1.1, SQL
TECHNICAL PROFICIENCIES
Technology
Tools
Operating Systems
Windows Server 7/XP/2003, Unix
Development Languages
T-SQL, MDX, PL/SQL, C#, VB.NET, C/C++, VB6, VBA, PowerShell, Unix
Shell (C, Korn)
Databases
MS SQL Server 2016/2012/2008, MS Access 2003/97, MySql, Postgres, DB2, SyBase, Oracle, Netezza
ETL
SSIS, Powershell, C#, VB.Net, Talend
Reporting Tools
SSRS, PowerBI, Crystal Reports
Data Modeling Tools
ERWin, Visio, IEF/COOL: Gen
Other Tools
TFS, Visual Studio, Excel, RedGate, PVCS, VSS
EDUCATION
Qualification
Institute
Bachelors of Science Degree, Computer Science
University of Toronto – Toronto, Ontario
Contact this candidate