SQL Developer in Seattle, WA, United States
Senior Data Engineer2017 - PRESENTSermo
Technologies: Python, Jupyter Notebook, Ruby, Clojure, Amazon Web Services (AWS), PostgreSQL, Sisense
- 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.
Senior BI/ETL Consultant2017 - 2017Blue Cross
Technologies: Tableau 9/10, SSIS/SSAS, PowerDesigner
- 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.
BI Solutions Architect2015 - 2017Absolute Security, Inc.
Technologies: SQL Server 2016, SSIS/SSAS/SSRS, Dell Boomi, NetSuite, Salesforce, Java, Python, PL/SQL, Tableau, PostgreSQL, Spring, Sesame Solution
- 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.
ETL Consultant2015 - 2015BC Hydro
Technologies: Java, JSF, PostgreSQL, Oracle PL/SQL, Greenplum, PostgreSQL, Informatica, Hadoop, Spring Batch
- 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.
Senior BI Consultant2013 - 2015Wipro
Technologies: Java, JSF, TOAD, OBIEE (Procurement, Finance Analytics)/OBIA, Informatica, PL/SQL, DAC, Hyperion, Oracle EBS
- 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 126.96.36.199 to 188.8.131.52 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.
Consultant2013 - 2013Telus
Technologies: Java, jQuery, JasperReport, Spring, Hibernate, Oracle
- Built the user UI using HTML5 on JBoss AS 6.0.
- Created single-page applications using HTML5, jQuery, and JSON.
- 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.
PL/SQL Consultant2011 - 2013Government of Alberta
Technologies: Spring, JSF, Hibernate, Tomcat, OBIEE/BI Publisher, Oracle Report, PL/SQL
- 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.
ETL/Reporting Consultant2011 - 2013Flint Energy Services Ltd
Technologies: Crystal Report, SSAS, SSIS, SSRS, SQL
- 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.
System Analyst2010 - 2011Blue Cross
Technologies: ADF, JSF, JDeveloper, CSS, Oracle 10g, PL/SQL, TOAD, JUnit
- Implemented manage enrollment group role and enrollment group report pages.
- Designed and implemented create enrollment package pages.
ETL Consultant2006 - 2009CGI
Technologies: Oracle Business Intelligent Suite, Oracle Warehouse Builder, Java, Oracle Discoverer, Struts, Java, Tomcat, Hibernate, JasperReport, IBM WebSphere, DB2, AIX
- 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).
System Designer | Developer2006 - 2006ZediT, Inc.
Technologies: C#, SSAS, SSIS, SSRS, SQL
- 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.
Software Engineer2004 - 2006Betcorp, Inc.
Technologies: C#, SSAS, SSIS, SSRS, SQL
- 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.
Java/ETL Developer2003 - 2003eHealthInsurance, Inc.
Technologies: Java, SQL, Strut, Tomcat, Oracle
- 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).
Java Developer1999 - 2002Alcatel Network Support Systems, Inc.
Technologies: Oracle, Java
- Developed a telecom management information system.
- Implemented a mobile communication expenses calculation system.
- 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 184.108.40.206 to 220.127.116.11 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.
LanguagesSQL, Python, Java
ToolsSQL 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
StoragePostgreSQL, SQL Server Integration Services (SSIS), AWS Data Pipeline Service, AWS S3, Dell Boomi, SAS SQL
ParadigmsAgile Software Development
PlatformsAWS Lambda, Amazon Web Services (AWS), Oracle Data Integrator (ODI), Salesforce
FrameworksHadoop, Spark, Django
Libraries/APIsSalesforce API, jQuery
- Certificate in Applied Data Science with Python2017 - 2018University of Michigan - Ann Arbor, MI, USA
- Master's degree in Engineering Management2006 - 2009University of Alberta - Edmonton, Canada
- Certificate in Data Warehouse2006 - 2006Kimball University - Seattle, WA, USA
- Bachlor's degree in Electronic Engineering1995 - 1999Shanghai Jiaotong University - Shanghai, China
- Applied Data Science with PythonAPRIL 2018 - PRESENTUniversity of Michigan via Coursera
- ITIL Foundation Certificate in IT Service ManagementJUNE 2015 - PRESENTCCTA
- Oracle Business Intelligence Foundation Certified Implementation SpecialistAUGUST 2011 - PRESENTOracle University
- CFA (Chartered Financial Analyst) Level ISEPTEMBER 2010 - SEPTEMBER 2017CFA Institute
- Oracle Certified Professional Database Admin (Oracle 10g OCP)MAY 2007 - PRESENTOracle University
- Microsoft Certified Technology Specialist for SQL ServerAPRIL 2005 - PRESENTMicrosoft
- Sun Microsystems Certified Java ProgrammerAUGUST 2002 - PRESENTSun