I was wrong about spreadsheets (2017) • Hacker News Board

Re: I was wrong about spreadsheets (2017)

#61

Post by kyllo » Fri, Jul 12, 2019, 5:19 AM UTC

mirimir wrote: post #33

halfeatenpie wrote: post #26

I work in critical infrastructure planning. My organization builds software in R, Python, and other programming languages customized for these major organizations. So many critical infrastructures, billions of dollars in planning, and just systems are built out of Excel. It's amazing. You'd assume something that services millions of people a day would have some more sophisticated and customized solution, but you're w…

On the same hardware, with the same table, Excel in Windows 7 does a cross-tab faster than MySQL in Ubuntu does. Indeed, MySQL chokes if there are more than a few hundred columns, but Excel just keeps going. And it uses all CPU cores. Edit: In case anyone is wondering why I did that, I wanted a simple visualization of ping-location results for thousands of IPv4 from several hundred ping-probe locations. So that meant…

The other thing people don't realize about Excel is that it has a compressed, in-memory, columnstore analytics database built into it. It was called Power Pivot then Power Query, not sure if it changed names again, but it's actually a SQL Server Analysis Services Tabular Model, the same thing that's in Power BI as well. It's going to perform much better than a row-oriented RDBMS at aggregating columns because that's all it was designed to do.

Re: I was wrong about spreadsheets (2017)

#62

Post by cosmie » Fri, Jul 12, 2019, 5:19 AM UTC

Someone1234 wrote: post #13

This video by Joel Spolsky taught me a lot of new Excel tricks/QoL improvements: https://www.youtube.com/watch?v=0nbkaYsR94c It is amazing how rich the ecosystem is. I didn't know about pivot tables. Excel has always impressed me, and continues to do so the more I learn.

It's unfortunately not available in the Mac client, but Excel also has an amazingly powerful and useful tool called PowerPivot/Get & Transform[1].

You can connect to external data sources such as CSV files, Excel files, any database with an ODBC connector, APIs, all kinds of neat things. Ingest that data into your Excel file, create an enforce constraints and relationships within your data model, gives you incredibly robust data munging and analysis functionality[2], and then expose all of that as a PivotTable. And the functionality itself bypasses the limitations of Excel such as max data size or computationally inefficient formula implementations, as it uses a separate data storage and computational engine that's a highly compressed columnar data store.

The PowerPivot work is also mostly transferable to PowerBI and Analysis Services. Taken together, you've got all the tools to apply progressive enhancements for end users. Let them create their Excel-based stuff. When it starts to become more mission critical, non-performant, or error-prone, provide them with support to clean it up in the ways that video from Joel Spolsky mentions. When it hits growing pains from that, refactor it further to leverage the built-in data modeling capabilities to enforce some integrity, automation, and potential data volume scaling. And when you hit growing pains with that, or the underly process/usage finally matures to a state of stability, or you need to address security/access/audit-ability concerns, transfer that data model and everything to either PowerBI or an Analysis Server deployment and migrate the management to IT.

I don't see it in practice very often, but it's an incredibly effective and frictionless way to both enable your business users to innovate their work processes via the tools they know, while also providing a non-disruptive way to mitigate your business becoming reliant on apocryphal spreadsheets being passed around to support critical business functions. And by design alleviates many of the causes of "automation" projects failing.

[1] https://support.office.com/en-us/article/get-transform-and-p...

[2] It doesn't rely on the same functions exposed for Excel formulas, but rather a language called M for ETL-like needs and DAX for calculations. https://support.office.com/en-us/article/how-power-query-and...

[3] https://en.wikipedia.org/wiki/Power_Pivot#Product_history_an...

Re: I was wrong about spreadsheets (2017)

#63

Post by chaostheory » Fri, Jul 12, 2019, 5:21 AM UTC

Someone1234 wrote: post #20

taneq wrote: post #8

I feel the same way about Access, too. It doesn't matter whether they're "real programming", it matters that they let real people solve real world problems for themselves, who otherwise would have to pay real programmers real money to develop real applications.

Has Access solved the corruption issue that plagued it for tens of years? While you can connect it to a "real" database engine trivially, a common use-case is via a single MDB file that could become damaged. You could damage the MDB a number of ways including: - Leave Access running before shutdown (or power loss) - Intermittent network outage to the file server - Multiple users trying to access the same MDB (or anti…

Access and FileMaker made sense before the advent of open source, specifically Rails. Django, Postgresql, and MySQL. Both products make little sense now.

Re: I was wrong about spreadsheets (2017)

#64

Post by linker3000 » Fri, Jul 12, 2019, 5:21 AM UTC

That's all very well and good, but can I make a plea for organisations to stop using spreadsheets to produce tabled documents.

This seems to be endemic for information security surveys and would be fine for questionnaires with preset answers, but when a question starts "Describe..." and wants a full description of your software development cycle from an InfoSec perspective, it's painful edit-wise - even if you can copy/paste an answer from a different spreadsheet completed earlier.

Re: I was wrong about spreadsheets (2017)

#65

Post by viraptor » Fri, Jul 12, 2019, 5:22 AM UTC

TBurette wrote: post #35

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

That's what Excel Tables are for. You get a table within your spreadsheet and instead of using cell references like =Sum(C2:C7), you can use structured reference like =SUM(DeptSales[Sales Amount])

Totally. I know about it. The problem is that approximately nobody using excel does. (Yes, finance, data processing, analysts etc. do. Compared to the numbers with excel installed - still ~nobody.)

Re: I was wrong about spreadsheets (2017)

#66

Post by viraptor » Fri, Jul 12, 2019, 5:24 AM UTC

epistasis wrote: post #37

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

I'd recommend watching this YouTube video; it's only one person's take on the right way to use Excel, but Excel has many ways to handle these issues: https://m.youtube.com/watch?v=0nbkaYsR94c

Yup, love it, keep recommending it to people.

Re: I was wrong about spreadsheets (2017)

#67

Post by skrebbel » Fri, Jul 12, 2019, 5:26 AM UTC

viraptor wrote: post #21

My issue with spreadsheets is that they could be improved a lot with minimal changes, not nobody seems to do it in popular office packages. Making them more database-like and making table data first-class (at least you can make named tables in excel on windows) could be used to push people a bit more towards organised data, without changing how anything works. Half of the mess that makes excel hell comes from the fac…

> the fact it's too easy to

I'm not sure you fully appreciate why Excel is so successful.

Re: I was wrong about spreadsheets (2017)

#68

Post by simplecomplex » Fri, Jul 12, 2019, 5:28 AM UTC

I like spreadsheets because they are reliable and logical, unlike whatever trash programmers tend to use. Spreadsheets do what they say they do. Outputs are functions of inputs, like math.

Meanwhile software “engineers” are using programming languages that eschew logic, reject reason, reject mathematics, reject determinism, and do different things each time you run them.

Yeah I’ll take a spreadsheet over whatever ball of confusion software engineers are chasing their tail with.

Re: I was wrong about spreadsheets (2017)

#69

Post by tome » Fri, Jul 12, 2019, 5:31 AM UTC

A bit short on details. What exactly was it about this spreadsheet that made it hard to recreate in a programming language? Personally I find this hard to believe unless one of the key deliverables is "pretty much every parameter to this calculation must be tweakable by the user with the results being visualised instantly".

Re: I was wrong about spreadsheets (2017)

#70

Post by MooMooMilkParty » Fri, Jul 12, 2019, 5:32 AM UTC

sheetjs wrote: post #60

MooMooMilkParty wrote: post #55

Earlier quoted context omitted.

You're blowing my mind here a little bit. I don't use MySQL but do use Excel and the R/Python data science stacks quite a bit. I regularly open data with tens or even hundreds of thousands of columns in the latter by using lazy computations. Is this not the case with database approaches? What kind of hardware are we talking? Presumably Excel has to load everything into memory at once since it's immediately viewable,…

> Presumably Excel has to load everything into memory at once since it's immediately viewable, right? Depends on the file format. An XLS or XLSB file can contain special markers for where each logical row starts, so it can randomly access rows; Both also can persist "calculation chains, which are a simplified dependency graph. The binary formats also store formulae in a parsed representation allowing easy scans to se…

Aha! As far as persisting calculations, this makes sense. I guess in my head I was thinking "pure" data where everything opened was precomputed. Forgive my ignorance about database solutions, but do they not implement something similar?

But now you've got me thinking, it would be nice if libraries like Dask could allow for flagging of symbolic operations like this to be written to disk for quickly saving metadata where intermediate steps don't explicitly need to be saved.