Business System Analyst
Resume:
Mark Connelly
Thousand Oaks, CA
**********@*****.***
Objective: To leverage my skills in system analysis, business analysis, programming, database design and reporting, front-end design, and documenting systems and processes to help businesses efficiently and effectively manage and improve their business processes.
Experience:
GuideWell, Florida June 2023 to Dec 2024
Business System Analyst Contractor
Perform business system analysis and documentation work for claims IT department for GuideWell’s Blue Cross of Florida claims division.
Work with business side to understand and collect business requirements.
Create IT documentation such as SRSs, flow charts, etc.
Create and run queries on DB2 using Toad to analyze data in data warehouse.
Work with DBA’s to implement updates to data warehouse tables.
MC Design, Thousand Oaks, CA July 2022 to June 2023
Business and Systems Analyst and Programmer Consultant
Business and system analysis, website and database design for Microsoft .Net based e-commerce websites, database reporting, and more.
Analyze business needs and research industry, and collect business requirements to facilitate website and database design and programming. Clients include rockgearmtn.com, rocamassage.com, and photorockbar.com.
Design and program relational back-end database for the website using SQL Server 2016 with custom tables, views, custom functions, and stored procedures.
Design and program ASP.Net ASPX website front-end using Visual Studio .Net 3.5 Web Forms, C#, Java script Ajax and jQuery. Implement controls, master pages, app data, app themes, and pages with separate code behind.
Create shopping cart functionality with cookies and session model to handle multiple products and quantities for purchase by the buyer, and integrate the purchase selections to the PayPal API.
Design graphics elements for the site using adobe Photoshop and used both CSS and tables to format site layout.
Perform user testing via local server and via cloud web server. Test stored procedures and site functionality.
Shoot photographs of product for use on the website.
Create management reports using SSRS and Microsoft Access, and other software as needed.
GMPC, Los Angeles, CA
System Analyst August 2021 to July 2022
Performed system analysis, reporting, ad-hoc system troubleshooting, and more for the Avid division of GMPC. AVID is a sportswear clothing company catering to the sport fishing market selling clothing for both wholesale and direct to consumer supply chain. Tasks included multiple daily and weekly data analysis and reporting tasks, handling daily to weekly ad hoc system issues, and handling various requests and problem solving issues from business systems manager and end-users from various departments.
Some of the key projects and tasks included:
Analyzed the company ERP system called Visual and its many tables, views, and stored procedures in SQL Server 2019, along with some guidance from the system business owner, to find problems with the data and document those issues. Wrote SQL Server stored procedures and views for various analysis, data validation, and reporting needs. Because system lacked documentation and relational diagrams, one major task involved reverse engineering the tables against the forms, and to create data testing views to validate table relationships.
Analyzed and helped deal with and document data migration issues between Visual and external product sales ordering websites Nu Order and Shopify. Found issues and causes that kept Visual and Nu Order from being perfectly in sync in regards to fields such as order quantities, order status, prices, discounts, and product availability. Example: found that percentage discounts applied caused order totals to be mismatched because of different rounding logic between the systems. Corrected the existing code used for Visual extracts to fix the problem.
Created SSRS reports to pull data from SQL Server ERP database for management use.
Created or enhanced Excel pivot and non-pivot reports with Power query pulling data from SQL Server ERP database using custom stored procedures or views, or imbedded SQL.
Ran daily to weekly queries to validate sales data ftp’d from NU Order to the Visual ERP database. Built a custom database with an Access interface and the SQL Server database to check that orders transferred successfully between the Visual system and Nu Order.
Designed an Access database front-end with SQL Server back-end to enable the catalog products manager to more easily create line sheets for product selling seasons In NU Order based on his requirements. One key area was to help apply quantity thresholds to prevent over ordering for pre-book and in-season orders.
Validated and audited sales order data to look for data entry mistakes and errors that affect reporting and data accuracy. Documented issues and made recommendations to manager and end-users. Example: Because of product availability issues or customer changes, one or more sizes of a product could be cancelled. If every size was cancelled, the product was given a cancelled status. However, the sales support person was also cancelling the product when only some sizes were cancelled. That caused the order total to be incorrect on the order screen and caused a reporting issue as the system was not designed to be used in that way. I made the recommendation to stop cancelling partially fulfilled orders.
Helped troubleshoot and document Visual Basic errors the Visual system generated. Because the code was locked down, issues were written up and sent to the vendor company’s tech support to apply fixes to the system.
Implemented ideas from the systems manager and suggested ideas to solve issues. Documented errors of the internal ERP system, external sales order website systems, and scanning software used in the warehouse.
Contacted vendor companies for support of their systems to send issues found and get business rules.
Help assist with problems relating to Shopify to ERP data migration.
Documented and tested Nu Order API calls for future API data transfer project using Postman.
MC Design, Thousand Oaks, CA June 2019 to July 2021
Business and Systems Analyst and Programmer Consultant
Business and system analysis, website and database design for Microsoft .Net based e-commerce websites, database reporting, and more.
Analyze business needs and research industry, and collect business requirements to facilitate website and database design and programming. Clients include rockgearmtn.com, rocamassage.com, and photorockbar.com.
Design and program relational back-end database for the website using SQL Server 2016 with custom tables, views, custom functions, and stored procedures.
Design and program ASP.Net ASPX website front-end using Visual Studio .Net 3.5 Web Forms, C#, Java script Ajax and jQuery. Implement controls, master pages, app data, app themes, and pages with separate code behind.
Create shopping cart functionality with cookies and session model to handle multiple products and quantities for purchase by the buyer, and integrate the purchase selections to the PayPal API.
Design graphics elements for the site using adobe Photoshop and used both CSS and tables to format site layout.
Perform user testing via local server and via cloud web server. Test stored procedures and site functionality.
Shoot photographs of product for use on the website.
Create management reports using SSRS and Microsoft Access, and other software as needed.
Bank of America, Agoura Hills, CA Contract: July 2018 to Jan 2019
Business Systems Analyst
Contract project as a system analyst to help IT department enhance the bank’s mortgage loan servicing database system. The mortgage bank needed to make changes to their mortgage tracking database because of new government regulations. Due to a tight deadline, the bank brought in many analysts for a short-term project.
Mortgage loan servicing system called iFullfil was being upgraded to handle new banking rules. The system was an intranet web-based system built using Java server pages with an Oracle 10 backend database.
Worked on an Agile team as an analyst to interpret and document business rules and code changes.
Analyzed HTML, Java code, custom functions, and database structures.
Documented the changes to the code into their document tracking system Team Foundation Server (TFS) for each iteration of the project.
Attended programming and business review meetings each week attended by all members of the Agile team.
Assisted programmers on my team if they did not fully understand the business requirements.
Used the Rally Agile tracking system to monitor and track the changes and progress.
2-20 Records Management, Edison, New Jersey Contract: July 2016 to July 2017
Business System Analyst and Programmer
Contract project to fix and enhance warehouse storage management database systems used for all stages of their warehouse operations. 2-20 Records Management is a physical records storage and management company based in New Jersey with offices and warehouses in California, Arizona, and New York.
Performed system analysis of various database systems and work flow processes used for inventory scanning and cataloging for the warehouses. Analysis was to learn about existing systems so that I could determine ways to fix problems and make enhancements based on management needs and system user needs.
Worked with CFO and on site managers to understand issues. Traveled to sites in California and Arizona to observe operations and systems.
Worked with site IT staff to learn about processes and discuss and document future changes to the systems.
Worked with system end-users to learn about issues they encountered and troubleshoot coding errors they encountered.
Worked on and enhanced systems used for processes such as box barcode scanning and medical records file manual entry. Redesigned database table structures and added new stored procedures to improve processes.
Fixed Visual Basic code used in Microsoft Access based front-end systems over a Citrix environment with SQL Server 2012 as the back-end relational database. Designed and programmed new user interface forms make the forms more user friendly and professional and to prevent user errors.
Designed and programmed management reporting tools and reports so managers could check employee work progress and check if goals were being met. Programmed reports with both data grids and with charts and graphs. Reporting was done with Visual Basic, Access and Excel.
Created custom ETL processes to import text file data feeds from various clients into SQL Server.
Designed employee tracking and progress reporting tools for management in Access with charts and graphs. Worked with and trained end-users of the systems. Systems were improved to allow employees to do work faster without errors, and management could track employee work progress to determine if employees are meeting production standards.
MC Design, Thousand Oaks, CA November 2015 to July 2016
Business Analyst and Programmer Consultant
Business and system analysis, website and database design for Microsoft .Net based e-commerce websites, database reporting, and more.
Analyze business needs and research industry, and collect business requirements to facilitate website and database design and programming. Clients include rockbarcycling.com, and rocaball.com.
Design and program relational back-end database for the website using SQL Server 2016 with custom tables, views, custom functions, and stored procedures.
Design and program ASP.Net ASPX website front-end using Visual Studio .Net 3.5 Web Forms, C#, Java script Ajax and jQuery. Implement controls, master pages, app data, app themes, and pages with separate code behind.
Create shopping cart functionality with cookies and session model to handle multiple products and quantities for purchase by the buyer, and integrate the purchase selections to the PayPal API.
Design graphics elements for the site using adobe Photoshop and used both CSS and tables to format site layout.
Perform user testing via local server and via cloud web server. Test stored procedures and site functionality.
Shoot photographs of product for use on the website.
Create management reports using SSRS and Microsoft Access, and other software as needed.
St. Jude Medical Devices, Sylmar, CA Contract: August 2015 to October 2015
Business System Analyst and Programmer
Contract project to fix, enhance, and complete a company-wide reporting project using SQL Server, PowerPoint and Excel. The project was initially started by another contractor, but the contractor left the project before completing it. The system only had SQL Server tables when I picked up the project and a PowerPoint presentation needing automation.
Performed system analysis and reverse engineering of an existing PowerPoint slide report with embedded Excel objects and graphs, and dynamically loaded data coming from an external SQL Server relational database.
Worked with staff reporting administrator to collect business requirements and solve problems he was having with the existing code and logic.
Documented business rules needed for the reporting system.
Visual Basic code was written for the logic to communicate with the external database and sync the data to the embedded Excel object and dynamic graphs.
The database was modified and stored procedures were created to enable the PowerPoint slides to use ADO connections to query the database with parameters from the user.
Over 20 slides had their own unique data and the data for each slide was queried when each slide was opened.
Designed and programmed multiple Excel data entry forms using Visual Basic to allow the end user to query and update the data tables directly without having to use SQL Server Management Studio. This made the process easier to manage.
Darice, Inc., Strongsville, Ohio Off-and-on Contract project: March 2014 to June 2015 Business System Analyst and Programmer
Contract project to build out a data entry system for Darice. Darice operates a chain of large craft and art supply stores in the Midwest. Darice’s product manager wanted to implement a new data entry system for entering and updating product data into a SQL Server product tracking database management system. The manager requested for Excel to be used as the user interface to the database.
Analyzed business requirements from the head manager and discussed various options to implement the project.
Successfully built a new SQL Server 2008 relational database with tables, stored procedures, and views to store and query data.
Wrote VBA scripts to program two different Excel workbooks used to enter and query data.
Designed and programmed dynamic drop-downs, list boxes and forms with ADO connections to call SQL Server stored procedures to query the data.
Created forms with Visual Basic code to add data and to update data to the tables via stored procedures with insert and update SQL scripts.
Dynamic drop-downs, list boxes and forms enabled the user to click in any cell of the worksheet to open up the form interface used for data entry or editing.
Project was done non-contiguously throughout the year as client made requests for updates or new additions sporadically throughout the year.
Artonpaper.biz, New York, NY Off-and-on Contract: June 2014 to July 2015
Analyst and Web Site Programmer
Contract project to design a website to show artist’s paintings and allow for browsing and purchases.
Collected business requirements from the artist and made recommendations on how to proceed with the project.
Photographed over 100 art pieces with digital SLR camera and tweaked images in Photoshop imaging software.
Designed website front-end and graphics using Visual Studio 2008 with ASP.Net 3.5, Web Forms utilizing CSS for formatting, jQuery, Java script, AJAX, and C #code.
Designed a new SQL Server 2005 relational database to track the file stored jpegs and artwork data. Stored procedures, views and custom functions were programmed.
The site enabled users to click on thumbnail images posted by the database to the web pages in order to view larger images. The site was also set up to allow a user to buy a painting if it was available for sale. A shopping cart for purchasing with PayPal API integration was set up.
Medtronic, Northridge, CA Contract: February 2014 to May 2014
Business System Analyst and Programmer
Contract project to fix and complete a new product defects tracking database system for Medtronic’s glucose monitor product manufacturing facility.
Performed system analysis of an existing Access database system and worked with on-site IT support person to gather business requirements and understand issues and needs for the system.
Reverse engineered the existing database structure and data elements. Wrote system documentation and created a data dictionary of business terms.
Designed the relational model for the new SQL Server 2008 database.
Created tables, views, functions and stored procedures. Business System Analyst and Programmer
Migrated existing data over from the Access database using SSIS scripts.
Interviewed managers on the factory floor to get feedback and ideas for new enhancements.
Improved the Access front-end to work more efficiently with the new database structure and to help users do their work more efficiently and prevent data entry errors. Wrote VBA code to improve interface experience, enforce business rules, and incorporate error handling that was missing. The existing system needed a lot of work.
As the company did not use a Citrix hosting setup, the Access front-end forms ACCDB file was installed on over 10 work stations.
Trained the assembly line employees on how to use the system and monitored them to tweak the system over the testing period. The database system was used for three work shifts per day and had to be stable since IT staff was not on site for night shifts.
Designed and wrote the user manual and other documentation in that described the functions and how to use the system. Used Microsoft Word for the documentation.
Bank of America, Westlake Village, CA Contract: June 2013 to December 2013
Business Systems Analyst
Contract project to analyze and convert an Excel based accounting tool into a database managed accounting system used by the mortgage accounting department. The original Excel based accounting system was used to track housing expenses for foreclosures at a summary level for each home the bank managed. Management wanted the ability to query and report on the data at a detailed level. Since detailed level meant 2 to 3 million records, the finance department wanted to convert the Excel based business rules and logic into a relational database reporting system.
Worked with accountants to collect, interpret and document existing business rules used in the Excel based process. Formal documentation did not exist at the start of the project.
Converted the business rules into a SQL Server 2008 database model.
Designed and coded tables, views, functions, and stored procedures to support the new system.
ETL processes utilizing SSIS were created to import data from various sources databases, text files, and Excel files. Stored procedures were written to call the DTSX package to import the data when the users triggered the process.
To help run the process, which was based on complicated logic covering various mortgage types, a user interface with sequential process steps with and checks and balances was designed in Microsoft Access 2007.
A user form interface was designed by me and Visual Basic script was programmed to call a stored procedures that populated the reporting tables based on the business rules. Users could run the processes as needed when month-end processes were run, do analysis and generate journal entry reports.
Created process flow diagrams in Visio. Created system design and the user manual documentation in Word.
Code had to be checked in and out via Team Foundation Server (TFS).
Seedyphotos.com, Oak Park, CA January 2013 to May 2013
Web Site Programmer
Contract project to design a website to show photographs and allow for browsing and purchases.
Designed website front-end and graphics using Visual Studio 2008 with ASP.Net 3.5, Web Forms utilizing CSS for formatting, jQuery, Java script, AJAX, and C #code.
Designed a new SQL Server 2005 relational database to track the file stored jpegs and artwork data. Stored procedures, views and custom functions were programmed.
The site enabled users to click on thumbnail images posted by the database to the web pages in order to view larger images. The site was also set up to allow a user to buy a painting if it was available for sale. A shopping cart for purchasing with PayPal API integration was set up.
Bank of America, Westlake Village, CA April 2011 to August 2012
Business Systems Analyst
Contract project to analyze and improve upon the bank’s custom metadata tracking database and reporting tools that were being developed. The reporting system was needed in order to track data migration and lineage across all of the bank’s mortgage database systems and provide reporting mechanisms for management.
Performed system analysis on existing SQL Server 2008 database used to house the metadata and lineage for reporting.
Worked with head manager and associates to gather requirements for reporting. Excel was requested for holding and reporting of the summarized data.
To streamline the process, I designed an Access 2007 front-end form to export data from the SQL Server database to Excel. To extract the data into the Excel worksheet, I wrote VBA code in Access that extracted the data and applied formatting with drill-down capability to the result in Excel.
SQL Server views and stored procedures to query the tables was written and the procedures called from the Access form via VBA script events.
Another reporting project to analyze the metadata of many tables and fields of the multiple database systems was also needed. The scope of the project entailed querying hundreds of fields in multiple tables and comparing the fields between the systems to map the date lineage from system to system.
To create a more efficient method of doing this task, I designed a Microsoft Access front-end database with VBA code that automated the querying of the data against multiple databases in SQL Server, Oracle, and Teradata. The custom frontend had a form with button events and custom ETL scripts which could query multiple database systems at the same time using the same SQL statement and then export the data to Excel.
Extracted data was analyzed by me to identify issues with the derivation of data types, etc. and to validate the lineage of data moving between systems.
The custom querying database allowed the process to execute much faster as 6 different table sources could be set up to be queried simultaneously.
Anthem/ WellPoint Health Networks, Woodland Hills, CA Permanent: October 2000 to December 2010
Sr. Programmer Analyst
Worked as contractor and employee for various IT and finance departments designing and supporting multiple database management and reporting systems. Worked for the Life, Disability, Dental, and Workers Comp claims, sales, finance and IT departments. Over the many years I designed, programmed, and managed multiple databases used in business operations for data entry, for accounting operations, for management reporting, to implement data validation for ETL processes and system migration, and to lower operational costs by making various business processes more efficient.
Responsible for all areas of database development, from the initial business analysis phase through production and support. Multiple database systems including Access, SQL Server and Oracle were developed for new systems or queried for reporting systems. Access, Excel and Word was used extensively for custom reporting tools.
Collected and interpreted business requirements from department managers and end users of the systems.
Translated business rules into database designs and programmed the back-end database system tables and queries and programmed the front-end interfaces and reporting tools.
Designed systems using Visual Basic or ASP.
Created business process documentation, data dictionary documents, system documentation, and user system manuals.
Designed a project tracking database to manage all aspects of projects.
Suggested ways to improve business processes and then implement those ideas for operational improvements to save time and money. Found flaws with existing systems and built improvements.
Trained and supervised users of the database systems,
Analyzed data and performed user acceptance testing procedures myself and with others.
Performed the actual business function on some database projects until staff was hired.
Some of the system I designed include the life insurance membership case and claims tracking systems, workers comp accounting systems, life insurance department medical evidence underwriting system, life insurance department sales incentive calculation and payment tracking system, dental department finance check balancing system, and more.
Computer Technologies:
Systems and Software: Visual Studio 2017 and older. ASP.Net, SQL Server 2000, 2005, 2008, 2012, 2017. SQL Server Management Studio, SSIS, SSRS, Microsoft Access, Microsoft Power BI, Microsoft Word, Excel and PowerPoint (all versions of Office Professional from 2.0 to 2013), Microsoft Visio 2007 and 2013, TFS, Rally Agile tracking software, Adobe Photoshop, and more.
Languages: C# and VB script for ASP.Net 3.5 Web Forms, Java Script, HTML, CSS, jQuery, Visual Basic, Visual Basic for Applications (VBA), SQL Server TSQL, ADO, DAO, ASP, and Oracle PL/SQL.
Education:
Continuing studies via online web sites like Udemy, classes, and books: Topics include project management, web technology such as ASP.Net, HTML, XML, AJAX, JavaScript, CSS, etc., database design, Microsoft Office automation, SQL Server and related tools (SSRS, SSIS), Visual Studio, Oracle, Digital marketing.
Los Angeles City College: Web page design class.
Los Angeles City College: Oracle database 10g class.
Los Angeles City College: Java Script class.
Art Center College of Design: Business for Designers class.
Art Center College of Design: Studio photography class.
UCLA: Project management class.
UCLA: Studio photography class.
UCLA: XML class.
San Francisco City College. Relational database design class:
Berkeley Extension: Graphic design class.
Berkeley Extension: Business Communication class.
State University of New York at Stony Brook: Bachelor of Arts, Political Science.
Contact this candidate