Most impactful software engineering
Overview
DB saved 50% time, effort
ADP Payroll (Seattle, WA) – Made a database to automate reports; saved >50% of the employees' time and effort
| Before | After |
|---|---|
| PROBLEM: Payroll Specialists had very manual and labor-intensive reports to create. They had everything in various spreadsheets and documents, which took a long time to sift through when compiling data needed for weekly reports. | MY SOLUTION: Use a database to unify and create reports. 1. I created a MySQL database to connect all of their spreadsheets and documents into one cohesive place. 2. I made reports that ran automatically on the schedule they needed them. |
| What used to take 8 hours, now took 4 hours. | RESULTS: This new system freed up 50% of their time to do other things. |
92% drop in helpdesk queue
Microsoft (Shanghai, CHINA) – Redesigned their Helpdesk system that reduced their call queue by 92%
| Before | After |
|---|---|
| PROBLEM: Daily call queue of 120+ customers never reduced. Each day, the director randomly chose a single time zone of North American customers to be called: 1. Call the customer's home. 2. If no answer, call their office, but only on another day. 3. If the 2nd call wasn’t answered, the case was closed as “unanswered”.  | MY SOLUTION: Call customers' preferred number at preferred time. 1. I was hired to manage a call center where staff would write down customers' issues for Microsoft Engineers to reply; instead, I saw many issues I knew the answer to and solved most of them while the customers were on the phone. 2. I redesigned their website and system to: - Use customers' preferred phone number and time. - Sort calls by time zone of customers’ preferred time. |
| (10 calls) / (120 calls) = 8% | RESULTS: - Queue reduced from 120 to 10 calls in 2 weeks. - Duplicate and repeated calls reduced to 0. - Customer satisfaction skyrocketed due to me solving most of their questions on the phone. |
12x faster warranty website
HP (Mt. View, CA) – Redesigned their database tables; website ran about 12x faster
| Before | After |
|---|---|
| PROBLEM: Web application ran very slow. Lead Engineer optimized the database table for humans:  His code that queried the database:  | MY SOLUTION: I re-designed the database table and code. I optimized the database table for computers:  I re-wrote his code as:  |
SELECT * needs 2 network trips (1 for field list + 1 for field) WHERE clause requires scanning the whole table | RESULTS: My code ran about 12 times faster. |
75% increase in productivity
Bridgestone (Bloomington, IL) – Automated their label printing system; freed up 75% of staff's time
| Before | After |
|---|---|
| PROBLEM: Staff didn't have enough time to do everything needed. 1. Staff would print the labels needed for a single part of a particular batch. (5 min) 2. Staff would wait (15 min) until that part finished before printing labels for the next part because their SQL application would print only one part at a time. 3. Staff would go back to Step 1, repeating the long wait times throughout the day. | MY SOLUTION: Change how their SQL Server application operates to select and print labels of all steps of a batch at a single time. I re-designed their software to print all labels of all steps of a given batch at a single time, so the staff were available to do other things while all the needed labels printed on their own. |
| 15 of every 20 min were waiting (75%) | RESULTS: The staff's waiting time (75% of the day) became productive time on other tasks |
0 wrong orders + time saved
Pueblo Grocery (Miami, FL) – Created a product ordering system that resulted in 0 wrong orders and 25% time saved
| Before | After |
|---|---|
| PROBLEM: There were so many wrong orders. Staff would walk up and down aisles to count the number of each item on the shelves as well as the number in the storage room to determine when and how much to re-order. (2 hours every day) | MY SOLUTION: Use a database to streamline their ordering process. 1. I created a SQL database that received item information directly from their pricing guns and check-out registers. 2. I wrote queries and reports to show how often each item was being bought and when to order more of each item. |
| 2 of 8 hours = 25% | RESULTS: - no more wrong orders - 25% saved of staff's daily time |
$1 million per month saved
A mall landlord (Miami, FL) – Ran SQL queries to uncover a $1 million discrepancy in monthly rent totals
| Before | After |
|---|---|
| SITUATION: The landlord of a mall billed his renting stores and shops every month. Over $100 million each month. Seeing 9-digit numbers made me think about things I've seen in various movies, so I wondered if these numbers were accurate since a tiny amount off would be easily missed and probably nobody would notice. | MY SOLUTION: I made SQL queries to confirm the monthly totals that were coming out of their Siteseer application. |
| RESULTS: The numbers were, in fact, off by 1%, which equated to a little more than $1 million dollars each month. |
Law contracts that reuse text
A law firm (Santa Clara, CA) – Created DITA-like database to reuse paragraphs of text in multiple contracts
| Before | After |
|---|---|
| PROBLEM: Law firm made dozens of contracts daily and needed an easier way to create new contracts by reusing existing paragraphs in other contracts. They were spending way too much time copying-and-pasting. And they wanted to be able to reuse paragraphs in any order in their new contracts. And they needed a solution that used only their existing software: Microsoft Office. A. Using MS Word, paralegals would search for paragraphs of text that their firm had written in other contracts to copy and paste them into a new contract. B. This was very time-consuming and tedious. C. They didn't have a central place for all paragraphs that were commonly reused in their client contracts. | MY SOLUTION: Per their budget, I created a MySQL database to store all of their contract paragraphs and made a form mechanism to add paragraphs to a new contract that allowed them to rearrange the paragraphs. I created a database with four tables: - Paragraph_Type - for the category and use cases - Paragraph_Text - for the text, keywords, and Paragraph_Type - Contract_Type - for the different contract categories - Contract_Template - of the commonly used contracts and paragraphs I made a mechanism that shows a blank form where the lawyer would choose from dropdown lists the paragraph types and paragraphs of text, searchable by keywords, and movable (up or down) within the new contract. I made queries to find specific paragraphs. I made reports that were contracts with all of their front matter followed by their desired paragraphs followed by the legal disclaimers at the end. |
| RESULTS: I created something similar to DITA three years before IBM. The law firm loved their new contract-creation system. |
Company reorg Visualization
AMAT (Santa Clara, CA) – Displayed dynamically how different company reorganization scenarios would affect the whole
| The problem | Every few years, AMAT performed company-wide re-orgs that improved some departments but hurt other departments. |
| VP's vision | To simulate a proposed re-org before implementing it to predict how each re-org scenario would affect productivity, imbalances, skill gaps, and department budgets. |
| My solution | VB code to dynamically display the proposed re-org from MySQL queries after an ETL workflow of multiple data sources. |
| Extracted data | Description |
|---|---|
| PeopleSoft database | ~20,000 rows of employees' salary, bonus, and perks. |
| PowerPoint slides | ~40,000 slides (1 - 4 per employee), containing performance reviews, strengths, weaknesses, skills, and skills needed. |
| Word documents | ~20,000 DOCX files (1 per employee), containing lists of the employees' personality traits, projects they've worked on, and the teams they've worked with. |
| Transformed data | Automated with VB scripts, with manual review for edge cases |
|---|---|
| Type Conversion & Normalization | - Bonuses and perks were text fields that I had to convert to integers. - Annual performance reviews (written by different managers who listed skills in different orders rated with different adjectives and point scales) were items that I had to normalize for comparison and calculations. - Personality types and team names were items that I had to normalize (spelled/abbreviated the exact same way) for comparison and calculations. |
| Dedupe & Cleaning | - Many employees were reviewed twice or three times requiring me to manually merge their annual reviews into a single review - Many annual reviews were missing fields that needed to be filled in or contain a value. - Many reviews were listed in a different order requiring me to ensure all fields present and in the same order for the relational database loading via queries. |
| Enrichment & Calculation | - I needed to add new fields, such as TotalComp = salary+bonus+PerkValue - I needed to add new values for the Strength/Weakness types and TeamGroup types. - I needed to quantify the Strengths, Weaknesses, PerkValue, and Skills. |
| Standardization | I replaced string names (such as "strong", "very good") with standardized numerical IDs and mapped them to lookup tables. |
| Loaded data | Description |
|---|---|
| Into a MySQL database | After making all the data match the relational database fields and schema, then I was able to load the dataset. |
| Queried by MySQL | Once loaded, I used MySQL queries (and sub-queries) to rearrange the data per the new re-org scenario and used VB code to display the results in a huge matrix that spanned three screens wide and four screens long. |
The result
- Unstructured-to-relational ETL with entity resolution.
- A dynamically rendered VB desktop app that drew a matrix of departments vs. skills, color-coded.
- A VB app that could be re-run with any new set of parameters, which is created from a MySQL database.
To use it:
- From dropdown lists, select a department and then select which new manager or department it would report to.
- Repeat step 1 for every affected department.
Then my application generated a table spanning three screens horizontally and four screens vertically with corresponding squares to summarize how each category of each department would be affected in the proposed reorg, color-coded for items gained/lost.
The outcome
Prototype demonstrated to the VP; shelved when PeopleSoft salary access was revoked.
1,000s of duplicates in prod
Komodo Toys (Hong Kong) – Removed 1,000s of duplicate records while database was in production
| Before | After |
|---|---|
| PROBLEM: There were 10,000's of duplicate addresses in a 100,000+ row SQL database due to being entered slightly differently. 3,000 - 5,000 records are added every day, 24/7. 3 - 5 people always using 1 MySQL database. Hong Kong customers enter addresses differently: - Sometimes adding their complex’s name, or not - Writing the complex's name above or below the street name - Sometimes adding postal code, or not - Sometimes abbreviating different words - District / Dst / Dstct / D. - Complex / Cmplx / Cmpx - Phase / Building / Bldg / Suite / Ste - Sometime using different number formats - Roman (I, II, III, etc.) - English (1, 2, 3, etc.) - Chinese (一，二，三, etc.) | MY SOLUTION: Make every address field a dropdown list in order to make a unique key by concatenating the distinctive fields. Make every address field a dropdown list: - District (Tsimshatsui, Mongkok, etc.) - Complex (The Red Dragon, etc.) - Section (Phase, Building, Bldg., etc.) - Room (Suite, Ste., #, Number, No., etc.) - Number (6, 06, VI, 六) Add a field that is a concatenation of key fields. - District, Street, Room Numbers, etc. Query the new concatenated field for duplicates. - Delete the duplicates. Add a Unique Key restraint on the new key field. - To prevent the entering of duplicate records. |
| RESULTS: Desired duplicate-free list with < 5 minutes of downtime. |
3 dept unified with a DB
Lung Electronics (Hong Kong) – Unified the Marketing, Testing, and Sales departments with a database
| Before | After |
|---|---|
| SITUATION: - 1,000's of products sourced from Taiwan. - 10 PMs tracked their products their own way. - PMs swapped products every month to avoid fraud. - Products needed to be tested for quality. - Sales people sold products in Europe. PROBLEM: PMs and Sales people spent many days each month learning new products that were organized differently. | MY SOLUTION: Combine all products into a unified database to normalize how all products are organized. - Product-switching took only a few minutes. - Easier to track who managed which products when. - Easier to sell since all data was available in real-time. - Faster to track which ones needed testing, which were being tested, which passed, and which failed. |
| RESULTS: When a visiting Oracle engineer saw what I created (especially the table headers change specs to match the selected product), he was stunned and said, "I didn't know Access could do that!" |