Pedro J M Fidalgo
Verified Expert in Engineering
Data Warehouse Design Developer
Pedro is a data warehouse & BI pro over multi-terabyte based systems (Oracle, SQL Server, DataStage, SQL, PL/SQL, T-SQL, SSIS, MicroStrategy, & BusinessObjects). He's also an experienced database designer/developer (Relational 3NF or DWH Dimensions, Kimball). He has worked with various tech: RAD (VBA Access, C#, VB6, Excel, Java), report/dashboard, complex process automation, data analysis & enrichment, SQL tuning, and algorithms.
ETL, Microsoft Excel, Microsoft Access, C#, Visual Basic for Applications (VBA), SQL, Oracle SQL, Data Warehousing, Data Warehouse Design, Databases, Excel Reporting, Database Modeling, SQL Server 2015
The most amazing...
...thing I created was a full 2 meter wide glider model from scratch when I was 13 years old; this is like programming, each piece in the correct position.
Software Developer | DWH Support
Institute of Informatics, Government of Portugal
- Refurbished and optimized a 63-hour-long ETL data mart chain to an IBM DataStage Parallel Server 9.1. Reduced the chain-critical path to 23 hours.
- Developed several PL/SQL Oracle packages regarding the Portuguese annual financial statement political measures. The measures are mostly related to a national wage declaration system that all companies must adopt.
- Implemented several new operations aimed at the national social security system. The operations were developed towards a fat Swing MVC client using Java and running business rules layer over Oracle Sun One application server.
- Optimized and developed several data analysis and ETL processes using VBA, C#, DataStage, SSIS, and PL/SQL, mostly over Oracle and SQL Server.
- Developed the full data migration process regarding two new national social security applications (household management and income management). The process includes three full Oracle schemas divided into five new schemas.
- Developed Several Oracle and SQL Server PLSQL/TSQL data quality and migration processes that handle several million row-based datasets.
- Developed Layoff benefits Oracle / PLSQL processes related to the COVID-19 pandemic to support Portuguese companies and their employees.
- Developed a high number of automation processes, databases, data analysis processes, and software applications for companies like Sperry Van Ness (Svn.com), Metanoia, Inc. (Metanoia-inc.com/index.html), Kingstone Insurance Company (Kingstoneinsurance.com), American Forest Management, Inc. (Americanforestmanagement.com), Quidel (Quidel.com), Telemedicine Clinic (Telemedicineclinic.com), Survalyzer (Survalyzer.ch/en), Branded (Brandedaustralia.com.au/default.asp), Univesity of Aukland (Auckland.ac.nz/en.html), iTrade USA (iTradeusa.com), BI-works (Bi-worx.com), Creative Dynamic, Inc. (Creativedynamicinc.com), ValYou More, Inc., FD4Cast (Fd4cast.com), CSU Industries (Csuindustries.com), Verdico Solutions (Verdicos.com/index.php), and IRISOS (Irisos.ch).
Kiser + Vogrin Design, LLC
- Developed a full automation process to parse outputs produced by an AutoCAD application. Once parsed, a set of standard management reports were produced so management decisions can be sustained and supported.
- Followed an agile approach on a constant back-and-forth requirement adjusting and version testing in the development process.
- Reduced the time spent in producing reports to a few seconds.
Institute of Informatics, Government of Portugal
- Participated in all phases of the development of the National Wage Declaration data mart project (requirements definition, data analysis, dimensional modeling, ETL development, metadata development, testing, report development). Built the main fact table that holds more than 3 billion records.
- Aided in all phases of the development of the National Identification and Qualification data mart project (requirements definition, data analysis, dimensional modeling, ETL development, metadata development, testing, report development).
- Worked in all phases of the development of the National Public Insurance data mart project (requirements definition, data analysis, dimensional modeling, ETL development, metadata development, testing, report development).
- Participated in all phases of the development of the Unemployment Benefits data mart project (requirements definition, data analysis, dimensional modeling, ETL development, metadata development, testing, report development).
- Helped in all phases of the development of other minor data mart projects (requirements definition, data analysis, dimensional modelling, ETL development, metadata development, testing, report development).
- Participated in all phases of the development of the Current Account data mart project (requirements definition, data analysis, dimensional modeling, ETL development, metadata development, testing, report development). Main partitioned fact table over than 3,5 billion records;
- Worked as a team player in the development of the anti-fraud platform system. Worked on the requirements definition, data sources analysis, ETL development, dimensional modeling, FICO business rules definition OOP development, Java multi-thread orchestrator development, metadata development, and report development.
- Developed a high-number of data analysis and data transformation processes using PL/SQL, IBM DataStage, VBA, C#, SSIS, T-SQL, and Access.
- Developed a high number of reports or reporting solutions using Microsoft Access, MicroStrategy, Business Objects, Excel, PowerPoint (including VBA).
- Built a large number of automation processes using mostly VBA combined with SQL, PL/SQL, T-SQL, C#, and SSIS.
- Refurbished several processes mostly tuning SQL and using more recent ETL tools or database features.
ParaRede Electronic Business Solutions — SA
- Developed MIS systems, ETL processes with VB6, and reports were created using BusinessObjects (desktop and web intelligence).
- Created the entire intranet database model;
- Worked on internal SAP project management regarding the MIS integration.
System Administrator | Programmer
General Electric Capital ITS
- Worked on SQL Server, Lotus Notes, and Windows Server administration.
- Developed process automation aimed at the financial department.
- Worked on EDI software development using Delphi 3 and SQL Server.
- Developed Microsoft Access databases.
- Developed a call center database using Microsoft Access.
- Performed data analysis using Microsoft Excel, VBA, and SQL.
Tolerance Management Engineering Softwarehttps://tolerance360.tech/
• 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.
Insurance Analytics Solutionhttps://kingstonecompanies.com/corporate-profile/default.aspx
Full Inventory Management Solutionhttps://www.arcaindustries.com/
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.
Production Management Solutionhttps://www.brandedaustralia.com.au/
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.
Enquiry Database Solutionhttps://www.dgrv.coop/
I developed a full farmer inquiry database solution that consolidates answers to over 100 questions and produces several data analyses, statistics, and reports.
Acute Pancreatitis Inquiry Database Solutionhttps://www.auckland.ac.nz/en.html
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.
Healthcare Professionals' Bonus Toolhttps://www.mountsinai.org/
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 full 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.
Outlook Contacts Management ETL Processhttps://www.franklinparkllc.com/
The process uses a properly developed SQL Server back-end database that imported all contacts from the Exchange instances.
Once imported, several clean-up and validation processes are running against each contact (e.g., checking the number of e-mail messages sent and received during a specific period of time), validating the e-mail addresses using proper web-services 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.
Interactive Dashboard Based on System Dynamics
• 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.
Microsoft Access VBA Development for a Work Order Scheduler Planner
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.
Oracle to SQL Server conversion consulting of an healthcare software database
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.
Quality Management Reports Automationhttps://www.telemedicineclinic.com/
• 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.
Excel Based Reporting Tool, Consolidating Monthly Inputs from 50 Marketshttps://www.iqvia.com/
• 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.
Prospero Suite - Custom Reporting Toolhttps://www.finartis.com/
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.
SQL Server, Access Client and Excel Reports Meeting Management Solution
Visual Basic 6 (VB6), Visual Basic for Applications (VBA), SQL, Excel VBA, C#, C#.NET, T-SQL (Transact-SQL), Visual Basic, Delphi, Java, Python
ADO.NET, .NET, .NET 4, ASP
Microsoft Excel, Toad, Microsoft Access, Excel 2016, Microsoft Visual Studio, Microsoft Project, Erwin, Microsoft PowerPoint, Microsoft Power BI, Microsoft Word, Subversion (SVN), Bugzilla, Novell NetWare, TortoiseSVN, Eclipse IDE
ETL, ETL Implementation & Design, Dimensional Modeling, Database Design, Business Intelligence (BI), Database Development, OLAP, Object-oriented Programming (OOP), Data Science
Oracle, Jakarta EE, Unix, NetBeans
PL/SQL, Relational Databases, Oracle SQL, Oracle PL/SQL, Microsoft SQL Server, SQL Performance, Oracle 11g, SQL Server Integration Services (SSIS), Datastage, SQL Server Data Tools (SSDT), SQL Server 2017, DB, Database Architecture, Data Pipelines, SQL Server 2012, Databases, Database Modeling, OLTP, MySQL, PostgreSQL, Exadata
Data Warehousing, Data Warehouse Design, Microsoft Data Transformation Services (now SSIS), SQL Server 2019, Analytics, Applied Mathematics, Simulations, Statistics, Mathematics, Optimization Algorithms, Access VBA, Data Analytics, Data Visualization, Back-end, Query Composition, Performance Tuning, Query Optimization, Data Engineering, Data Migration, Data Analysis, Data Extraction, Excel Reporting, SQL Server 2015, CSV, CSV Export, Relational Data Mapping, Excel Macros, Macros, Invoicing, Algorithms, PL/SQL Tuning, Optimization, Database Optimization, SAP BusinessObjects (BO), SSIS Custom Components, Economics, Finance, Dashboards, Monte Carlo Simulations, Windows Server 2000, MicroStrategy, Financials, Financial Modeling
Master's Degree in Applied Mathematics
Technical University of Lisbon - ISEG - Lisbon, Portugal
Coursework Toward Master's Degree in Economics
Catholic University - Lisbon, Portugal