Pedro J M Fidalgo, Data Warehouse Design Developer in Lisbon, Portugal
Pedro J M Fidalgo

Data Warehouse Design Developer in Lisbon, Portugal

Member since November 6, 2015
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.
Pedro is now available for hire

Portfolio

Experience

Location

Lisbon, Portugal

Availability

Part-time

Preferred Environment

ETL, Microsoft Excel, Microsoft Access, C#, Visual Basic for Applications (VBA), SQL, Oracle SQL

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.

Employment

  • Software Developer | DWH Support

    2013 - PRESENT
    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 SSIS (social security information system) has over than 12,000 final users distributed all over the country. 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 that will be divided into five new schemas. The development uses DataStage 9.1 Parallel Server, SQL, and PL/SQL.
    Technologies: Exadata, Oracle, Jakarta EE, Java, Visual Basic for Applications (VBA), Bugzilla, Subversion (SVN), T-SQL, Microsoft SQL Server, PL/SQL
  • Programmer

    1998 - PRESENT
    Freelance Work
    • 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).
    Technologies: Oracle, SQL Server Integration Services (SSIS), Microsoft SQL Server, C#, T-SQL, PL/SQL, SQL, Visual Basic for Applications (VBA)
  • Information Manager

    2001 - 2013
    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.
    Technologies: BusinessObjects, Datastage, Erwin, MicroStrategy, T-SQL, Visual Basic for Applications (VBA), Microsoft SQL Server, Unix, PL/SQL, Oracle
  • IT Manager

    2000 - 2001
    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.
    Technologies: SAP BusinessObjects (BO), ASP, Visual Basic 6 (VB6), Microsoft SQL Server
  • System Administrator | Programmer

    1998 - 2000
    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.
    Technologies: Windows Server 2000, Microsoft SQL Server, Novell NetWare, Microsoft Access, Delphi, Visual Basic
  • Programmer

    1997 - 1998
    Euro Marketing
    • Developed a call center database using Microsoft Access.
    • Performed data analysis using Microsoft Excel, VBA, and SQL.
    Technologies: Microsoft Excel, Visual Basic for Applications (VBA), Microsoft Access

Experience

  • Tolerance Management Engineering Software

    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.

  • Insurance Analytics Solution
    https://kingstonecompanies.com/corporate-profile/default.aspx

    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.

  • Full Inventory Management Solution
    https://www.arcaindustries.com/

    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.

  • Production Management Solution
    https://www.brandedaustralia.com.au/

    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.

  • Enquiry Database Solution
    https://www.dgrv.coop/

    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.

  • Acute Pancreatitis Inquiry Database Solution
    https://www.auckland.ac.nz/en.html

    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.

  • Healthcare Professionals' Bonus Tool
    https://www.mountsinai.org/

    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 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 Process
    https://www.franklinparkllc.com/

    Developed a full 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 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

    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.

  • Microsoft Access VBA Development for a Work Order Scheduler Planner

    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.

  • Oracle to SQL Server conversion consulting of an healthcare software database

    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.

  • Quality Management Reports Automation
    https://www.telemedicineclinic.com/

    I developed a full quality reports production automation process. The process uses Excel and VBA automation processes to:

    • 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 Markets
    https://www.iqvia.com/

    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.

Skills

  • Languages

    Visual Basic 6 (VB6), Visual Basic for Applications (VBA), Transact-SQL, SQL, C#, T-SQL, Visual Basic, Delphi, Java, C#.NET, Excel VBA
  • Frameworks

    ADO.NET, .NET, ASP
  • Tools

    Microsoft Excel, Toad, Microsoft Access, Microsoft Visual Studio, Microsoft Project, Erwin, Subversion (SVN), Bugzilla, Novell NetWare, TortoiseSVN, Eclipse IDE
  • Paradigms

    ETL Implementation & Design, Dimensional Modeling, Data Science, Object-oriented Programming (OOP), ETL, Database Design
  • Platforms

    Oracle, Jakarta EE, Unix, NetBeans
  • Storage

    Relational Databases, Oracle SQL, Oracle PL/SQL, SQL Developer, Microsoft SQL Server, SQL Performance, Oracle 11g, SQL Server Integration Services (SSIS), Datastage, PL/SQL, Exadata, SQL Server Data Tools (SSDT), SQL Server 2017
  • Other

    Data Warehousing, Data Warehouse Design, Microsoft Data Transformation Services (now SSIS), SAP BusinessObjects (BO), BusinessObjects, Windows Server 2000, MicroStrategy, SQL Server 2019, SSIS Custom Components, Analytics, Applied Mathematics, Economics, Mathematical Simulation, Statistics, Mathematics, Optimization Algorithms, Access VBA, Powerpoint VBA

Education

  • Master's Degree in Applied Mathematics
    1996 - 2005
    Technical University of Lisbon - ISEG - Lisbon, Portugal
  • Coursework toward Master's Degree in Economics
    1992 - 1996
    Catholic University - Lisbon, Portugal

To view more profiles

Join Toptal
Share it with others