I was a big fan of MAD magazine in my school days . My favorite recurring sketch was Spy vs Spy.
This is Cloud vs Cloud.
The two concepts of ‘cloud’ that people tend to confuse when it comes to spreadsheets. I’ll try and demystify it.
WYNTK #1
(That’s … What You Need To Know #1)
Google sheets are spreadsheets in the cloud. By that I mean, we can create a spreadsheet that’s out there on the internet, that can be updated by anyone authorized to open it.
That is one spreadsheet that’s shared by many people.
On the other hand, the idea of cloud I’m referring to as The Cloud Idea (and I’m not sure if this is the best term for it) is where many different spreadsheets are working with the same data. Think of it this way. If we separate the data from a spreadsheet and store it outside the spreadsheet, then other spreadsheets can also connect to (you know what ‘connect to’ means?) and use that same data even though they may be completely different spreadsheets, created for different purposes by different functions or different departments.
WYNTK #2
The cloud concept I refer to is not confined to the internet. The same idea can be applied just on your local machine. The next section explains it.
WYNTK #3
A little history lesson.
In the 1980s when PCs became a thing in the office, we had lots and lots of different ‘software packages’ that was designed for the various aspects of running a business. For example, accounting packages, HR packages, Payroll, for project management, and Line-Of-Business (software specific to the industry – eg. recruitment agencies).
There was one problem.
They had their own data inside them. So if you updated a customer address on the accounting system the sales people’s system didn’t know about it. The HR system and Payroll system should talk to each other, ideally. But they were probably made by different companies and their data was locked in their separate databases.
So the concept of client-server was born. The idea was to separate the data from the software that’s on the user-side, so that the same data is used by the many systems/uses /processes in the organisation.
This not only eliminated duplication of data but also duplication of effort. Whereas (before) processes had to go (via people) from system to system, often on print outs and re-keyed in, now a process is a simpler task of updating the same data by the various people, who could be in different departments. There’s a single version of the data. It is modified as the business processes progress.
You’d hear the term ‘two tier’ also for client-server. Same thing. Or ‘n-tier’ where the logic is also separated for re-use.
WYNTK #4
Now here’s the missing link!
In 1995 Microsoft gave us this client-server capability with Excel and the other office products also. It’s not surprising as this was the big idea at the time. They also made it possible for us to easily connect with non-office products, such as accounting systems and HR and the list I gave above. The marketing buzzword was the Digital Nervous System – and described in Bill Gates’ second book ‘Business @ The Speed of Thought’.
I remember (but I can’t find it on the net) a TV commercial in the late 90s. The screen was split into half. On the right is a car factory production line and cars coming through being painted red by the robots. On the left a customer is ordering a new car, and he decides on blue. The salesman clicks a button on something like a tablet (not sure what, ‘cos this was when tablets only came on prescription!). The nozzle turns blue and the next car is painted blue!
(The cynic in me says the next car is likely to come out a red-ish purple, but hey …)
Bill Gates’ point is, when some data changes in one place it should trigger related changes elsewhere.
And Microsoft made it possible for us to do this with Excel. Say I update a cell on a spreadsheet and it can trigger-off an entry in the accounting system – by producing a journal entry that feeds into the accounting system. (Provided it’s setup to do so, of course)
The piece of tech is ADO, and it came with Excel and all the other Office products. ADO drove a feature in Excel called MS Query, which was a wizard based way of connecting with other systems – but we could also by-pass the wizard completely and roll our own with a mixture of ADO, SQL tied together with VBA. All of which is pretty easy to learn and apply.
WYNTK #4a
Today we have Get and Transform – formerly known as PowerQuery. This is a more powerful version of what MS Query was, but the principle is the same. And the interface lets you configure the imports from a variety of data sources and populate a data model.
With MS Query I could get the data and populate a PivotTable by filling the pivot cache. I learned this from Bob Umlas in a book circa 1997.
Remember though, Get and Transform is ‘get’ only. To have client-server functionality we need to update data on the backend as well as read it. For that we need to roll our own with ADO and SQL, tied together with VBA. I would argue that this is a lot easier than Get and Transform. (You can try my Teach Yourself SQL tool [TBA]. With this you will do Get and Transform on an Excel sheet connecting to an external data source, as well as UPDATE and INSERT and DELETE)
WYNTK #5
Excel and ADO makes it possible for us to send SQL statements to (say) an Access database from Excel. This makes Excel and Access work seamlessly together in a client-server architecture. (I’ve just said that in #4a!)
What this means is, whereas we used to have all our data in the spreadsheet, saved in the spreadsheet file, now we could have the data separated from the spreadsheet so that it lives in a central location, accessible by the bigger process (and people) to which it belongs.
- We no longer need to email spreadsheets to each other.
- We no longer need to link spreadsheets.
- We no longer need to fight over who has a shared spreadsheet open for edit.
- We no longer need to stress over many other things
- We could do this as far back as 1998.
WYNTK #6
So, let’s bring these ideas together.
The cloud idea I describe is, a client-server architecture where we separate the data from the spreadsheets and make them shareable by any user-spreadsheet that needs to access it. This means we can automate processes across many functions, department.
Many things we struggle with when the data is on sheets can be dramatically improved when Excel works with the data in tables outside the spreadsheets. (See WTF to OMG!)
In the simplest form, the central data store can be an Access file on a shared drive. (It is possible to make the central store an Excel file but that is too hard, and unnecessary. It can even be a CSV file!).
The same principle works over the internet, simply by pointing the Excel spreadsheet to a data location on the internet – eg. Amazon Web Services, Microsoft Azure, or the organisation’s own data services.
The observant among you will have figured out already. By ‘the cloud idea’ I mean ‘client-server architecture’. But most people will not know what a client-server architecture is. I know this, I asked my grandmother. She didn’t know.
But most people know ‘Cloud’. But then, when it comes to spreadsheets (no-thanks to Google) when we say ‘spreadsheet and cloud’ in the same sentence most people mean a single spreadsheet that’s shared among many – which is not what I mean (WYNTK #1). But when we use phone apps and subscription services on the internet we understand ‘client-server over the internet’. So ‘the idea of cloud’ is one way to get the meaning across. I still don’t think it’s ideal. I may need to invent a completely new word for this that does not as yet come up in a Google search. (Competition: come up with a suitable word to describe all of the above and win a prize!)
Writing that last paragraph made me realise how sad it is that we’ve had this AMAZINGLY POWERFUL CAPABILITY in Excel for over 20 years …
… and not only is it not known by the very people it can benefit (by boosting their careers like a Saturn V) but there isn’t even a popular term to describe it!
Note
This is just a quick and dirty introduction. I’ll be modifying this article in the coming months to make it clearer. And link the ideas to the demonstrations – live and on video.
The real cool thing is to demonstrate on a live webinar – I mean actually LIVE! It has to be live because we’ll be doing things on our own spreadsheets (in real time) on the webinar and seeing the result as other people on the webinar also participate on their Excel spreadsheets. I don’t think anyone has done this before …
Hey, it may not be Live Aid. But it will be Cool Aid!
Writing this brought back great memories of the pioneering days of Excel development. Already mentioned Bob Umlas, Myrna Larson, Tom Ogilvy (people suspected he’s a team, as his output on the newsgroups was massive. He’s actually a real person. Sent me an email recently), Bill Manville (big PivotTable question-answerer, but I think I overtook him eventually on the Compuserve Excel forum which was all there was in those days), and the late Chip Pearson, and John Green, Stephen Bullen, Rob Bovey, John Walkenbach. It was a different eco-system to today …
Add comment