Re:Excel Never Dies
by stilisstuk on Monday March 08, 2021 @02:55PM
(#26390677)
Yes pp can handle data. But vba can not. And most spreadsheets contain vba for reporting and magic interfaces for managers. Slow and unmaintainable.
Any day: rmarkdown and csv
Re:Excel Never Dies
by texasbigdata on Monday March 08, 2021 @01:03PM
(#26389172)
Big fan of this, but powerpivot sits on vertipaq (I believe) which is an in memory columnar DB or sorts (apologies if that’s incorrect). So at this point you’re getting awfully close to direct querying (another msft feature) which while analogous resembles more traditional db/client if you squint hard enough.
But yes, big fan of vertipaq which I believe also powers PowerBI.
Just to clarify.. Yes, vertipaq is the tech behind power pivot, power bi and sql server analysis services (in tabular mode) and the same column-oriented storage is also used in sql server. Excel generates queries against the data stored in a vertipaq model. You cannot write normal excel formulas on top of it and you have to use DAX (a unique to msft language, which is the replacement for MDX) instead, which is pretty much a no-go for anyone but well-trained power users.
I dispute this. Yes, the normal spreadsheet view of excel will buckle under 1M rows, but excel has another feature called "Power Pivot" that is backed by an embedded database and scales into the high millions at least.
I've personally used excel on a dataset of 18M rows and PowerPivot handled it just fine.
[0] https://support.office.com/client/Data-Model-specification-a...
[1] https://support.office.com/client/power-pivot-powerful-data-...