March 11, 2012 at 1:32 am
I am taking a question (whose answer is by subscription only) that is posted at http://www.excelanswers.com/general/Q_25267100-When-is-Excel-not-the-answer.jsp and asking it here. I have same question:
When is a task/project too big for Excel? When does Excel become inadequate? When should something like MS Access be used?
As well as summarised points - I would really like some URLs to whitepapers or articles on the issue - please. Actually, I would like this even more than the points - so I can refer someone to these sources.
-
March 11, 2012 at 1:47 am
what is the task ?
without that information, the only answer I can think of is "it depends"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 1:58 am
Quote from book Pro SQL Server 2008 Analysis Services
by Phili Janus and Guy Fouché
"What do we do when we have 500 products (or more—consider Amazon.com!) and tens of thousands of records? What about millions? We can't expect Microsoft Excel to create pivot tables from all those records for us."
Why can't we expect MS Excel to be the solution? What volume of data is too much for Excel? If depends is the answer, give a few factors it depends on to steer me in some direction, please.
-
March 11, 2012 at 2:03 am
well....one limitation is going to be the max number of rows you can have in an excel worksheet.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 3:11 am
is that 65K rows?
-
March 11, 2012 at 3:22 am
http://lmgtfy.com/?q=how+many+rows+can+I+have+in+an+excel+spreadsheet
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 3:31 am
well, exactly, I googled to get the answer. Can you give a little more lead? For example, I didn't know to ask 'by rows'. So a little more info will help me steer google better.
-
March 11, 2012 at 3:48 am
you posted this question in Analysis service forum...so can I assume you are asking about SSAS v Excel...is this correct?
Have you used SSAS..or for that matter a SQL database?
what experience do you have of Excel?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 7:49 am
From my point of view there's no valid argument for comparing Excel and a relational database (or the baby version of it AKA MS ACCESS):
Excel is a bunch of spreadsheets with cells that can be addressed directly or indirectly. Also, within one spreadsheet it's possible to store multiple "tables" (next to each other or randomly spread across the grid). Within such a "table" there's no such thing like a referential integrity or the like.
I've heard of a few people that managed to (mis-)use Excel to work like a relational database. But for sure it's not an easy task... And not recommended either.
When would I use Excel: if the data I need to deal with are random and there's no need to apply any relational set theory to it.
March 11, 2012 at 11:39 am
:alien:
Excel is used all the time to aggregate data that's been retrieved from a relational database with a query. Now with PowerPivot you don't even need to know how to write a SQL Query (and yes there is referential integrity).
So, I don't understand why it's so difficult, for an experienced analyst to answer the question, 'when is Excel not robust enough to use to aggregate data?".
-
March 11, 2012 at 11:52 am
my apologies, I realize now that my question as first posed, could easily have been interpreted as asking if Excel can be used as a relational database. I whole heartedly agree that not.
I am asking because I want to know when does one decide to move to server side analytical engine and away from Excel.
-
March 11, 2012 at 12:09 pm
aitchkcandoo (3/11/2012)
I am asking because I want to know when does one decide to move to server side analytical engine and away from Excel.
suggestion
provide a scenario where you are unclear whether to use Excel or SSAS.
eg
data...row count
data...column headers
type of analysis you require....
simple sums....YTD/period to date
comparisons ....This YTD v last YTD
and so on
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 12:32 pm
you are not able to tell me in general terms at which volume of data you would advise a client to use Excel PowerPivot to analyze data versus an Analysis Services or even whether this is a key consideration among other named things?
I do not have a scenario. I am asking as a stranger in your land.
-
March 11, 2012 at 12:51 pm
aitchkcandoo (3/11/2012)
you are not able to tell me in general terms at which volume of data you would advise a client to use Excel PowerPivot to analyze data versus an Analysis Services or even whether this is a key consideration among other named things?I do not have a scenario. I am asking as a stranger in your land.
are you expecting an "answer" that provides you with a URL that definitively says
"when data rows are greater than n rows and column headers are greater than n...use x app else use Y app"...cos I still think the answer is "it depends"
kind regards
ps....you seem to have moved the goalposts...you are now referring to PowerPivot.
pps...if I am not mistaken the original question posted on "excelanswers" was posted on 2003-12-14 at 19:07
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
March 11, 2012 at 1:13 pm
What does it depend on? Is it just so many things that it is impossible to categorize and generalize at all? When are you satisfied with Excel and when do you know you must reach for Analysis Services?
PowerPivot or simple Pivot....both are excel. Power is an enhancement that permits relating worksheets along key constraints (among other things).
-
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply