ExcelBanter - View Single Post - PLEASE READ IF YOU PROGRAM: Help Continue Visual Basic

Harlan Grove

wrote...
....
i just think that it's insanity to claim that Excel is more powerful
than SQL. I mean-- TSQL is just plain faster and more powerful than
VBA. Any way you look at it.

But it's all referencing. It's not calculations unless they're cached,
in which case they took a lot of time to generate when the cube was
created, and it'd take a significant amount of time to regenerate if a
substantial portion of the cube changed.

Spreadsheets have a poor referencing scheme, but they handle the
calculations better aside from the standard aggregates cached in OLAP
cubes: COUNT, SUM, AVERAGE, MIN, MAX, etc. Nontrivial matrix
calculations, such as inverses and determinants (or
eigenvectors/eigenvalues) are ill suited to relational or
multidimensional referencing. There really are some operations in which
all you need for referencing is simple array indexing, and all the real
work is done in the calculations.

That's one key difference between OLAP and spreadsheets. OLAP is geared
towards data sets that retain most of their data but add to it over
time. Spreadsheets are geared towards using completely different data
sets (entries) from one calculation cycle to the next. Of course OLAP
would be quicker with old data, but generating a spreadsheet would
likely be a lot quicker than generating a new cube. They deal with very
different data domains, so it shouldn't be surprising that they differ
in form. You deal with predominantly static data. I don't. We deal with
different data, so we prefer different tools.