Favorite Hacks and Tricks

This post is going to suffer from lack of focus at the outset. Sometimes, I discover that a working model, somewhat similar to a life-hack, for doing something technical is both simple and effective. These are also subjects that I have rarely, if ever, seen written up in the various books and wikis and FAQs and advice centers. Other than I think these are interesting and without a solid alternate reference, there isn’t much unifying the list.

Maybe someday a pattern will emerge as I add to the list and I will have material for a book. In the meantime, enjoy — let me know if you like something, or if you think it is covered somewhere else even better than my attempt here.

Business data analysis

Spreadsheet jocks, powerpivot users, SQL report writing.

Date fields are everywhere and most data analysis involves either putting data into bins by day/week/month/year or putting in date order and summarizing by time period. The problem is that sort and summary algorithms work horribly on date fields. All report writers, by default, summarize based on data that is IDENTICAL (address counts for each state, for example) If the reports you need are not pre-summarized by month/year, etc. then it seems like a huge amount of work to divide up.

Solution: create a new column or field and use this formula (pseudo-code):

newfield = STRING(YEAR(mydatefield)) & STRING(MONTH(mydatefield))

It is important that the months all come out 2-digits. Some languages allow for formatting by template — you would use YYYYMM. This new field now SORTS in alphabetical order by year/month and summarizing functions (e.g. Excel Subtotal) will subtotal for each month.

IT Staff for Small Company

Desktop management and repair when short-staffed.

Let’s face it, all IT at small companies is short-staffed. So a user reports a fleeting problem — computer crashes, blue screens, software acting up, possible virus, hard disk is full, or a driver seems to be missing or went bad. Diagnosis time is one of your most precious resources. And many of these problems don’t seem to stay “fixed” when you finally figure out what you think went wrong. Dual problem — you don’t know if your diagnosis was correct and you don’t know if your fix returned your user to full status.

Solution: Do not diagnose. As part of an upgrade cycle for all desktops/laptops in the organization, purchase 1 or 2 extra computers and install them to “company standard” configuration. User reports a problem that can’t be fixed with 15 minutes of review and training… immediately install the new computer, transfer the user’s files (without any personalization, humour apps, music streaming, other crap) and reclaim the non-working computer. Now reformat the old computer to FACTORY settings and install it to company standard. Another user reports a problem — take your refurbished computer and install it, transfer the user’s files and reclaim the bad one. Continue the cycle.

The biggest key here is that every user with a problem receives a “new” computer (new to them). And you have a known-good state that you delivered it. No diagnosing and no guessing. It just turns out that users with factory-installed OS configurations have fewer problems than those that were repaired or the registry was updated or bad software removed. Those techniques work, but they never return the computer to a known state. By purchasing new computers regularly, this method also rotates in newer equipment and can be used to rotate out ancient equipment. Other variations help support power users by replacing their equipment with new, refurb their old (probably powerful) computers and distribute them as “new” to other users. Over time, the entire base of hardware is updated.

Another side benefit is better handling of hardware problems. It turns out hardware problems are really rare if you implement a 4-year upgrade cycle. What looked like a hardware problem often completely goes away if you do factory restore on the computer. And if you compare the price of hardware (written in 2015) to IT staff time, you find that hardware diagnosis and repair is almost never worth it. If a computer will not restore to factory OS, then trash it or part it out.