By Ben Heinl

For years it has been easy as a software vendor to get nods of agreement when you stand in front of a customer and say something like “Excel is unregulated and leads to many errors that can be costly – and how do you know how to untangle a linked-workbook Excel jungle when the person that built it hands it over to you?” Pretty much everyone in FP&A has been hurt by Excel Hell, some more than others. However, the most common answer from our industry to this problem has for decades been something along the lines of “you need to replace rudimentary spreadsheets with a more modern / sophisticated solution.”

My first response is… “Excel actually is pretty sophisticated – look at how much they can do.” But my second response is, “You don’t need to eradicate spreadsheets – you just need to manage them.” TM1 is often described in a shorthand way as “Excel on steroids” and while I don’t love that description, it ain’t wrong. Even Manny Perez – the inventor of TM1 – often referred to TM1 as a Spreadsheet Management System – very much like an RDBMS (Relational Database Management System), which is a well-established approach to managing relational database tables.

Why is Excel Hell a hard problem? Despite everyone recognising it as a problem for decades and hundreds of software vendors claiming to kill the need for spreadsheets – the problem persists. In fact, it is whispered in the quieter corners of BI (Business Intelligence) software world amongst those that have been around long enough that all the BI industry has really achieved is just a bunch of pretty staging layers for Excel.

So why does the data in a fancy dashboard so often just end up back in Excel? Because so much of working with data is… um… working with data (reconciling , sifting, inquiring, verifying beyond filtered or flat reports) as opposed to just looking at it (the domain of Power BI or other read-only BI tools). “Drilling Down” is not working with your data – it is just looking at it. It is also not “analysis” despite what the BI industry has been trying to sell you for decades.

People work better with their numbers when they can really get their hands on it – do their own calculations, add some of their own data and comments, pivot it, format it with complete freedom, etc. Nothing enables this better than the core idea of spreadsheets – which at its core has the idea of Cell Orientation. Cell Orientation is when a user has access to an array of individual cells and in any one of those cells you can put data (numbers or words!) or a formula – and that formula can refer to any cell in any worksheet in any workbook. This fundamental quality of cell orientation is what makes spreadsheets so unbelievably flexible and powerful for modelling (needed for reporting, planning and analysis) and this is one of the core design principles Manny Perez embedded in his invention of the Functional Database (which he branded as TM1).

And because the Functional Database is cell-oriented, it mirrors the same qualities as Excel – just with the scale, security, performance and multidimensionality of a database – i.e. Excel on steroids. And Manny recognised early on that the ideal user interface for a Functional Database was… a spreadsheet – because you can display any cell in any TM1 cube in any cell in any spreadsheet.

So TM1 did not restrict the user analyst’s freedom like basically every other BI tool did in their limited reports and dashboards. In fact, it enhanced it even further. But! (and this is a big but) – it allows for more law to be put around the freedom because now you can set up automated and regulated data loads into the TM1 database with robust security and enterprise performance tuning and allow hundreds or even thousands of users to use the same model like a spreadsheet at the same time. Manny aptly called this principle Freedom Under the Law. He wrote about it at length – it is worth reading about it directly from the man. You can also hear him talk about it in the documentary about Manny and his invention Beyond the Spreadsheet: The Story of TM1 and in this interview we did with him. 

But the title of this article also mentions “Power BI Purgatory” – the annex that Microsoft built next to Excel Hell. Power BI is now the most popular of all Business Intelligence tools and it also does not solve Excel Hell. In fact from what I can see, it is creating another type of Purgatory as it also creates a proliferation of unregulated, individual data islands of freedom and little law. And read-only table-based Power BI can do so little in compared to Excel, so it is the greatest of staging layers for Excel maybe ever (which is ironic, as sometimes Power BI uses Excel as a source to present it in a dashboard only for a user to dump it back into Excel).

It looks like the industry and FP&A professionals are burying their heads in the sand that this is happening. People mistakenly look to things like Fabric or semantic layers and seem to miss the elegant solution of the Functional Database for some reason. In the end, every organisation needs to find the right balance of freedom under the law. Manny was right… again.