: After a IT review, some stored procedures have been identified with hard coded values. Approximately 200 stored procedures have been identified as having hard-coded values. Create a UDF to pass the value needed from a SQL Server table.  Comment out the hard-coded values in the stored procedures and replace this code with a call to the UDF.

*- Create a UDF

*- Create a SQL Server table to hold the lookup and needed values.

*- Create an Excel spreadsheet to log the work done on the stored procedures and its output.

*- Add test information to the spreadsheet.

Test the stored procedures in DEV environment.

Test any output the stored procedures create.

Make the change to the stored procedure, Comment out the hard-coded values and add the UDF code.

Retest the stored procedures in the DEV environment.

Retest any output the stored procedures create (files, data tables,

Update the spreadsheet with the test information.

Test the stored procedures in UAT environment.

Test any output the stored procedures create.

Make the change to the stored procedure, Comment out the hard-coded values and add the UDF code.

Retest the stored procedures in the UAT environment.

Retest any output the stored procedures create (files, data tables,

Update the spreadsheet with the test information.

Test the stored procedures in PROD environment.

Test any output the stored procedures create.

Make the change to the stored procedure, Comment out the hard-coded values and add the UDF code.

Retest the stored procedures in the PROD environment.

Retest any output the stored procedures create (files, data tables,

Update the spreadsheet with the test information.

SSRS, SSAS, SSIS

SQL Server migration project

Automated Medicare & Medicaid reports for the State of Illinois

Created Tabular style data warehouse

Worked as a team leader with two other developers on project to analyze and enhance SQL Server 2014 stored procedures. Performance  tuned approximately 200 stored procedures and suggest ways to improve performance. Change hard coded values to call User Defined Function (UDF) to select values from SQL Server table. Test stored procedures and any output in a test environment and provide feedback to IT team. Create an Excel spreadsheet to record findings of testing.

• Created master Excel spreadsheet to record test progress and analyze trends in Stored Procedures.

User Defined Function call allowed great flexibility in stored procedures.

Team leader for a Windows Server migration from 2008 to 2016 and a SQL Server migration project from SQL Server 2012 to SQL Server 2016. Worked in a high transaction volume environment (Trading) with teams in London, England, Paris, France, and other U.S. cities. Light project management in an Agile environment with 2 week sprints. Used  T/SQL to created new databases, tables, views, and stored procedures. Added column store indexes to tables and updated stored procedures in a Development, UAT, Pre-prod, & Production environment. Imported data from multiple data sources, cleanse and transform data and load data into data destinations. Created, modified & deployed complex SSIS packages and used them to create data tables. Other SSIS packages tested data in all 4 environments (Dev, UAT, Pre-Prod, Prod). Created application written in C# to launch a series of test (unit testing) to compare table counts in all environments. Created SSRS & Report Builder reports and used data-driven subscriptions to deliver the reports to managers and other employees. Tracked the progress of the project using Jira.

• Used Windows Server Migration Tools to migrate server roles, OS settings, shares, features and other settings between servers.

• Moving SSIS packages to SSISDB allowed the use of global variables and the ability to move packages from environment to environment easily.

• Added column store indexes to improve the speed of SQL Server queries and stored procedures.

Team leader with three developers responsible for creating, updating, and maintaining SQL Server  stored procedures and Report Builder/SSRS reports. Created & automated Medicare and Medicaid patient & financial compliance/non-compliance reports using SSIS packages and stored procedures. Extracted data from the CMS (Centers for Medicaid and Medicare Services) and QNXT databases as the basis for reporting. Studied and changed QNXT database schema. Provided production support for CMS and QNXT users and IT staff. Trained development team to use SQL Server 2012 advanced features. Performed code reviews with IT staff. Created MS Access application to monitor Cigna case workers and staff. Other duties as assigned.

• Saved 30 hours of weekly development time by automating Medicaid/Medicare compliance and non-compliance reports for the State of Illinois. Reports were delivered on time, in the correct format, and reduced the likelihood of human error.

• Created desktop application in MS Access to allow Cigna managers to monitor case workers and other Cigna employees.

• Video training allowed SQL Server Developers to quickly understand new concepts and Microsoft Best Practices while creating stored procedures and other SQL Server objects.

Created SQL Server 2014 SSAS Tabular Data Warehouse to store client data. Created dimension tables and fact tables from OLTP data to be part of the Data Warehouse. Developed SSIS packages that import data from Excel, CSV, or text files into SQL Server tables and in Valence Health standard format or BCBS format for importing data into a database or data warehouse. Created star-schema based SSAS cubes to analyze data. Created reports for management and stakeholders using SSRS.

Accomplishments include:

• Team leader on the project to build the Tabular Data Warehouse and store client data in-house.

• Created the SSAS cubes for the Data Warehouse to analyze data, and as the basis for analytics and reporting.

• Created complex, reusable T/SQL stored procedures that automate file format testing to eliminate data errors.

Used SQL Server 2008R2 and SQL Server 2012 to create Data Marts to store client data. Created stored procedures to automate database tasks and encrypt healthcare data. Developed C# Winform application to encrypt/decrypt health care data to be transferred to other hospitals and healthcare facilities.  Created a C# console application to import health care data from different data sources such as text files, Excel spreadsheets and CSV file sources and output the data in a custom format.

Accomplishments include:

• Data Marts that were created enabled various departments to work with data. Data Marts gave users, hospital managers and administrators to help identify, analyze, and output healthcare data.

• SQL Server stored procedures successfully encrypted healthcare data and prevented unauthorized viewing.

• C# console application successfully imported data to be used for analysis and to share with other healthcare facilities.

Created financial reports using Address Book Master, Sales Order Detail File, Item Master, Account Ledger, Purchase Order Detail File from JD Edwards database/tables. Used SQL Server 2012 to create stored procedures to solve various business problems. Analyze and tune SQL queries and stored procedures to improve performance. Built Tabular model Analysis Services database with SQL Server 2012. Created Data Marts for Production and Sales Depts. Created a WinForms  C# front-end to allow Accounting to run real and staged month-end processing. Automated time and expense reporting for accounting department using custom stored procedures, SSIS packages and VB.Net custom interface. Created custom tabular, matrix, and bar chart reports for accounting managers. Assisted management with various SQL Server projects.

Accomplishments include:

· Created a Tabular designed Analysis Services database that proved extremely fast and allowed data to be analyzed quickly and easily.

Created custom accounting and financial reports using data from JD Edwards ERP Software (Enterprise Resource Planning). Built online reports website for reports storage and management.

· Created SSAS cubes using SQL Server Analysis Service that use data warehouse data as the basis for analytics and reporting.

Review functional requirements and create design specifications for SSIS packages and SSRS reports. Build data sources (DS) and data source views (DSV) to enhance the database schema as the basis for SSAS cubes and SSRS reports. Create and deploy SSIS packages. Created client designed custom reports and charts using SQL Server 2008 SSRS. Wrote stored procedures to verify selected data. Reports created include drill-down reports, bar charts, line charts, & summary reports.

Accomplishments include:

· Assisted senior management in requirements gathering, designing, building, and implementing SQL Server Business Intelligence SSRS reports, & SSIS packages.

· Robust SSIS packages made analyzing data and ETL operations run smoothly.

· Automated SSRS reports saved development time and standardize report development and delivery.

Creating data source views allows users to create Named Calculations and Named Queries when creating SSIS packages and SSRS reports

Created new SSIS packages for the Business Intelligence group for Health Care Service Corporation. Created, developed and deployed an SSIS Management Framework application to organize, manage, monitor, and restart SSIS packages from a single Winforms central application.  Created complex stored procedures used to support SSIS programming. Created a deployment tool written in C# capable of deploying .SQL scripts, classic .ASP and .NET .ASPX pages, web services files and SSIS packages. Created custom interactive drill-through matrix reports for managers. Write code to assist QA in unit testing. Helped colleagues understand how to use custom software.

Accomplishments include:

• New SSIS Management Framework organized SSIS packages and allowed for central management.

• New SQL stored procedures created an e-mail alert system based on previous time slots to allow a user to check for SSIS package failure and be notified if a failure took place.

New WinDeploy deployment tool will help eliminate deployment errors while creating logs in text format to alert the users of any errors a script may have encountered. The rollback feature in the WinDeploy tool allows management to roll back the SQL Server 

• database server to a previous known good state if a decision is made to do so. The rollback is performed in minutes instead of hours.

Lead developer to create custom SSIS packages using SQL Server 2008 that output specially

formatted text files to be used for predictive analytics. SSIS tasks ‘include Control Flow tasks, Data Flow tasks, File System task, Script task, FTP task and Send Mail task, Transformations include Data Flow Transformations,  Conditional Split, Data Conversion, Lookup and Fuzzy Lookup. SSIS packages populated SQL Server staging tables to be further validated before being imported to Data Warehouse tables. Created a data warehouse using the traditional database design methodology to create the initial fact tables and dimension tables, design relations, indexes, and map fields for reports

Accomplishments include:

• Created SSIS packages that automatically create files used for custom software packages.
• Created a new data warehouse as the basis for data analysis (SSAS) and reports (SSRS).

Created data dictionary, and schema design documents using stored procedures that read database and table extended properties

Added functionality or enhanced legacy systems written in Visual FoxPro 6.0. Used Microsoft Visio to create an overview of a proposed Data Warehouse. Designed and created a Data Warehouse as the basis for SQL Server reports (SSRS). Created SQL Server 2005 queries and stored procedures to add functionality and maximize application efficiency while increasing security. Converted older DTS packages or created new SQL Server 2005 SSIS packages to extract data from SQL Server or other data sources and cleanse or transform the data before sending it to a destination source. Acted as a backup resource for several custom developed applications

Accomplishments include:

• Successfully converted SQL Server data to assist HR in establishing a new major payroll provider, saving revenue and simplifying HR and payroll functions nationwide.

Designed & built new SSIS packages that extract mainframe data and load SQL Server tables as the basis for major PC applications. The new SSIS packages streamlined ETL operations