Jesse Roberge

(INTP)(5w4)(S/C)

1425 W Jacinto Ave

Mesa, AZ 85202

(480) 620-6386

YeshuaAgapao@gmail.com



***********

*OBJECTIVE*

***********



To be your back-end to middle tier database and application

performance guru.



*********

*SUMMARY*

*********



**Ten Years of experience in developing database intensive web

  applications.

**Extensive experience in overhauling improperly designed database

  schemas including improperly merged entities, improper references

  between entities, references on the many-side entity failing to

  reference the full unique key of the one-side entity, and first &

  second normal form violations.

**Expertise in writing code for performance and diagnosing, isolating,

  and correcting performance bottlenecks utilizing profiler traces,

  dynamic management views, and application code analysis.

**Experience with ETL processes for importing vendor data for

  e-commerce productss, assimilating bulk job postings, SHRM member

  loads and re-loads, andintegratingg corporate acquisitions' entire

  databases.

**Bring structure and performance to a programming environment

  (classic ASP) which normally is lacking, by utilizing my insights

  and the wisdom of more experienced developers on the team.

**Experience in integrating custom business logic structure in ASP.net

  by extending the page class utilizing inheritance

  and abstract classes.

**Search-heavy web applications that can handle well over a hundred

  thousand page views per day on each server and databases that handle

  over 1500 simultaneously logged in application users.

**Very good out-of-the-box analytical problem-solver.



******************

*TECHNICAL SKILLS*

******************



****Operating Systems****



Windows XP, Windows NT 4.0, Windows 2000,

Windows Server 2003, Windows Vista



****Development****



T-SQL, Classic ASP, ADO, ADO.net, PHP, C#, JavaScript, VBScript,

VB.Net, Visual Basic, C/C++, HTML, DHTML, CSS



****Database Platforms****



SQL Server 2005, SQL Server 2008, SQL Server 2000,

SQL Server 7, MySQL, MS Access '97-2003.



****Project Experience****



Search Engines, Search Agents, Hierarchies, Mapping, Distancing,

Surveys, Product Reviews, Shopping Carts, Tracking, Reporting



****Database Tasks****



index planning / tuning, query optimization, Schema design /

data modeling, normalization, stored procedure / view

development, backups, performance monitoring, user / permissions

management, SSIS, DTS, log shipping, replication.



*********************

*PREVIOUS EMPLOYMENT*

*********************



Medical Data Express - Mesa, Arizona

Sr. SQL Developer, .Net Developer, and SSIS developer

March 2009 to May 2009



**Solved two critical performance issues in aggregate T-SQL queries

  that caused the data load for EncounterPro and Pend Analyzer to

  monopolize the CPU on the SQL Server for many hours.

**Analyzed profiler traces and SQL Server 2005 DMV data for worst

  offending queries. Optimized the database and application

  performance of the EncounterPro application addressing poorly

  written queries and missing and poorly designed indexes.

**Helped adapt the data load SSIS packages from one client that uses

  Oracle for use in a new client that uses Sybase. The SSIS packages

  utilized variables, expressions, loops, and scripts.

**Wrote a NCPDP parser with data-driven schema mapping in VB.Net that

  has flexible schema-mapping.

**Wrote an EDI parser that used data-driven EDI implementation guides

  to convert EDI files to an identical relational schema in VB.Net.

  Later T-SQL would convert and aggregate the EDI response data to a

  star schema for reporting.

**Skills used: 65% VB.Net with ADO.net and SQLBulkCopy, 15% SSIS,

   10% C#.Net for libraries + SQLCLR, 10% T-SQL; 90% SQL Server 2005,

   10% SQL Server 2008; Windows Vista, Windows Server 2003



Integreat Concepts (Med3000 / Mitsui) - Scottsdale, Arizona

Senior SQL Server 2000 / 2005 Database Developer, T-SQL guru, and Database Performance Analyst

September 2007 to February 2009



**Optimized the database and application performance of the IC-Chart

  electronic health records system addressing poorly written queries,

  missing or poorly designed indexes, and excessive ADO web-DB

  round-trips.

**Analyzed profiler traces and SQL Server 2005 DMV data for worst

  offending queries. Optimized the database and application performance

  of the IC-Chart electronic health records system addressing poorly

  written queries, missing or poorly designed indexes, and excessive

  ADO web-DB round-trips.

**Wrote complex data scrubbers, particularly on the isapisal table

  (audit log for HIPAA and CCHIT stuff).

**Wrote automated backup and highly configurable index / statistics

  maintenance stored procedures for both the DEV and client production

  sites.

**Built application customizations for Carle Clinic, Integreat's

  largest client, with over 1500 doctors over a hospital and dozens of

  multi-doctor clinics with over 1500 simultaneously logged-in users

  on their EMR system.

**Responsible for the backups for the development and QA servers.

**Performed SQL code reviews to prevent poorly written SQL code and

  queries from reaching production.

**Skills used: 75% T-SQL, 20% Classic ASP / VBScript + ADO, 2% C# for

  CLI, 2% C# for SQLCLR, 1% SSIS; 80% SQL Server 2005, 20% SQL Server

  2000; Windows XP, Windows Server 2003



Jobing.com - Phoenix, Arizona

Classic ASP Web Developer and SQL Server 2000 / 2005 Database Developer + Administrator + Architect

February 2001 to June 2007



**Refined and expanded the relational database schema design for the

  job board site.

**Optimized queries, refined application code, and tweaked indexes to

  enhance the performance of the job board site.

**Wrote ad-hoc queries and stored procedures for numerous ad-hoc and

  production reports utilizing heavy-duty T-SQL aggregate queries with

  many joins and derived tables.

**Designed and coded ASP applications for the new features of the

  job and resume search engine & agents, mapping, area/category

  hierarchy system, and many other areas of the job board site.

**Coded C# .net applications for automation tasks such as search

  agents, e-mail notifications, etc.

**Performed most of the development DBA duties and some of the

  production DBA duties including backups, permissions, and

  performance diagnostics and tuning.

**Wrote data import / ETL processes to import and convert SQL Server,

  CSV flat-file, and MySQL data from bulk job posters and corporate

  acquisitions.

**Designed and implemented the normalized OLTP and dimensional star

  schema OLAP schema design, T-SQL stored procedures, and classic ASP

  code for an extensive site traffic and event tracking system and its

  reports.

**Skills used: 40% T-SQL, 40% Classic ASP / VBScript + ADO, 10% C# for

  CLI & WinForms + ADO.net, 3% ASP.net / C# + ADO.net, 3% JavaScript,

  2% DTS, 2% HTML + DHTML + CSS; 80% SQL Server 2000, 20% SQL Server

  2005; Windows XP, Windows 2000



Allegro Medical - Scottsdale, Arizona

Classic ASP Web Developer and SQL Server 7.0 Database Developer + Administrator + Architect

October 1999 to January 2001



**Designed, implemented, maintained, and upgraded the relational

  database schema and infrastructure for the e-commerce site.

**Developed stored procedures for the e-commerce site's bulk

  interactions with the database.

**Coded ASP for the shopping cart, universal promotions, customer

  login, affiliate tracking, traffic tracking, and many other

  features of the e-commerce site.

**Wrote data import / ETL processes to import and convert flat file

  CSV data from product vendors.

**Administered the SQL Server database backups and security on the

  database on the production server at DataReturn and the internal

  development server.

**Skills used: 60% Classic ASP / VBScript + ADO, 35% T-SQL,

  3% JavaScript, 2% HTML; 95% SQL Server 7.0, 5% MS Access '97;

  Windows NT



************************

*EDUCATION AND TRAINING*

************************



SQL Server Magazine Connections Conference 2008 - Las Vegas, NV

http://www.devconnections.com/shows/sqlfall2005/default.asp?s=137

November 2008<BR />

Hardcore T-SQL, SQL Server internals and architecture, SQL Server 2008

  new features, DBA best practices, and relational data warehousing



SQL Server Magazine Connections Conference 2005 - Las Vegas, NV

http://www.devconnections.com/shows/sqlfall2005/default.asp?s=137

November 2005<BR />

Hardcore T-SQL, SQL Server internals and architecture,

  SQL Server 2005 new features, DBA best practices, and CLR

  functions & stored procedures



SUNY Institute of Technology at Utica/Rome - Utica, NY

http://www.sunyit.edu

September 1997 to May 1999

BS degree in Computer and Information Science



Orange County Community College - Middletown, NY

http://orange.cc.ny.us/

September 1994 to May 1997

AAS degree in Computer Information Systems



************

*REFERENCES*

************



Available upon request.



**************************************

*          PROJECT HISTORY           *

*Medical Data Express - Mesa, Arizona*

* http://www.medicaldataexpress.com  *

**************************************



General Performance Optimization



**Utilized DMV data from sys.dm_exec_requests to spot the problem

  statement within the problem stored procedure and addressed poorly

  written and redundant aggregate subquery that was causing the

  Pend Analyzer to appear to just 'hang'.

**Diagnosed and revised an aggregate query in a claim roll-up stored

  procedure that caused to intermittently have a runaway execution

  time, monopolizing the CPU on the server.

**Diagnosed some of the worst offending missing indexes and sent

  reports suggesting such indexes in three performance impact groups.

**100% T-SQL on SQL Server 2005, also utilizing SQL Server Profiler

  and SQL Server 2005 Dynamic Management Views.



NCPDP Parser



**Utilizes a strongly typed DataSet for mapping and schema metadata to

  make the same core codebase able to import any NCPDP file.

**Utilizes a dynamically created DataSet based on the schema & mapping

  data and uses SQLBulkCopy to load the tables.

**Maps loops and subloops within NCPDP segments to its own tables with

  foreign-keys correctly interconnected.

**Parses 50,000 NCPDP transmissions in about 90 seconds on local

  workstation connecting to internal SQL Server over the LAN.

**85% VB.net on Visual Studio 2008 targeting .Net Framework 2.0,

  15% C#.net on Visual Studio 2008 targeting .Net Framework 2.0



EDI Parser



**Parses all of the hierarchical data in a EDI file into a relational

  schema. Segment=Row, 2nd+ segments in a loop FK-links back to the

  1st segment in the loop. 1st segment of the loop

  FK-links back the 1st segment of the parent loop

  (ISA/GS/ST envelopes are treated as loops).

**Initially implemented for HIPPA 997 and 824 response files.

  Will work with 277 and 835 response files and even the mighty HIPPA

  837 files for historical insurance claims once the implementation

  guideline schema/mapping metadata is entered into the DataSet.

**Utilizes a strongly typed DataSet for mapping and schema metadata

  to make the same core codebase able to import any NCPDP file.

**Coded custom parsing and SegmentIdentifier-to-LoopID

  mapping/looker-upper for performance.

**Utilizes a dynamically created DataSet based on the schema &

  mapping data and uses SQLBulkCopy to load the tables.

**Automatically creates destination table structure since no schema

  transformation is attempted. Mapping/Schema metadata DataSet

  simplified to three tables.

**Automatically discovers the Segment/Element/SubElement separators

  from the fixed-length ISA segment.

**Utilizes an abstract base-class for the core parsing logic and

  Database specific inheriting class for the database-specific code.

  Allows easy extendability for importing to other database platforms

  such as Oracle, DB2, and Sybase.

**Utilizes a strongly typed key/value pair custom collection for

  tracking the foreign key relationships and identity seeds.

  Has its own enumeration code utilizing a linked list so ForEach

  loops do not have to cast from a DictionaryItem object.

**95% VB.Net on Visual Studio 2008 targeting .Net Framework 2.0,

  5% T-SQL on SQL Server 2005



******************************************

*             PROJECT HISTORY            *

*Integreat Concepts - Scottsdale, Arizona*

*          http://www.igreat.com         *

******************************************



General Health Summary Optimization



**Eliminated excessive application-to-database round-trips,

  particularly in health management indicators, which

  averaged 200-250 per page view.

**Performed a major overhaul to the advance

  directives stored procedure, optimizing the nested aggregates

  (de-dupe by non-unique date, then by the primary key) to use

  ROW_NUMBER, significantly improving performance.

**Utilized a horizontal delimited data -to- vertical

  table conversion inline table-valued function to

  stored-procedure-ize ad-hoc queries that would otherwise have

  to remain dynamically generated SQL.

**80% T-SQL on SQL Server 2005, 20% Classic ASP



Health Management Indicators Trigger Overhaul



**Eliminated the nested cursor infestation in these triggers and

  the stored procedures that they called.

**Rewrote the triggers from scratch from the business logic.

**Achieved a greater than 200% performance improvement, primarily

  from cursor-busting, but also from the simpler general logic.

**Team project with two other developers

**100% T-SQL on SQL Server 2005



General Visit Entry Optimization



**Eliminated excessive application-to-database round-trips,

  particularly in answer saving and the initial creation of the visit,

  which sometimes exceeded over 1500 during one save-and-open.

**Utilized a horizontal delimited data -to- vertical table conversion

  inline table-valued function to stored-procedure-ize ad-hoc queries

  that would otherwise have to remain dynamically generated SQL.

**Rewrote the visit_entry portion of the answer-save process using a

  pre-SQL 2008 method of row constructors to create a temporary table

  and then treated as a data import/synchronization process from there.

**Team project with two other developers

**70% T-SQL on SQL Server 2005, 30% Classic ASP.



Database-wide Index Overhaul and Utility/Diagnostic Procs



**Used profiler traces with self-written worst offender aggregators,

  sys.dm_db_missing_index_* dynamic management views, and for certain

  tables, crawling through the application code and tallying up

  WHERE and JOIN predicates.

**Over 200 indexes added or modified.  Over 100 indexes dropped.

**Developed a script generator that will produce index

  synchronization code for running on a client.  It makes the index

  on the client look the way you want it no matter how it is

  currently set up at the client.

**Focused the main purpose of the utility procs on index optimization,

  but also focused DMV-based worst offenders, lock and connection

  management, and some SQL Server internals.

**Gained significant acceptance by management and co-workers for the

  IndexUsage, MissingIndexes, ConnectionSummary, and TraceAnalyzeDMV

  procs, winning approval to make them part of a release to get these

  procs onto all of the client sites for use by development and support

  staff to diagnose performance problems.

**50% T-SQL on SQL Server 2005, 50% Index Tuning and

  other non-code database tuning



Automatic Index Defragmenter and Statistics Updater



**Optimized for server up-time instead of defragmentation

  effectiveness or execution efficiency.  Typical setups would

  rebuild smaller indexes and reorganize large ones regardless of

  fragmentation in the interest minimizing blocking for 24/7 up-time.

  The parameters could be tweaked to change this behavior, however.

**Highly configurable, enabling the same code to

  be released to every client.

**Scanned indexes for fragmentation one by one in

  priority order up to the scanning time limit, and then

  defragment the indexes one by one by priority, then

  triage order  up to the defragmentation time limit.

**Updated statistics in a priority, then index vs column statistics,

  then triage order.  It did not need a scanning phase.

**Defragmented over both fragmentation and page density

  (if scanned with SAMPLED OR FULL but not LIMITED).

  Page density is adjusted by fill-factor and index padding.

**Gained high confidence by management, which decided to push to make

  it a requirement for performance support in the IC-Chart releases.

**100% T-SQL on SQL Server 2005



Isapisal / Audit Log /

"Defense against HIPAA-related lawsuits table" scrubber



**Filled-in missing patient information utilizing other isapisal

  records linking back to the same item.

**Was mindful of patient moves and involves triple-nested

  derived tables with aggregates.

**Looked out for changing blind keys without a move record to mark it.

**Utilized dynamic SQL and a dynamically created view to allow

  automated flexibility from clients' differing archiving habits.

**100% set-based - no cursors, no loops.

**100% T-SQL on SQL Server 2005



Automated Database Backup System



**Accommodated a database naming convention that involved renaming

  databases as they get re-purposed for a new release of IC-Chart.

**Utilized a name history module for the automated database

  backup system that keeps track of database name history and

  automatically renames backup folders accordingly.

**Ran DBCC CheckDB, then back up the database, then delete old

  backups of each database one-by one sequentially.

**Sent email if a database backup fails and

  moves on to the next database.

**Accepted a database name wildcard filter parameter to allow the

  day of the week to be controlled for weekly schedules without

  re-inventing the wheel of the SQL Server Agent scheduler.

**100% T-SQL on SQL Server 2005



Health Management Indicators Trigger Rewrite #2



**Replaced nested aggregates with an OUTER APPLY to a subquery with

  a ROW_NUMBER.  Eliminates two self-joins.

**Pre-computed this aggregate and stored in a table variable and

  used in two updates. Was formerly four updates repeating the

  whole computation for two subsets of the rows.

**Reduced CPU consumption of updating rows in the

  patient_hm table by 60-70%, which SUM-aggregated to one second of

  CPU-time per 100 executions.

**100% T-SQL on SQL Server 2005



*******************************

*       PROJECT HISTORY       *

*Jobing.com - Phoenix, Arizona*

*  http://phoenix.jobing.com  *

*******************************



Consistent Performance Guru



**When first starting, the resume search was sped up from 'unusable,

  timing out' to something quite reasonable.

**Designed most of the database schema and overhauled some of the

  existing schema for the enhancements to the Jobing.com website.

**In 2001, the overall site was optimized from something

  mediocre to average to quite zippy in speed with no hardware

  upgrades.

**In 2003, the site averaged over 4,000,000 page views per

  month on one web/database server and one offsite SMTP server.

**In 2004, the site averaged over 6,000,000 page views per

  month on one database server, one web server, and one SMTP server -

  two CPUs, eight disk RAID 5, 4gig RAM on the database.

**In 2005, the site averaged over 9,000,000 page views per

  month on one database server (same setup on DB server, faster

  CPUs), two web servers, one SMTP server, one image/map server,

  and one upload server.

**In 2006, the site averaged over 11,000,000 page views per

  month on the same setup with an external fibre-channel

  RAID 1+0 box added.

**50% T-SQL on SQL Server 2000, 35% Classic ASP + ADO,

  10% Index Tuning and other non-code database tuning,

  5% C# + ADO.net



Search Engine Overhaul



**Performance optimization and a lot of new features over

  several projects.

**Original version was using LIKEs and bringing all data into

  page for manual ranking, and was running select statements

  for every individual result.

**Over 150,000 page views per day on search_results.asp

  alone on a single web and a single database server.

**Full Tracking and integration into visitors, sessions,

  page views, and logins.

**Great-Circle Distancing - Both filtering and display.

**On-the fly parameter changes.

**JobingTV and Career Expo integration.

**Expansion of areas based on the universal hierarchy system.

**50% T-SQL on SQL Server 2000, 50 % Classic ASP + ADO



Tracking and reporting



**Visitors, sessions, page views, searches, misc events, and logins.

**Extensive integration into the other systems on the site.

**Designed and implemented Pre-generated aggregate

  reporting tables in a star schema.

**Wrote the stored procedures that populate the fact tables from

  the OLTP tracking tables.

**Wrote a good amount of reporting stored procedures particularly

  with web traffic, searches, and job applications.

**Major data mining potential in the searches. Result keyset

  tracked with clicks, views, FTI ranks, and area-expansion rank.

**85% T-SQL on SQL Server 2000, 15% Classic ASP + ADO



Search Agents



**Full featured searches with the same level of tracking

  and reporting as the regular searches.

**E-mail frames customized by market. All markets and HRW

  clients run in the same process run.

**20,000 job search agents crunched per hour without the

  e-mails - just crunching and tracking the search queries.

**8,000 job search agents crunched per hour when including

  the e-mail sending with a single mail server on the same network.

**65% T-SQL on SQL Server 2000, 25% C# + ADO.net, 10% Classic ASP + ADO



Universal Survey System



**Implemented as virtual interviews, HRW event sign-ups,

  Custom member registration, and HRW universal form chunks.

**Almost any type of question including multi-answer check

  boxes and multi-selects.

**Universal foreign key system allowed loose but seamless

  integration into virtually any system.

**Universal admin, user, and reporting interfaces.

**60% Classic ASP + ADO, 40% T-SQL on SQL Server 2000



Smart 'Entry' Page



**Converts City/State/Zip into Affiliates.

**Many misspellings are accommodated for.

**Robust for users that cannot follow directions.

**Multiple result conflict resolution, especially for

  city-only queries.

**Will use the area hierarchy system to send state results

  to the largest regional site if there is no state-level site.

**Will send to a portal HRW client site if there is absolutely

  no jobing site to accommodate the query.

**80% T-SQL on SQL Server 2000, 20% Classic ASP + ADO



Affiliate/Profiles



**Sites customized by market or by individual client.

**Jobs restricted by one or more of any combination of areas,

  categories, or companies.

**Custom colors, font sizes, and style sheet parameters. HRWs

  have custom framing and a content management system.

**75% Classic ASP + ADO, 25% T-SQL on SQL Server 2000



Universal Hierarchy System



**Implemented for areas and categories.

**Base data is adjacency and the 'rendered' data is a very

  elaborate implementation of transitive closure.

**Optimized for a very high read to write ratio.

**Query virtually any subset, including 'cousins' and

  'aunts/uncles' of a reference node within the tree without

  the need of a join.

**Powers search area expansion in job and resume search and

  Area/Category listings in various forms.

**Can support hierarchical structures as complicated as

  Amazon.com's and Ebay's categories.

**100% T-SQL on SQL Server 2000



Universal Mapping



**Universal .net Microsoft MapPoint mapping component and

  distancing component.

**Has Great-Circle Distance functions and Address to Geocode

  conversion functions.

**Accepts Geocodes or parameters for the universal PK-lookup

  system to look up cached geocodes from the database.

**Actual mapping function returns a URL on Microsoft's server,

  a local image path, or the image itself.

**MapPoint maps are fully cached, with parameterized database

  location and parameterized file-system paths and automated

  sub-folder creation.

**Great-Circle Distancing functions have been ported to both

  an ASP include file and a SQL Server scalar-function.

**MapPoint is obsoleted by Google Maps, which is a lot easier

  to code for and just better.

**50% C# + ADO.net, 30% Classic ASP + ADO, 20% T-SQL on SQL Server 2000



Universal job listing system



**Extremely flexible way to show jobs for affiliates with too

  few jobs to need or want a search.

**Emulated 3-tier structure (classic asp) with the middle tier

  as an include-file function library.

**Three major formats and all display components are optional.

**Three overall structural options plus over a dozen

  individual element options for the layout.

**5 Restriction options beyond the affiliate's filters -

  further by one or more companies, one or more areas one

  or more categories, one or more industries, or the job's age.

**60% Classic ASP + ADO, 40% T-SQL on SQL Server 2000



Universal site_email system



**Universal system of configurable e-mails and universal

  removal of e-mail addresses from hyperlinks on the site.

**Emulated 3-tier structure (classic asp) with the middle

  tier as an include-file function library.

**Customizable header/footer frame for each e-mail type

  or category.

**Additional, multiple footer messages by e-mail type.

**Can auto-lookup e-mails from IDs fed by page/link (meaning

  is determined by e-mail type).

**It also can be fed plain e-mail addresses, or run off of

  e-mail addresses put in the database record.

**Universal Tracking for auditing and reporting tracks

  everything except the e-mail body text.

**Tracking includes links to session / visitor / navigation /

  login tracking.

**60% T-SQL on SQL Server 2000, 40% Classic ASP + ADO



Universal PK-to-data Lookup System



**Used by Site_Email system and map geocoding.

**One stored procedure call will look up one or two values

  via parameters.

**It looks up the FK reference via metadata tables and

  retrieves the values via output parameters.

**Another version of the stored procedure will return the

  looked up values for multiple comma separated PK values

  to a table variable.

**100% T-SQL on SQL Server 2000



Universal banner system



**Multiple random banner pools.

**Multiple display locations per page, same pool or

  different or mixture of same and different.

**Multiple restriction dimensions per banner/pool combination.

**Banners are weighable within their random pools. A weight of

  'four' means four entries in the random pool.

**Universal click and view tracking that can support very

  analytical data mining.

**Very adaptable and flexible.

**50% T-SQL on SQL Server 2000, 50% Classic ASP + ADO



FeedMeSQL



**Universal SQL-to-HTML service that can work via web browser,

  write to file, or send thru e-mail.

**Older ASP version that has a 'middle tier' include file and a

  universal portal ASP file. ASP file interface needs admin login.

**Many reports in JRD use direct function calls to FeedMeSQL.

  The ASP page that requires admin passes SQL through the query

  string to FeedMeSQL for those <15 min whip em up quick reports.

**.Net version has a FeedMeSQL.dll middle tier and a

  universal Portal .EXE and also a number of other processes

  call FeedMeSQL through the DLL.

**.Net FeedMeSQL can also do Basic or Pretty HTML, Excel, and CSV.

**Biggest Time savings is when sending multiple result sets

  in separate worksheets in an Excel file.

**60% C# + ADO.net, 35 % Classic ASP + ADO, 5% T-SQL on SQL Server 2000



DataAssimilator



**Imports an import data file into a staging table on any SQL Server.

**Currently supports CSV and one worksheet at a time from excel.

**Either can import to a pre-existing table or to just have it

  read the schema out of the import file and create a new table.

**Option to run an import script automatically after staging

  import is completed.

**Option to send a status report e-mail via FeedMeSQL.dll.

**90% C# CLI + ADO.net, 10% T-SQL on SQL Server 2000



Database integration of acquired companies



**Integrated the databases of two acquisitions -

  Careers Colorado and Rhinomite.

**SQL Server and MySQL data sources.

**Used DTS to import to staging tables and

  T-SQL upserts to assimilate.

**Additional staging tables for metadata mapping.

**More staging tables to reduce the number of joins required for

  unique identification of related table data and foreign-key mapping.

**Took advantage of natural-data alternate keys (Unique constraints on

  real data rather than generated identity values) to enable the

  upserts to work.

**20% DTS, 80% T-SQL on SQL Server 2000



Data import / integration / ETL of HRWebsite member data.



**Over three dozen human resource organizations serviced; both new

  clients and refresh synchronization with new clients.

**Primarily flat-file CSV data sources, some Access and Excel.

**Varying quality of data, but none were really bad.  Mediocre to

  good.  Some 1NF violations, primarily with member names.

**DTS to staging table, bulk T-SQL upserts to assimilate.  Any

  required light scrubbing was done with T-SQL case statements and

  string functions.

**20% DTS, 80% T-SQL on SQL Server 2000



Data import / integration / ETL of bulk job postings



**Banner health was the first client.  Later there were about a

  half-dozen bulk-posting clients.

**Banner health used excel spreadsheets as a data source.

**The later clients were conformed to an intermediate schema and

  used CSV files as a data source.

**Used DTS (for Banner Health) or an automated .Net process (the

  other clients) to import the CSV data to staging tables and used

  T-SQL upserts to assimilate the data.

**Source data quality was fairly good.

**10% DTS, 30% C# + ADO.Net, 60% T-SQL on SQL Server 2000



***************************************

*           PROJECT HISTORY           *

*Allegro Medical - Scottsdale, Arizona*

*    http://www.allegromedical.com    *

***************************************



Overhaul of shopping cart



**Previous system had an unsuccessful attempt to differentiate

  an order from a customer, which caused an inability to have

  an effective order history beyond the order date.

**Designed additional normalized schema for adding support for

  storing and handling multiple addresses and multiple credit cards.

**Normalized the product categories and product suppliers database

  schema into separate tables so a product can have more than

  one category or more than one supplier.

**50% T-SQL on SQL Server 7, 50% Classic ASP + ADO



Product review system



**Short ratings survey plus a text review.

**Users can vote whether the review was helpful or not.

**Review rating questionnaires are customizable by product

  category.

**Summaries integrated into featured product rotators and

  product listings.

**60% Classic ASP + ADO, 40% T-SQL on SQL Server 7



Unlimited capacity featured product rotator



**Existing featured product system did not rotate and was

  limited to six products because of 1st normal form violation.

**Separate random pools for the home page, each category,

  and each subcategory.

**Pool entries were weighable. A weight of four would mean

  'four entries into the pot', but only within the code, not

  in the database.

**50% T-SQL on SQL Server 7, 50% Classic ASP + ADO



Wheelchair configurator



**3-Tiers: Sections, Sub-Sections, and choices.

**Custom configurator profiles for each product.

**Flexible enough to use on non-wheelchairs such

  as catheter sizes and product colors.

**Seamless integration into the shopping cart.

**70% Classic ASP + ADO, 30% T-SQL on SQL Server 7



Universal promotion system



**Fully integrated into the shopping cart.

**Highly flexible to accommodate marketing's unpredictable ways.

**Promotion conditions could be scope-restricted to one or

  more products, one or more subcategories or categories,

  one or more industries, or global.

**Promotion conditions can be expressed as product quantity

  or dollar amount.

**Promotion conditions can be greater than, less than, between,

  or equal too.

**Promotions can be locked by a coupon code with or without

  other conditions.

**Multiple promotion concurrency handling and automatic

  resolution of line-item conflicts.

**75% T-SQL on SQL Server 7, 25% Classic ASP + ADO



Tracking system



**Powers many of the memory features of the site.

**Visitors, sessions, searches (keywords only), and

  limited page views.

**Able to generate reports on page views, sessions, visitors,

  search keywords, and logged in customers by product,

  subcategory, category, or industry.

**Used the SessionID to look up the most recent product,

  category, subcategory, or industry viewed by the customer

  and fed the 'continue shopping' button with that information

  to have the button send him back there automatically.

**85% T-SQL on SQL Server 7, 15% Classic ASP + ADO



Import / Integration / ETL of vendor product data



**Imported and synchronized (insert, update, delete)

  product data from two of Allegro's largest vendors.

**CSV flat-file data source.

**Data quality was very poor.  Rampant 1NF violations and

  much scrubbing was required.

**DTS to import to staging table.  Application code was required to

  scrub the data because of its poor quality and inconsistency.

**Application assimilated all of the scrubbed data into the

  e-commerce schema.

**10% DTS, 75% Classic ASP, 15% T-SQL on SQL Server 7.0