Skilling up to code with data

Steph Locke (@SteffLocke)

2017-04-20

Steph Locke

github.com/StephLocke | itsalocke.com | T: SteffLocke

Agenda

Agenda

  • Why code with data
  • What coding languages can be used
  • Coding best practices
  • Source control
  • Testing
  • Continuous testing (& deployment)
  • Coding your infrastructure
  • Wrap up

Why code with data

Have any of these happened to you?

  • Someone changed a database in production and now everything has gone Pete Tong
  • Someone changed a report and now someone says the numbers are wrong, but what was changed?
  • Someone deleted the spreadsheet on the F:/ drive

Have any of these happened to you?

  • All the charts in a Word report are pictures, now you have to go through the spreadsheet again and make them
  • None of the charts in a Word report were pictures, someone played with spreadsheet, and now you have to go through and put it all back the way it was
  • Someone wants some new information in a report and you have to amend the SQL, the pivot charts, the reports, the text

Have any of these happened to you?

  • The auditors want to know how you know you’re right
  • Someone decided to change the company font and all the reports must conform by YESTERDAY
  • Someone didn’t set the range up on Excel correctly and the report has been missing data for months

Positives!

  • Reproducible
  • Controllable
  • Auditable
  • Extendible

What coding languages can be used?

Excel

Not everything has to be coded

  • VBA
  • M & DAX

Databases

  • SQL
  • XML
  • BIML

Analytics

  • Python
  • R
  • JavaScript
  • SQL

Glue

  • PowerShell
  • bash

Coding best practices

Hello world

  • Hello world!
  • KISS
  • DRY
  • Modularise
  • Test, test, test
  • Document as you go

Style

  • White space
  • Comments
  • (Sometimes) bigger is better
  • Consistency

Paradigms

Take the time to learn about how the language works

  • Procedural
  • Declarative
  • Functional
  • Object-Oriented

Source Control

Commandment

Thou shalt source control all the things

Why?

Coz reasons

  • a backup system for your code
  • a means of tracking why you made changes
  • a way of sharing code
  • a system for multiple people to work safely
  • another potential way to deploy code manually
  • options for automated testing and deployment
  • audit trails

Core concepts

  • Centralised vs Distributed
  • Repository
  • Branch
  • Commit

Testing

Commandment

Thou shalt always test before committing

Types of tests

  • Unit
  • Functional
  • User
  • Smoke test
  • Reconciliation

Unit testing

Test discrete bits of code

  • Success
  • Errors
  • Warnings
  • Edge cases
  • Bugs

Continuous testing (& delivery)

Why do it?

  • Avoid works on my machine
  • Verify that everyones changes work
  • More faith in the product
  • Less time spent manually doing it

Core concepts

  • Build
  • Job
  • Results

Tools

  • MS Build
  • Travis CI
  • Bamboo
  • GitLab

Coded infrastructure

Why do it?

  • Avoid infrastructure!
  • Confidence in the environments
  • Time saving

Local

  • Chocolatey
  • PowerShell

General

  • PowerShell
  • Puppet
  • Chef

Containers

  • Contained code
  • Scripted infrastructure
  • Bundled dependencies
  • Lower maintenance than VMs
  • Docker

Conclusion

Why code?

  • To save time
  • To be right and know it
  • To be replacable

What to code?

From databases to reports, you can code it!

How best to do it?

  • KISS
  • Test, test, test

Where to go from here?

  • Get this online & use the various links
  • Get started!
  • Keep in touch: @SteffLocke