Bloating, not boating. Boating is when you get into a boat and sail down the river. This is ‘bloating’!
When I’m demonstrating ways of transforming existing spreadsheets (ones that are currently doing an important job in the workflow of an accounting department), I’m often asked why I don’t use one of the canned features of Excel – particularly Power features such as Power Pivot and the new BI tools.
There’s a reason.
The aim is to transform an existing spreadsheet. And to do that with the minimum of disruption, and the minimum of departure from the users’ comfort zone.
This is easy if you have some code running when a button is clicked (most users don’t get disoriented by seeing a button appear on the sheet!). Connecting to a data source with ADO and running a SQL query is also completely invisible to the user.
So, not only is this solution path (what I would call …) direct, it is also robust – there are few (fewest) moving parts.
Instead, when you let a major Excel feature get in on the act we have a major big thing right in the middle of the process. That would make users run for cover!
And why? What’s the actual advantage of Get and Transform (Power Query) for example when we can connect directly with ADO, and run a simple (they usually are almost always ‘simple’) SQL query?
In the Celebrity Chef example, for example, the key to the solution is that the data is collected outside the spreadsheet. This is necessary so that the Attendees’ menu selections (‘menu’ as in food!) get into our centrally located table. This is not so easy if the table is inside a spreadsheet, though not impossible – but why complicate it?
But, as always, I’m open to anyone convincing me that the added intervention of (1) Get and Transform and (2) a Pivot Table adds anything to the aim of keeping the transformation of a spreadsheet ‘lite on changes to user experience’.
Remember, we’re trying to Re-engineer, not Replace. Any major upheaval is heading towards Replace in the eyes of the users.
Until someone does convince me I shall file it under ‘solutions looking for problems’ when people ask me ‘why don’t you use X, Y or Z?’
See also To a Man With a Hammer. aka. The Philosophy of the Right Tool.
Add comment