5 ms·

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-c

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-spreadsheets-done-right-1991/ https://instadeq.com/blog/posts/no-code-history-lotus-improv...

qsort 3y ago
Which is itself the poor man's groupby. At some point you've just got to admit you have the wrong abstraction. Strong Zalgo vibes.
- airstrike 3y ago
  Or at some point you realize we need some new abstraction ;-)
- jasode 3y ago
  >Which is itself the poor man's groupby. 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.
  - qsort 3y ago
    If you are grouping over them, they shouldn't be columns in the first place, the original SQL is "right". Rows and columns aren't symmetric! 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.
    - fifilura 3y ago
      There are real use cases for pivoting data in SQL not only for presentation. Tables can be too "long". 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
    - toyg 3y ago
      > Reshaping data should be a presentation-time decision 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.
      - quantified 3y ago
        For business analyst purposes, the reshape should be a display-time activity. Finance models may start with billions but tend to present a manageable quantity to the user. The chain of calculations back to any stored data is the real key, along with what your interactivity needs for multi-user update are.
    - tomnipotent 3y ago
      > should be a presentation-time decision, not a query-time decision 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.
    - mr_toad 3y ago
      Cross tabulations are a fundamental part of statistics, business and scientific analysis. That they aren’t supported by the relational model just means that an RDBMS is not a full fledged analytical tool.
- contravariant 3y ago
  Goupby may be the higher abstraction, but it's not necessarily better. 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.
- quantified 3y ago
  They're different. Group by only gets you so far. Actual modeling of business scenarios deals with a lot of irregularity and the specialized spreadsheet-like tools were mostly much much better at expressing this.
vondur 3y ago
The linked article specifically mentions Lotus Improv as the app that had this functionality in it. Interesting how Steve Jobs was able to get Lotus to make it a NeXT exclusive app initially.
RGamma 3y ago
Sounds a lot like what you can do with tabular model (PowerPivot) and DAX (measures) in Excel now.
Brian_K_White 3y ago
The article says Improv is where pivot tables started.
dannyobrien 3y ago
I remember going to the UK launch of Lotus Improv as a newbie journalist. It was really notable how both how flashy and professional it was compared to other products (in retrospect, I'm presuming that was Jobs' influence). Nonetheless, I think they really struggled to explain pivot tables, and why, in itself, that feature was sufficient to move to a new application and a new hardware platform.
- fractallyte 3y ago
  Improv included an excellent animated presentation which explained its features perfectly. The problem was to get new users to sit their asses down and actually watch it. In hindsight, this was obviously a UX failure - but I equally blame users who lacked any attention span.
fiddlerwoaroof 3y ago
I believe this is a sort of descendant of Improv: https://quantrix.com/products/quantrix-modeler/ https://quantrix.com/products/quantrix-modeler/
- steve1977 3y ago
  It is, and like Improv, has its roots in NeXSTSTEP. http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdPartySoftware/PersonalProductivity/SpreadSheet-Database/Quantrix/Quantrix.html 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.
ghaff 3y ago
One of the "crimes" (he types hyperbolically) of Microsoft Office is that is basically sucked all the air out of the room for anything else in the non-graphical artist office productivity area that wasn't Office or a pretty direct knock-off. 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.
- znpy 3y ago
  To be honest i saw a teacher in high school working on his own textbook)the second revision of an already published textbook) in word and while it had the classic wysiwyg experience, it was typographically okay, almost ready to be printed.
  - ghaff 3y ago
    Word, or even something like Google Docs which lacks some features in areas like Section numbering, isn't terrible. I published a book using Google Docs and basically decided anything it couldn't do I didn't need or could handle manually. But, if I were actually come up with a wish list for a low-end publishing platform it would probably look a bit different than Word.
- firecall 3y ago
  A widely shared article from a few years back claimed that Excel was the world's most popular design tool. I googled - I couldnt find the article :-) I think it was in the Guardian or some such.
  - tnecniv 3y ago
    I’d believe it if you count every random plot people make as “design.” 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!
II2II 3y ago
I have not used spreadsheets very often since the mid-1990's. One of the reasons: I was excited by the potential of Improv, but disappointed when I realized that it had no future. Little did I realize that pivot tables were a different take on the concept! (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.)
huhtenberg 3y ago
Half of the QZ article is literally about Salas and Improv.
re5i5tor 3y ago
On NeXT 2 years before Windows
getravi 3y ago
Never knew Improv existed. There are tools that are similar to the vision of Improv. I use Anaplan at work everyday and it is exactly what a modern cloud based SaaS version of Improv would feel like. It is a multi billion dollar company and it worked because it did not go after the spreadsheet space but played along nicely with it. 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.
- quantified 3y ago
  Improv's model was poor though, it was based on cell positions and it was easy to double-count. Basically was bad at various semantic aspects. I worked on a small conpetitor back in the day and we competed on modelling, there were a number of others too. 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.
kagakuninja 3y ago
You may want to read about DataPivot, developed by Brio Technology during the same period as Lotus. Brio had a patent on the pivot data aggregation algorithm, I'm not sure how Lotus's pivot table worked... https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology
somat 3y ago
I use postgres in the role of a better* spreadsheet. 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.
- ElectricalUnion 3y ago
  The missing part "better spreadsheet" for me is some standard, simplified reactive evaluation declaration syntax. I'm probably wrong, of course. 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/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm https://www.oracle.com/webfolder/technetwork/tutorials/obe/d...

qsort 3y ago

Which is itself the poor man's groupby. At some point you've just got to admit you have the wrong abstraction. Strong Zalgo vibes.

airstrike 3y ago
Or at some point you realize we need some new abstraction ;-)
jasode 3y ago
>Which is itself the poor man's groupby. 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.
- qsort 3y ago
  If you are grouping over them, they shouldn't be columns in the first place, the original SQL is "right". Rows and columns aren't symmetric! 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.
  - fifilura 3y ago
    There are real use cases for pivoting data in SQL not only for presentation. Tables can be too "long". 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
  - toyg 3y ago
    > Reshaping data should be a presentation-time decision 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.
    - quantified 3y ago
      For business analyst purposes, the reshape should be a display-time activity. Finance models may start with billions but tend to present a manageable quantity to the user. The chain of calculations back to any stored data is the real key, along with what your interactivity needs for multi-user update are.
  - tomnipotent 3y ago
    > should be a presentation-time decision, not a query-time decision 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.
  - mr_toad 3y ago
    Cross tabulations are a fundamental part of statistics, business and scientific analysis. That they aren’t supported by the relational model just means that an RDBMS is not a full fledged analytical tool.
contravariant 3y ago
Goupby may be the higher abstraction, but it's not necessarily better. 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.
quantified 3y ago
They're different. Group by only gets you so far. Actual modeling of business scenarios deals with a lot of irregularity and the specialized spreadsheet-like tools were mostly much much better at expressing this.

vondur 3y ago

The linked article specifically mentions Lotus Improv as the app that had this functionality in it. Interesting how Steve Jobs was able to get Lotus to make it a NeXT exclusive app initially.

RGamma 3y ago

Sounds a lot like what you can do with tabular model (PowerPivot) and DAX (measures) in Excel now.

Brian_K_White 3y ago

The article says Improv is where pivot tables started.

dannyobrien 3y ago

I remember going to the UK launch of Lotus Improv as a newbie journalist. It was really notable how both how flashy and professional it was compared to other products (in retrospect, I'm presuming that was Jobs' influence). Nonetheless, I think they really struggled to explain pivot tables, and why, in itself, that feature was sufficient to move to a new application and a new hardware platform.

fractallyte 3y ago
Improv included an excellent animated presentation which explained its features perfectly. The problem was to get new users to sit their asses down and actually watch it. In hindsight, this was obviously a UX failure - but I equally blame users who lacked any attention span.

fiddlerwoaroof 3y ago

I believe this is a sort of descendant of Improv: https://quantrix.com/products/quantrix-modeler/ https://quantrix.com/products/quantrix-modeler/

steve1977 3y ago
It is, and like Improv, has its roots in NeXSTSTEP. http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdPartySoftware/PersonalProductivity/SpreadSheet-Database/Quantrix/Quantrix.html 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.

ghaff 3y ago

One of the "crimes" (he types hyperbolically) of Microsoft Office is that is basically sucked all the air out of the room for anything else in the non-graphical artist office productivity area that wasn't Office or a pretty direct knock-off. 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.

znpy 3y ago
To be honest i saw a teacher in high school working on his own textbook)the second revision of an already published textbook) in word and while it had the classic wysiwyg experience, it was typographically okay, almost ready to be printed.
- ghaff 3y ago
  Word, or even something like Google Docs which lacks some features in areas like Section numbering, isn't terrible. I published a book using Google Docs and basically decided anything it couldn't do I didn't need or could handle manually. But, if I were actually come up with a wish list for a low-end publishing platform it would probably look a bit different than Word.
firecall 3y ago
A widely shared article from a few years back claimed that Excel was the world's most popular design tool. I googled - I couldnt find the article :-) I think it was in the Guardian or some such.
- tnecniv 3y ago
  I’d believe it if you count every random plot people make as “design.” 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!

II2II 3y ago

I have not used spreadsheets very often since the mid-1990's. One of the reasons: I was excited by the potential of Improv, but disappointed when I realized that it had no future. Little did I realize that pivot tables were a different take on the concept! (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.)

huhtenberg 3y ago

Half of the QZ article is literally about Salas and Improv.

re5i5tor 3y ago

On NeXT 2 years before Windows

getravi 3y ago

Never knew Improv existed. There are tools that are similar to the vision of Improv. I use Anaplan at work everyday and it is exactly what a modern cloud based SaaS version of Improv would feel like. It is a multi billion dollar company and it worked because it did not go after the spreadsheet space but played along nicely with it. 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.

quantified 3y ago
Improv's model was poor though, it was based on cell positions and it was easy to double-count. Basically was bad at various semantic aspects. I worked on a small conpetitor back in the day and we competed on modelling, there were a number of others too. 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.

kagakuninja 3y ago

You may want to read about DataPivot, developed by Brio Technology during the same period as Lotus. Brio had a patent on the pivot data aggregation algorithm, I'm not sure how Lotus's pivot table worked... https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology

somat 3y ago

I use postgres in the role of a better* spreadsheet. 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.

ElectricalUnion 3y ago
The missing part "better spreadsheet" for me is some standard, simplified reactive evaluation declaration syntax. I'm probably wrong, of course. 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/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm https://www.oracle.com/webfolder/technetwork/tutorials/obe/d...