Pedro J M Fidalgo

Developed a full Tolerance Engineering software solution using C#.NET over Excel engine. The solution works as a full Excel add-in and provides the following features:

• Support back-end database SQL Server and Azure;
• Multi-user solution, user profiles, and different permissions;
• Parts design management;
• Parts stack building, part dimension management, stack optimization using several methods like RSS;
• Parts dimension loading from Excel files or pure Excel ranges using an asynchronous ETL process;
• BOM management and hierarchy loading from Excel or worksheet ranges selection;
• Virtual parts creation, management and full virtual parts hierarchy relation creation (virtual parts inside virtual parts);
• Virtual dimensions;
• High level of data analysis reports related to Stacks, Virtual Parts, BOM, among others;
• Full preferences setting that even stores windows sizes and positioning by the user;
• Tasks workflow management;
• First Article Inspection management;
• Monte-Carlo simulation;
• Impact analysis simulation regarding parts: the software allows users to preview the impact a new part version will have on all existing stacks and virtual parts.

For Kingstone company, I developed a full policies analytical processing tool that received a huge set of insure quote input CSV files regarding Rhode Island, New York, New Jersey, Connecticut, and Massachusetts. The full ETL automation process was fully developed using SQL Server SSIS making deep usage of C# script components—the ETL process uses a multi-thread queue engine developed using TSQL language. Users access a full reporting tool developed using Microsoft Access and VBA. The tool generates over 150 reports and analyses, automatically comparing Kingstone Insurance's position to that of the competitors on several metrics.

Developed a full inventory management solution for the company Asset Recovery Company of America (ARCA). The solution was entirely developed using SQL Server in the back end (nowadays using AWS located instance). The front end was developed using Microsoft Access.

The multi-user solution manages over 5 million part numbers. It defines the required attributes depending on the part category and sub-categories, producing many reports and analyses and importing data from Excel files. It also manages users, automatically uploads parts images to One Drive, and generates the public URL using Office 365 API calls.

A full multi-user production management solution for an Australia-based company. It is a multi-user solution whose back end was developed using SQL Server Express. I was tasked with the entire database design, triggers, procedures, development, and actual maintenance of the rich desktop front-end developed using Microsoft Access.

The solution manages orders, production schedule, pricing, sales, and stocks. It assumed the development of a high set of reports, including a dynamic report that allows users to select required fields, their dimension, and positioning, among other parameters.

A solution developed for DGRV (the German Cooperative and Raiffeisen Confederation)—the national apex organization and top-level auditing confederation of the cooperative sector in Germany that is located in Mozambique.

I developed a full farmer inquiry database solution that consolidates answers to over 100 questions and produces several data analyses, statistics, and reports.

I developed a full inquiry management solution for the University of Auckland to consolidate over 300 questions related to pancreatitis that were presented to patients.

The solution required loading the answers manually and once data was loaded, the solution I developed produced a high level of data analysis, descriptive statistics, and reports.

I developed a full Oracle-based database for Mount Sinai hospitals to store, process, and analyze Mount Sinai professionals' performance.

The data is stored in Oracle, while users interact with it using a rich Microsoft Access desktop client.

The project required the development of complex ETL processes using Oracle external tables and a complete set of Oracle PLSQL packages to transform CSV and plain text files into properly normalized database tables.

Microsoft Access client allowed users to manage main database entities' data, trigger the ETL process, execute data analysis, and generate reports.

Developed an ETL automated process to analyze, mark, and clean the full company Outlook and Exchange contacts list.
The process uses a properly developed SQL Server back-end database that imports all contacts from the Exchange instances. Once imported, several clean-up and validation processes run against each contact (e.g., checking the number of e-mail messages sent and received during a specific period), validating the e-mail addresses using proper web service providers. Once validated, the relevant contacts are marked to be deleted. The front-end Access solution allows IT administrators to validate them manually if required and automatically trigger the Exchange deletion process.

For a Ph.D. graduation, I developed a full dynamic Excel-based simulation dashboard.

• The solution has an initial parameter settings window where the user defines all population generation parameters—the number of patients, the probability of several patient attributes like the rate of diabetes or obesity.
Users will be able to define the statistical parameters for each patient like random income value, using a log-normal distribution, each patient's age using a Gaussian distribution, slow-paced patient attributes use an exponential distribution. Population generation uses inverse statistical distribution random number generation and Monte Carlo logic approach.

• For the process workflow, users can define parameters like the minimum number of doctors, the number of weeks to simulate, evaluation exam cost, accommodation daily cost, average insurance coverage, maximum working hours per day, minimum and maximum session time, and much more.
Once triggered, the patients enter the system and they will follow the possible paths. Each patient path will be affected by the associated random attributes when the full simulation population is generated.

Once finished, a full set of descriptive statistics and charts is produced.

For a Canada-based company.
The project required the development of a Microsoft Access-based tool that allows production management users to:

• Define the department's hour capacity during a specific interval period (e.g., 9 months from the current date).

• Load, using a full ETL, process the full list of orders and their details (including production steps).

• Define the expected number of resources available per week in each department.

• Trigger the forecast simulation process that uses a greedy algorithm that will start consuming the available capacity, always trying to satisfy the first incoming delivery date orders.

• The tool will produce several outcome reports that will allow management users to check and forecast if the actual installed capacity will be enough to satisfy the actual demand allowing management users to identify possible future production bottlenecks.

For a US-based client.
The project consisted of the full conversion of an entire Oracle database used as a healthcare back-end management software to a SQL Server-based one:

• The process required Oracle to SQL Server data types mapping.
• Indexes conversion.
• Oracle objects conversion to SQL Server compliant ones or Oracle replacement objects by SQL Server ones.
• All Oracle PL/SQL procedures and functions to T-SQL-based ones.
• The process tried to use ANSI SQL-based queries as much as possible as a future expansion to other databases was being considered.
• It required a full ETL data migration process from Oracle to SQL server.

I developed a full quality reports production automation process where it uses Excel and VBA automation processes. The following was achieved:

• Import base data for reports generation using an ETL process inside Excel.
• Define the charts and tables to be produced on the final Word-based set of reports.
Using a bookmarking naming logic approach, the configuration area allows users to define each of the tables and charts on the final report templates.
• Once triggered, the process generates the full quality management word file report for the selected country and office.

Developed the project for IQVIA, the UK, that required the creation of a monthly reporting process comprising 4-5 dashboards, based on inputs from 50 markets (based on the same template across markets), including Access, Excel, and Powerpoint automation based tool was developed with the following features:

• Import of monthly inputs through VBA ETL processes to feed the back-end Access repository.

• Processing of monthly analysis (4-5 dashboards) based on pre-defined outputs.

• Processing of cumulated annual analysis (4-5 dashboards) based on pre-defined outputs.

• Linkage of outputs to pre-defined Powerpoint template to populate and update data in slides based on latest loaded data and selected analysis Market.

All report and dashboard refreshing processes were automated using VBA and SQL language.

I built a complete set of financial reports and refresh processes aimed at weekly and monthly top management analysis for a Finartis client.

The process uses complex PLSQL packages to process and consolidate data. It provides an Excel layout with custom-developed ribbons and windows that allow several users to connect and trigger any required reports.

Developed a full multi-user solution using SQL Server and Microsoft Access that allows CDSA to perform a much more accurate annual meeting scheduling process. The solution loads raw data using proper ETL processes and performs a lot of data validations and feedback reports on the fly so overlapping periods, priorities, or other relevant details can be considered when assigning registrants to legislators.