Data Engineer
Cigna Healthcare
SQL Server, SSIS, SalesForce, CozyRoc, C#
Cigna are unifying all of their data from disparate global systems into a data warehouse using a logical data mode. Data sources include: SQL Server 2019, Oracle, flat-files and XML, loaded to their data warehouse in a common data model, and distributed to data consumers including Salesforce and reporting dashboards.
-
The existing data load was taking 1 hour 53 minutes to load the data, I was brought in to optimise the SQL and succeeded in reduced it to 8 minutes (same hardware and output);
-
I re-developed the push processes that extracted data from the warehouse to the new Salesforce solution (HealthCloud) to work incrementally instead of the previous full loads to significantly decrease the data volume and increase load times;
-
Lead Developer of SSIS packages to migrate the data from Cigna’s Einstein system into their replacement SalesForce (Einstein) solution using CozyRoc connector: asynchronous bulk parallel data load. Working with the SalesForce team to fix type, field width, picklist, trigger and workflow issues, and the incoming data team to address data quality issues;
-
Development of generic SSIS packages (driven by settings held in the database) written in C# to import data from flat-files, export data to flat-files, merge flat-files, and load complex XML files using XSD files – all generic in operation.
Scottish Social Services Council
Azure: Synapse, ADF, LogicApps, Data Lakes,
Lake Database, KeyVault, Power App, PowerBI
Lead Azure data engineer. Design and development of Azure Data Factory and Synapse Workspace pipelines to extract, load and transform data from on-premises SQL instance, SharePoint hosted Excel documents, Dynamics 265, MongoDB, JSON and Jira into Synapse Analytics data warehouse. Port of existing ADF pipelines to Synapse Workspace. Design and build of tabular cubes for Power BI consumption. Azure DevOps build of repositories, pipelines to copy ADF and synapse workspace artifacts to new environments, and CI/CD pipelines for both ADF and Azure synapse workspace to automatically move artifacts between development and production. Documentation and training of permanent employees (eight 1-hour training sessions).
Azure Data Factory, Synapse Workspace, Data Flow, Key Vaults, Data Lake, Lake Database, Logic Apps, Power Apps, Power Automate, Power BI, tabular cubes, extensive SQL, DevOps, security (VPN, managed private endpoints, identities, etc.), linked services, integration runtimes, MongoDB, database replication and database administration (backup, restore, and security).
Standard Aberdeen Investments
Data Warehousing, SSIS and T-SQL
Build and support of Data Warehouses for Standard Aberdeen’s core reporting hub. SSIS jobs to load XML, Excel and CSV files, data validation and integrity analysis, data transformation and performance tuning of the load processes and databases. I was specifically engaged to help with the transition between Barclay’s POINT and Bloomberg PORT for the analysis and loading of performance, risk, scenario and analytic data (XML, Excel and CSV) into the data warehouse. SSIS jobs to load historical data (Excel), performance tuning, fixes, BAU, user support, documentation and emergency releases to live. Extensive C# code to load and validate the XML files into staging. Small team working in an Agile environment utilising Visual Studio Team Services.
ScotRail
Big Data, DataFactory v2, Azure Analysis Services, LogicApp
Architectural design and build of system for ScotRail to process and report on earnings (individual transactions) over five years. The system was built to load millions of rows and store hundreds of millions of rows (up to a billion) in the tabular cube that was optimised for fast reporting. Data load from external source using Azure SQL, Azure Data Factory v2, DataBricks (Scala) and T-SQL, tabular cube design with VS 2017 using extensive complex DAX for calculations and load in Azure Analysis Services (Logic App). Cube design optimised using partitioning married to segment size, and built to enable quick and easy integration with existing Power BI controls and dashboards. Extremely fast response time when the cube data was accessed from Power BI and fast load from the source into the cube (approximately 15 minutes – old system took 3 hours). I did the full architectural design, all of the coding, and the documentation. Solution delivered ahead of schedule.
Royal London
Data Warehousing, SSIS and T-SQL
Build and support of Data Warehouses for Royal London’s Solvency II regulatory requirements. SSIS jobs to load CSV and text files, apply data quality rules, apply business rules, transform, report on data and business quality breaches for resolution via manual adjustments (submitted via CSV), and feed into the Kimball methodology data warehouses. Design of tables and views, development of stored procedures and functions, performance tuning of code and SSIS packages. I was assigned to optimise and tune their SSIS jobs – I reduced the code count in the SSIS packages by up to 85% using code reuse and achieved a three-fold increase in package and T-SQL performance. Use of TFS and SharePoint for version control, deployment, testing and change control.
Scottish Widows Investment Partnership
SQL, Data Warehousing, SSIS and SSAS
SWIP had issues with accurate & timely reporting from OLTP systems due to data spread. Engaged to develop reporting that unified data. Scoped business requirements, designed & developed SSIS packages, tested data loading from SQL Server, web services, DataStage, FTP, Advantage and flat-files, implemented SSIS jobs to push data through the Kimball methodology data warehouse & marts to the SSAS cubes and made the cubes accessible via Excel. Succeeded in delivering reporting that enabled SWIP to be aware of their portfolio position from a single source.
Weir
SQL, SSAS, SSIS, SSRS and Data Warehousing
Weir purchased a custom solution using SQL Server 2014, SSIS & SSAS to load data from Excel into a data warehouse, data marts & cubes to unify external invoice data. Engaged to optimise functionality to include internal invoicing: new SSIS feeds, SSAS cubes and dashboard (SSRS). Scoped requirements, designed & built SQL database & data warehouse; developed ASP.Net front end to upload data; built new SSIS jobs to load data to the Kimball methodology data warehouse, data marts & SSAS cubes; designed and built cubes and SSIS reports. Succeeded in delivering unified global invoicing data across the Group’s 60+ international companies together with critical KPI data enabling substantial savings to be achieved for the purchasing managers.
Ignis Asset Management
C#, SQL, SSIS, ASP.Net, Finance and Complex Algorithm
Ignis purchased an application to assist decision making for the investment of a £30bn fund portfolio. Engaged to address issues with the algorithm and functionality. Liaised with Senior Trader to scope requirements, designed functional specification, developed system in ASP.Net & SQL Server, designed and built SSIS packages to extract data from multiple sources into a unified database, communicated progress to build business value and improved BAU through knowledge transfer. Succeeded in clearing issues & extending functionality to enable robust business critical decision making.
Weir
ETL MDX – WPF C# - Excel VBA: Surfacing of dashboards to iOS/Android
Senior executives wished to view TM1 sourced reports, critical KPI data and dashboard views on mobile devices: iPad, iPhone, Android and Surface Pro. I liaised with senior executives and designed a WPF/C#/Excel/DAX solution that extracted data from TM1 (92 Turbo Integrator ETL data feeds containing approximately 8,000 lines of code and 1,000 MDX queries), generated complex multi-layer charts in Excel using VBA, converted the Excel to HTML injecting Javascript to link within and to other surfaced reports fully supporting touch gestures, and delivered to mobile devices via one or more of: web site, OneDrive and Dropbox. The system was designed to handle off-line use and provided high quality graphical reports that could be fully explored via touch gestures including drill-down to PDF data packs. The solution was delivered on-time and to budget, and was well received by the Weir directors and senior executives.
Scottish Widows Investment Partnership
SSIS, SQL Server, ThinkFolio & XEC
SWIP used ThinkFolio and XEC (XIP Enterprise Compliance) financial applications with ETL feeds from organisations, including Bloomberg, Fitch & Barclays. Engaged to provide front end support and solution architecture. Liaised with the business to capture requirements, designed & built feeds in SSIS, tracked data failures and processed the data through SQL Server into the Thinkfolio & XEC databases. Succeeded in ensuring the accuracy & timely arrival of data into these systems which were essential for high performing daily operations. Control of key automated batch jobs that were scheduled to load data including full manual intervention to address data load failures.
Glasgow Caledonian University
SSRS, SSIS, T-SQL and Data Warehousing
GCU had a range of reports built for their student administration system that had data performance and system locking issues (due to concurrent queries). Engaged to replace existing SSRS Developers & address issues. Liaised with senior managers across finance, policy and planning, recruitment, graduations, course leaders, etc. to scope requirements and identify formula for critical KPIs, implemented database replication of key tables, moved reporting onto the replicated instance, reworked all existing reports, removed embedded SQL from SSIS reports and replaced functionality with optimised stored procedures and table returning functions. Developed a suite of T-SQL stored procedures and functions to unify the provision of data to the reports and to the OBIEE data warehouse and marts. Succeeded in developing a suite of dynamic reports (>100) aligned to business needs and over 50 T-SQL algorithms to populate the data warehouse.
Scottish Widows
SQL Server Performance Tuning (DB & SQL)
Scottish Widows internally developed and deployed a Windows & SQL Server based commercial banking application. I was specifically engaged to address database and application performance, and user issues including poor user perception. Worked with users to identify areas of concern, optimised and performance tuned the database and T-SQL queries, implemented caching and re-worked front-end screens. Succeeded in delivering an eleven-fold increase in the performance of the application (on the same hardware and with no reduction in functionality) and enhanced user satisfaction across the organisation.
Glasgow Caledonian University
SSIS, SQL Server and Oracle
GCU purchased a student administration system with an SQL Server database that replaced a system using Oracle. Engaged to transfer & transform data from Oracle to SQL Server. Liaised with senior managers to scope requirements, performed data checking & statistical analysis, developed SSIS packages to load data from Oracle, transformed & loaded data into SQL Server and developed a package with custom VB.Net to byte transform picture files into JPG format. Succeeded in delivering this previously failing project to demanding deadlines.