Scroll To View More
Jeffrey Zhao, SQL Developer in Seattle, WA, United States
Jeffrey Zhao

SQL Developer in Seattle, WA, United States

Member since February 24, 2018
Jeffrey is now available for hire
Jeffrey Zhao, SQL Developer in Seattle, WA, United States
Jeffrey Zhao

SQL Developer in Seattle, WA, United States

Member since February 24, 2018
As a senior professional with over 15 years working in the IT field, Jeffrey has in-depth technical knowledge and hands-on experience in the design and implementation of BI, data warehouses, ETL solutions, and data pipelines to satisfy challenging requirements. He's an expert in flexibly customizing ETL solutions with a variety of BI tools/coding languages such as Python, SQL, PostgreSQL, SSIS/SSAS, OBIEE/OBIA, Informatica, Java, and more.
Jeffrey is now available for hire

Portfolio

  • Sermo
    Python, Jupyter Notebook, Ruby, Clojure, Amazon Web Services (AWS)...
  • Blue Cross
    Tableau 9/10, SSIS/SSAS, PowerDesigner
  • Absolute Security, Inc.
    SQL Server 2016, SSIS/SSAS/SSRS, Dell Boomi, NetSuite, Salesforce, Java...

Experience

  • SQL, 15 years
  • Java, 10 years
  • Informatica ETL, 5 years
  • Python, 5 years
  • Hadoop, 4 years
  • Tableau, 4 years
  • Spark, 1 year
  • Django, 1 year
Seattle, WA, United States

Availability

Part-time

Preferred Environment

Linux, Eclipse, Jupyter Notebook, Informatica

The most amazing...

...thing I’ve built was an ETL framework for a highly custom Java app which was used to load data from multiple source systems to an SAS dataset.

Employment

  • Senior Data Engineer

    2017 - PRESENT
    Sermo
    • Designed and implemented a drug-rating data warehouse (PostgreSQL).
    • Created pipelines and ETL processes with Python from multiple source systems (PostgreSQL, SQL Server, event stream, and logs) into a drug-rating data warehouse.
    • Designed and implemented a quarterly report with Jupyter Notebook.
    • Designed and implemented ETL processes with AWS data pipeline to load data from S3 into data warehouse.
    • Worked with data scientists to ensure proper understanding and provide more raw data as needed.
    • Provided suggestions for improvements for source system data capturing.
    • Implemented PySpark scripts to analyze huge server login attempt log on a spark cluster and use the pattern found to block malicious login.
    • Created monitoring functions with AWS Lambda to automatically detect changes of files in S3 folders.
    Technologies: Python, Jupyter Notebook, Ruby, Clojure, Amazon Web Services (AWS), PostgreSQL, Sisense
  • Senior BI/ETL Consultant

    2017 - 2017
    Blue Cross
    • Provided analytical visualizations based on feedback and general direction from the business stakeholders.
    • Upgraded Tableau from version 9 to version 10 and set up the Tableau production environment for better performance and customized outlook (PBC logo, icon, and more).
    • Implemented a drug ETL through SSIS to provide data for visual analytics.
    Technologies: Tableau 9/10, SSIS/SSAS, PowerDesigner
  • BI Solutions Architect

    2015 - 2017
    Absolute Security, Inc.
    • Developed a BI strategy roadmap aligned with the core business objectives.
    • Designed and implemented a new DW/BI system that integrated data from different source system including cloud data sources such as NetSuite and Salesforce, a local finance database, and a data lake.
    • Designed and implemented Tableau workbooks that sourced Salesforce data.
    • Designed an ETL framework which involved historical and delta loading, SCDs, batch processes, performance analyses, data quality, and profiling.
    Technologies: SQL Server 2016, SSIS/SSAS/SSRS, Dell Boomi, NetSuite, Salesforce, Java, Python, PL/SQL, Tableau, PostgreSQL, Spring, Sesame Solution
  • ETL Consultant

    2015 - 2015
    BC Hydro
    • Created and supported an ETL solution; including the design, build/coding, unit testing, and review for SMD reporting modules from/to PostgreSQL/Greenplum databases.
    • Implemented a Hadoop data lake to help resolve data retention for large data sets and complex issues to provide users with access to large data sets for business intelligence/analytic purposes.
    Technologies: Java, JSF, PostgreSQL, Oracle PL/SQL, Greenplum, PostgreSQL, Informatica, Hadoop, Spring Batch
  • Senior BI Consultant

    2013 - 2015
    Wipro
    • Led the technical side in the implementation of data warehouse solutions for various ATCO member companies covering data modeling/architecture, data warehouse standards, data quality management, data provisioning and cleansing, data analysis, data migration, ETL process/specification design/implementation, BI best practice promotion, and the design of the BI infrastructure.
    • Worked with the business managers and end users to understand and collect business requirements.
    • Customized an Oracle PO ETL.
    • Designed and implemented customized ETL data models and processes for an Oracle purchase order performance module to replace seeded OBIA PO module and deployed to production.
    • Managed and wrote the reports build/coding and demo using BI Answers and BI Publisher.
    • Monitored Production ETL processes; helped to diagnose and resolved pre-existing mapping or performance issues and review designs for optimal ETL load times and report execution times.
    • Worked with the DBA team to troubleshoot issues between development, testing, and production.
    • Implemented and ensured adherence to change control/migration procedures (from development to testing to production).
    • Worked on invoice projects and a subproject (AE invoices and Hyperion planning project).
    • Collected requirements from the client directly and prepared requirement specification documents for final review and approval.
    • Developed a project plan and time estimation with the project manager.
    • Prepared architect design documents and the initial proof of concept for new solutions.
    • Coached junior developers.
    • Designed and implemented BI object security at the category level to allow users to access different category folders based on individual roles.
    • Architected and redesigned the infrastructure layer of the invoice project hosting multiple member companies (distribution, transmission, and more) on BI server where each member company owns a separate invoice ETL loading process while the presentation layer can be hosted on the same OBIEE front-end website. The final implementation significantly improved the performance (reduce the ETL executing time from 45 minutes per day to 2 minutes per day in average) and increased the capacity of the support team to be able to hold more projects for other member companies.
    • Architected and designed BI Publisher to access to various data sources so that the reports created in BI Publisher can be embedded into company content management system to allow user access to all the information from just one spot.
    • Designed and developed a star schema model in OBIEE for a budget project allowing users to drill down and analyze info through multiple dimensions/levels such as time, department, category, project, task, and more.
    • Provided data models and data maps (extract, transform and load analysis) of the data mart and feeder systems in the aggregation effort. Also reversed engineered the data structures of the reporting system.
    • Designed and developed the OBIEE answers analysis and dashboards including, graphs and charts.
    • Directed a team in developing a new budget fact star schema and join with existing dimensions of actual (cost center, service, period, and more) with OBIEE admin tool so that actual and budget data can be analyzed side by side at the front-end analysis tool (answer).
    • Worked on a BI reporting integration project where I collected user requirements via a JAD session to understand business expectations of a new powerful reporting tool.
    • Prepared requirement specification documents and wrote architect design documents.
    • Evaluated the BI reporting tools and delivered proof of concept projects.
    • Designed and implemented the BI Publisher Security Model architecture and integrated BI Publisher with a role-based security model into a WebLogic security model using an external LDAP server for authentication.
    • Managed BI-object security at different levels (row level, column level, and object level) in the OBIEE repository through initial blocks and customized tables.
    • Configured the data source connections in BI Publisher to authorize users who have access to different resources (Oracle, SQL Server, OBIEE analytics, Excel, flat file in certain folders with limited access, XML feed, and more) to generate and consume reports.
    • Architected and designed BI Publisher so that a user can access various data sources so that reports created in BI Publisher can be embed objects into the company content management system to allow user to access to information from one site.
    • Designed and implemented a metadata repository providing a centralized information source for the data models, data maps, processes, documents, contact lists, project calendars and issues.
    • Designed and developed self-service objects in Oracle Discoverer to allow business users create reports by themselves.
    • Developed SQLs to query and manage Informatica metadata.
    • Created a Java application to integrate these SQLs for functions like object tracing and object impact analysis.
    • Designed and implemented a data-quality-management framework as the foundation for CIS prototype project.
    • Designed the CIS data model, a metadata repository providing mappings between all logical models, physical models, systems, databases, processes, procedures, organizations, people, projects, and activities.
    • Managed and was responsible for the ETL and reporting solution design, build/coding, unit testing, and review for CIS reporting modules and Oracle EBS modules for financial, project, and procurement analytics.
    • Enhanced, developed, and tested the BI reports/dashboards and RPD BI layer.
    • Developed an OBIEE merge procedure and standard for the team to standardize the workflow of OBIEE repository migration with lower cost and better efficiency.
    • Promoted BI standards and best practice across the supporting team and provided training for the team.
    • Designed and implemented SQL scripts to test daily incremental loading ETL workflow of OBIA.OBIA and OBIEE Upgrade.
    • Assisted in the migration of OBIA from 7.9.6.1 to 7.9.6.4 and OBIEE 10g to 11g.
    • Designed auto-testing solutions for the upgrading OBIA system with a high efficiency and in a low-cost manner.
    • Performed an analysis of the latest patches of OBIA after an upgrade and applied mandatory patches partially based on business requirement.
    • Handled the direct energy transition data migration by designing and implementing ETL processes to extract and convert a huge volume of records from the mainframe system to SAP system with high performance within a limited time-window, based on business requirements.
    Technologies: Java, JSF, TOAD, OBIEE (Procurement, Finance Analytics)/OBIA, Informatica, PL/SQL, DAC, Hyperion, Oracle EBS
  • Consultant

    2013 - 2013
    Telus
    • Built the user UI using HTML5 on JBoss AS 6.0.
    • Created single-page applications using HTML5, jQuery, and JSON.
    • Invoked RESTful endpoints and manipulated page content by using JavaScript frameworks.
    • Developed and implemented the business logic component in middle tier using the Spring framework and the Hibernate framework for back-end persistence.
    • Used Agile methodology to develop and deliver the project.
    • Designed the reporting models and dashboard for AMS with JasperReport.
    Technologies: Java, jQuery, JasperReport, Spring, Hibernate, Oracle
  • PL/SQL Consultant

    2011 - 2013
    Government of Alberta
    • Developed an interactive data query system (a web-based application to allow a user to join datasets from different source databases and generate relational or dimensional reports easily).
    • Designed and implemented an interactive data query system framework through Spring, JSF, and Hibernate;
    • Built a data source access module including relational database assess such as Oracle, SQL Server, and flat file access such as TXT or CSV files.
    • Implemented a local reference table module that allows a user to parse and load flat files such as text, CSV, XML, or JSON dataset to local data schema created by configuration.
    • Built an e-inspection and e-collision system.
    • Designed the data model of an e-inspection reporting system with Oracle Designer.
    • Designed and implemented ETL process with Informatica.
    • Developed and designed the major reports of e-inspection with OBIEE and BI publisher and mentored less experienced developers.
    • Built OBIEE repositories for three layers.
    • Created measures and level-based measures in the business model.
    • Built a dimension hierarchy in the business model layer.
    • Implemented slowly changing dimensions in different mappings as per requirements.
    • Designed and implemented major reports of e-inspection with Oracle report builder and a PL/SQL package.
    Technologies: Spring, JSF, Hibernate, Tomcat, OBIEE/BI Publisher, Oracle Report, PL/SQL
  • ETL/Reporting Consultant

    2011 - 2013
    Flint Energy Services Ltd
    • Designed data models and implemented SSIS/SSRS solutions in the SmartPlant Material system.
    • Worked with DBA in the configuration and administration of designing and developing the ETL process of a data mart project utilizing the Microsoft BI solution;
    • Performed ETL design to ensure that the data transformations, data cleansing, and exception reporting were created according to specifications.
    • Provided training and guidance to the internal technical staff.
    Technologies: Crystal Report, SSAS, SSIS, SSRS, SQL
  • System Analyst

    2010 - 2011
    Blue Cross
    • Implemented manage enrollment group role and enrollment group report pages.
    • Designed and implemented create enrollment package pages.
    Technologies: ADF, JSF, JDeveloper, CSS, Oracle 10g, PL/SQL, TOAD, JUnit
  • ETL Consultant

    2006 - 2009
    CGI
    • Worked for Alberta Finance in the role of an Oracle Data Warehouse lead developer.
    • Designed, developed, and implemented Oracle 10g Data Warehouse for Alberta Finance (a strategic information reporting system). SIRS, the very first BI project of Alberta Finance all the ETL processes had to load data from an Oracle transaction system to an Oracle data warehouse and create derived OLAP cubes. The presentation layer includes both OLAP reports and relational reports and utilizes Oracle portal to display them.
    • Designed and implemented a summary reports module with Jasper Report component and DB2 for a mental health reporting system on J2EE Application Server (Websphere 6.1); done on behalf of Alberta Health & Wellness.
    • Created standard BI modules with Java against Db2 database based on non-functional requirement. Defined the conventions, standards, and migration strategy with best practice and mature BI methodology for BI project in AH&W.
    • Designed and implemented ETL Foundation components which provides a framework for executing SAS (and non-SAS) ETL processes including functions and capabilities such as managing the execution of load streams and jobs, collection of command execution metadata (Foundation Operational Metadata) which includes overall stream execution status and error/warning details, common error handling and common recovery via automated rollback and more.
    • Designed, developed and implemented ACCIS's (Alberta Continuing Care Information System) initial release involving Spring, Hibernate, Struts, and EJBs on a J2EE Application Server (WebSphere 6.1) against a Db2 database in an AIX environment.
    • Interpreted business needs and translated them into data and processes that could be automated, including load processes and file maintenance processes.
    • Designed, developed, and implemented the back-end and batch processes using Java and XML.
    • Designed parsing components with Perl script to extract and convert specific format data into relational records and load them into DB2 database; done for AH&W BI pharmaceutical information network usage activity reporting.
    • Designed and implemented ETL processes with SAS Data Integration Studio. Integrated all ETL processed with ETL foundation framework to load data into BIE data warehouse. All done for AH&W BI information products.
    • Provisioned changes to the DSR System in order to meet the business, technical, and operational requirements; done on behalf of AH&W Delivery Site Registry System (DSR).
    Technologies: Oracle Business Intelligent Suite, Oracle Warehouse Builder, Java, Oracle Discoverer, Struts, Java, Tomcat, Hibernate, JasperReport, IBM WebSphere, DB2, AIX
  • System Designer | Developer

    2006 - 2006
    ZediT, Inc.
    • Designed and developed technology procedures.
    • Addressed problems by gathering and translating user requirements into documented data mapping and acquisition strategies for the data mart project and prepared specifications for programmers to follow based on user requirements.
    • Worked with developers in the configuration and administration of designing and developing the ETL process of a data mart project utilizing the Microsoft BI solution.
    • Created and designed the data mart fact and dimension tables to create multi-dimension cubes for analyzing and provided ongoing technical support for the BI implementation.
    Technologies: C#, SSAS, SSIS, SSRS, SQL
  • Software Engineer

    2004 - 2006
    Betcorp, Inc.
    • Designed the application architecture for a back-end management system used by customer care department to track services provided to customers.
    • Designed data models, process models, and workflow models of existing processes and the proposed solutions using best practice techniques and industry standard process modeling tools.
    Technologies: C#, SSAS, SSIS, SSRS, SQL
  • Java/ETL Developer

    2003 - 2003
    eHealthInsurance, Inc.
    • Designed and developed a revenue recognition system project using Java, Tomcat, JSP, Java Servlet, and Oracle.
    • Designed and implemented ETL components for an RRS system in Java which could automatically upload all kinds of data formats (text, Access, Excel to relational database, and vice versa).
    Technologies: Java, SQL, Strut, Tomcat, Oracle
  • Java Developer

    1999 - 2002
    Alcatel Network Support Systems, Inc.
    • Developed a telecom management information system.
    • Implemented a mobile communication expenses calculation system.
    Technologies: Oracle, Java

Experience

  • Online Donation Analysis Tool (Development)

    Per the client's request, I built an online donation analysis tool which will extract donation information from multiple donation sites and present the aggregated data in AWS Redshift through Tableau. The ETL processes are created through AWS Lambda function which saves client's great efforts and costs of setting up and operating a separate ETL server.

  • Absolute Data Warehouse (Development)

    Absolute's data warehouse had to be replaced with a new DW system as Absolute’s ERP system and CRM system were being moved to the cloud.

  • ETL Foundation Components (Development)

    I designed and implemented ETL Foundation components which provides a framework for executing SAS (and non-SAS) ETL processes including functions and capabilities such as managing the execution of load streams and jobs, collection of command execution metadata (foundation operational metadata) which includes overall stream execution status and error/warning details, common error handling and common recovery via automated rollback, and more.

  • OBIA and OBIEE Upgrade (Development)

    I completed the migration of OBIA from 7.9.6.1 to 7.9.6.4 and OBIEE 10g to 11g.

  • Informatica Metadata Analysis (Other amazing things)

    I developed SQLs to query and manage Informatica metadata; implemented the Java application to integrate these SQLs for functions like lineage analysis and impact analysis.

Skills

  • Languages

    SQL, Python, Java
  • Tools

    SQL Server BI, Oracle Business Intelligence Enterprise Edition 11g (OBIEE), Informatica ETL, Tableau, SAS Data Integration (DI) Studio, AWS CLI, Oracle Designer, Oracle Warehouse Builder (OWB), Erwin, IBM Cognos
  • Storage

    PostgreSQL, SQL Server Integration Services (SSIS), AWS Data Pipeline Service, AWS S3, Dell Boomi, SAS SQL
  • Paradigms

    Agile Software Development
  • Platforms

    AWS Lambda, Amazon Web Services (AWS), Oracle Data Integrator (ODI), Salesforce
  • Frameworks

    Hadoop, Spark, Django
  • Libraries/APIs

    Salesforce API, jQuery
  • Other

    NetSuite

Education

  • Certificate in Applied Data Science with Python
    2017 - 2018
    University of Michigan - Ann Arbor, MI, USA
  • Master's degree in Engineering Management
    2006 - 2009
    University of Alberta - Edmonton, Canada
  • Certificate in Data Warehouse
    2006 - 2006
    Kimball University - Seattle, WA, USA
  • Bachlor's degree in Electronic Engineering
    1995 - 1999
    Shanghai Jiaotong University - Shanghai, China
Certifications
  • Applied Data Science with Python
    APRIL 2018 - PRESENT
    University of Michigan via Coursera
  • ITIL Foundation Certificate in IT Service Management
    JUNE 2015 - PRESENT
    CCTA
  • Oracle Business Intelligence Foundation Certified Implementation Specialist
    AUGUST 2011 - PRESENT
    Oracle University
  • CFA (Chartered Financial Analyst) Level I
    SEPTEMBER 2010 - SEPTEMBER 2017
    CFA Institute
  • Oracle Certified Professional Database Admin (Oracle 10g OCP)
    MAY 2007 - PRESENT
    Oracle University
  • Microsoft Certified Technology Specialist for SQL Server
    APRIL 2005 - PRESENT
    Microsoft
  • Sun Microsystems Certified Java Programmer
    AUGUST 2002 - PRESENT
    Sun
I really like this profile
Share it with others