With apologies to George Michael!
So the other day I wrote about Excel and that it has Memory. Like you have on a memory calculator – but a heck of a lot more powerful.
It’s not referred to as ‘memory’. Actually, it’s not referred to as anything. I have not seen this mentioned anywhere in any books or training courses out there.
But memory it is.
For example, (among other places) Excel can get data into its memory from a data model – either from a data model in the workbook, or a data model that’s outside the workbook.
Since Power Pivot became a thing, power users know of the data model that’s inside the workbook. But most people don’t know that, with Excel 5 onwards, we could work with a data model outside the workbook since the mid-1990s. When we used MS Query Excel connected with a data model outside the spreadsheet. When MS Query retrieved the data, Excel held it in its memory. We then got Excel to do something with it. Most of the time we just got it to paste it on a sheet. In most of my work around that time, I got Excel to put it in the Pivot Cache, so that it can be further manipulated interactively in some reporting interface (thank you Bob Umlas). Like how we do a dashboard today.
Today, MS Query (the same idea) is sooped up and sits on the Ribbon as Get and Transform. Essentially, it’s getting data from outside and creating a data model that’s inside the workbook.
But, in an enterprise scenario, having the data model inside the workbook has no advantage over a data model outside the workbook. Workbooks!
And that’s the point. Workbooks – plural. A data model should be accessible from any direction. Any spreadsheet that needs to work with that data. Many spreadsheets.
A question for Microsoft: why have a data model inside the spreadsheet when we always could work with one outside, which is (by far) a more powerful architecture in an organisation?
So, if George Michael suggests going outside, hell yeah! I’m up for it. For data modeling, that is …
(Even if the LAPD are out to get me)
Add comment