William Herrick Email and Phone Number

- Microsoft SQL Server 2019 data-warehouse developer implementing data models for ETL pipelines. - Designed reusable, templated Common Table Expression (CTE) patterns and combination matrix values to publish fact and dimension records in adhering with Kimball Methodology for data-warehousing.- Microsoft Power BI designer with both on-premise and cloud implementations.- Developed Databricks, Scala code to export sample data from Advana to CSV.- Created Databricks, SQL notebooks for data analysis and interrogation.

- Microsoft Power Platform developer with expertise in developing Power Apps, canvas, model app, Common Data Service, and SQL connectors with Power Automate Flows.

- Advana, Databricks engineer developing notebook functions, reusable code templates, and workflows with Python, SQL, and Scala to render and transform data frames for analysis and dashboards. -- Python and SQL: I preferred using either language for ETL operations. -- Scala: I preferred for utility tasks such as backups, file movements, and data exports.- Designed data processing pipelines using Advana, Databricks platform with Delta tables, and AWS S3 storage. Uploaded custom code table to S3 storage for availability in Bronze environment. Developed and maintained Bronze to Silver environment data transformations for Qlik ingestion.- Created scheduled ETL and backup jobs with PySpark utilities and API calls.- Designed and implemented Scala notebook to backup Dev and Production Databricks notebook libraries to Confluence site (note, this was prior to GIT integration).- Identified and documented existing data processing pipelines by creating entity relationship diagrams (ERD) with process flows in Microsoft Visio which allowed better collaboration for troubleshooting and data integrations.- Maintained and troubleshot Qlik connect data connections of Databricks tables/views and built sample visuals.

- Data-warehouse developer responsible for mapping and loading data for US Army medical and personnel data.- Created data models and identified fields for assignment in dimension and fact tables by reviewing interface specifications and decomposing characteristics of source data.- Migrated and troubleshot SSISDB packages from SQL Server 2012 to SQL Server 2019 across 10 instances.- Installed and configured over 10 Microsoft SQL Server 2019 instances with Failover Clustering utilizing DMV’s to troubleshoot SQL Server Agent jobs ran successfully ensuring SID’s were sync’d and local account privileges.

- Monitored funds in excess of $150M with automated spreadsheets using Power Query and Power Pivot for data transformation and aggregation versus budgets.- Created charts and graphics with Power BI for program analysis of financial plans versus budgets.- Developed independent and dynamic notification routines with Power Automate and Microsoft Lists.

- Business-intelligence and data-warehouse developer responsible for sourcing information from internal/external systems and applying complex business logic for cost and usage data analytics.- Utilized Scaled Agile Framework (SAFe) methods to determine effort, work-breakdown, and critical path for projects.- Developed and maintained software for ETL/ELT data ingestion processes using combinations of T-SQL, JSON (JavaScript Object Notation), Extensible Markup Language (XML), C#, and PowerShell to populate the data-warehouse with real-time event messages and time-based snapshots.- Generated multiple enterprise reports using SQL Server Reporting Services (SSRS) connected to SQL Server databases which included various reporting features such as group by, drill down, drill-through, sub-reports, single valued parameters, multi-value parameters, and dynamic workflow parameters.- Developed Tabular-Model cubes using SQL Server Analysis Services (SSAS) and wrote DAX measures for Microsoft Power BI dashboards to include switches for month/quarter/annual period calculations.- Built qualitative and quantitative metric formulas in SSAS with Visual Studio for management dashboards that highlighted historical trends, customer/product churn, and future forecasts.- Created custom PowerShell scripts ran from SQL Agent jobs to retrieve data from MongoDb databases.- Involved in writing complex T-SQL stored procedures which included transact statements, database object validation, and exception handling for the ease of debugging and displaying notification messages.- Setup linked SQL Server to Oracle NetSuite with ODBC connector and created PL-SQL statements for retrieving data instead of manual file extracts that allowed for daily versus monthly refreshes.- Designed and implemented API data pipelines to interface with external systems to retrieve and process daily, transactional data with XML and JSON payloads.

- Principle developer for call center system which processed 10,000 leads and 100,000 calls daily.- Worked with stakeholders and Agile production teams across units to identify business needs and solutions.- Architected SQL Server database model and objects to process call center transactions along with developing C# API’s leveraging Entity Framework needed to support cloud-based, dialer appliance.- Setup linked SQL Server to MySQL databases located in AWS cloud server in order to reconcile transactions received via Rabbit-MQ service bus events captured with Entity Framework class models backed by stored procedures.- Developed Do-Not-Call phone screening C# application that interrogated 300,000 customer records per day and captured the results in a star-schema database structure for faster lookups.- Created numerous SSRS parameterized reports with tables, matrix, charts, and sub-reports.- Maintained and monitored over 20+ Microsoft SQL Server 2016/2019 instances in AlwaysOn, High-Availability (HA) environment executing failovers for performance issues, SQL Server Cumulative Updates (CUs), and T-SQL script software releases.- Configured and maintained Windows Server 2016 virtual machines for Microsoft SQL Server High Availability ensuring SID’s were sync’d and local account privileges.- Utilized Logic Monitor and New Relic applications to monitor activity and troubleshoot SQL Server databases for 24x7 on-call support activities. Also, utilized DMV’s and sp_WhoIsActive to rectify performance issues to include, blocking, missing indexes, temp_db usage, table statistics updates, and store procedure recompiles.- Greatly involved in database performance tuning to optimize SQL queries and re-engineer stored procedures using query analyzer to create, modify and recompile indexes on database objects.- Authored T-SQL stored procedures and functions based on change requests applying constraints and writing triggers.

- Project manager and lead developer for data warehouse and business intelligence (BI) initiatives.- Gathered and documented ETL requirements based on functional specifications and identified project deliverables and work breakdown structures per CMMI best practices.- Developed scenarios for unit, integration testing to ensure that all the components work correctly.- Implemented complex business logic as per the client data integration requirements with custom Visual Basic 6 and .NET service components along with dynamic transformations with Extensible Stylesheet Language (XSL).- Created ETL packages using heterogeneous data sources for SQL Server 2008r2, Oracle, Flat Files, XML files, etc and applying transformations using T-SQL.- Worked on dimensional data modeling using star and snowflake schemas for fact and dimension tables.- Analyzed and designed disaster recovery, replication, and encryption strategies for SQL Server data.- Automated file mergers with file sizes greater than a gigabyte which achieved timelier data availability for the Air Force during fiscal year-end closeout.

- Principal user contact for Air Force Special Operations Command/Financial Management computer systems. Responsible for the evaluation, testing, and implementation of new software and hardware technologies.- Created and implemented an Intranet dashboard for executives and portfolio managers which fostered new insight into funds execution and facilitated a better collaborative approach to financial management.- Migrated and maintained corporate website content to Broad-Vision web portal platform.- Designed software for planning, budgeting, and trend-analysis that reduced support costs 20% while improving information accessibility.- Re-engineered congressional funding transaction management process resulting in the identification of out-of-balance conditions and improved service to users.

- Analyst to Headquarters Air Force Special Operations Command in support of a data-warehouse.- Delivered software training courses to hundreds of users covering financial management and logistics topics; often received praise for professionalism and the knowledge attained from the instruction.- Developed "Slide Maker" prototype application for use with the Info-Analyst client software which satisfied the number one system change request for the Commander's Resource Integration System.- Created organizational website that allowed for easier dissemination of guidance, policies, procedures, and contact information.

- Led the implementation of an enterprise Air Force financial management data-warehouse system.- Responsible for system certification and accreditation, maintaining and updating technical documentation as well as user documentation and instructional curriculum manuals.- Researched, recommended and implemented network enhancements that improved system reliability and improved performance; network response times were increased 85%.- Identified solution for retrieving missing legacy system data and created a program to convert report files into data files which enabled users to reconcile financial statements.

- Managed Microsoft Windows Servers, Microsoft Workstations, a Sybase SQL Server database instance, an Oracle database instance, network security, and network connectivity in support of 150 users.- Performed all levels of hardware and software systems support for computers and printers.- Reduced system rollout times by 75% and achieved desktop standardization utilizing PC imaging.- Provided installation and technical support of a variety of client/server systems for customers.

- Served as a financial advisor to decision makers with financial plans in excess of $10M and served as a logistician, resolving and researching customer inquiries and inventory management issues.