Data & Coding

SQL

  • I keep some SQL scripts around just to help remember some of the SQL I’ve done over the years – PIVOT tables can be so fun – and there’s lots of different ways to roll around in the data like a dog: https://github.com/gitberry/SQLSnippets
  • Here’s a project to show off MS Azure ML techniques I learned. https://www.youtube.com/watch?v=mn2Cy-0oiH8 (Heads up – I was nervous and nodded my head a lot – kinda distracting to watch years later – and I think the cool Microsoft host vblogger could hardly keep from laughing out loud…) Lauren Tran – the Microsoft Data Scientist and I collaborated on getting the right feature set to complement her ML algorithms (https://github.com/laurentran/sequence-labeling) – I was particularly proud of the security and privacy approaches we took to this project – since it was a proof of concept and never did get implemented in production for some very good reasons. I also earned my stripes on PIVOT and optimization for T-SQL – I even put a simple PIVOT demo on my github account: https://github.com/gitberry/SQLSnippets/blob/main/SQLPivotDemo01.sql
  • Have you ever have to keep close tabs on the structures in your database? Need to automate it a bit and the cool RedGate utilities you really want to use aren’t in the budget for this single thing? Don’t worry be happy – take this code that generates a simple hash of a database structure to do a quick compare. Verbose options area great for data dictionaries for the data surfers in your org… https://github.com/gitberry/sqlHash

Where’s the line between Data and GIS? Trick Question! Browse a couple of utilities I threw together to get some Geomatic data from one place to another:

  • https://github.com/gitberry/LatLonDMS2Dec – turn LatLon values with degrees, minutes, seconds – into a degrees decimal… which is what most GIS systems require now.. But every once in a while a retro app dumps you a bunch of stuff you need to convert before slipping into the next system.
  • https://github.com/gitberry/KMLPoints – ever need to take that google maps KML file and get tabular data for something else?? This little chunk of code is something I threw together for that – and of course – when I got coding I thought of all kinds of cool things to do to make it even more useful the next time…

Coding

The term “full-stack” has very specific definitions in certain circles. Although data – mostly SQL – is my primary passion I code whatever/whenever/however the task requires. So if I need to write/fix something in VB, javascript, Vuejs, SQL, C# or python – done! I don’t think of HTML as a “language” – but it’s like the air – just everywhere. So whether a full-fledged system is necesary (ie SMS, emails, MFA, night batching data and full fledged workflow UI) or a utility like SSO, LTE for special situation or one of the many many API’s – wether in the older XML/SOAP, home rolled specs or JSON/REST – I just research, collaborate – and do it.

Development & Data: a few standouts

Delinquent Loan Management & Notification System:

This system was the product of collaboration with product owners in the Loss Prevention department of Conexus – a collaborative and iterative design process that I had the joy of implementing. The (OLTP) database design incorporated the ability to store, log and present information that had multiple OLAP data sources. This system had automation via MS SQL and IIS to daily import sophisticated delinquency information, and API’s and services that managed the automatic SMS messaging of daily templated notifications – as well as the managing of SMS messaging relating to the delinquencies. The users needed to perform sophisticated data and messaging tasks – and with a simple interface that they had envisioned, designed by myself, a business analyst and heavy consultation with the users, we were able to reduce their workload significantly. The complexity had been severely underestimated initially, but via iterations in design, the owners/users were able to instruct the design process to get what they wanted within a reasonable development time-frame. What we lost in speed (it was deployed six months later than original estimates) we gained in user satisfaction, system stability and staff efficiency.

Duplicate Detection System:

A unique data integrity challenge required a data process to identify potential duplicates which needed a drop-dead simple UI experience for backend staff to quickly visually identify false positives and process true positives. The data challenges were solved with daily SQL jobs, and automated email notification in conjunction with a specialized user interface to reduce the complication to as simple as possible for the users. Over the years this app has saved credit union members hundreds of thousands of dollars and avoided untold headaches and hours of work for members and staff – members and other staff are rarely aware of this tool that lifts so much for so many by just a few quiet staff in a back-office department. Mission accomplished!