Excel (PowerPivot) vs MS Access vs SQL Server

[krazykrivda]

I've been searching the web high and low to help me explain, compare, and differentiate between data manipulation using various Microsoft Office tools. I've found many links and articles pre-PowerPivot and PowerView days, but cannot find anything more current.

Please help me understand the MAJOR differences between Excel, Access and SQL Server.

The basics that are known is that Excel is king for analysis of data, and quick-adhoc calculations and visual manipulations.
Access can store and link more data more effectively than Excel (PowerPivot), but has now lost pivot tables etc, but can feed INTO excel.
SQL Server (at least of these 3) is KING when it come to LARGE data sets, tables, connections, relationships, etc.

 

[Smitty]

Welcome to the Board!

Excel is awesome for data analysis, and as a lot of people find out it isn't a data warehouse.
Access is a great small size data warehouse (<2GB) & query tool, but if all you're doing is Pivot Table type analysis off of large data sets then PowerPivot makes it pretty much obsolete. And don't forget that PowerPivot is built on SQL Server anyway, and you do have relational abilities, which is one of the coolest things about it. I've got views that can update in PowerPivot 100x faster than queries in Access, so there's a lot to be said for it in the right role.
SQL Server is the ultimate as far as databases go, but overkill if you can accomplish the same thing in Access. And SQL Server is much harder to learn than Access.

It essentially comes down to what you need to do. I have a large Access database that we migrated to SQL Server this year, but we still use Access for the front-end and Excel/PowerPivot for data analysis, so they do all play well with each other.

 

[krazykrivda]

Welcome to the Board!
...
It essentially comes down to what you need to do. I have a large Access database that we migrated to SQL Server this year, but we still use Access for the front-end and Excel/PowerPivot for data analysis, so they do all play well with each other.

Thanks for your input. I've become pretty well versed in excel, and have use Access as a stepping stone when data became to big for excel. I've progressed to SQL Server in the last year or so maintaining 30-40 tables and building various scripts, queries, and the like to keep the data as accurate and updated as possible. SQL Server is maintaining month over month and year over year data in many different aspect for me at the moment.

I loosely am existing in all 3 platforms, depending on the task. Leveraging SQL Server heavily for large datasets, data pulls, and general data manipulation; wall this meanwhile still utilizing Excel to do the heavy lifting in terms of analysis.

Not being as familiar with PowerPivot, and the capabilities, especially in contrast with Access now, I'm unsure which medium to best use.

Any general rules of thumb or use cases to differentiate is helpful, but thank you very much for your input

 

[Kyle123]

XPosted here Excel (PowerPivot) vs MS Access vs SQL Server