Project Portfolio

Converted an Oracle 11 database to a PostgreSQL 16.2 database

• Wrote 10 PL/SQL queries outputting PL/pgSQL: tables, constraints, indexes, sequences, views, m-views, triggers and functions
• Converted language and moved data using Ora2Pg.
• Used C# and Visual Studio to enable the main desktop dashboard to use the new PostgreSQL database

PeopleStrategy is a PEO (Professional Employer Organization

• Re-designed, forward engineered and populated one multi-tenant database having 60 tables from the legacy 209 single-tenant databases totaling 313,500 tables and 1,463,000 views.
• Re-designed included structures for [HR], [Payroll], [Direct Deposit], [Person], [Institution] and [Contact].
• Symmetrically encrypted all PII (Personally Identifiable Information) columns.
• Wrote triggers, stored procedures and views in PostgreSQL.

Used Erwin Data Modeler v12, Postgres 12, PgAdmin/PG Studio, PostgreSQL, GitHub and Jira

Date Overlaps: I work for OrderBasics between other projects and in my spare time

Designed and developed OrderBasics B2B Web Application from concept through to Beta release.

• Requirements discovery involved working closely with leading experts in fabric retail and wholesale.
• OrderBasics Features:
• Enables fabric Retailers to search for and Order fabric from multiple Wholesalers by using a wildcard search or standardized lookup values including [Line], [Shape], [Theme], [Basic Color], [Vendor] and [Designer].
• Relies on wholesalers uploading a standardized dataset of their new fabric releases and images.
• Standard lookup values are applied to each row of a wholesaler’s upload.
• Example: Although uploaded Fabric data may have hundreds of different color names, one or more of 17 colors is applied to each uploaded row.
• Includes 58 tables and 95 stored procedures.

Designed the UI and wrote code using: T-SQL, C#, JavaScript & HTML. This included ETL, OLTP, OLAP w star schema and incidental DBA work. Also used ERWin, SQL Server Management Studio and Visual Studio.

Senior Data Architect/Developer with Accenture for Kaiser Permanente’s Data Modernization Effort.

Data Modeler and Oracle Developer

• Maintained Erwin Data Models containing 16 subject Areas; 46 data models; 1,799 tables.
• Redesigned 46 relational data models and sub-models to be intelligible by anyone.
• Created, wrote and tested 3,598 Oracle DDL scripts.
• Closed 650 Jira Tickets
• Created monthly Erwin Data Model reports and Data Dictionary reports as well as ‘How to’ instructions for both.

Also used: Oracle 11g, Erwin Data Modeler 2020, GitHub, Jira, Oracle SQL Developer v17

Converted five(5) Sybase v15 databases to SQL Server 2018:

• The 5 databases totaled almost 2,000 objects (including tables, views, and stored procedures).
• Used SSMA (Microsoft SQL Server Migration Assistant 8.5 for Sybase Adaptive Server Enterprise)
• Manually fixed all converted DB SP’s (also adding error trapping, code cleanup and converting ANSI SQL-89 to ANSI SQL-92 syntax)
• Designed two Audit tables in their own DB fed by insert, update and delete triggers on all DB tables.

Used Oracle v12, SQL Developer v18 and ER/Studio Data Architect v17

Senior Data Modeler for Texas Department of Transportation data infrastructure modernization effort.  Worked closely with Business Data Analysts, other modelers and business owners to:

• Create intelligible logical and physical relational data models and sub-models. (The current data model has 288 entities)
• Generate/write DDL from model for database implementation
• Reverse engineer from ORACLE database into ER/Studio data models
• Compare/Merge data models from database

Also used: Oracle v12, SQL Developer v18 and ER/Studio Data Architect v17

Designed and built the Payroll Management System data warehouse from concept through to production/go live. Also analyzed, reverse engineered and documented PeopleSoft Financials, PeopleSoft HR and MasterTax. PeopleSoft Financials and PeopleSoft HR have a combined total of nearly 100,000 tables. Their relationships are purposefully obfuscated. Over the course of a few months I was able to accurately identify and model most their relevant data marts.

Also used: SQL Server, T-SQL, Oracle, ER/Studio and ERWin.

Short Term Contract: Designed and maintained data models. Mapped tables and columns from disparate data sources to a target database.

Also used: PL\SQL and ERWin.

Remote Work: I reverse engineered 12 Medicaid & Medicare databases into intelligible data models. Texas shops out all its Medicaid & Medicare to Accenture. Accenture had no pre-existing data models for any of the 12 Medicaid & Medicare databases that they manage for the state of Texas. Some data models had over 2,000 entities. Not all of these tables were used.

Also used: ER/Studio and ERWin.

Remote Work: Penton aggregates and sells data from various industries. I’ve re-designed, built and populated five (5) different databases: Aircraft, Automotive (2) and Agriculture (OLTP & OLAP). I took all of these from concept to go-live/production. This includes all ETL/Data Migration, triggers, stored procedures and functions.

Also used: Pentaho, Oracle 11g, SQL Server 2012, Erwin 7.3 and SQL Developer

The RRC regulates the gathering, transmission and distribution of oil, gas, coal and uranium in Texas. It no longer regulates railroads.

The RRCs database system of record resides on an antique hierarchical IMS Mainframe (initially designed in 1966 for the Apollo program). They hired me to:

• Relationally model all 22 IMS databases (totaling 573 entities and 4,799 attributes).
• Create data modeling standards and metadata and data dictionary management.
• Create the “Pipeline Permitting Database” from concept to production. This involved Modeling, forward engineering, ETL and writing all triggers, functions and procedures (totaling 65 entities and 305 attributes).
• Compare/Merge data models from database

All 638 entities and 5,104 attributes are well documented with both: Purpose and ETL Source. Used Oracle 10 and Erwin Data Modeler

Designed, built and populated an Oracle data warehouse having 45 tables and an ETL process that reads in 71 different kinds of JSON objects totaling several hundred thousand events daily. I also created an Informatica processes interacting with JSON source and Salesforce. This involves rigorous requirements discovery, data modeling and ETL/data migration.

Also used: ERWin Data Modeler, Oracle 11, Informatica, JSON source and Salesforce

Redesigned, engineered and populated database systems for the Texas WIC program (Women, Infants and Children) of the Texas Department of State Health Services. This includes the databases for each of the 600+ WIC clinics across Texas as well as the State DSHS database. This involved requirements discovery, Data Modeling and ETL/data migration. Used ERWin Data Modeler, SQL Server and T-SQL.

Designed an enterprise wide corporate database including all data used by the company in six large disparate departmental databases. Used ERWin Data Modeler, SQL Server and T-SQL. LISCR is the second largest ship registry in the world having almost 400 employees.

Lead data modeler and developer for Milliman & Robertson's Milliman Care Guidelines® (MCG) database product which is now used in over 1,000 hospitals. The MCG database stores Milliman & Robertson's managed healthcare guidelines. These guidelines previously existed only in seven volumes of hard copy as well as in several thousand HTML files. The data model I designed (using ERWin 3.5.2) has 72 entities and stores four of the seven volumes of Milliman & Robertson's managed healthcare guidelines. I generated a SQL Server database from the ERWin data model; then wrote ETL routines which read several thousand HTML files and wrote specific data to the appropriate tables and fields in the database. Also used HL7 and C#.

Designed and built a database system Managed-Enterprise for use by HMO's. It is a healthcare transaction processing and information system integrating communications between healthcare providers, health plans, and ancillary groups, significantly reducing claim and payment processing time, and lowering the administrative cost of provider and health plans. Managed-Enterprise is a standardized, all-insurer, Internet-enabled connectivity system to handle claims submission, referrals and authorizations, eligibility verification, formulary compliance, and rapid electronic payments. It is HL7 compliant enabling interoperability between disparate electronic Patient Administration Systems (PAS), Electronic Practice Management (EPM) systems, Laboratory Information Systems (LIS), Dietary, Pharmacy and Billing systems as well as Electronic Medical Record (EMR) and Electronic Health Record (EHR) systems. Also used HL7

Designed and built a database system that quickly and easily imports, stores and retrieves very disparate MLS’s (Multiple Listing Service) real estate data sources. This system allows searching across all imported MLSs from one unified database. MLS data sources do not adhere to any standards of structure or content. They are as different from one another as snowflakes. The system I designed includes all ETL routines, an OLTP and OLAP (with Star Schema) database. The OLTP database I designed has fifty- one tables. The legacy system had sixty-nine different databases, all of which had 500 tables. Also used: SQL Server 2005, Transact SQL, SSIS and DTS.