Scaling our spreadsheet engine from thousands to billions of cells
causal.app
causal.app
I wish I could have met duanec, that guy wrote probably 10% of the code for Excel. Sadly he had retired 2 years before I started working on Excel.
Fun fact: when they were about to ship Excel 1.0, they didn’t know what to name it then so they had an internal poll. The name that garnered the most votes was “Mr Spreadsheet”. Needless to say, marketing vetoed it.
Like HR, they take the fun out of everything.
The older I get the more I hate people who work in marketing :(
♫ Make it a big one, with columns all neat ♫
♫ Give it two tabs, and pivot tables ♫
♫ And column headers I can use as chart labels ♫
Recalc or die
Thanks for sharing :)
Companies like Amazon are still using TM1 for their forecasting. We're trying to build a 21st century version at https://causal.app
Sadly, the only way I’ll be able to use these features is if Microsoft bought them out and integrated them into Excel. It’s ubiquitous at almost every workplace you go to, but the only innovation of the past 20 years seems to be PQ (which is awesome), and xlookup.
Could you name the exact killer features you see in this one, compared to, say, etherpad or https://pad.envs.net ?
LibreOffice is so, so far behind the curve already, and I’d be laughed out of the building.
Only in workplaces which use Windows. But granted, that's the large majority of them.
> so I can use the same workflows and templates
LibreOffice Calc opens Excel files well enough.
> and don’t have to raise a request to install some new app
It's not "some" new app. It has an estimated 200 Million users worldwide, and is probably the most popular FOSS application after browsers.
LivreOffice does not do PowerQuery, and while it ‘opens Excel files well enough’, it probably only has 70% feature parity at most.
They themselves claim that it has ‘tens of millions of users’…
I’m not saying it’s a bad - or new - product. It’s just yet another customer requesting to install something. Very, very few (exactly zero) workplaces I have visited in Oceania and South East Asia are using it.
You must refer to a range of cells for it to work - in Excel this would be for instance =SUM(A:A).
In Google Sheets: generally you write a formula and hit Ctrl+Shift+Enter which will turn it into an array formula.
In Excel there are many more options in how to handle this, one of which is to define a table in Excel (Insert -> Table) and then refer to the table and column (=TableName[Column]).
The obvious way to do it is as follows:
* Setup an excel sheet with "Name" and "Age" in A1 and B1, then put some sample data in. * Highlight the data and go insert -> table and hit 'ok' * Where it says Table Name: put in "Users"
If we want to refer to the array of names in that table, you would now say =Users[Name] in a formula.
As another example, if we wanted to find the average age, we could simply say =Average(Users[Age]) passing in the ages as an array.
If we made another two columns - MathsScore and EnglishScore, we can either automatically create a column adding these up ([@MathsScore]+[@EnglishScore]), or alternatively these ranges can then be used in spill formulas.
https://support.microsoft.com/en-us/office/guidelines-and-ex...
There have actually been a couple of tries at them. The modern “dynamic” array formulas are a little less fiddly.
* Why the design choice to tie the calc engine directly to a DB vs computing dependencies and returning values post-facto? * How does vectorization scale when using "volatiles" like offset-style formulas that may dynamically change the calc graph?
Every time I tried to recreate stuff in Excel in Pandas/Numpy, 1:1 multiplication wasn't the issue, the weird dependencies that prevented using their vectorized capabilities were.
If you want to eventually leverage SIMD and GPGPU computation C++ is almost mandatory, languages like Rust/Nim/Zig are getting there but still behind in many areas.
Is this mostly about GPGPU? I think you can just import and use SIMD intrinsics in Rust. Zig lets you do that and additionally features native vector types whose operations generate the appropriate SIMD instructions.
There's nothing like Highway yet, but for many use cases it seems like a lot more trouble to use Highway than to write several implementations that use intrinsics directly.
Though on second thought: for this particular company’s usecase, since they only need this for the server backend it would be best to stick to a certain hardware configuration and optimize the hell out of it. For example they can just stick to Xeon servers and use raw AVX512 intrinsics which can be particularly fast for number crunching. And since GPUs are incredibly expensive to host (and also has latency costs associated with it), it might be best to just stick with CPU SIMD.
Everything from regulatory document , CRM data and checklists everything. Having billions of row might sound problematic but when a google sheet gets capped or slow they will either create a new sheet on the same file or a new file. A RE firm over its 10 year operations has hundreds and hundreds of spreadsheets. They even have spreadsheets to locate spreadsheets.....
Having everything in one spot is a slightly better thing to do. You can't pry spreadsheets from small businesses no matter what justification you have. From a practical standpoint upping the limits is kind of a good thing.
We usually clean everything up manually and create a proper database that they can query easily. Or they can just tell us what they need through email.
You pretty quickly end up with very large numbers.
Do you have any plans for models which exceed the size that can be reasonably processed on one machine?
You have inspired me to try replacing one of the hash tables in an algorithm I'm working on with an array to see what happens. It won't be exactly the same algorithm afterwards, but possibly the benefits will outweigh the disadvantages.
Indeed, moving memory back and forth from CPUs to GPUs has an overhead. There are ways to mitigate this though! I vaguely remember that one of the patterns in reducing this movement was to keep the data in the GPU as much as possible. I haven't kept up with the latest tech in GPUs off late. When I first played around with CUDA, ArrayFire (arrayfire.com) (no affiliation) was a promising library, and might be a good fit for your GPU prototypes?
It'd be nice to actually see billions of cells in action. Otherwise it's just marketing for their product disguised as a technical blog post. For all we know it doesn't actually work that well in practice.
EDIT: Found it! https://youtu.be/1SNxaJlicEU (slightly NSFW, skip to about 3:30)
I’m also interested in how causal fits into the taxonomy here: https://www.scattered-thoughts.net/writing/an-opinionated-ma...
This is the approach our product Grist (https://www.getgrist.com) takes for its calculation engine.
Keeping track of dependencies slows it down for the initial load, but beyond that, it only needs to calculate what's needed, so most updates are fast. Even though the engine literally evaluates user-defined Python code for every cell.
Cap the amount of cells and essentially partition. Using 2 machines could conceivably double performance after scaling up stops working. So a 1bn row sum could conceivably ask for 5 200m sums then sum those.
Maintain a float32 and float64 version. Compute the float32 first and return to user, then update the result.
You could conceivably run some kind of ml model or even regular logic to predict the API call your formula engine will need to make, eg. If the customer status typing a column of sum range, you could optimistically fetch that part as soon as you see that element.
Optimistically caching is obviously high variance and resource draining.
The 10000iq version is coordination free horizontal scaling with scyladb algorithm
I see this concept in a few applications and wondering I'd need to re-invent my own
In any case, if you sum a list of numbers you have to look at each value. There’s no way around it. You can parallelize, like MapReduce, but the number of items is unchanged. You can preaggregate, but at some point you need partial sums.
I've built my own cheapo one in js to support my financial modeling, but it's not quite general purpose so I get away with a lot. Would be curious how to take it to the next level without reinventing the wheel.
You need to solve recursive dirty updates.