BI is the Anti-Spreadsheet

We have been making real leaps and bounds recently in our internal use of Business Intelligence processes and tools and wanted to share some thoughts on how this has changed my perspective on the use of spreadsheets.

Any of us with years of experience in the computerized corporate world have laid eyes on a few spreadsheets in our time. Personally, I don’t recall a business world that didn’t involve the passing of ‘Excel’ attachments around the team.

Spreadsheets are incredibly powerful and flexible tools. According to Wikipedia they came to the market in the late 1970’s and exploded into our consciousness in the 1980’s. I remember seeing my father tinkering away on them when I was a kid.

It really is hard to imagine life without modern spreadsheets such as Microsoft Excel, LibreOffice Calc or Google Docs. They are free-form and flexible. They can house a complicated budget for a project, give you a sandbox to estimate Financial Instrument values, hold a to-do list with comments or act as a calendar for time planning. What haven’t we seen spreadsheet's used for?

Saying all of that, with so very much data on our radar, I have developed a pet hate for the propagation of spreadsheets. More and more of them ... for everything.

And when it comes to real Business Intelligence (BI) we need tools that allow us to explore data in ways that are not always easy with spreadsheets.

Spreadsheets are not Great Databases

How many times have we heard or said ourselves, “I’ll just check the Spreadsheet.” Where the data we need is kept and maintained in a spreadsheet file somewhere. i.e. We are using a spreadsheet file as a source of truth to house critical data. It’s so very convenient. It's a database.

With cloud-hosted browser based productivity suites like Google Docs or Office365 this approach got even easier as we don’t run the risk of conflicting versions in our inbox after someone emails-and-updates (version conflicts, anyone?).

The thing is folks, spreadsheets are not ideal as databases as a data set grows or things get complicated. They are fragile in the hands of the clumsy and can become quite clunky to use with relatively medium-sized data sets. We have all opened an overloaded spreadsheet and watched it grind away rendering. In my time working at a financial services business I remember having to go and get a coffee while a massive macro-laden Excel spreadsheet opened up and refreshed itself from a number of online data sources. It was great once it was opened but not exactly a stellar user experience. And macro's are a bit of a dirty word in some circles from an IT security point of view.

Also, spreadsheets are often only a point-in-time source of truth. The data can get stale quickly and this isn't always easy to remedy once we've changed things locally with visualisations or extra calculations.

True Excel Wizards are Relatively Rare

There is no question of the power of the modern spreadsheet. They can be scripted and configured to do so much. With functionality only growing. However it is my experience that really competent super-users are few and far between. I certainly do not count myself as one.

When we want to really interrogate data sets we need more than just Pivot Tables, Smart Data Filters and some rudimentary graphs.

I know that it’s possible to run SQL queries on some spreadsheet platforms, but this does seem a little bit wrong. And it’s not something that I any of my team has ever done. Excel wrestlers are not always software developers with SQL capabilities.

Real BI Should be Iterative

Controversial Opinion: while Spreadsheets can be used for BI, when compared to proper BI Tools suites, they are quite constraining and sometimes primitive (that's a statement I'll justify in another post).

Organisational Business Intelligence initiatives (rolling out new software solutions) are not always successful and sometimes they unfortunately crumble in a heap of uselessness for non-technical reasons.

It’s unfortunately common that after an Enterprise BI tool and Data Warehouse product has been purchased and deployed, there is still only very limited ability for team members to easily self-service their data and analytics needs. If changing a BI dashboard requires a three week plan with a Business Analyst, an Enterprise Architect, a Software Developer and a Front End expert then you are not where you need to be. It needs to be relatively easy.

Getting your data into a data warehouse so that you can query it and build meaningful and useful BI dashboards should not be akin to landing a rocket on the Moon. It must be easy and iterative so that we can explore data and drill down to meaningful discoveries.

If you can’t start a work day asking a question of your data and finish the day with a meaningful dashboard or visualisation, then you are not where you need to be.

BI needs to be iterative. Query, review, discover and repeat. That is the aspiration.

Power of Open Source and BI

Catalyst have had really good cadence in our analytics improvements over the last two years since we applied a lean suite of open source tools to see and understand the bottomless pit of data that we have inside our organisation. From AWS billing costs and utilisation, CRM activity all the way to financial and operational data. With the holy grail of being able to (wait for it) cross-reference disparate data sources now something that is quite simple.

Catalyst are hearing more and more similar stories from partners and clients alike. Thankfully, there is considerable adoption of Enterpise Open Source BI tools like Metabase (which we absolutely love) and a number of Extract Transform Load (ETL) like such as pygrametl.

Which open source tools has your team been applying to explore your data?