It's too bad that the pivot table is a poor approximation of a true multidimensional spreadsheet, for example Lotus Improv: https://instadeq.com/blog/posts/no-code-history-lotus-improv...
The spreadsheet model is a good example (even if Excel is probably the best thing in Microsoft Office. But it also means that if you can't make Word do a good enough job for desktop publishing you generally have to go to InDesign which is probably way overkill if yoiu're not a publishing professional.
I googled - I couldnt find the article :-)
I think it was in the Guardian or some such.
It blows my mind how much people do in excel. On one hand it’s pretty cool how much it can do but you end up with these monster spreadsheets that should really be their own program of some sorts.
One example I think about a lot (because I use the end product a lot) is Fangraphs’ ZiPs model that predicts baseball stats for upcoming seasons. It’s 20 years old and, from what I’ve been told, a massive Excel book with some VB. The creator was a stats major but they didn’t do much programming in his curriculum so Excel was the option he was most comfortable / productive with. Yet, he’s doing a massive analysis over every player in the league using decades of historical data. The thought of doing that in Excel makes my stomach churn but if it works for him then I guess it’s good enough!
These days I hear a lot about Tableau but the few times I have tried it out I was not a fan. It definitely handles larger datasets that Excel cannot but everything feels hidden behind menus that are unintuitive. In fairness I have never spent the proper time to sit down with it and maybe this kind of workflow works really well for business users.
The Improv article concludes "the key strategy mistake was to try to market Improv to the existing spreadsheet market. Instead, if the product were marketed to a segment where the more structured model was a ‘feature’ not a ‘bug’ would have given Lotus the time to learn and improve and refine the model to a point where it would have satisfied the larger market as well." and Anaplan seems to not have made this mistake. They have carved out a niche in the EPM (Enterprise Performance Management) market.
I've also worked on Anaplan and and its modeling is also much better, so please don't lower it to any version of Improv! It isn't, or at least wasn't a little while back, good at cross-metric ("line item" to them") calculations and presentations, but still easier to get something complex correct.
The real mistake of Improv was that it wasn't 1-2-3 so Lotus didn't know how to narket it or sell it.
At some point you've just got to admit you have the wrong abstraction. Strong Zalgo vibes.
SQL GROUP BY creates summarized horizontal rows.
Instead, pivot tables are more analogous to crosstab queries which creates summarized vertical columns. It "pivots" data groupings by rotating from horizontal to vertical.
The older versions of SQL dialects that didn't have the newer cross tab syntax required convoluted CASE syntax to "simulate" pivot tables which didn't really work that well since one had to know ahead of time -- all the unique values -- to put in each CASE condition branch.
Reshaping data should be a presentation-time decision, not a query-time decision. You have a dataset, a relation in the algebraic sense, and you are choosing to display it in some way: as a table, as a pivoted table, as a pie-chart...
Conflating the two is a consequence of spreadsheets having an unbeatable UX but a terrible data model that lets you treat rows as columns and vice-versa.
You need data points to be on the same row if you want to do arithmetic operations on on different types of values. For example
k + 3.14* number_of_chimneys - 0.13 * age - 1.34*neigbourhood_criminality as house_price
When you potentially billions or trillions of data points, it isn't.
Rows and columns have limits. You need some hard logic for true multidimensional data at scale.
The chain of calculations back to any stored data is the real key, along with what your interactivity needs for multi-user update are.
Agree, but sometimes you just need to shove the results of a SQL query into an Excel file and you don't want to get fancy.
You're either 1) overwriting Sheet B and then using a pivot table in Sheet A to get the final presentation, 2) pivoting in the code/program executing the query before writing to Excel or 3) pivoting in SQL and skipping the code and Excel pivot table altogether.
I run into this a lot with data used for financial modeling, or financial reporting that heavily relies on using dates/categories as column/row headers.
More interestingly, SQL has a hard time with the contextual calculations (actuals come from this table and are aggregated, forecasts come from those 12 tables and are computed by the following formula series, now apply that logic across N variables for actual and forecast) and a really painful time defining computed rows (like fields in some rows being ratios of the same field in other rows).
That's what the modeling tools like Anaplan, Tidemark and some others excel at.
Dimensional models are basically modules (generalised linear spaces), a groupby can do the same things but doesn't really give much useful structure to work with (at best the result is ordet independent, most of the time).
This is also why sums and counts tend to be more useful than averages.
(The other reason for abandoning spreadsheets was performance. I forget how good/bad Improv was in this respect, but I doubt that I would have stuck with spreadsheets since the data sets I was dealing with weren't really appropriate for them.)
In hindsight, this was obviously a UX failure - but I equally blame users who lacked any attention span.
http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdP...
There used to be a version of Quantrix Modeler that was affordable for „home users“, it’s been quite a while though.
Huge asterisk: Better is a very subjective, Ad-hoc data entry is terrible by comparison, but bulk operations are much nicer. The real reason for the change was that I was starting to loathe the spreadsheet data model. I am not fond of how easy it is screw up data in the big bag of cells data model that spreadsheets offer. The core feature I really wanted is row level security, for rows to to stick together.
Oracle has a pretty crazy feature where it handles a query like a temporary spreadsheet where you can enact reactive queries upon [1], that I haven't found in other SQL DBs.
I know you can do that using window functions + recursive CTEs but under this "reactive evaluation of a sub-query" use case they tend to get ugly and incomprehensible real fast.
[1] https://www.oracle.com/webfolder/technetwork/tutorials/obe/d...