I used to work for a hospital that used this strategy for almost all of its internal reporting (I was contracted to ease the transition to Business Objects); it can get out of hand quickly.
Every morning between 8:45 and 9AM, doctors and administrators would come in, fire up Excel and refresh their data, the slowdown on the network was noticeable (pretty much a 'select *' on a massive de-normalised reporting tables hundreds of times throughout the site).
The flexibility that Excel offered some (not-too-technical) power users in those circumstances was fantastic, though, as much as I hate to admit it.
Yep, it's got it's limits - I would only use this in production with carefully crafted queries to try too counter this problem.
My usual use is against MS CRM data, which puts a 10k line limit on by default. First instinct for many people is to disable the limit, but in reality it's a good fail safe!
By the way: in MS SQL Server, one can also do the reverse: use Excel spreadsheets as the backing store for SQL Server queries (http://msdn.microsoft.com/en-us/library/ms141683.aspx).
It is not easy to set up, but way easier than using csv for getting raw data from Excel into SQL Server, or for running a clean-up query on data in Excel.
Re:Excel as a database
by drakeandrews on Monday April 08, 2013 @09:19PM
(#5515889)
You sir, are a godsend. This is wonderful. Thank you.
Is it read only or can it be read/write?
Then, bingo! You've got real, structured, data available in Excel so you can run Pivot Tables, build charts, and filter data to your hearts content. I use it frequently to build read-only data views for people who want to analyse their data in ways they don't know how to do using more native tools.
This microsoft post is for Office 2007, but applies equally in newer versions: http://office.microsoft.com/en-au/excel-help/connect-to-impo...