Mark D Van Alstyne
Expert
Business Intelligence Analyst and SQL Developer, Senior Database Engineer,
Senior Internet Application Architect, an expert problem solver, and an asset
to any team.
|
Languages/Tools |
Databases
Years
Experience |
|
SQL (21 Years of experience, Many Certifications) PL/SQL (Oracle; 7 Years) Transact-SQL (T-SQL; Sybase and
Microsoft; 19 Years) SQRiBE / SQR / (OLAP / Business
Intelligence) Erwin (16 years; OLTP, HOLAP, Star,
Snowflake, Dimensional) SSIS/DTS (Microsoft) PL and Transact SQL Profiling and
Performance Tuning SQL*Plus, SQL*Loader, MS Query
Analyzer, ISQL ColdFusion (CFML) 3 through 9 MX (Certified Brainbench
Master) AJAX / HTML / DHTML (11 years) JavaScript (Certified
Brainbench Master) Java XPath / XSL / XSLT xBASE [ dBASE
II, III, IV / Clipper 5 / FoxPro DOS & Windows ] AWK / SED / GREP Advanced Regular Expressions Korn-Shell Scripting Windows Scripting |
Oracle 7 Oracle 11g 2 Oracle 10g 3 Oracle 9i 2 Oracle 8i 1 Oracle 7.3 2 MS
SQL Server 6.5/7/2000/2005/2008 7 Sybase 12 Sybase 15 2 Sybase 12.5 4 Sybase 11 3 Sybase 11 Replication Server 1.5 Sybase 11.5 Adaptive Server 1 Sybase 10 3 OMNIConnect 2.5 Access 5 xBASE 12 SQLAnywhere 1 Foxpro 4 MDI DB2 Gateway 2.5 Clipper 2 Omnidex <1 |
PROFESSIONAL TRAINING &
CERTIFICATION:
BS
Business Administration: Information Systems & Quantitative
Analysis
BS
Business Administration: General Management
Extended Advanced Standard General Ledger, US Department of
Treasury (8/08)
Oracle 9i DBA Fundamentals II (4/05)
Introduction to Oracle 9i: SQL [1Z0-007 Certification]
(7/05)
BrainBench
Certified
BrainBench
Certified Master MS SQL Server 2000 Administration (2/05)
BrainBench
Certified Master RDBMS Concepts (5/02)
BrainBench
Certified Master Cold Fusion MX Programmer (5/06)
Sybase Certified Associate – Adaptive Server Administrator (7/98)
Sybase Replication Server:
MCI (7/96)
Sybase System 10 Performance and Tuning: ServerLogic
Corporation (2/96)
Sybase System Administration: Metacorp Strategies
Inc. (4/95)
Allaire Spectra Training: ICG (8/00)
Fast Track to Sybase: CTR, inc. (6/94)
Computer II & III; Z80 Assembly, Basic, and Pascal:
Basic Programming (1979)
SECURITY CLEARANCE:
Background
Investigation (BI) – Public Trust Level 6 Designation
Work
History – 7 Years
Personal
Information – 5 Years
Education
Verification
Financial
Review
PROFESSIONAL
& SOCIAL ORGANIZATIONS:
Worked in conjunction with the IBM task
force to determine the holistic computing needs of PSU and how to meet those
needs.
Association
for Systems Management,
Student Chapter; PSU
President
(Elected) : Membership grew more than 500%.
Secretary
(Appointed) :
Organized quarterly diskette sales. Revenue per term of $1,100
Chairperson: Undergraduate Micro-Computer Lab
Advisory Board. Advised PSU’s Finance
vice-president on general access computer lab policies, management, budget, and
new equipment recommendations.
Activities:
Cycling (Ride the
PROFESSIONAL EXPERIENCE:
|
Small Business
Administration, Office of
Financial Systems |
7/2006 – Current |
Database
Engineer Consultant [Sybase 15, Transact-SQL, Oracle 11g, PL/SQL, ColdFusion MX
9, XSLT, dBASE IV]
Created a data
warehouse comprised
of all of the SBA’s transactional loan history at the general ledger level.
Created the automated
verification and validation of public facing reports produced by the Office of
Financial Analysis before they could be presented to the public.
Subsequent phases allowed for the full
automation of the process. The Office of
Financial Analysis & Modeling office sends via FTP a CSV file of their
report. This triggers a load of their
data followed by the auto-generation of 5 workbooks containing over 30
reports. The reports compare their
totals to our generated totals. If there
are any differences beyond the defined thresholds of amounts or percents, the
rows are highlighted red or yellow (Depending on the severity), the associated
sheet within Excel is also highlighted, and the report distribution is then
adjusted to another audience for review.
(All fully automated)
The reports also allow for drill down by
linking through to the ColdFusion MX 9 server for every amount shown. If the user clicks on an amount, it will
request a detailed spreadsheet of the supporting loans which comprise the
amount (in real time directly from the data warehouse).
This process is now fully automated and
requires no involvement from the Office of Financial Systems Denver Finance
Center staff.
The data warehouse is now widely utilized for
extensive agency wide automated reporting involving everything from Budget
Authority, Recovery Act Balances, Microloan Intermediary Disbursement and
Outstanding Balance Reports, Automated Journal Voucher generation, Return on
Assets, Recovery Act OMB Grants, and many more.
Created the Negotiable Instrument
Control System (NICS)
which manages the workflow of incoming and outgoing negotiable instruments
(Checks) which flow through various departments within the office of the Chief
Financial Officer. Checks are scanned on
arrival in the mailroom, visually verified and confirmed within the system upon
transfer from the mailroom to the deposit processing room, then scanned again
prior to departure.
Reconciliation between arriving and departing
checks then ensures all checks are accounted for and processed.
Users of NICS record scanned images for both
incoming (Mailroom) and outgoing checks (Deposits or returned to Treasury), and
it allows for the automated matching and manual reconciliation of the checks.
If there are any uncertainties regarding matching MICR values between arriving
and departing checks, then the items are presented to the users visually
overlapping each other. Imagine holding
two pieces of paper up to the light and then adjusting their alignment (and/or
zoom) until you can determine if they are identical. This is what the assisted matching of check
images does for the users utilizing advanced DHTML and AJAX programming.
Other authorized users within the
agency can then search for items when necessary rather than using the older
microfilm technology.
The system is linked with the SBAs loan
accounting data which can then be utilized when searching for checks by loan
number, check number, amount, or deposit ticket number.
A fully automated Excel report generates
monthly and shows how well each department is meeting their goals for
processing negotiable instruments. The
report has dual scale graphs (% completed within 1 day) combined with a bar
chart (showing volume), with an associated data table (conditionally color
coded for how well goals are being met).
The first iteration of this project was
completed and in production within 30 days of initiation. There have been several additional iterations
adding improved functionality.
Created the initial Financial Data
Quality Assurance Framework document used by the agency to satisfy the SBA Data Quality
Plan requirements of the Federal Funding Accountability and Transparency Act (FFATA)
and the American Recovery and Reinvestment Act (ARRA).
Created screens to
enhance the budget execution workflow of the SBA to satisfy requirements of the
Anti-Deficiency Act (ADA). These web screens
determine the remaining Budgetary General Ledger balances for apportionments
and allotments and then allow authorized users to either allot or to un-allot funding authority while ensuring adequate funds are
available for all actions. After a
confirmation workflow via the rule-of-two, the allotments are then transmitted
in real-time via web-services and text files into their appropriate
systems (Agency General Ledger, eTran loan origination system, Mainframe Loan Accounting
System, and the LAA system) Automated emails are then sent across the agency
after every approval outlining the funding authority changes.
Worked in a team
to create a fully automated Excel spreadsheet report generation process. The reports allow for the presenting of database driven
reports (Sybase and Oracle) within Excel utilizing VBA Script, Windows Script
Host, and event driven execution. These
reports were adopted by the agency for reporting the Consolidated General
Ledger Balances daily to management and reporting the Recovery Act Funds
Availability on a daily basis. The many reports that I authored included pivot tables, graphs,
trend lines, conditional formatting, drill down, and many other automated
features for a truly interactive user experience.
Completed a course in the Extended
Advanced Standard General Ledger offered by the US Department of Treasury and taught by
Michael Lamb.
|
ADP Ft Collins, |
4/2006 – 6/2006 |
Database
Engineer Consultant [PL/SQL] (3 Month Contract)
Developed Oracle 9i PL/SQL Packages,
Triggers, Stored Procedures, Functions, DDL, and DML for an extranet application that
facilitates job applicant processing (Background checks, reference checks, and
criminal history).
Authored a PL/SQL Meta-phone
package which
allows the comparison of phonetic phrases and phonetic pattern matching of
strings to identify county locations from free form address data.
Utilized ClearCase source control,
TOAD, and PL/SQL Profiling to facilitate development. Authored
data models utilizing Oracle Designer.
|
TravelClick |
4/2005 – 3/2006 |
Software
Engineer (PL/SQL, Java, CFML, HTML, JavaScript)
Developed Oracle 8i & 9i PL/SQL
Packages, Triggers, Stored Procedures, Functions, DDL, DML for an extranet in the Hotel and
Travel industry. Developed Java which was
then called from Cold Fusion to handle the compiling, sending, receiving, and
parsing of XML.
Created Cold
Fusion markup language
(CFML) components (CFC) for both graphical user interface (GUI) and backend database
interaction. Created data modeling and data
definition language (DDL) to support development efforts.
Worked with a team
of 7 other senior developers to coordinate the creation of a new international version
of the company’s flagship product (Channel Manager). Utilized SourceOffsite (Visual Source Safe) source control, Homesite, Eclipse, TOAD, and RapidSQL
to facilitate development.
Developed Asynchronous Javascript and XML (AJAX) to provide client server style behavior within the
browser-based user interface. Authored data models utilizing
Erwin.
|
InsightAmerica |
2/2005 – 4/2005 |
OO-Developer
Consultant (3 month Contract)
Developed Object Oriented Cold Fusion (Mach-II) solutions that provided sophisticated
access to public record information.
Technologies involved include Omnidex, MS SQL
Server 2000, Cold Fusion MX 7, Eclipse, VSS, and HTML. Utilized
Subversion source control and Eclipse to facilitate development. Developed Asynchronous Javascript
and XML (AJAX) to provide client server style behavior within the browser-based
user interface.
Authored data
models
utilizing Erwin.
|
CenterStone
Technologies, Inc. |
9/2000 – 12/2004 |
Vice
President of Technology, Architect, Database Engineer, Technical Team Lead
Performed
technical implementations for new clients. Using the Inovis Trusted Link Enterprise (6.0.2) translator to
transform EDI into text/XML and then SQL to transform the text/XML into the
normalized OLTP tables of the application. Outbound OLTP -> Text -> EDI/XML was
also my responsibility (Complete life-cycle integration). EDI Documents created: Outbound (850,
997) Inbound (855, 856, 997).
Architected and then developed DTS
packages to perform the proprietary EDI connectivity with trading partners (Marmot, The
North Face, Mountain Hardware, Obermeyer, Pearl
Izumi, and many others) Many of the
electronic data interchanges were proprietary formats which were managed by DTS
packages. Some of the DTS packages also
performed EDI 850, 997 and 856 formats that were created and processed using
DTS packages and stored procedures that I authored.
Proprietary integrations included
Customer Master upload (hierarchical) with home-grown phonetic
address merging; Availability/Inventory uploading (w/ On-Hand and Open-To-Sell
availability); Style/Product Master upload/download (w/ multiple price lists
and currencies). All proprietary
integrations were capable of the full (CRUD) modification ability (Create,
Read, Update, Delete/Disable).
Authored comprehensive enterprise data
models with
multiple subject areas utilizing Erwin.
Managed a small
yet highly skilled web development team in the development of a B2B website application.
Utilized Agile
Development Methodology
for Service Management.
Developed extensive Asynchronous JavaScript
and XML
(AJAX) to provide client server style behavior within the browser-based user
interface as well as a robust stand-alone AJAX order management Hypertext
Application (HTA) that was used by traveling the sales people of our clients.
Technologies involved included ColdFusion
5, MS SQL Server 2000, Windows NT 4.0, Visual Source Safe 6.0, Exchange Server
2000, Windows 2000, and Outlook 2000.
Programming languages employed were
HTML, DHTML, JavaScript, Cascading Style Sheets,
Responsibilities included database
administration, data modeling, technical sales (Commission based), personnel
management, senior management involvement, application architecture,
application design, and full life-cycle iterative application development.
|
EchoStar
Communications, Corp. |
9/2000 - 2/01 |
Database
Engineer Consultant
Worked
on the Web order Management Development team creating reusable and maintainable
reports (With dynamic SQL which built SQL to use for the OLAP reports).
Used Transact-SQL and Cold Fusion Markup Language. Also assisted with
troubleshooting production performance issues.
|
ICG
Communications, Inc. |
1/1999 - 9/2000 |
Architect,
Database Administrator, Consultant
Architected and
implemented an enterprise Internet/Intranet/Extranet network/server/database
infrastructure
for ICG Communications, Inc. which provides 100% availability of all web
servers and databases during all planned maintenance of the hardware, software,
and databases. As well as near 100% availability during any
unplanned outages. The architecture utilizes Resonate Central Dispatch
software to load-balance the production web-servers.
Architected, managed development, and implemented a RAS/VoIP order
entry, workflow (Bi-directional), auto port and TN assignment, and
capacity management web site. (The RASinator
project)
Architected,
developed, and implemented an On-Line Auction for quarterly sales
incentive programs, which utilize Cold Fusion and MS SQL Server.
Authored
comprehensive enterprise data models with multiple subject areas utilizing Erwin.
Developed Asynchronous
Javascript and XML (AJAX) to provide client server style
behavior within the browser-based user interface.
Architected, developed, and implemented nationwide a Sales Force and
Field Automation lead management, distribution, and statusing
web application utilizing Cold Fusion and MS SQL Server.
Architected,
developed, and implemented an ISP Extranet web site (The menu interface
was out-sourced) which involved DSL Order entry by ISPs and Order Statusing utilizing NorthPoint
Communications Extranet. A large expansion of this project is planned and will
include ICG order entry, statusing, trouble tickets,
loop qualify, and interfaces to billing and provisioning via Vitria's businessware server.
There will also be multiple APIs associated with multiple DSL vendors.
Acted as the MS SQL Server 2000 DBA for the web development department.
Assisted developers
with analysis, requirements gathering, data-modeling, and SQL.
Technical
lead of a team of 4 developers for a logical and physical circuit provisioning
and capacity planning web based tool which tracks equipment
cross-connects down to the DS0 level.
Worked on a HR
Benefits project which involved linking MS SQL Server databases with Oracle databases to provide direct access to Oracle Peoplesoft tables invisibly via MS SQL Server linked
tables.
While at ICG, I became very familiar with telecommunications terminology
including the technical infrastructure and the business issues of most of the data
products.
Primestar,
Inc. |
1/1998 - 2/1999 |
Database
Administrator, Consultant
Implemented an automated notification
of database problems via SQLMail on 12 MS SQL Servers. Created a
database threshold stored procedure for implementing incremental backups of the
Sybase databases. Became
familiar with Oracle enough to be considered at the junior level technically. Supported Powerbuilder and Cold Fusion developers with their SQL
needs.
Using MS Project,
I managed a project for automating the Payroll Timecard data entry via a Cold
Fusion web
application using a MS SQL Server backend. Worked
closely with the developer and the end users to determine the requirements. Created the logical and physical data models
in Erwin and created stored procedures which extracted CSV comma delimited files
which were e-mailed directly from the MS SQL Server via SQLMail
to the end-users. Also
used Silverrun to create a data flow diagram of the
process.
Architected and implemented an
enterprise Internet/Intranet/Extranet network/server/database infrastructure for Primestar,
Inc. which provides 100% availability of all web servers and databases during
all planned maintenance of the hardware, software, and databases. As well as near 100%
availability during any unplanned outages. The architecture utilizes Resonate Central
Dispatch software to load-balance the production web-servers.
Installed and configured Resonate
Central Dispatch, SQRiBE Reportmart/WebSQRiBE, and Scopus WebTeam.
Generated budget details within Solomon
Financials from actuals with a zero variance using SQL.
TCI, SUMMITrak (CSG) |
3/1997 - 12/1997 |
Database
Administrator, Consultant
Responsible for upgrading to Sybase
11.0.2.2 in a SMP production environment which consists of 11 hosts with 39 databases on 26 dataservers including 11 replication servers (This was accomplished with no down time for
the users during regular business hours).
Prepared for and performed a complex data-model upgrade of the
replication environment involving over 100 tables in this complex production
environment. Implemented multiple DSI
threads in production and performed performance tuning of the replication
server configuration variables. Setup
function string replication which supports real-time DSS reporting from this
distributed architecture.
Worked in a team of 13 Database
Administrators
Supporting 200 dataservers, 150 LTMs, 40 Replication
servers, and 300 Openserver Applications.
Assisted
Datamodelers, Developers, Testers, and other DBAs in
all database related issues.
Responsible for Production Support,
Non-Production Support, Replication Server maintenance and tuning, Stored Procedure optimization, and
automating processes through the creation of ksh, awk, and perl scripts including:
Creating ksh
scripts which
monitor replication latency, historical monitoring statistics, multi-server
consolidated blocking reports, replication queue activity, stored procedure
index usage report, replication server DDL extractor, automated sa/ltm/rsi
password changing scripts, automated Sybase version upgrade scripts for dataservers and replication servers, and many other useful ksh, awk, and perl
scripts.
Sun SparcServer
1000/4000 : Sybase 11.0.2 (Sun Solaris, Word 6.0,SQL
Server Manager,
Sybase Historical Monitor Server, Sybase Monitor
Server/Client, Sybase Replication Server Manager,
UNIX SCCS.
|
MCI, Video
Conferencing Reservations |
6/1996 - 3/1997 |
Database
Administrator, Consultant
Responsible for
converting to Sybase 11 in a SMP environment. Administering
development and production Sybase dataservers and
replication servers. Troubleshooting replication and dataserver
problems. Worked in a DBA team
environment to design and implement automated dumps of Sybase transaction logs
for improved recoverability. Programmed stored procedures, triggers, embedded SQL, SQR, and some
C++. Created complex UNIX scripts
in Korn Shell that utilize awk, sed, grep,
and many other tools. Also created
automated server log monitors that sent out text pages and e-mail of errors
that occur in the Sybase logs.
RS/6000 :
Sybase 11.0.1 (AIX SMP) S-Designer 5.0, Word 6.0, Windows NT/95, eXceed, SQR SQL
Server Manager, Sybase Historical Monitor Server, Sybase Monitor Server/Client, IBM Configuration Management Version Control.
|
Marketing One,
Inc. |
3/1992 -6/1996 |
Database
Administrator
Responsible
for all databases and database gateways. Ensured integrity and maintained all
databases. Established
security controls and backup strategies.
Performed recovery of databases when necessary. Maintained server and gateway software and
related operating system settings. Assisted developers with code reviews and performance testing. Approved database design changes to minimize
redundancy, and kept track of where all data is stored and how various systems
use that data. Maintained comprehensive
documentation for all
databases and database maintenance procedures. Monitored databases to
pro-actively identify problem areas where performance improvements where
possible.
Duties
included regular maintenance and performance enhancements on the following
systems and platforms:
Sun SparcServer
1000: Sybase 11.0.1 (Powerbuilder, Access, Foxpro,
Solaris UNIX, SMP); Converted from 10.0.2
Sun SparcStation
20: Sybase 4.9.2 (Powerbuilder,
Access, Platinum, Solaris UNIX, SMP)
Novell Netware: MS-DOS (Foxpro, dBASE, Excel)
NT Server 3.51:
Installed/ Configured the following:
OMNIConnect
10.5, NT SNA server 2.11,
MDI AS/400 Database Gateway, and SQLAnywhere
Responsible for
all databases
(3 production, 3 development, and 3 test databases), network administrative
duties, and database gateways. The multiple
databases and servers ran on numerous platforms.
In addition to administrative tasks, I
designed and created a program in Powerbuilder to
perform automated monitoring of the entire Sybase system. Program included automated paging and e-mail
notification of problems.
This position has included being the
primary Sybase contact for Marketing One, Inc., and actively troubleshooting
connectivity issues to the OMNIConnect database. The database servers reside on individual
systems that have been upgraded, enhanced, and maintained by me. Installed and setup the AS/400 side of the SNA
server’s communications (via communication protocols), and configured a vendor
software database (maintained by the vendor) on the AS/400. The NT SNA Server, OMNIConnect,
SQLAnywhere and related hardware were purchased by Marketing One on my
recommendation.
Programmer
Analyst 3-92
- Promoted
Responsible
for the installation and support of the company’s financial software package
(Platinum),
including user data modeling, history conversions, physical
implementation and configuration. I received a
"Silver
Eagle Award" from the VP of Operations for my work on this project.
I revamped the company’s sales tracking
system
(originally in dBASE) by developing new front end and
back end features. I also provided
intense performance tuning during and after the progressive conversion to Foxpro.