We provide IT Staff Augmentation Services!

Data Architect And Etl Architect Consultant Resume

Dallas, TX

SUMMARY

Data Architect with over 20 years of experience modeling data to be stored in diverse environments including Data Warehouses, Data Marts and Operational Data Stores for the Telecom, Retail, Healthcare and Financial industries. I’ve successfully designed CRM, operational, financial, billing, and marketing data structures to support applications, BI and reporting systems. Skilled at analyzing data requirements then developing conceptual, logical and physical data models to meet the requirements on multiple database platforms including Teradata, Oracle, SQL Server and DB2.

WORK HISTORY

Confidential, NY 

Data Architect and Solution Architect (Consultant)

My primary role was as a data architect to design the logical data model for a new Enterprise Data Warehouse as part of their Editorial Data Management System project. Gathered requirements for the replacement of a 20 year old IT application used to capture financial information related to grants, grant making foundations, grant recipients, and the corporations that support them.

We gathered business requirements through discovery meetings with a cross - section of the business units within the organization. I reverse engineered the current data structures using ER Studio and shared the reverse engineered physical ER diagrams with the IT staff. The current operational systems were built with PowerBuilder and Sybase during the 1990’s. As part of the data modeling effort, I performed data quality checks and data profiling on the existing operational systems.

We recommended an MDM solution for mastering the organizations, individuals, relationships, and contact information. We also recommended a data warehouse system to support the data consumption by downstream products. Data consumers included online publications and various web portals with search capabilities for grant information. A star schema design was used on the analytic data marts.

I designed the logical data model for the new Enterprise Data Warehouse. My document deliverables included a Logical Solution Architecture, Dimensional Bus Matrix, Current Data Flows, Future-State Data Flows, Logical Data Model, and Source-to-Target mapping templates. I worked with the business analyst and core business team to draft the first copy of the Business Requirements Document.

Tools: ER Studio, Sybase 12.5, SQL Server 2008, MySQL, Visio, MS Project

Confidential, Dallas, TX 

Data Architect and ETL Architect Consultant

Part of a team that designed a new Enterprise Data Warehouse (EDW) from the ground up. I recommended the Microsoft BI stack based on the size and complexity of the corporate data. Evaluated and profiled the data in the sources systems (POS, gaming systems, financial accounting system, ordering, event management systems, and marketing systems). I created the logical and physical models for the EDW transaction fact tables. Created the physical model for the ODS to capture and store the operational transaction data. Designed change-data-capture (CDC) processes to identify changed data for load into the EDW from the ODS.

Evaluated and recommended the Microsoft BI stack for the physical implementation of the database engine, ETL, and reporting tools. Created SSIS projects and packages to move data between the operational systems, the ODS, and the EDW. Created batch processes and scheduled them to execute nightly execution of the SSIS packages. Assisted the report developers with SQL and stored procedures needed to execute the reporting.

While acting as the DBA, I created all DDL and implemented the physical designs in the database. I performed performance tuning on all physical tables to include the proper indexes needed for ETL and reporting queries. I determined partitioning schemes when needed for performance improvement.

Out team positioned TopGolf for future growth by creating a world class, scalable BI design.

Tools: SQL Server 2012, MySQL, SSIS, Erwin 7.3.11

Confidential, lano, TX 

Enterprise Data Modeling and Architecture (Consultant)

Integrated new data structures related to the Value Based Insurance Design into an existing data management information hub (DMIH) and operational data store (ODS). I assisted in the logical/physical designs of the relational application databases. Determined new structures needed for the ODS to support downstream data consumers including other applications and reporting. Designed and implemented the structures using the Erwin data modeling tool. I generated DDL for physical table creation including indexes, referential constraints, and partitioning on a DB2 UDB target platform.

Designed an Earned Incentive application database and the ODS counterparts. Replicated the data structures from the DB2 UDB database into a Teradata platform to be used by the Earned Incentives engine for determining if insurance plan members have met activity requirements necessary to earn various discount and reward incentives.

Designed dimensional models using the health care industry standard data models purchased from IBM (HPDM) as guidelines. I customized the Atomic Data Model (ADM) and the Dimensional Warehouse Model (DWM) to meet Blue Cross Blue Shield needs on a Teradata platform. Worked on data marts to be used for reinsurance, risk corridors, and risk adjustment analysis related to the federal Affordable HealthCare Act reporting requirements.

Responsible for the physical design of the database structures on Teradata. Determined primary indexes required to do common distributions of large tables (1 billion+ rows) across the Teradata amps for optimal performance. Created junk dimensions to reduce the number of joins required on large star-schema joins. I redesigned view DDL for performance tuning to minimize spool file creation. Determined statistics gathering requirements based on data manipulation frequency and volume. I suggested secondary index creation and removal.

Tools: Teradata 13, DB2 zOS, DB2 UDB, TOAD, Erwin 7.3, IBM MDM

Confidential, lano, TX 

Enterprise Data Modeling and Architecture (Consultant)

Designed and implemented a Customer Data Warehouse (CDW) for the entire JC Penney enterprise. I created the logical and physical models using Erwin. I utilized a 3NF design using industry standard party role model concepts. I sourced the data structures from the IBM MDM product. Using ERWin, I created subject areas for Customer Account, Customer Relationships, Customer Preferences, Customer Contact Methods, and Customer Source Lineage. I worked together with the ETL developers to create the high level and detailed ETL designs. I assisted in implementing Informatica Change Data Capture (CDC) for near real-time ETL architecture. I delivered DDL to the DBA team implement the physical CDW database on Teradata. I wrote an extensive view environment to support the marketing group. Designed external data enrichment processes to capture quality measurements on email and phone numbers from 3rd party vendors.

Built an ODS on top of Sterling application to share order and other CRM information with various applications and reporting systems.

I reviewed the entire CRM marketing view structures on Teradata to suggest performance improvements. Determined primary indexes required to do common distributions of large tables (500 Million +) across the Teradata amps for optimal performance. Rebuilt tables with a new Primary Index, where an inconsistent Primary Index had been used, in order to align them with other large tables. I redesigned view DDL for performance tuning to minimize spool file creation. Eliminated multiple passes at large tables that were buried in layers of views in the marketing system. Changed “union” statements to “union all” statements where de-duplication was not required. Suggested changes to “partition by” and “order by” clauses in the analytic (windowing) queries to remove functions that were unnecessarily causing performance issues. Created range distribution queries to validate Primary Index skew issues and illustrate them to the IT staff. Determined statistics gathering requirements based on data manipulation frequency and volume. I suggested secondary index creation and removal.

Designed and implemented a data staging environment for their Enterprise Size Optimization solution. I designed the staging areas necessary to capture sales and product information necessary to feed the SAS size/pack optimization product. I worked together with the ETL developers to design the high level and detailed ETL processes. We implemented the staging environment on Oracle 10g.

Tools: Informatica 9.1, IDQ, IDE, Informatica Metadata Manager, Informatica CDC, Oracle 10g, Teradata 12, DB2 zOS, DB2 UDB, TOAD, Erwin 7.3, KSH Scripting, JIRA, IBM MDM

Confidential, Irving, TX 

Data Architect / ETL Developer (Consultant) - Verizon FIOS

Our team constructed a master customer dimensional data mart. The data mart consists of a master customer (party) dimension and the associated master address dimension. Designed and implemented snapshot history facts to capture profile information about customers, accounts, and addresses for targeted marketing and BI/operational reporting. Utilized the Informatica data quality management suite (IDQ and IDE) to identify and merge customers and addresses. Designed external data flows for customer data enrichment including demographic and psychographic attributes for targeted marketing and BI reporting. Created data models and implemented the physical structures in Oracle. I provided performance tuning tasks on all SQL statements, such as column indexes, SQL restructuring, and hints when necessary. Designed and implemented all transient table structures used in the ETL data flows. I profiled the source system data for content and quality. I wrote all source-to-target data mappings.

Tools: Informatica 8.6, IDQ, IDE, Oracle 9i and 10g, Teradata 12, TOAD, Erwin 7.2, KSH Scripting, Appworx 8, Donnelly

Confidential, Dallas, TX

Data Architect / ETL Developer (Consultant) - Business Intelligence Practice

Build customer data warehouse pulling information for 6 source systems including Siebel CRM. Warehouse tracked campaigns and programs, sponsors, participants and donations.

Designed and built a donor-centric data warehouse for the American Heart Association. Captured donor transactions related to donations and event participation. Acquired enrichment attributes from 3rd party vendors. Analyzed six different source systems for candidate data attributes based on high level requirements provided by the business owners. Documented customer attributes related to donations, communications, and non-revenue activities from all 6 sources. I profiled the source system data for content and quality. I presented the profiled results to the business owners to assist in attribute selection.

W designed a relational data warehouse structure using the Corporate Information Factory methodology (Bill Inmon). I used the ERwin data modeling tool to capture and document the data warehouse physical design. Created domains for all major attribute types including codes, descriptions, id’s, and audit columns. I mentored junior developers on data modeling and logical/physical database design. Created and maintained the data dictionary.

Forward engineered the model, including tables, indexes, constraints, views, and defaults into a MSSQL 2008 database. I assisted the DBA and system administration staff in determining the appropriate SAN, memory, and CPU hardware configurations. I provided the DBA staff with requirements for the backup/recovery strategy.

Designed, tested, and implemented the ETL code in Informatica for data acquisition from the six source systems into the data warehouse database. I developed over 300 Informatica mappings. I created source-to-target mapping documents for all target tables. I mentored junior developers on ETL best practices and ETL performance tuning.

I created the ETL workflows for the initial (historical) data loads as well as the incremental (recurring) data loads. I coordinated the load schedules around the source system schedules. Coordinated monthly loads with the business owners, based on their operational month-end close schedules.

I managed the defect tracking, assignment, and resolution. Facilitated weekly meetings with the BI reporting vendor to resolve issues and identify enhancement requirements. Produced detailed status reports for IT and business owners.

Tools: Informatica 8.6, Oracle 9i and 10, TOAD, Erwin 7.2, KSH Scripting, Windows Scripting, ATG, IBM MDM, Siebel 7, DataFlux, Merkle, Informz, Community Coder

Confidential, New York, NY 

Architect / Technical Director - Strategic Database Systems

Successfully designed, hosted, and implemented a data warehouse to support marketing automation and business intelligence reporting for Citigroup. The data warehouse captures detailed and aggregate key metrics related to the Citi loyalty program (https://www.thankyou.com). Designed the star-schema dimensional models for analysis using the Ralph Kimball methodology, including the following subject areas:

  • Loyalty Member Point Activity Analysis
  • Loyalty Member Award Redemption Analysis
  • Case Management Analysis
  • Web Behavior Analysis

We worked with the project management team to create business requirements documents and functional specifications. I assisted in creating the overall project plan. I estimated SAN and NAS space requirements for data staging, data warehouse, reporting cubes, and tool metadata repositories. I created network and architecture diagrams to share with the customer. I maintained the ERD and data dictionary documentation for the source systems and the data warehouse which was distributed to the entire project team.

Implemented the star-schema data model as dimension and partitioned fact tables in Oracle 10g. Maintained and generated the physical database structure using the ERwin data modeling tool. I worked with the DBA staff to create indexing and partitioning best practices. I mentored ETL developers on star-schema design and load processes. I coordinated the installation and configuration of Ab Initio as the ETL tool on 3 Linux servers. I coordinated the installation and configuration of Cognos as the reporting tool on a large 64-bit windows server.

I wrote complex queries using the windowing and analytic functions provided in Oracle 10g. I analyzed and modified SQL queries for performance tuning. I wrote SQL queries in the Unica Affinity Campaign tool for marketing automation.

Interviewed and assembled a team of developers to perform the build of the data warehouse. The team included 2 ETL developers, 1 DBA, 2 Cognos developers, and 2 .NET developers. I managed the workload for all team members. I was responsible for performance reviews and salary administration of the 7 developers. I reported project status to IT and business owners.

Tools: PL/SQL, Oracle 9i and 10g, TOAD, Erwin 7, KSH Scripting, Windows Scripting, Ab Initio, Unica, Teradata, Neteza

Confidential, TX 

Data Architect / ETL Developer (Consultant) - Business Intelligence Team

Our team was responsible for the redesign and enhancement of the existing Enterprise Data Warehouse (EDW) and data marts. This includes relational 3NF design of the EDW and the star-schema designs in the data marts. I was the project lead on the implementation of several new data marts for medical insurance claims and policy analysis. Profiled the source system data, produced the high-level ETL design for data acquisition, and created mock-up and prototypes for report design. Reverse engineered, created, and maintained all data models using the Erwin data modeling tool.

Evaluated and made suggestions for database design improvements including indexing and partitioning. Create project definition documents, requirements definition documents, and detailed design documents. Produce resource estimates and project timelines. Work with business partners, developers, and IT management to successfully complete project deliverables.

Developed and maintained Informatica ETL mappings for the case management data mart and several medical claim data marts. I analyzed several hundreds of undocumented ETL mappings to create missing source-to-target mappings.

Tools: Informatica 7, Oracle 8i, TOAD, Erwin 4.1, KSH Scripting, Windows Scripting,

Confidential,Dallas, TX 

Technical Architect / ETL Developer / Manager - Business Intelligence Team

Responsible for all technical aspects and management of the Confidential Kinko’s Data Warehouse. This includes project management, multi-dimensional data modeling, database design, database performance tuning, ETL design, hardware/network configurations, data acquisition/profiling, and report interface/delivery.

Work with the business teams across the enterprise to achieve consensus on conformed dimensional attributes and dimensional hierarchies. Designed and implemented dimensional hierarchies for operational organizations, sales portfolios, products, and G/L accounts. Designed dimension bridge tables to facilitate reporting on customer hierarchies.

Worked with our business partners to define and prioritize projects and enhancements. Estimated staffing, hardware, and software resource requirements. Provided detailed project status to business and IT executive staff. Crafted weekly CIO status updates. Managed production support, including on-call assignments, maintenance schedules, and release schedules. Managed the problem tracking system and assigned problem tickets to production support staff. Manage level 3 support and worked with help desk personnel to define notification and escalation procedures. Work with IT Directors to determine tactical and strategic direction.

Successfully architected BI solutions using Ralph Kimball multi-dimensional star schema methodology. I conducted logical/physical design sessions with subject matter experts to define dimensional attributes and fact measurements. I determined the proper fact grain on all fact tables based on reporting requirements. I validated the data model designs against the business and technical requirements. Accumulated a vast knowledge of different business areas through the design of the following BI reporting solutions:

Sales Transaction Data Mining, Commercial Sales Analysis and Forecasting, Product Performance, Store Profitability, G/L Financial Analysis, Workforce Management and Forecasting, AR Invoice Analysis, Pricing Analysis, Order Analysis, Flash Sales and Labor Analysis, Service Quality Indices, Package Shipment Analysis (Teradata), Copy Machine Usage Analysis, Fraud Detection, Tax Audit, Coupon Analysis

Participated in the design, procurement, and implementation of all BI hardware, including database servers, ETL servers, and BI reporting servers. Primary liaison with operation groups for hardware, operating system, and network designs. I participated in all software and hardware evaluations related to BI solutions. This included software for data encryption, address standardization, customer demographic capture, activity based management, ETL development, database engines, integrated schedulers, and business intelligence reporting.

I was responsible for full life-cycle activities of data warehouse project management. This includes business requirements gathering, conceptual and technical design documentation, data flow and ETL design, coding, testing, and implementation. Also responsible for all areas of change management, including test plans, test result documentation, code reviews, production migrations, production validation, and overall SOX compliance. I reported project status and timeline tracking to business and technical owners of the project. Provided resource estimates to business owners.

Determined BI impact of any data changes in all upstream source systems. We coordinated downstream changes with external data users. Worked directly with the ETL architect/developers, BI reporting architect/developers, legacy data architects, DBA’s, and the application architects/developers to implement ETL changes as a result of application changes in the source systems. We managed overall ETL schedule including the daily, weekly, and monthly ETL cycles. I coordinated ETL extraction times with operational database and application teams.

My secondary responsibilities included ETL development and cube design. I designed DataStage ETL jobs/batches. Unloaded source data from several data sources including Oracle, SQL Server, and flat files. I designed dimension and fact table ETL jobs. I designed Essbase outlines, input files, load scripts, and calc scripts. I participated in activity based costing model design and implementation.

I executed database performance analysis by researching query plans and index usage. Analyze database bulk loading efficiencies and strategies. Tune queries to achieve optimized query plans. Optimize queries to leverage database table partitioning.

Tools: DataStage 7, Oracle 9i, Teradata, TOAD, Erwin 4.1, KSH Scripting, Windows Scripting, Retek, Kronos, PeopleSoft G/L and HR

Confidential , OK 

Manager of Loadmaster Training Support - Air Freight and Aerial Delivery

Activated in the Air Force Reserve in the wake of the 9/11 attack on the World Trade Confidential . I managed freight loading mission in support of C-17 loadmaster training school. Responsible for the day-to-day freight upload/offload operations at Altus Air Force Base, OK. We successfully trained 187 loadmaster trainees in an aggressive training program over a 9 month period.

I supervised the activities of 8 non-commissioned officers and 37 airmen. I was responsible for status reporting to the Group and Wing executive officers. Planned, managed, and executed a $500K annual budget. Coordinated load times, cargo content, and flight schedules between the loadmaster school, the pilot training school, the refueling mission, and the airfield operations staff. I coordinated maintenance of all cargo handling equipment with the vehicle maintenance group.

I retired from the Air Force Reserve in March 2006.

Confidential , TX 

Architect / ETL Developer / Manager - Data Warehouse

Architected multi-dimensional data models and implemented them as datamarts in direct support of the marketing, sales, and operations departments. Evaluated and selected the data warehouse tool suite used for data acquisition (ETL), database storage (DB Engine), and reporting. I managed the workload for 5 ETL developers. I was responsible for all hardware, operating system, application software, and network configurations. Created detailed evaluation criteria for selection process between Informatica and DataStage ETL products. Implemented star schema designs on Sybase IQ database engine. Wrote hundreds of data extract processes to facilitate data movement to external datamarts. Designed, coded, and implemented ETL programs to load dimension and fact tables using Informatica.

Data Conversion Lead / Data Modeler/ ETL Developer - CRM/AR/AP/General Ledger

Responsible for the conversion of 5 different data centers running different customer service, billing, accounts payable, general ledger, and alarm monitoring systems into a single Oracle application. I built 4 teams to work on concurrent conversion operations. I managed ETL design and taskings for 14 Informatica developers. I worked dual roles as manager and developer.

I performed all project manager duties during the conversion. Schedule iterative data releases for user validation during the conversion life-cycle. I created data validation reporting for all areas of data conversion to ensure accurate conversions of data. I supplied milestone and status reporting to executive staffs of local and parent companies. I assisted in the selection of test team members, testing plans, and testing criteria. Worked with core team of business partners to drive-out and document conversion requirements.

I wrote Informatica ETL mappings to convert alarm monitoring data from a home-grown SQL Server application into the Oracle monitoring application. We gathered technical requirements from alarm monitoring support staff. Extreme accuracy required because of the nature of the data. Bad data could result in non-dispatch of emergency services to locations with life threatening situations.

I led the data conversion of 10 million customer’s data in 1.5 years without interruption in service to alarm monitoring, billing, or payment. Saved the company $10M/year in support costs related to maintaining 5 versions of enterprise software.

Confidential

I designed ETL jobs in shell scripts with embedded SQL necessary for centralized storage of customer call Confidential data. I created JavaScript code using Sybase Application Server for a customer search engine.

Tools: Informatica 5, Oracle 8i, TOAD, Erwin 4.1, KSH Scripting, Windows Scripting, Maestro

Confidential , TX 

Architect / ETL Developer - Data Warehouse

I was the primary architect for an enterprise reporting data warehouse. I was responsible for the logical/physical database design. We implemented a relational back-end design using Bill Inmon methodology. I designed large fact tables in Sybase IQ to hold long distance call records. Over 1 billions records contained in the largest fact table. We successfully implemented monthly BI reporting for capacity planning, rate verification, and overall sales trending.

I designed ETL processes using Korn Shell scripts running on large Sun machines. We incorporated embedded SQL, Awk, SED, and FTP into the ETL scripts. We sourced data from multiple applications. We pulled dimensional data for customers and phone numbers from legacy hierarchical databases. I manually partitioned the physical database tables for query performance. I designed rate verification queries that saved the company millions of dollars in over-charges from long distance carriers. We reduced monthly runtimes from several days to hours.

DBA - Application Development

I was the primary DBA for call Confidential application development. We supported the customization and implementation of Vantive call Confidential software. I was responsible for database design, implementation, backup, recovery, and tuning of Sybase databases. I was also responsible for data integration between application databases.

Web Portal Developer/Lead 

Team Lead for the conversion of existing customer commission reporting system to a web portal. Set up multiple NT machines with IIS web services and Cold Fusion application servers. I created programs to convert existing flat file reports to database format. I performed stress testing on web and application servers by simulating HTTP form requests using WebLoad software. Created test plans and documented test results of stress/load testing.

Tools: Power Designer, Sybase SQL Server 11, Sybase IQ, Erwin 3, KSH Scripting, Windows Scripting, Sybase Rep Server, Embarcadero

Confidential  - Plano, TX 

Data Modeler - Corporate Systems

  • Flexible Benefits Projects
  • HRMS
  • Internal Job Posting
  • Mexico/Canada HRMS
  • Global Authorizations
  • Raise and Bonus Subsystems
  • Skills Inventory

DBA - Research and Development

Development DBA for CASE tool development projects. Provided remote database design and support for multiple remote CASE tool development projects using INCASE. I performed design, implementation, backup, restore, and performance tuning of database tables.

UNIX System Administrator - Research and Development

Our team was responsible for distribution of internal CASE tool release software. I created C-Shell scripts to build software release tapes for multiple OS and tape media environments. Certified UNIX administrator on SUN OS, Solaris, SCO, Solaris x86, Silicon Graphics (IRIX), and HP (HPUX). Provide remote UNIX SA support to multiple offsite development efforts. Provide UUCP email communications between remote developers and the R&D staff. I setup DNS servers and Web Servers. I wrote cross-platform backup and recovery processes.

NT System Administrator - Corporate Systems

Administrator for NT Servers and LAN software including MS Mail Server, Sybase SQL Server, Microsoft SQL Server, Powerbuilder, IEF, and Oracle. Install all Confidential software including MS Office product suite. Debug Confidential issues and install/configure Confidential and server hardware. Configure BIOS and RAID settings for optimal performance.

Hire Now